See õpetus näitab, kuidas Excel SUMIF ja SUMIFS Functioonid Excelis ja Google'i arvutustabelites, et liita kokku andmed, mis vastavad teatud kriteeriumidele.
SUMIF -funktsioonide ülevaade
Funktsiooni SUMIF saate Excelis kasutada teatud väärtust sisaldavate lahtrite summeerimiseks, väärtusest suuremate või sellega võrdsete lahtrite summeerimiseks jne.
(Pange tähele, kuidas valemisisendid ilmuvad)
Funktsiooni SUMIF süntaks ja argumendid:
| 1 | = SUMIF (vahemik, kriteeriumid, [summa_vahemik]) |
vahemik - Lahtrite vahemik, mille suhtes soovite kriteeriume rakendada.
kriteeriumid - kriteeriumid, mida kasutatakse lahtrite lisamiseks.
summa_vahemik - [valikuline] Lahtrid, mida liita. Kui summa_vahemik välja jätta, liidetakse selle vahemiku lahtrid kokku.
Mis on SUMIF -funktsioon?
Funktsioon SUMIF on üks vanemaid arvutustabelites kasutatavaid funktsioone. Seda kasutatakse lahtrite vahemiku skannimiseks, kontrollides konkreetset kriteeriumi ja lisades seejärel väärtused nendele väärtustele vastavas vahemikus. Algne SUMIF -funktsioon piirdus vaid ühe kriteeriumiga. Pärast 2007. aastat loodi funktsioon SUMIFS, mis võimaldab paljusid kriteeriume. Enamik üldist kasutust jääb nende kahe vahel samaks, kuid süntaksis on mõned kriitilised erinevused, mida arutame kogu selle artikli jooksul.
Kui te pole seda juba teinud, saate suure osa sarnasest struktuurist ja näidetest läbi vaadata artiklis COUNTIFS.
Põhinäide
Vaatleme seda registreeritud müügi loendit ja tahame teada kogutulu.
Kuna meil oli kulu, negatiivne väärtus, ei saa me lihtsalt põhisummat teha. Selle asemel tahame liita ainult need väärtused, mis on suuremad kui 0. „Suurem kui 0” on meie SUMIF -funktsiooni kriteeriumid. Meie valem selle kinnitamiseks on
| 1 | = SUMIF (A2: A7, "> 0") |
Kahe veeru näide
Kuigi esialgne SUMIF -funktsioon oli loodud selleks, et saaksite kasutada kriteeriume arvude vahemikule, mille soovite summeerida, peate suure osa ajast rakendama teistele veergudele ühe või mitu kriteeriumi. Vaatleme seda tabelit:
Kui kasutame esialgset funktsiooni SUMIF, et teada saada, kui palju banaane meil on (lahtris D1 loetletud), peame andma soovitud vahemiku. summa viimase argumendina ja nii oleks ka meie valem
| 1 | = SUMIF (A2: A7, D1, B2: B7) |
Kui aga programmeerijad lõpuks mõistsid, et kasutajad soovivad anda rohkem kui ühe kriteeriumi, loodi funktsioon SUMIFS. Selleks, et luua üks struktuur, mis toimiks mis tahes arvu kriteeriumide jaoks, nõuab SUMIFS, et summa vahemik on kõigepealt loetletud. Meie näites tähendab see, et valem peab olema
| 1 | = SUMIFS (B2: B7, A2: A7, D1) |
MÄRKUS. Need kaks valemit annavad sama tulemuse ja võivad tunduda sarnased, seega pöörake hoolikalt tähelepanu sellele, millist funktsiooni kasutatakse, et veenduda, et loetlete kõik argumendid õiges järjekorras.
Kuupäevadega töötamine, mitu kriteeriumi
Arvutustabelis kuupäevadega töötades, kuigi kuupäeva on võimalik sisestada otse valemisse, on hea tava, kui kuupäev on lahtris, nii et saate valemile lihtsalt viidata. Näiteks aitab see arvutil teada, et soovite kasutada kuupäeva 27.5.2020, mitte numbrit 5 jagatuna 27 -ga jagatuna 2022. aastaks.
Vaatame järgmist tabelit, mis registreerib saidi külastajate arvu iga kahe nädala tagant.
D2 ja E2 saame määrata vahemiku algus- ja lõpp -punkti, mida soovime vaadata. Meie valem selle piirkonna külastajate arvu summeerimiseks võiks olla järgmine:
| 1 | = SUMIFS (B2: B7, A2: A7, "> =" & D2, A2: A7, "<=" & E2) |
Pange tähele, kuidas kriteeriumide loomiseks suutsime võrdluse „=” lahterviidetega ühendada. Isegi kui mõlemat kriteeriumi rakendati samale lahtrivahemikule (A2: A7), peate vahemiku kirjutama kaks korda, üks kord iga kriteeriumi kohta.
Mitu veergu
Kui kasutate mitut kriteeriumi, saate neid rakendada samale vahemikule, nagu me tegime eelmise näitega, või saate neid rakendada erinevatele vahemikele. Kombineerime oma näidisandmed sellesse tabelisse:
Oleme seadistanud mõned lahtrid, et kasutaja saaks sisestada otsitava lahtritesse E2 kuni G2. Vajame seega valemit, mis liidab kokku veebruaris korjatud õunte koguarvu. Meie valem näeb välja selline:
| 1 | = SUMIFS (C2: C7, B2: B7, "> =" & F2, B2: B7, "<=" & G2, A2: A7, E2) |
VÕI tüüpi loogikaga SUMIFS
Siiani on kõik meie kasutatud näited olnud JA -põhine võrdlus, kus otsime ridu, mis vastavad kõigile meie kriteeriumidele. Nüüd kaalume juhtumit, kui soovite otsida võimalust, et rida vastab ühele või teisele kriteeriumile.
Vaatame seda müügiloendit:
Soovime liita nii Aadama kui ka Bobi kogumüügi. Selleks on teil paar võimalust. Lihtsaim on lisada kaks SUMIFS -i kokku järgmiselt:
| 1 | = SUMIFS (B2: B7, A2: A7, "Adam")+SUMIFS (B2: B7, A2: A7, "Bob") |
Siin lasime arvutil arvutada oma individuaalsed skoorid ja seejärel liidame need kokku.
Meie järgmine valik sobib siis, kui teil on rohkem kriteeriumivahemikke, nii et te ei soovi kogu valemit korduvalt ümber kirjutada. Eelmises valemis käskisime arvutil käsitsi lisada kaks erinevat SUMIFS -i. Kuid saate seda teha ka siis, kui kirjutate oma kriteeriumid massiivi sisse, näiteks:
| 1 | = SUM (SUMIFS (B2: B7, A2: A7, {"Adam", "Bob"})) |
Vaadake, kuidas massiiv on lokkisulgude sisse ehitatud. Kui arvuti seda valemit hindab, saab ta teada, et soovime arvutada SUMIFS -funktsiooni iga meie massiivi üksuse jaoks, luues seega numbrimassiivi. Väline SUM -funktsioon võtab selle numbrimassiivi ja muudab selle üheks numbriks. Valemihindamist läbides näeks see välja selline:
| 123 | = SUM (SUMIFS (B2: B7, A2: A7, {"Adam", "Bob"}))= SUM (27401, 43470)= 70871 |
Saame sama tulemuse, kuid suutsime valemi natuke napisõnalisemalt välja kirjutada.
Toorikutega tegelemine
Mõnikord on teie andmekogul tühjad lahtrid, mida peate leidma või vältima. Nende kriteeriumide seadmine võib olla pisut keeruline, nii et vaatame teist näidet.
Pange tähele, et lahter A3 on tõeliselt tühi, samas kui lahtril A5 on valem, mis tagastab nullpikkuse stringi "". Kui tahame leida kogusumma tõesti tühjad lahtrid, kasutaksime kriteeriumi "=" ja meie valem näeks välja selline:
| 1 | = SUMIFS (B2: B7, A2: A7, "=") |
Teisest küljest, kui tahame saada summa kõigi lahtrite jaoks, mis näevad visuaalselt tühjad, muudame kriteeriumid väärtuseks „” ja valem näeb välja selline
| 1 | = SUMIFS (B2: B7, A2: A7, "") |
Pöörame selle ümber: mis siis, kui soovite leida tühjade lahtrite summa? Kahjuks ei lase praegune disain teil nullpikkust stringi vältida. Võite kasutada kriteeriumi „”, kuid nagu näites näete, sisaldab see siiski rea 5 väärtust.
| 1 | = SUMIFS (B2: B7, A2: A7, "") |
Kui te ei pea arvestama lahtreid, mis sisaldavad nullpikkusi, peaksite kaaluma funktsiooni LEN kasutamist SUMPRODUCT -is
SUMIF Google'i arvutustabelites
Funktsioon SUMIF töötab Google'i arvutustabelites täpselt samamoodi nagu Excelis:
