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 üres vagy üres celláinak megfelelő adatok összegzésére.
Összeg, ha üres
Először bemutatjuk, hogyan lehet összegezni a sorokat üres cellákkal.
A SUMIFS függvény összesíti azokat az adatokat, amelyek megfelelnek bizonyos feltételeknek.
Az összesítéshez használhatjuk a SUMIFS függvényt Pontszámok számára Játékosok nevek nélkül az alábbi példában.
| 1 | = SUMIFS (C3: C8, B3: B8, "") |
Dupla idézőjeleket („”) használunk az Excel üres cellájának ábrázolására. Példánk figyelmen kívül hagyja Játékosok A, B, C és D, és összegzi az ismeretlen pontszámokat Játékosok.
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 (""):
Ahhoz, hogy a csak szóközöket tartalmazó cellát üres cellaként kezelje, hozzáadhatunk egy segítő oszlopot a TRIM funkcióval, hogy eltávolítsuk a felesleges szóközöket az egyes cellák értékéből:
| 1 | = TRIM (B3) |
A SUMIFS függvényt alkalmazzuk a segítő oszlopra, és most pontosan kiszámítja az összeget.
| 1 | = SUMIFS (E3: E9, D3: D9, "") |
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 egy segítő oszlop nem megfelelő az Ön igényeinek, akkor a SUMPRODUCT függvényt a LEN és a TRIM funkcióval kombinálva használhatja az üres sorok összegzésére.
| 1 | = SUMPRODUCT (-(LEN (TRIM (B3: B9))) = 0), D3: D9) |
Ebben a példában a SUMPRODUCT függvényt használjuk egy bonyolult „összeg ha” számítás 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 karakterből álló nevek TRUE -ra változnak:
| 1 | = SUMPRODUCT (-({HAMIS; HAMIS; IGAZ; HAMIS; IGAZ; HAMIS; IGAZ)), {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 ({0; 0; 1; 0; 1; 0; 1}, {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 üres vagy csak szóközökből álló nevek:
| 1 | = SUMPRODUCT ({0; 0; 15; 0; 8; 0; 50) |
Végül a tömb számai összeadódnak:
| 1 | =73 |
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 üres a Google Táblázatokban
Ezek a képletek pontosan ugyanúgy működnek a Google Táblázatokban, mint az Excelben.
