Stiahnite si ukážkový zošit
Tento tutoriál ukáže, ako použiť funkciu SUMIFS na súčet údajov týkajúcich sa prázdnych alebo prázdnych buniek v Exceli a Tabuľkách Google.
Suma, ak nie je prázdna
Najprv ukážeme, ako sčítať údaje týkajúce sa prázdnych buniek.
Na zhrnutie všetkých môžeme použiť funkciu SUMIFS Výsledky pre Hráči s prázdnymi menami.
| 1 | = SUMIFS (C3: C8, B3: B8, "") |
Ak chceme zhrnúť riadky s prázdnymi bunkami, vylúčime ich Výsledky s chýbajúcim Hráč mená. Vo funkcii SUMIFS používame kritériá „nerovná sa prázdne“ („“).
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:
| 1 | = SUMIFS (D3: D9, B3: B9, "") |
Na to, aby sme s bunkou obsahujúcou iba medzery zaobchádzali tak, ako by to bola prázdna bunka, môžeme pridať pomocný stĺpec pomocou funkcií LEN a TRIM na identifikáciu Hráči s menami.
Funkcia TRIM odstráni nadbytočné medzery zo začiatku a konca hodnoty každej bunky a funkcia LEN potom spočíta počet zostávajúcich znakov. Ak je výsledok funkcie LEN 0, potom Hráč meno musí byť prázdne alebo môže obsahovať iba medzery:
| 1 | = DĹŽKA (TRIM (B3)) |
Na pomocný stĺpec použijeme funkciu SUMIFS (súčet je väčší ako 0) a teraz vypočíta súčet presne.
| 1 | = SUMIFS (E3: E9, D3: D9, "> 0") |
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 je potrebné zaobchádzať s akýmikoľvek bunkami obsahujúcimi iba medzery rovnakým spôsobom, ako keby boli prázdne, ale použitie pomocného stĺpca nie je vhodné, potom môžeme na zhrnutie údajov týkajúcich sa buniek použiť funkciu SUMPRODUCT v kombinácii s funkciami LEN a TRIM obsahujúce prázdne Hráč mená:
| 1 | = SUMPRODUKT (-(DĹŽKA (OSTRIH (B3: B9))> 0), D3: D9) |
V tomto prípade používame funkciu SUMPRODUCT na vykonávanie komplikovaných výpočtov „sum if“. Poďme sa pozrieť na vzorec.
Toto je náš konečný vzorec:
| 1 | = SUMPRODUKT (-(DĹŽKA (OSTRIH (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 viac ako 0 znakmi sa zmenia na TRUE:
| 1 | = SUMPRODUCT (-({TRUE; TRUE; FALSE; TRUE; FALSE; TRUE; FALSE}), {25; 10; 15; 5; 8; 17; 50) |
Ďalej dvojité pomlčky (-) konvertujú hodnoty TRUE a FALSE na 1 s a 0 s:
| 1 | = SUMPRODUCT ({1; 1; 0; 1; 0; 1; 0}, {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áč mená, ktoré nie sú prázdne alebo nie sú vyrobené iba z medzier:
| 1 | = SUMPRODUCT ({25; 10; 0; 5; 0; 17; 0) |
Nakoniec sa čísla v poli sčítajú
| 1 | =57 |
Viac podrobností o použití booleovských príkazov a príkazu „-“ vo funkcii SUMPRODUCT nájdete tu
Suma, ak nie je prázdna, v Tabuľkách Google
Tieto vzorce fungujú v Tabuľkách Google úplne rovnako ako v programe Excel.
