Ez az oktatóanyag bemutatja, hogyan kell használni Excel SUMIF és SUMIFS Functionok az Excelben és a Google Táblázatokban bizonyos feltételeknek megfelelő adatok összegzésére.
A SUMIF funkció áttekintése
Az Excel SUMIF függvényével összeadhatja az adott értéket tartalmazó cellákat, az értéket meghaladó vagy azzal egyenlő cellákat.
(Figyelje meg, hogyan jelennek meg a képletbemenetek)
SUMIF függvény szintaxisa és érvei:
| 1 | = SUMIF (tartomány, feltételek, [összegtartomány]) |
hatótávolság - Az a cellatartomány, amely ellen alkalmazni kívánja a feltételeket.
kritériumok - A hozzáadandó cellák meghatározásához használt kritériumok.
összeg_tartomány - [nem kötelező] Az összeadandó cellák. Ha az összeg_tartomány kihagyásra kerül, akkor a tartomány celláit összeadjuk.
Mi a SUMIF funkció?
A SUMIF függvény a táblázatokban használt egyik régebbi funkció. Arra szolgál, hogy átvizsgáljon egy cellatartományt, és ellenőrizze az adott feltételt, majd összeadja az értékeket egy tartományban, amely megfelel ezeknek az értékeknek. Az eredeti SUMIF függvény csak egy kritériumra korlátozódott. 2007 után létrejött a SUMIFS függvény, amely számos feltételt tesz lehetővé. Az általános használat nagy része ugyanaz marad a kettő között, de a szintaxisban vannak kritikus különbségek, amelyeket ebben a cikkben tárgyalunk.
Ha még nem tette meg, a hasonló struktúra és példák nagy részét áttekintheti a COUNTIFS cikkben.
Alapvető példa
Tekintsük ezt a rögzített eladások listáját, és szeretnénk tudni a teljes bevételt.
Mivel volt ráfordításunk, negatív értékünk, nem tehetünk csak egy alapösszeget. Ehelyett csak a 0 -nál nagyobb értékeket szeretnénk összegezni. A „nagyobb, mint 0” lesz a kritériumunk egy SUMIF függvényben. A mi képletünk ennek kimondására
| 1 | = SUMIF (A2: A7, "> 0") |
Két oszlopos példa
Míg az eredeti SUMIF funkciót úgy tervezték, hogy egy feltételt alkalmazzon az összegezni kívánt számtartományra, sok esetben egy vagy több feltételt kell alkalmaznia más oszlopokra. Tekintsük ezt a táblázatot:
Ha most az eredeti SUMIF függvényt használjuk, hogy megtudjuk, hány banánunk van (a D1 cellában felsorolva), akkor meg kell adnunk a kívánt tartományt. összeg utolsó érvként, és így lenne a képletünk
| 1 | = SUMIF (A2: A7, D1, B2: B7) |
Amikor azonban a programozók végül rájöttek, hogy a felhasználók egynél több kritériumot szeretnének megadni, létrejött a SUMIFS függvény. Annak érdekében, hogy egyetlen struktúrát hozzon létre, amely tetszőleges számú feltételnek megfelelne, a SUMIFS megköveteli, hogy először az összegtartomány szerepeljen. Példánkban ez azt jelenti, hogy a képletnek annak kell lennie
| 1 | = SUMIFS (B2: B7, A2: A7, D1) |
MEGJEGYZÉS: Ez a két képlet ugyanazt az eredményt kapja, és hasonlónak tűnhetnek, ezért figyeljen arra, hogy melyik függvényt használja, és győződjön meg arról, hogy az összes argumentumot a megfelelő sorrendben sorolja fel.
Dátumokkal való munka, több kritérium
Amikor a táblázatban lévő dátumokkal dolgozik, bár lehetőség van a dátum közvetlen bevitelére a képletbe, a legjobb gyakorlat, ha a dátumot egy cellában tartja, így csak hivatkozhat a cellára egy képletben. Ez például segíti a számítógépet abban, hogy tudja, hogy 2020.05.27 -én szeretné használni a dátumot, és nem az 5 -ös számot osztva 27 -el osztva 2022 -vel.
Nézzük a következő táblázatot, amely kéthetente rögzíti egy webhely látogatóinak számát.
A D2 és E2 -ben megadhatjuk a vizsgálni kívánt tartomány kezdő és végpontját. Ekkor a képletünk a látogatók számának összegzésére ebben a tartományban a következő lehet:
| 1 | = SUMIFS (B2: B7, A2: A7, "> =" & D2, A2: A7, "<=" & E2) |
Figyelje meg, hogyan tudtuk összekapcsolni a „=” összehasonlítását a cellahivatkozásokkal a feltételek létrehozásához. Továbbá, annak ellenére, hogy mindkét kritériumot ugyanazon cellatartományra alkalmazták (A2: A7), kétszer kell kiírni a tartományt, minden kritériumonként egyszer.
Több oszlop
Több feltétel használata esetén ugyanazon tartományra alkalmazhatja őket, mint az előző példánál, vagy különböző tartományokra. Kombináljuk a mintaadatokat ebbe a táblázatba:
Beállítottunk néhány cellát, hogy a felhasználó beírhassa a keresni kívánt elemeket az E2 – G2 cellákba. Szükségünk van tehát egy képletre, amely összeadja a februárban szedett alma teljes számát. A képletünk így néz ki:
| 1 | = SUMIFS (C2: C7, B2: B7, "> =" & F2, B2: B7, "<=" & G2, A2: A7, E2) |
SUMIFS OR típusú logikával
Eddig a példák mindegyike ÉS alapú összehasonlítás volt, ahol olyan sorokat keresünk, amelyek minden kritériumunknak megfelelnek. Most megvizsgáljuk azt az esetet, amikor keresni szeretné annak lehetőségét, hogy egy sor megfeleljen egyik vagy másik kritériumnak.
Nézzük ezt az értékesítési listát:
Szeretnénk összeadni Adam és Bob összes értékesítését. Ehhez van pár lehetősége. A legegyszerűbb két SUMIFS összeadása, például:
| 1 | = SUMIFS (B2: B7, A2: A7, "Adam")+SUMIFS (B2: B7, A2: A7, "Bob") |
Itt a számítógép kiszámította az egyéni pontszámokat, majd összeadtuk őket.
A következő lehetőségünk akkor jó, ha több kritériumtartomány áll rendelkezésére, például ha nem szeretné, hogy a teljes képletet többször át kell írnia. Az előző képletben manuálisan azt mondtuk a számítógépnek, hogy adjon hozzá két különböző SUMIFS -t. Ezt azonban úgy is megteheti, hogy a kritériumokat egy tömbbe írja, például:
| 1 | = SUM (SUMIFS (B2: B7, A2: A7, {"Adam", "Bob"})) |
Nézze meg, hogyan épül fel a tömb a göndör zárójelek között. Amikor a számítógép kiértékeli ezt a képletet, akkor tudni fogja, hogy SUMIFS függvényt szeretnénk kiszámítani a tömbünk minden elemére, ezáltal számok tömbjét hozva létre. A külső SUM függvény ekkor veszi ezt a számtömböt, és egyetlen számmá alakítja. A képlet kiértékelésén keresztül így néz ki:
| 123 | = SUM (SUMIFS (B2: B7, A2: A7, {"Adam", "Bob"}))= SUM (27401, 43470)= 70871 |
Ugyanezt az eredményt kapjuk, de egy kicsit tömörebben ki tudtuk írni a képletet.
Üres helyekkel való foglalkozás
Néha az adathalmaz üres cellákat tartalmaz, amelyeket meg kell találnia vagy el kell kerülnie. Ezek kritériumainak beállítása kissé bonyolult lehet, ezért nézzünk egy másik példát.
Ne feledje, hogy az A3 cella valóban üres, míg az A5 cella képlete nulla hosszúságú „” karakterláncot ad vissza. Ha meg akarjuk találni a teljes összeget valóban üres cellák, a „=” kritériumot használnánk, és a képletünk így nézne ki:
| 1 | = SUMIFS (B2: B7, A2: A7, "=") |
Másrészt, ha meg akarjuk kapni az összes cellát, amely vizuálisan üresnek tűnik, akkor a feltételeket „” -re változtatjuk, és a képlet így néz ki
| 1 | = SUMIFS (B2: B7, A2: A7, "") |
Fordítsuk meg: mi van, ha meg akarja találni a nem üres cellák összegét? Sajnos a jelenlegi kialakítás nem engedi elkerülni a nulla hosszúságú karakterláncot. Használhat „” kritériumot, de amint a példában is látható, továbbra is tartalmazza az 5. sor értékét.
| 1 | = SUMIFS (B2: B7, A2: A7, "") |
Ha nem kell számolni a nulla hosszúságú karakterláncokat tartalmazó cellákat, akkor fontolja meg a LEN függvény használatát a SUMPRODUCT -ban
SUMIF a Google Táblázatokban
A SUMIF funkció pontosan ugyanúgy működik a Google Táblázatokban, mint az Excelben:
