Pobierz przykładowy skoroszyt
Ten samouczek pokaże, jak używać funkcji SUMIFS do sumowania danych odpowiadających pustym lub pustym komórkom w Excelu i Arkuszach Google.
Suma, jeśli puste
Najpierw pokażemy, jak sumować wiersze z pustymi komórkami.
Funkcja SUMIFS sumuje dane spełniające określone kryteria.
Możemy użyć funkcji SUMIFS, aby zsumować wszystko Wyniki dla Gracze bez nazw w poniższym przykładzie.
| 1 | =SUMA.WARUNKI(C3:C8;B3:B8;"") |
Używamy podwójnych cudzysłowów („”) do reprezentowania pustej komórki w programie Excel. Nasz przykład ignoruje Gracze A, B, C i D i sumuje wyniki dla nieznanych Gracze.
Traktowanie przestrzeni jako pustych komórek - z kolumną pomocnika
Musisz zachować ostrożność podczas interakcji z pustymi komórkami w programie Excel. Komórki mogą wydawać się puste, ale program Excel nie będzie ich traktować jako puste. Może się tak zdarzyć, jeśli komórka zawiera spacje, znaki końca linii lub inne niewidoczne znaki. Jest to częsty problem podczas importowania danych do programu Excel z innych źródeł.
Jeśli musimy traktować komórki zawierające tylko spacje w taki sam sposób, jak gdyby były puste, formuła z poprzedniego przykładu nie zadziała. Zwróć uwagę, że formuła SUMIFS nie uważa komórki B9 poniżej („”) za pustą:
Aby traktować komórkę zawierającą tylko spacje tak, jakby była pustą komórką, możemy dodać kolumnę pomocniczą z funkcją TRIM, aby usunąć dodatkowe spacje z wartości każdej komórki:
| 1 | =PRZYC(B3) |
Stosujemy funkcję SUMIFS do kolumny pomocniczej, która teraz dokładnie oblicza sumę.
| 1 | =SUMA.WARUNKI(E3:E9;D3:D9;"") |
Kolumna pomocnicza jest łatwa do utworzenia i czytelna, ale możesz chcieć mieć jedną formułę do wykonania zadania. Zostało to omówione w następnej sekcji.
Traktowanie przestrzeni jako pustych komórek - bez kolumny pomocniczej
Jeśli kolumna pomocnicza nie jest odpowiednia dla Twoich potrzeb, możesz użyć funkcji SUMPRODUCT w połączeniu z funkcjami LEN i TRIM, aby zsumować puste wiersze.
| 1 | =PROD.SUMA(--(DŁ(PRZYM(B3:B9))=0);D3:D9) |
W tym przykładzie używamy funkcji SUMPRODUCT do wykonania skomplikowanego obliczenia „suma jeśli”. Przejdźmy przez formułę.
Oto nasza ostateczna formuła:
| 1 | =PROD.SUMA(--(DŁ(PRZYM(B3:B9))=0);D3:D9) |
Najpierw funkcja SUMPRODUCT wyświetla tablicę wartości z dwóch zakresów komórek:
| 1 | =SUMPRODUCT(--(DŁUG(PRZYC({"A"; "B"; ""; "C"; ""; "XX"; " "}))=0),{25; 10; 15; 5 ; 8; 17; 50) |
Następnie funkcja TRIM usuwa spacje wiodące i końcowe z Gracz nazwy:
| 1 | =SUMPRODUCT(--(LEN({"A"; "B"; ""; "C"; ""; "XX"; ""})=0),{25; 10; 15; 5; 8; 17; 50) |
Funkcja LEN oblicza długości przyciętego Gracz nazwy:
| 1 | =PROD.SUMA(--({1; 1; 0; 1; 0; 2; 0}=0);{25; 10; 15; 5; 8; 17; 50) |
Z testem logicznym (=0), każdy przycięty Gracz nazwy z 0 znakami są zmieniane na TRUE:
| 1 | =PRODUKTSUMA(--({FAŁSZ; FAŁSZ; PRAWDA; FAŁSZ; PRAWDA; FAŁSZ; PRAWDA}),{25; 10; 15; 5; 8; 17; 50) |
Następnie podwójne myślniki (-) zamieniają wartości TRUE i FALSE na 1 i 0:
| 1 | =PROD.SUMA({0; 0; 1; 0; 1; 0; 1};{25; 10; 15; 5; 8; 17; 50) |
Funkcja SUMPRODUCT następnie mnoży każdą parę wpisów w tablicach, aby utworzyć tablicę Wyniki Tylko dla Gracz nazwy, które są puste lub składają się tylko ze spacji:
| 1 | =PROD.SUMA({0; 0; 15; 0; 8; 0; 50) |
Na koniec liczby w tablicy są sumowane:
| 1 | =73 |
Więcej szczegółów na temat używania instrukcji logicznych i polecenia „-” w funkcji SUMPRODUCT można znaleźć tutaj.
Suma, jeśli pusta w Arkuszach Google
Te formuły działają dokładnie tak samo w Arkuszach Google, jak w programie Excel.
