Preuzmite primjer radne knjige
Ovaj će vodič pokazati kako koristiti funkciju SUMIFS za zbrajanje podataka koji se odnose na ćelije koje nisu prazne ili koje nisu prazne u Excelu i Google tablicama.
Zbroj ako nije prazno
Prvo ćemo pokazati kako zbrojiti podatke koji se odnose na ćelije koje nisu prazne.
Možemo upotrijebiti funkciju SUMIFS da zbrojimo sve Bodovi za Igrači s praznim imenima.
| 1 | = SUMIFS (C3: C8, B3: B8, "") |
Za zbrajanje redaka s ćelijama koje nisu prazne, isključujemo Bodovi s nestalima Igrač imena. Koristimo kriterije "nije jednako praznom" ("") unutar funkcije SUMIFS.
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:
| 1 | = SUMIFS (D3: D9, B3: B9, "") |
Za tretiranje ćelije koja sadrži samo razmake kao da je prazna ćelija, možemo dodati stupac pomoćnik pomoću funkcija LEN i TRIM za identifikaciju Igrači s imenima.
Funkcija TRIM uklanja dodatne razmake s početka i na kraju vrijednosti svake ćelije, a funkcija LEN zatim broji preostali broj znakova. Ako je rezultat LEN funkcije 0, tada je Igrač ime mora biti prazno ili samo s razmacima:
| 1 | = LEN (TRIM (B3)) |
Primjenjujemo funkciju SUMIFS na pomoćni stupac (Zbrajanje ako je veće od 0), a ona sada točno izračunava zbroj.
| 1 | = ZBIRA (E3: E9, D3: D9, "> 0") |
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 je potrebno tretirati bilo koje ćelije koje sadrže samo razmake na isti način kao da su prazne, ali upotreba pomoćnog stupca nije prikladna, tada možemo upotrijebiti funkciju SUMPRODUCT u kombinaciji s funkcijama LEN i TRIM za zbrajanje podataka koji se odnose na ćelije koji sadrži neiscrpno polje Igrač imena:
| 1 | = SUMPRODUCT (-(LEN (TRIM (B3: B9))> 0), D3: D9) |
U ovom primjeru koristimo funkciju SUMPRODUCT za izvođenje kompliciranih izračuna "zbroj 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 koje obrezano Igrač imena s više od 0 znakova mijenjaju se u TRUE:
| 1 | = SUMPRODUCT (-({TRUE; TRUE; FALSE; TRUE; FALSE; TRUE; FALSE}), {25; 10; 15; 5; 8; 17; 50) |
Dalje crtice (-) pretvaraju TRUE i FALSE vrijednosti u 1s i 0s:
| 1 | = PODIZVOD ({1; 1; 0; 1; 0; 1; 0}, {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č nazivi koji nisu prazni ili nisu napravljeni samo s razmacima:
| 1 | = PODIZVOD ({25; 10; 0; 5; 0; 17; 0) |
Konačno, brojevi u nizu se zbrajaju
| 1 | =57 |
Više pojedinosti o korištenju logičkih izraza i naredbe “-” u funkciji SUMPRODUCT možete pronaći ovdje
Zbroj ako nije prazan u Google tablicama
Ove formule rade potpuno isto u Google tablicama kao i u Excelu.
