Ta vadnica prikazuje, kako uporabljati Excel SUMIF in SUMIFS Functionov v Excelu in Google Preglednicah za seštevanje podatkov, ki izpolnjujejo določena merila.
Pregled funkcije SUMIF
Funkcijo SUMIF v Excelu lahko uporabite za seštevanje celic, ki vsebujejo določeno vrednost, vsotnih celic, ki so večje ali enake vrednosti itd.
(Upoštevajte, kako so prikazani vnosi formule)
Sintaksa in argumenti funkcije SUMIF:
| 1 | = SUMIF (obseg, merila, [obseg_vsote]) |
obseg - Obseg celic, za katere želite uporabiti merila.
merila - Merila, ki se uporabljajo za določanje, katere celice je treba dodati.
sum_range - [neobvezno] Celice za sestavljanje. Če je sum_range izpuščen, se celice v obsegu namesto tega seštejejo.
Kaj je funkcija SUMIF?
Funkcija SUMIF je ena starejših funkcij, ki se uporabljajo v preglednicah. Uporablja se za skeniranje skozi vrsto celic, ki preverjajo določeno merilo, nato pa seštejejo vrednosti v območju, ki ustreza tem vrednostim. Prvotna funkcija SUMIF je bila omejena le na eno merilo. Po letu 2007 je bila ustvarjena funkcija SUMIFS, ki omogoča množico meril. Večina splošne uporabe med obema ostaja enaka, vendar obstajajo nekatere kritične razlike v skladnji, o katerih bomo razpravljali v tem članku.
Če tega še niste storili, si lahko v članku COUNTIFS ogledate večino podobne strukture in primerov.
Osnovni primer
Razmislimo o tem seznamu zabeležene prodaje in želimo vedeti skupni prihodek.
Ker smo imeli strošek, negativno vrednost, ne moremo narediti le osnovnega zneska. Namesto tega želimo sešteti samo vrednosti, ki so večje od 0. "Več kot 0" je tisto, kar bo naše merilo v funkciji SUMIF. Naša formula, da to trdimo, je
| 1 | = SUMIF (A2: A7, "> 0") |
Primer z dvema stolpcema
Čeprav je bila prvotna funkcija SUMIF zasnovana tako, da lahko uporabite merilo za obseg števil, ki jih želite sešteti, boste morali večino časa uporabiti eno ali več meril za druge stolpce. Poglejmo to tabelo:
Če uporabimo prvotno funkcijo SUMIF, da ugotovimo, koliko banan imamo (naštetih v celici D1), bomo morali dati obseg, ki ga želimo vsota kot zadnji argument in tako bi bila naša formula
| 1 | = SUMIF (A2: A7, D1, B2: B7) |
Ko pa so programerji na koncu spoznali, da želijo uporabniki podati več kot eno merilo, je bila ustvarjena funkcija SUMIFS. Da bi ustvarili eno strukturo, ki bi delovala za poljubno število meril, SUMIFS zahteva, da je najprej naveden obseg vsote. V našem primeru to pomeni, da mora biti formula
| 1 | = SUMIFS (B2: B7, A2: A7, D1) |
OPOMBA: Ti dve formuli dobita enak rezultat in si lahko izgledata podobno, zato bodite pozorni na to, katero funkcijo uporabljate, da vse argumente navedete v pravilnem vrstnem redu.
Delo z datumi, več meril
Ko delate z datumi v preglednici, čeprav je datum mogoče vnesti neposredno v formulo, je najbolje, da imate datum v celici, tako da se lahko preprosto sklicujete na celico v formuli. Tako računalnik na primer pomaga vedeti, da želite uporabiti datum 27.5.2020 in ne številke 5, deljeno s 27, deljeno z 2022.
Poglejmo našo naslednjo tabelo, ki beleži število obiskovalcev spletnega mesta vsaka dva tedna.
Lahko določimo začetno in končno točko območja, ki ga želimo pogledati v D2 in E2. Naša formula za seštevanje števila obiskovalcev v tem območju je lahko:
| 1 | = SUMIFS (B2: B7, A2: A7, "> =" & D2, A2: A7, "<=" & E2) |
Upoštevajte, kako smo lahko združili primerjave »=« z referencami celic, da smo ustvarili merila. Čeprav sta bila oba merila uporabljena za isti obseg celic (A2: A7), morate obseg zapisati dvakrat, enkrat za vsako merilo.
Več stolpcev
Če uporabljate več meril, jih lahko uporabite za isti obseg, kot smo ga uporabili v prejšnjem primeru, ali pa jih lahko uporabite za različne obsege. Združimo naše vzorčne podatke v to tabelo:
Uporabnikom smo nastavili nekaj celic, da v celice od E2 do G2 vnesejo tisto, kar želijo iskati. Zato potrebujemo formulo, ki bo seštela skupno število jabolk, nabranih februarja. Naša formula izgleda takole:
| 1 | = SUMIFS (C2: C7, B2: B7, "> =" & F2, B2: B7, "<=" & G2, A2: A7, E2) |
SUMIFS z logiko tipa OR
Do sedaj so bili vsi primeri, ki smo jih uporabili, primerjava na osnovi AND, kjer iščemo vrstice, ki izpolnjujejo vsa naša merila. Zdaj bomo razmislili o primeru, ko želite poiskati možnost, da vrstica izpolnjuje eno ali drugo merilo.
Poglejmo ta seznam prodaje:
Radi bi sešteli celotno prodajo za Adama in Boba. Če želite to narediti, imate nekaj možnosti. Najenostavnejše je, da skupaj dodate dva SUMIF -a:
| 1 | = SUMIFS (B2: B7, A2: A7, "Adam")+SUMIFS (B2: B7, A2: A7, "Bob") |
Tukaj smo imeli računalnik izračunati naše posamezne rezultate in jih nato seštejemo.
Naša naslednja možnost je dobra, če imate več razponov meril, na primer, da vam ni treba večkrat prepisovati celotne formule. V prejšnji formuli smo računalniku ročno povedali, naj doda dva različna SUMIFS skupaj. To pa lahko storite tudi tako, da svoja merila zapišete v matriko, na primer:
| 1 | = SUM (SUMIFS (B2: B7, A2: A7, {"Adam", "Bob"})) |
Poglejte, kako je matrika sestavljena znotraj kodrastih oklepajev. Ko računalnik ovrednoti to formulo, bo vedel, da želimo izračunati funkcijo SUMIFS za vsako postavko v našem nizu in tako ustvariti niz številk. Zunanja funkcija SUM bo nato vzela ta niz številk in jih spremenila v eno samo številko. Ko bi stopili skozi oceno formule, bi to izgledalo tako:
| 123 | = SUM (SUMIFS (B2: B7, A2: A7, {"Adam", "Bob"}))= SUM (27401, 43470)= 70871 |
Dobili smo enak rezultat, vendar smo lahko formulo zapisali nekoliko bolj jedrnato.
Ukvarjanje s prazninami
Včasih bodo v vašem naboru podatkov prazne celice, ki jih morate poiskati ali se jim izogniti. Določitev meril za te je lahko nekoliko težavna, zato poglejmo še en primer.
Upoštevajte, da je celica A3 resnično prazna, celica A5 pa ima formulo, ki vrne niz dolžine »«. Če želimo najti skupno vsoto resnično prazne celice, uporabili bi merilo "=", naša formula pa bi izgledala tako:
| 1 | = SUMIFS (B2: B7, A2: A7, "=") |
Po drugi strani pa, če želimo dobiti vsoto za vse celice, ki vizualno izgledajo prazne, spremenimo merila na »«, formula pa izgleda tako
| 1 | = SUMIFS (B2: B7, A2: A7, "") |
Obrnimo ga: kaj, če želite najti vsoto praznih celic? Na žalost trenutna zasnova ne omogoča izogibanja nizu ničelne dolžine. Uporabite lahko merilo »«, vendar, kot vidite v primeru, še vedno vključuje vrednost iz 5. vrstice.
| 1 | = SUMIFS (B2: B7, A2: A7, "") |
Če ne želite šteti celic, ki vsebujejo nize dolžine nič, razmislite o uporabi funkcije LEN znotraj SUMPRODUCT
SUMIF v Google Preglednicah
Funkcija SUMIF v Google Preglednicah deluje popolnoma enako kot v Excelu:
