Ovaj vodič pokazuje kako se koristi Excel SUMIF i SUMIFS Functions u Excelu i Google tablicama za zbrajanje podataka koji zadovoljavaju određene kriterije.
Pregled funkcije SUMIF
Možete koristiti funkciju SUMIF u Excelu za zbroj ćelija koje sadrže određenu vrijednost, ćelije zbroja koje su veće ili jednake vrijednosti itd.
(Obratite pažnju na to kako se pojavljuju unosi formule)
Sintaksa i argumenti funkcije SUMIF:
| 1 | = SUMIF (raspon, kriteriji, [zbroj_zbroja]) |
domet - Raspon ćelija na koje želite primijeniti kriterije.
kriterijima - Kriteriji koji se koriste za određivanje ćelija koje treba dodati.
zbroj_područje - [izborno] ćelije za zbrajanje. Ako je sum_range izostavljen, umjesto toga se zbrajaju ćelije u rasponu.
Što je funkcija SUMIF?
Funkcija SUMIF jedna je od starijih funkcija koja se koristi u proračunskim tablicama. Koristi se za skeniranje kroz niz ćelija radi provjere određenog kriterija, a zatim zbrajanje vrijednosti u rasponu koji odgovara tim vrijednostima. Izvorna funkcija SUMIF bila je ograničena na samo jedan kriterij. Nakon 2007. godine stvorena je funkcija SUMIFS koja dopušta mnoštvo kriterija. Većina opće uporabe ostaje ista među njima, ali postoje neke kritične razlike u sintaksi o kojima ćemo govoriti u ovom članku.
Ako već niste, većinu slične strukture i primjera možete pregledati u članku COUNTIFS.
Osnovni primjer
Razmotrimo ovaj popis zabilježene prodaje i želimo znati ukupni prihod.
Budući da smo imali trošak, negativnu vrijednost, ne možemo napraviti samo osnovni iznos. Umjesto toga, želimo zbrojiti samo vrijednosti koje su veće od 0. "Više od 0" će biti naši kriteriji u funkciji SUMIF. Naša formula da to kažemo jest
| 1 | = SUMIF (A2: A7, "> 0") |
Primjer s dvije kolone
Iako je izvorna funkcija SUMIF osmišljena kako bi vam omogućila da primijenite kriterij na raspon brojeva koje želite zbrojiti, velik dio vremena morat ćete primijeniti jedan ili više kriterija na druge stupce. Razmotrimo ovu tablicu:
Sada, ako koristimo izvornu funkciju SUMIF kako bismo saznali koliko banana imamo (navedenih u ćeliji D1), morat ćemo dati raspon koji želimo iznos kao posljednji argument, pa bi i naša formula bila
| 1 | = ZBIR (A2: A7, D1, B2: B7) |
Međutim, kada su programeri na kraju shvatili da korisnici žele dati više od jednog kriterija, stvorena je funkcija SUMIFS. Kako bi se stvorila jedna struktura koja bi funkcionirala za bilo koji broj kriterija, SUMIFS zahtijeva da se prvo navede raspon zbroja. U našem primjeru to znači da formula mora biti
| 1 | = ZBIRA (B2: B7, A2: A7, D1) |
NAPOMENA: Ove dvije formule dobivaju isti rezultat i mogu izgledati slično, stoga pažljivo obratite pozornost na to koja se funkcija koristi kako biste bili sigurni da ste sve argumente naveli ispravnim redoslijedom.
Rad s datumima, više kriterija
Prilikom rada s datumima u proračunskoj tablici, iako je moguće unijeti datum izravno u formulu, najbolje je da datum bude u ćeliji tako da se možete jednostavno pozvati na ćeliju u formuli. Na primjer, ovo pomaže računalu da zna da želite koristiti datum 27.5.2020., A ne broj 5 podijeljen s 27 podijeljen s 2022. godinom.
Pogledajmo sljedeću tablicu koja bilježi broj posjetitelja web stranice svaka dva tjedna.
Možemo odrediti početnu i završnu točku raspona koji želimo pogledati u D2 i E2. Naša formula tada za zbroj broja posjetitelja u ovom rasponu mogla bi biti:
| 1 | = ZBIRA (B2: B7, A2: A7, "> =" & D2, A2: A7, "<=" & E2) |
Zapazite kako smo uspjeli spojiti usporedbe "=" s referencama ćelija da bismo stvorili kriterije. Također, iako su se oba kriterija primjenjivala na isti raspon ćelija (A2: A7), morate ispisati raspon dva puta, jednom po svakom kriteriju.
Više stupaca
Kada koristite više kriterija, možete ih primijeniti na isti raspon kao što smo to učinili u prethodnom primjeru, ili ih možete primijeniti na različite raspone. Kombinirajmo naše uzorke podataka u ovu tablicu:
Postavili smo neke ćelije za korisnika da unese ono što želi pretraživati u ćelijama E2 do G2. Stoga nam je potrebna formula koja će zbrajati ukupan broj ubranih jabuka u veljači. Naša formula izgleda ovako:
| 1 | = ZBIRA (C2: C7, B2: B7, "> =" & F2, B2: B7, "<=" & G2, A2: A7, E2) |
SUMIFS s logikom tipa ILI
Do sada su svi primjeri koje smo koristili bili usporedba temeljena na I, gdje tražimo retke koji zadovoljavaju sve naše kriterije. Sada ćemo razmotriti slučaj kada želite tražiti mogućnost da red zadovolji jedan ili drugi kriterij.
Pogledajmo ovaj popis prodaje:
Željeli bismo zbrojiti ukupnu prodaju i za Adama i za Boba. Da biste to učinili, imate nekoliko mogućnosti. Najjednostavnije je dodati dva SUMIF -a zajedno, ovako:
| 1 | = SUMIFS (B2: B7, A2: A7, "Adam")+SUMIFS (B2: B7, A2: A7, "Bob") |
Ovdje smo dobili računalo da izračuna naše pojedinačne rezultate, a zatim ih zbrojimo.
Naša je sljedeća opcija dobra ako imate više raspona kriterija, pa ne želite ponavljati cijelu formulu. U prethodnoj formuli ručno smo rekli računalu da doda dva različita SUMIF -a. Međutim, to možete učiniti i tako da svoje kriterije upišete unutar niza, na sljedeći način:
| 1 | = ZBIR (ZBIRA (B2: B7, A2: A7, {"Adam", "Bob"})) |
Pogledajte kako je niz konstruiran unutar kovrčavih zagrada. Kad računalo ocijeni ovu formulu, znat će da želimo izračunati funkciju SUMIFS za svaku stavku u našem nizu, stvarajući tako niz brojeva. Vanjska funkcija SUM tada će uzeti niz brojeva i pretvoriti ga u jedan broj. Korakom kroz evaluaciju formule to bi izgledalo ovako:
| 123 | = ZBIR (ZBIRA (B2: B7, A2: A7, {"Adam", "Bob"}))= SUM (27401, 43470)= 70871 |
Dobivamo isti rezultat, ali smo formulu mogli sažetije zapisati.
Suočavanje s prazninama
Ponekad će vaš skup podataka imati prazne ćelije koje morate pronaći ili izbjeći. Postavljanje ovih kriterija može biti malo zeznuto, pa pogledajmo drugi primjer.
Imajte na umu da je ćelija A3 doista prazna, dok ćelija A5 ima formulu koja vraća niz "" "nulte duljine. Ako želimo pronaći ukupni zbroj uistinu prazne ćelije, upotrijebili bismo kriterij “=”, a naša bi formula izgledala ovako:
| 1 | = ZBIRA (B2: B7, A2: A7, "=") |
S druge strane, ako želimo dobiti zbroj za sve ćelije koje vizualno izgledaju prazne, promijenit ćemo kriterij u "", a formula izgleda ovako
| 1 | = ZBIRA (B2: B7, A2: A7, "") |
Okrenimo to: što ako želite pronaći zbroj ćelija koje nisu prazne? Nažalost, trenutni dizajn ne dopušta izbjegavanje niza nulte duljine. Možete koristiti kriterij "", ali kao što vidite u primjeru, on i dalje uključuje vrijednost iz retka 5.
| 1 | = ZBIRA (B2: B7, A2: A7, "") |
Ako ne morate brojati ćelije koje sadrže nizove nulte duljine, razmislite o upotrebi funkcije LEN unutar SUMPRODUCT -a
SUMIF u Google tablicama
Funkcija SUMIF radi potpuno isto u Google tablicama kao i u Excelu:
