Stáhněte si ukázkový sešit
Tento tutoriál předvede, jak použít funkci SUMIFS k součtu dat souvisejících s prázdnými nebo neprázdnými buňkami v Excelu a Tabulkách Google.
Součet, pokud není prázdný
Nejprve si ukážeme, jak sečíst data týkající se prázdných buněk.
K sečtení všech můžeme použít funkci SUMIFS Skóre pro Hráči s neprázdnými jmény.
| 1 | = SUMIFS (C3: C8, B3: B8, "") |
Abychom sečetli řádky s neprázdnými buňkami, vyloučíme Skóre s chybějícími Hráč jména. Uvnitř funkce SUMIFS používáme kritéria „nerovná se prázdné“ („“).
Zacházení s mezerami jako s prázdnými buňkami - se sloupcem pomocníka
Při interakci s prázdnými buňkami v aplikaci Excel musíte být opatrní. Buňky se vám mohou zdát prázdné, ale Excel je nebude považovat za prázdné. K tomu může dojít, pokud buňka obsahuje mezery, řádky nebo jiné neviditelné znaky. Toto je běžný problém při importu dat do Excelu z jiných zdrojů.
Pokud potřebujeme ošetřit buňky, které obsahují pouze mezery, stejným způsobem, jako kdyby byly prázdné, pak vzorec v předchozím příkladu nebude fungovat. Všimněte si, jak vzorec SUMIFS nepovažuje buňku B9 níže (”“) za prázdnou:
| 1 | = SUMIFS (D3: D9, B3: B9, "") |
Chcete -li zacházet s buňkou obsahující pouze mezery, jako by to byla prázdná buňka, můžeme přidat pomocný sloupec pomocí funkcí LEN a TRIM k identifikaci Hráči se jmény.
Funkce TRIM odstraní nadbytečné mezery ze začátku a konce hodnoty každé buňky a funkce LEN poté spočítá počet zbývajících znaků. Pokud je výsledek funkce LEN 0, pak Hráč název musí být prázdný nebo pouze z mezer:
| 1 | = DÉLKA (OŘEZ (B3)) |
Aplikujeme funkci SUMIFS na pomocný sloupec (Součet, pokud je větší než 0), a nyní vypočítá součet přesně.
| 1 | = SUMIFS (E3: E9, D3: D9, "> 0") |
Pomocný sloupec se snadno vytváří a je snadno čitelný, ale možná budete chtít mít jediný vzorec pro splnění úkolu. To je popsáno v další části.
Zacházení s mezerami jako s prázdnými buňkami - bez pomocného sloupce
Pokud je požadováno zacházet s jakýmikoli buňkami obsahujícími pouze mezery stejným způsobem, jako kdyby byly prázdné, ale použití pomocného sloupce není vhodné, pak můžeme použít funkci SUMPRODUCT v kombinaci s funkcemi LEN a TRIM k sečtení dat týkajících se buněk obsahující neprázdné Hráč jména:
| 1 | = SUMPRODUCT (-(LEN (TRIM (B3: B9))> 0), D3: D9) |
V tomto příkladu používáme funkci SUMPRODUCT k provádění komplikovaných výpočtů „sum if“. Pojďme si projít vzorec.
Toto je náš konečný vzorec:
| 1 | = SUMPRODUCT (-(LEN (TRIM (B3: B9))> 0), D3: D9) |
Za prvé, funkce SUMPRODUCT uvádí pole hodnot ze dvou rozsahů buněk:
| 1 | = SUMPRODUCT (-(LEN (TRIM ({"A"; "B"; ""; "C"; ""; "XX"; ""}))> 0), {25; 10; 15; 5 ; 8; 17; 50) |
Poté funkce TRIM odstraní úvodní a koncové mezery z Hráč jména:
| 1 | = SUMPRODUCT (-(LEN ({"A"; "B"; ""; "C"; ""; "XX"; ""})> 0), {25; 10; 15; 5; 8; 17; 50) |
Funkce LEN vypočítá délky ořezaných Hráč jména:
| 1 | = SUMPRODUCT (-({1; 1; 0; 1; 0; 2; 0}> 0), {25; 10; 15; 5; 8; 17; 50) |
S logickým testem (> 0) jsou všechny oříznuty Hráč jména s více než 0 znaky se změní na PRAVDA:
| 1 | = SUMPRODUCT (-({TRUE; TRUE; FALSE; TRUE; FALSE; TRUE; FALSE}), {25; 10; 15; 5; 8; 17; 50) |
Dále dvojité pomlčky (-) převádějí hodnoty PRAVDA a NEPRAVDA na 1 s a 0 s:
| 1 | = SUMPRODUCT ({1; 1; 0; 1; 0; 1; 0}, {25; 10; 15; 5; 8; 17; 50) |
Funkce SUMPRODUCT pak vynásobí každou dvojici záznamů v polích a vytvoří pole Skóre pouze pro Hráč jména, která nejsou prázdná nebo nejsou vytvořena pouze z mezer:
| 1 | = SUMPRODUCT ({25; 10; 0; 5; 0; 17; 0) |
Nakonec jsou čísla v poli sečtena
| 1 | =57 |
Další podrobnosti o použití booleovských příkazů a příkazu „-“ ve funkci SUMPRODUCT najdete zde
Součet, pokud není prázdný, v Tabulkách Google
Tyto vzorce fungují v Tabulkách Google úplně stejně jako v Excelu.
