Stiahnite si ukážkový zošit
Tento tutoriál ukáže, ako použiť funkciu SUMIFS na súčet údajov zodpovedajúcich prázdnym alebo prázdnym bunkám v Exceli a Tabuľkách Google.
Suma, ak je prázdna
Najprv si ukážeme, ako sčítať riadky s prázdnymi bunkami.
Funkcia SUMIFS sumarizuje údaje, ktoré spĺňajú určité kritériá.
Na zhrnutie všetkých môžeme použiť funkciu SUMIFS Výsledky pre Hráči v nasledujúcom príklade bez mien.
| 1 | = SUMIFS (C3: C8, B3: B8, "") |
Na zobrazenie prázdnej bunky v programe Excel používame dvojité úvodzovky („“). Náš príklad ignoruje Hráči A, B, C a D a súčet skóre pre neznáme Hráči.
Zaobchádzanie s priestormi ako s prázdnymi bunkami - s pomocným stĺpcom
Pri interakcii s prázdnymi bunkami v programe Excel musíte byť opatrní. Bunky sa vám môžu zdať prázdne, ale Excel ich nebude považovať za prázdne. K tomu môže dôjsť, ak bunka obsahuje medzery, zlomené riadky alebo iné neviditeľné znaky. Toto je bežný problém pri importe údajov do Excelu z iných zdrojov.
Ak potrebujeme ošetriť bunky, ktoré obsahujú iba medzery, rovnakým spôsobom, ako keby boli prázdne, potom vzorec v predchádzajúcom príklade nebude fungovať. Všimnite si, ako vzorec SUMIFS nepovažuje bunku B9 uvedenú nižšie („“) za prázdnu:
Na to, aby sme s bunkou obsahujúcou iba medzery zaobchádzali, ako by to bola prázdna bunka, môžeme pridať pomocný stĺpec s funkciou TRIM, aby sme z hodnoty každej bunky odstránili nadbytočné medzery:
| 1 | = OBLOŽIŤ (B3) |
Na pomocný stĺpec použijeme funkciu SUMIFS a tá teraz vypočíta súčet presne.
| 1 | = SUMIFS (E3: E9, D3: D9, "") |
Pomocný stĺpček sa ľahko vytvára a je ľahko čitateľný, ale na splnenie úlohy by ste možno chceli mať jeden vzorec. Toto je popísané v ďalšej časti.
Zaobchádzanie s priestormi ako s prázdnymi bunkami - bez stĺpca pomocníka
Ak pomocný stĺpček nie je vhodný pre vaše potreby, môžete na sčítanie prázdnych riadkov použiť funkciu SUMPRODUCT v kombinácii s funkciami LEN a TRIM.
| 1 | = SUMPRODUKT (-(DĹŽKA (TRIM (B3: B9)) = 0), D3: D9) |
V tomto prípade používame funkciu SUMPRODUCT na vykonanie komplikovaného výpočtu „súčet if“. Poďme sa pozrieť na vzorec.
Toto je náš konečný vzorec:
| 1 | = SUMPRODUKT (-(DĹŽKA (TRIM (B3: B9)) = 0), D3: D9) |
Najprv funkcia SUMPRODUCT uvádza pole hodnôt z dvoch rozsahov buniek:
| 1 | = SUMPRODUCT (-(LEN (TRIM ({"A"; "B"; ""; "C"; ""; "XX"; ""})) = 0), {25; 10; 15; 5 ; 8; 17; 50) |
Potom funkcia TRIM odstráni úvodné a koncové medzery z Hráč mená:
| 1 | = SUMPRODUCT (-(LEN ({"A"; "B"; ""; "C"; ""; "XX"; ""}) = 0), {25; 10; 15; 5; 8; 17; 50) |
Funkcia LEN vypočíta dĺžky orezaných Hráč mená:
| 1 | = SUMPRODUCT (-({1; 1; 0; 1; 0; 2; 0} = 0), {25; 10; 15; 5; 8; 17; 50) |
Pri logickom teste (= 0) budú všetky orezané Hráč názvy s 0 znakmi sa zmenia na TRUE:
| 1 | = SUMPRODUCT (-({FALSE; FALSE; TRUE; FALSE; TRUE; FALSE; TRUE}), {25; 10; 15; 5; 8; 17; 50) |
Ďalej dvojité pomlčky (-) konvertujú hodnoty TRUE a FALSE na 1 s a 0 s:
| 1 | = SUMPRODUCT ({0; 0; 1; 0; 1; 0; 1}, {25; 10; 15; 5; 8; 17; 50) |
Funkcia SUMPRODUCT potom znásobí každú dvojicu záznamov v poliach a vytvorí pole Výsledky len pre Hráč názvy, ktoré sú prázdne alebo sú vyrobené iba z medzier:
| 1 | = SUMPRODUCT ({0; 0; 15; 0; 8; 0; 50) |
Nakoniec sa čísla v poli sčítajú:
| 1 | =73 |
Viac podrobností o použití booleovských príkazov a príkazu „-“ vo funkcii SUMPRODUCT nájdete tu.
Suma, ak je prázdna, v Tabuľkách Google
Tieto vzorce fungujú v Tabuľkách Google úplne rovnako ako v programe Excel.
