Preuzmite primjer radne knjige
Ovaj će vodič pokazati kako koristiti funkciju SUMIFS za zbrajanje podataka koji odgovaraju praznim ili praznim ćelijama u Excelu i Google tablicama.
Zbroj ako je prazan
Prvo ćemo pokazati kako zbrojiti retke s praznim ćelijama.
Funkcija SUMIFS zbraja podatke koji zadovoljavaju određene kriterije.
Možemo upotrijebiti funkciju SUMIFS da zbrojimo sve Bodovi za Igrači bez imena u donjem primjeru.
| 1 | = SUMIFS (C3: C8, B3: B8, "") |
Koristimo dvostruke navodnike ("") za predstavljanje prazne ćelije u Excelu. Naš primjer zanemaruje Igrači A, B, C i D i zbraja bodove za nepoznato Igrači.
Tretiranje prostora kao praznih ćelija - s pomoćnom kolonom
Morate biti oprezni pri interakciji s praznim ćelijama u Excelu. Ćelije vam se mogu činiti praznima, ali Excel ih neće tretirati kao prazne. To se može dogoditi ako ćelija sadrži razmake, prijelome reda ili druge nevidljive znakove. Ovo je čest problem pri uvozu podataka u Excel iz drugih izvora.
Ako moramo tretirati sve ćelije koje sadrže samo razmake na isti način kao da su prazne, formula u prethodnom primjeru neće funkcionirati. Uočite kako formula SUMIFS ne smatra ćeliju B9 ispod (”“) praznom:
Za tretiranje ćelije koja sadrži samo razmake kao da je prazna ćelija, možemo dodati stupac pomoćnik s funkcijom TRIM kako bismo uklonili dodatne razmake iz vrijednosti svake ćelije:
| 1 | = TRIM (B3) |
Primjenjujemo funkciju SUMIFS na pomoćni stupac i ona sada točno izračunava zbroj.
| 1 | = SUMIFS (E3: E9, D3: D9, "") |
Stupac pomoćnik lako se stvara i lako se čita, ali možda biste htjeli imati jednu formulu za izvršavanje zadatka. Ovo je obrađeno u sljedećem odjeljku.
Tretiranje prostora kao praznih ćelija - bez kolone pomoćnika
Ako pomoćni stupac nije prikladan za vaše potrebe, tada možete upotrijebiti funkciju SUMPRODUCT u kombinaciji s funkcijama LEN i TRIM za zbrajanje praznih redaka.
| 1 | = SUMPRODUCT (-(LEN (TRIM (B3: B9)) = 0), D3: D9) |
U ovom primjeru koristimo funkciju SUMPRODUCT za izvođenje kompliciranog izračuna "zbroja ako". Prođimo kroz formulu.
Ovo je naša konačna formula:
| 1 | = SUMPRODUCT (-(LEN (TRIM (B3: B9)) = 0), D3: D9) |
Prvo, funkcija SUMPRODUCT navodi niz vrijednosti iz dva raspona ćelija:
| 1 | = SUMPRODUCT (-(LEN (TRIM ({"A"; "B"; ""; "C"; ""; "XX"; ""})) = 0), {25; 10; 15; 5 ; 8; 17; 50) |
Zatim funkcija TRIM uklanja početne i krajnje razmake iz Igrač imena:
| 1 | = SUMPRODUCT (-(LEN ({"A"; "B"; ""; "C"; ""; "XX"; ""}) = 0), {25; 10; 15; 5; 8; 17; 50) |
Funkcija LEN izračunava duljine obrezanih dijelova Igrač imena:
| 1 | = SUMPRODUCT (-({1; 1; 0; 1; 0; 2; 0} = 0), {25; 10; 15; 5; 8; 17; 50) |
S logičkim testom (= 0), bilo koji obrezan Igrač imena s 0 znakova mijenjaju se u TRUE:
| 1 | = SUMPRODUCT (-({FALSE; FALSE; TRUE; FALSE; TRUE; FALSE; TRUE}), {25; 10; 15; 5; 8; 17; 50) |
Dalje crtice (-) pretvaraju TRUE i FALSE vrijednosti u 1s i 0s:
| 1 | = PODIZVOD ({0; 0; 1; 0; 1; 0; 1}, {25; 10; 15; 5; 8; 17; 50) |
Funkcija SUMPRODUCT zatim množi svaki par unosa u nizovima kako bi proizvela niz Bodovi samo za Igrač prazna imena ili samo s razmacima:
| 1 | = PODIZVOD ({0; 0; 15; 0; 8; 0; 50) |
Konačno, brojevi u nizu zbrajaju se zajedno:
| 1 | =73 |
Više pojedinosti o korištenju Booleovih izraza i naredbe “-” u funkciji SUMPRODUCT možete pronaći ovdje.
Zbroj ako je prazno u Google tablicama
Ove formule rade potpuno isto u Google tablicama kao i u Excelu.
