Töltse le a példa munkafüzetet
Ez az oktatóanyag bemutatja, hogyan kell használni a SUMIFS függvényt az Excel és a Google Táblázatok nem üres vagy nem üres celláival kapcsolatos adatok összegzéséhez.
Összeg, ha nem üres
Először bemutatjuk, hogyan lehet összegezni a nem üres cellákra vonatkozó adatokat.
Az összesítéshez használhatjuk a SUMIFS függvényt Pontszámok számára Játékosok nem üres nevekkel.
| 1 | = SUMIFS (C3: C8, B3: B8, "") |
A nem üres cellákkal rendelkező sorok összegzéséhez kizárjuk Pontszámok hiányzóval Játékos neveket. A „nem egyenlő üres” („”) kritériumokat használjuk a SUMIFS függvényben.
A terek kezelése üres cellákként - Segítő oszloppal
Óvatosnak kell lennie, amikor az Excel üres celláival lép kapcsolatba. A cellák üresnek tűnhetnek, de az Excel nem kezeli üresnek őket. Ez akkor fordulhat elő, ha a cella szóközt, sortörést vagy más láthatatlan karaktert tartalmaz. Ez gyakori probléma, amikor adatokat más forrásokból importál az Excelbe.
Ha minden olyan cellát, amely csak szóközöket tartalmaz, ugyanúgy kell kezelnünk, mintha üresek lennének, akkor az előző példában szereplő képlet nem fog működni. Figyelje meg, hogy a SUMIFS képlet nem tartja üresnek az alábbi B9 cellát (""):
| 1 | = SUMIFS (D3: D9, B3: B9, "") |
Ahhoz, hogy a csak szóközöket tartalmazó cellát üres cellaként kezeljük, hozzáadhatunk egy segédoszlopot a LEN és a TRIM függvények segítségével az azonosításhoz Játékosok nevekkel.
A TRIM funkció eltávolítja a felesleges szóközöket az egyes cellák értékének elejéről és végéről, a LEN függvény pedig megszámolja a fennmaradó karakterek számát. Ha a LEN függvény eredménye 0, akkor a Játékos a névnek üresnek kell lennie, vagy csak szóközökből kell állnia:
| 1 | = LEN (TRIM (B3)) |
A SUMIFS függvényt alkalmazzuk a segítő oszlopra (Összegzés, ha nagyobb, mint 0), és most pontosan kiszámítja az összeget.
| 1 | = SUMIFS (E3: E9, D3: D9, "> 0") |
A segítő oszlop könnyen létrehozható és könnyen olvasható, de érdemes egyetlen képlettel rendelkeznie a feladat végrehajtásához. Erről a következő részben lesz szó.
A terek kezelése üres cellákként - segítő oszlop nélkül
Ha a csak szóközöket tartalmazó cellákat ugyanúgy kell kezelni, mintha üresek lennének, de a segítő oszlop használata nem megfelelő, akkor a SUMPRODUCT függvényt a LEN és a TRIM függvényekkel kombinálva használhatjuk a cellákkal kapcsolatos adatok összegzésére. nem üres Játékos nevek:
| 1 | = SUMPRODUCT (-(LEN (TRIM (B3: B9))> 0), D3: D9) |
Ebben a példában a SUMPRODUCT függvényt használjuk bonyolult „összeg ha” számítások elvégzésére. Nézzük végig a képletet.
Ez a végső képletünk:
| 1 | = SUMPRODUCT (-(LEN (TRIM (B3: B9))> 0), D3: D9) |
Először is, a SUMPRODUCT függvény felsorolja a két cellatartomány értékeinek tömbjét:
| 1 | = SUMPRODUCT (-(LEN (TRIM ({"A"; "B"; ""; "C"; ""; "XX"; ""}))> 0), {25; 10; 15; 5 ; 8; 17; 50) |
Ezután a TRIM funkció eltávolítja a vezető és a záró szóközöket Játékos nevek:
| 1 | = SUMPRODUCT (-(LEN ({"A"; "B"; ""; "C"; ""; "XX"; ""})> 0), {25; 10; 15; 5; 8; 17; 50) |
A LEN funkció kiszámítja a kivágott anyag hosszát Játékos nevek:
| 1 | = SUMPRODUCT (-({1; 1; 0; 1; 0; 2; 0}> 0), {25; 10; 15; 5; 8; 17; 50) |
A logikai teszttel (> 0) minden vágott Játékos a 0 karakternél hosszabb nevek értéke IGAZ:
| 1 | = SUMPRODUCT (-({TRUE; TRUE; FALSE; TRUE; FALSE; TRUE; FALSE}), {25; 10; 15; 5; 8; 17; 50) |
Ezután a kettős kötőjelek (-) alakítják át az IGAZ és HAMIS értékeket 1-re és 0-ra:
| 1 | = SUMPRODUCT ({1; 1; 0; 1; 0; 1; 0}, {25; 10; 15; 5; 8; 17; 50) |
A SUMPRODUCT függvény ezután megsokszorozza a tömbök minden bejegyzéspárját, hogy létrehozzon egy tömböt Pontszámok Csak Játékos nevek, amelyek nem üresek, vagy nem csak szóközökből állnak:
| 1 | = SUMPRODUCT ({25; 10; 0; 5; 0; 17; 0) |
Végül a tömb számai összeadódnak
| 1 | =57 |
További részletek a Boole-utasítások használatáról és a „-” parancsról a SUMPRODUCT függvényben itt találhatók
Összeg, ha nem üres a Google Táblázatokban
Ezek a képletek pontosan ugyanúgy működnek a Google Táblázatokban, mint az Excelben.
