Stáhněte si ukázkový sešit
Tento tutoriál předvede, jak pomocí funkce SUMIFS sčítat data odpovídající prázdným nebo prázdným buňkám v Excelu a Tabulkách Google.
Součet, pokud je prázdný
Nejprve si ukážeme, jak sečíst řádky s prázdnými buňkami.
Funkce SUMIFS shrnuje data, která splňují určitá kritéria.
K sečtení všech můžeme použít funkci SUMIFS Skóre pro Hráči v následujícím příkladu bez jmen.
| 1 | = SUMIFS (C3: C8, B3: B8, "") |
K vyjádření prázdné buňky v Excelu používáme uvozovky („“). Náš příklad ignoruje Hráči A, B, C a D a sečte skóre pro neznámé Hráči.
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:
Abychom s buňkou obsahující pouze mezery zacházeli, jako by to byla prázdná buňka, můžeme přidat pomocný sloupec s funkcí TRIM, abychom odstranili nadbytečné mezery z hodnoty každé buňky:
| 1 | = OŘEZAT (B3) |
Aplikujeme funkci SUMIFS na pomocný sloupec a ta nyní vypočítá součet přesně.
| 1 | = SUMIFS (E3: E9, D3: D9, "") |
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 pomocný sloupec není vhodný pro vaše potřeby, můžete použít funkci SUMPRODUCT v kombinaci s funkcemi LEN a TRIM k sečtení prázdných řádků.
| 1 | = SUMPRODUCT (-(LEN (TRIM (B3: B9)) = 0), D3: D9) |
V tomto příkladu použijeme funkci SUMPRODUCT k provedení komplikovaného výpočtu „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 0 znaky se změní na PRAVDA:
| 1 | = SUMPRODUCT (-({FALSE; FALSE; TRUE; FALSE; TRUE; FALSE; TRUE}), {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 ({0; 0; 1; 0; 1; 0; 1}, {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á jsou prázdná nebo jsou vytvořena pouze z mezer:
| 1 | = SUMPRODUCT ({0; 0; 15; 0; 8; 0; 50) |
Nakonec jsou čísla v poli sečtena:
| 1 | =73 |
Další podrobnosti o použití booleovských příkazů a příkazu „-“ ve funkci SUMPRODUCT najdete zde.
Součet je prázdný v Tabulkách Google
Tyto vzorce fungují v Tabulkách Google úplně stejně jako v Excelu.
