Excel: „Sumifs” ignoruje # n / a
Próbuję zsumować niektóre wartości (zarówno dodatnie, jak i ujemne) w kolumnie, ale nie sumują się, ponieważ istnieją wartości nd. Czy można to obejść? Zobacz poniższy przykład:
Każde miasto ma niższe obszary o dochodach, przestępczości i bezrobociu (dodatnie lub ujemne). Moim celem jest uzyskanie wyników w skali całego miasta w zakresie dochodów, przestępczości i bezrobocia poprzez zsumowanie niższych wyników dla obszarów. Używam `` sumifs '' w G2 jak w = SUMIFS (C$2:C$10,$A$2:$A$10, $ A2), a następnie przeciągając go do I10. W tym przykładzie zabawki jest tylko 10 wierszy, ale moje dane mają 1 milion wierszy, więc tak naprawdę nie mogę przeciągać. Wszelkie sugestie również będą pomocne!
Ale co najważniejsze, moim problemem jest to, że nie mogę użyć „sumifs” ze względu na wartości # N / A. Chcę ich zignorować.
Lub po prostu zastąpienie wszystkich wartości # N / A wartością 0 w 1 milionie wierszy również byłoby opcją.
ps Zrobiłem kilka badań dotyczących poprzednich podobnych pytań, ale wydaje się, że używają one różnych formuł „sumifs” ...
Odpowiedzi
Używanie funkcji SUMA.WARUNKÓW z <0 ORAZ> 0
Możesz użyć tego:
=SUMIFS(C$2:C$10,$A$2:$A$10,$A2,C$2:C$10,"<0")+SUMIFS(C$2:C$10,$A$2:$A$10,$A2,C$2:C$10,">0")
Unika zera (co w tym przypadku nie ma znaczenia). Unika również komórek, które zawierają wartości, które nie są liczbami, takie jak NA, co często zapobiega sumowaniu (unikanie ich rozwiązuje problem w tym przypadku).
Jeśli dobrze to rozumiem, chcesz otrzymać sumę zarówno liczb dodatnich, jak i ujemnych (tj. Ich istniejących wartości, a nie wartości bezwzględnych), jednak obecność pozycji NA uniemożliwia obecnie stosowanie twojego istniejącego podejścia.
Jeśli to nie jest poprawne, proszę o poradę.
KORZYSTAJĄC Z INTERIM LOOKUP, A NASTĘPNIE SUMUJ Z <0 ORAZ> 0
Alternatywnie można wstawić kolumnę odnośnika, która łączy wartości do oceny, a następnie użyć funkcji SUMA.JEŻELI.
Na przykład nowa kolumna J:
=$A2&C2
(zwracając uwagę na $ za A, jak również na brak $ za C)
Następnie wypełnij J1 po prawej stronie do K1 i L1 Następnie w M1:
=SUMIF(J$2:J$10,"<0",C$2:C$10)+SUMIF(J$2:J$10,">0",C$2:C$10)
Następnie wypełnij w prawo do N1 i O1
Potencjalny problem z tym podejściem polega na tym, że jeśli wypełnisz miliony wierszy, arkusz kalkulacyjny może znacznie spowolnić.
WYMIANA NA
Zastąpienie NA wartością 0 również zadziała, ale możesz nie chcieć utracić rozróżnienia między „0” a „niewyświetlane w danych źródłowych”.
Opublikuj swoją istniejącą formułę dla wartości NA, jeśli chcesz podążać tą ścieżką. Mogą być przekształcalne w coś zamiast NA, co jest znaczące i nie zapobiega sumowaniu.
Często preferowane jest wyszukiwanie danych, aby złapać pułapkę na możliwość wystąpienia NA i zwrócić jakiś inny, bardziej znaczący (lub w tym przypadku, bardziej przyjazny dla sumy) wynik.
Być może najłatwiejszym sposobem jest użycie "<>#N/A"kryteriów, które zignorują NA w kolumnie sumy. Możesz dodać dodatkowe kryteria dla innych kolumn warunków, jeśli kolidują one z numerycznymi sprawdzeniami warunków.=SUMIFS(C$2:C$10,$A$2:$A$10,$A2, C$2:C$10, "<>#N/A")
Dzięki: https://www.mrexcel.com/board/threads/sumifs-while-ignoring-n-a.1042338/ za napiwek.