Pobierz przykładowy skoroszyt
W tym samouczku zademonstrujemy, jak używać funkcji SUMIFS do sumowania danych związanych z niepustymi lub niepustymi komórkami w programie Excel i Arkuszach Google.
Suma, jeśli nie jest pusta
Najpierw pokażemy, jak sumować dane dotyczące niepustych komórek.
Możemy użyć funkcji SUMIFS, aby zsumować wszystko Wyniki dla Gracze z niepustymi nazwami.
| 1 | =SUMA.WARUNKI(C3:C8;B3:B8;"") |
Aby zsumować wiersze z niepustymi komórkami, wykluczamy Wyniki z zaginionym Gracz nazwy. Używamy kryterium „nie równa się pustemu” („”) wewnątrz funkcji SUMIFS.
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ą:
| 1 | =SUMA.WARUNKI(D3:D9;B3:B9;"") |
Aby traktować komórkę zawierającą tylko spacje tak, jakby była pustą komórką, możemy dodać kolumnę pomocniczą za pomocą funkcji LEN i TRIM do identyfikacji Gracze z imionami.
Funkcja TRIM usuwa dodatkowe spacje z początku i końca wartości każdej komórki, a funkcja LEN następnie zlicza liczbę pozostałych znaków. Jeśli wynik funkcji LEN wynosi 0, to Gracz imię musi być puste lub składać się tylko ze spacji:
| 1 | =DŁ(PRZYC(B3)) |
Stosujemy funkcję SUMIFS do kolumny pomocniczej (sumowanie, jeśli jest większe niż 0), która teraz dokładnie oblicza sumę.
| 1 | =SUMA.WARUNKI(E3:E9;D3:D9;">0") |
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 wymagane jest traktowanie dowolnych komórek zawierających tylko spacje w taki sam sposób, jak gdyby były puste, ale użycie kolumny pomocniczej nie jest właściwe, możemy użyć funkcji SUMPRODUCT w połączeniu z funkcjami LEN i TRIM, aby zsumować dane dotyczące komórek zawierające niepuste Gracz nazwy:
| 1 | =PROD.SUMA(--(DŁ(PRZYM(B3:B9))>0);D3:D9) |
W tym przykładzie używamy funkcji SUMPRODUCT do wykonywania skomplikowanych obliczeń „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 | =PROD.SUMA(--(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 | =PROD.SUMA(--(DŁ({"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 zawierające więcej niż 0 znaków są zmieniane na TRUE:
| 1 | =PRODUKTSUMA(--({PRAWDA; PRAWDA ; FAŁSZ; PRAWDA; FAŁSZ; PRAWDA; FAŁSZ}),{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({1; 1; 0; 1; 0; 1; 0};{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 nie są puste lub nie składają się tylko ze spacji:
| 1 | =PROD.SUMA({25; 10; 0; 5; 0; 17; 0) |
Na koniec liczby w tablicy są sumowane
| 1 | =57 |
Więcej szczegółów na temat używania instrukcji Boolean i polecenia „-” w funkcji SUMPRODUCT można znaleźć tutaj
Suma, jeśli nie jest pusta w Arkuszach Google
Te formuły działają dokładnie tak samo w Arkuszach Google, jak w programie Excel.
