Jak zsumować zakres komórek za pomocą wartości N / A?

11

Mam zakres i muszę go zsumować za pomocą funkcji SUMA, ale wynik jest N / A, jeśli jest jakaś wartość N / A. Jak ustawić funkcję SUM, aby traktowała wartość N / A jako wartość 0? Proszę pomóż!

microsoft-excel-2010

— Nam G VU
źródło

16

Użyj formuły tablicowej

=SUM(IF(ISNA(A1:A4),0,A1:A4))

Naciśnij Ctrl+ Shift+ Enterpo wprowadzeniu formuły

Uwaga: Zamień na A1:A4swój zakres

— wilson
źródło

1

+1: Ładne ulepszenie mojego rozwiązania - nie trzeba modyfikować oryginalnych danych.

— Mike Fitzpatrick

1

Miły. Nigdy nie myślałem o użyciu formuły tablicowej. Byłem zadowolony z używania = SUMIF (A1: A4, „<> # N / A”), dopóki nie odkryłem, że się psuje, jeśli arkusz kalkulacyjny zostanie otwarty w kopii programu Excel zlokalizowanej dla innego języka! Niestety zamiana „<> # N / A” na „ISNA ()” nie działa, ale formuła tablicowa jest dobrym rozwiązaniem wielojęzycznym. Niestety, nie jest to odpowiednie podejście do tego samego wyzwania z AVERAGEIF ()… :-(

— Joe Carroll

@JoeCarroll, możesz użyć=AVERAGE(IF(ISNA(A1:A4),"",A1:A4))

— wilson

Dzięki @wilson! Kiedy zastanawiałem się przez chwilę, nie pomyślałem o użyciu „” zamiast zera, głupio :-) Używanie zera wpływa oczywiście na wielkość próbki, co doprowadziło mnie do odrzucenia rozwiązania, ale puste komórki są po prostu ignorowane.

— Joe Carroll

Nie działa z magicznym Cr @ pXcel 2013, ale odpowiedź poniżej (= agregacja) działa jak urok!

5

Najłatwiejszy sposób:

Użyj SUMIFwartości> 0.

Tak więc składnia tego jest następująca

=SUMIF(A1:B2, "> 0")

Otrzymasz ten sam wynik, ponieważ zignoruje on wszelkie wartości nienumeryczne lub 0.

— JNK
źródło

2

Twoja sugestia nie będzie działać z liczbami ujemnymi. W każdym razie dzięki.

— Nam G VU

1

Można zrobić, =SUMIF(A1:B2, ">=0") + SUMIF(A1:B2, "<0")aby dołączyć liczby ujemne. Podejrzewam, że byłoby to szybsze niż zaakceptowane rozwiązanie z formułą tablicową, jeśli wydajność ma znaczenie.

— kizzx2

3

W programie Excel jest nowa funkcja, która doda wszystkie wartości dodatnie lub ujemne, ignorując NA.

=aggregate(9,6, range of your data)

9 służy do sumowania, a 6 do ignorowania wszystkich NA. Istnieją również inne opcje oprócz dodawania. Na przykład możesz wykonać produkt, odchylenie standardowe i tak dalej.

Więcej informacji na temat AGGREGATE tutaj .

— Muhammad Wajid Raza
źródło

eleganckie rozwiązanie dla programu Excel 2010 i później :)

— wilson

0

Zamiast tego użyj SUBTOTAL:

=SUBTOTAL(9,A1:A50)

— wariant
źródło

Próbuję, sumtotalale mam ten sam problem, co zsum

— fguillen

0

Możesz przekonwertować # N / A na zera w swoim zakresie źródłowym, a następnie użyć normalnej funkcji SUMA:

= JEŻELI (ISNA (OriginalFormula), 0, OriginalFormula)

— Mike Fitzpatrick
źródło

2

Od pakietu Office 2007 pojawiła się nowa funkcja, IFERRORktóra może wyglądać następująco: =IFERROR(OriginalFormula,0)znacznie ładniejsza.

— Michael

@agentmike, zgodziłem się, że często go używam.

— Mike Fitzpatrick

0

Jeśli istnieją formuły, które skopiowałbym jako liczby, zamień # nie dotyczy w kolumnie na 0, a następnie zsumuj jak zwykle

— cynthia
źródło

-1

W ten sposób wydaje się działać:

SUMIF(range;"<>#N/A";range)

Nie tak solidny, ale skuteczny!

— Bruno
źródło