SUBTOTAL patří mezi velmi praktické excelové funkce – jedná se totiž o typ výpočtu, který dokáže zastoupit až 11 dalších funkcí. SUBTOTAL je mj. automaticky používán v nástrojích Tabulka, Souhrny a Kontingenční tabulka. Její používání v buňce nemusí být ovšem příliš jasné, resp. tato funkce se může zpočátku jevit poněkud matoucím dojmem. Pojďme se tedy na ni podívat.

Kdy používat SUBTOTAL?

Funkci SUBTOTAL v Excelu použijete, pokud často filtrujete data, skrýváte či seskupujete řádky a potřebujete souhrnné výsledky z viditelných nebo všech buněk. Funkci nelze užít při skrývání sloupců, skryté sloupce nerespektuje.

Syntaxe

SUBTOTAL(konstanta_funkce;odkaz1;odkaz2; atd.)

Konstanta_funkce je číslo od 1 do 11 nebo od 101 do 111, které určuje typ funkce použité při výpočtu souhrnů v seznamu.

Odkaz1, odkaz2 atd. je 1 až 254 oblastí nebo odkazů (v Excelu 2007 >), u kterých má být proveden souhrn. Konstanta funkce 101 až 111 je součástí Excelu až od verze 2003. Pokud budete výpočet SUBTOTAL s touto konstantou otevírat v Excelu 2002 nebo starším, výsledkem výpočtu bude bohužel chyba.

Zahrnuje skryté hodnoty vs. Ignoruje skryté hodnoty

Ať použijete konstantu 1 – 11 či 101 – 111, výsledek funkce SUBTOTAL při filtrování automatickým filtrem bude vždy stejný.

Rozdíl bude patrný v okamžiku, kdy začnete ručně skrývat nebo seskupovat řádky.

Řádky 4 až 8 jsou skryty (označení+pravá myš+příkaz SKRÝT), řádek 10 je seskupen (označení+LSHIFT+LALT+kurzorová šipka doprava). Nyní lze sledovat rozdíl v použitých konstantách. Konstanty 101 až 111 spočítají pouze viditelné buňky, konstanty 1 až 11 všechny včetně skrytých.

Pokud budete kombinovat automatický filtr a ruční skrývání nebo seskupování, Excel neumí tyto dva stavy pro funkci SUBTOTAL rozlišit a dává přednost automatickému filtru. Znamená to, že výsledek funkce SUBTOTAL s konstantou 1 – 11 a 101 – 111 bude při zapnutém automatickém filtru a skrývání či seskupování řádků vždy stejný.

Další zajímavou vlastností funkce SUBTOTAL je nezahrnování již existujících výsledků funkce SUBTOTAL do vstupů jiné funkce SUBTOTAL. Prakticky to znamená, že když máme několik tabulek pod sebou a na každou z nich uplatňujeme SUBTOTAL, tak pokud počítáme „globální“ SUBTOTAL ze všech uvedených dat ve sloupci všech tabulek, pro výslednou funkci SUBTOTAL jsou vynechávány dílčí SUBTOTAL výsledky, čímž se data spočítají správně (nezdvojí se). Tento stav lze velmi dobře sledovat při použití Excelového nástroje Souhrny.

Pokud použijete výsledek funkce SUBTOTAL z řádku souhrnů nástroje Tabulka jako vstup do jiného vzorce, funguje vše v pořádku do okamžiku, než řádek souhrnů vypnete. V tomto případě výsledek výpočtu zobrazí chybu. Pokud řádek souhrnů aktivujete, výsledek bude opět v pořádku.

Potřebujete znát více? Rádi Vám nabídneme školení a konzultace (na naších nových stránkách) vytvořené na míru konkrétním požadavkům.