Funkci SUBTOTAL možná znáte jako součet, který reaguje na filtr. Jenže SUBTOTAL umí mnohem víc. Dokáže ignorovat jiné mezisoučty, automaticky přečíslovat jen viditelné řádky, pomoct s výpočty podle podmínky, vytvořit souhrny přes kartu Data, a dokonce obarvit každý druhý viditelný řádek tak, aby se formátování měnilo spolu s filtrem. V tomto videu si ukážeme pět triků, které využívají jednu důležitou vlastnost funkce SUBTOTAL: umí pracovat jen s tím, co je právě vidět.
Excelový soubor ke stažení:
SUBTOTAL ignoruje jiné SUBTOTAL
Začněme vlastností, která je velmi praktická u souhrnných tabulek. V příkladu máme prodejní data rozdělená například podle regionů. Za každou sekcí máme celkový součet. Na posledním řádku chceme sečíst tržby celkem. Pokud mezisoučty vznikly pomocí funkce SUBTOTAL, tak můžeme na řádku celkového součtu použít rovněž funkci SUBTOTAL, označit všechny hodnoty, včetně mezisoučtů a funkce vrátí správný výsledek.
SUBTOTAL totiž funguje jinak. Pokud ve svém rozsahu narazí na další buňku obsahující funkci SUBTOTAL, nezahrne ji do výsledku. SUBTOTAL je navržený přímo pro souhrnné reporty. Excel rozpozná, že v rozsahu existuje další SUBTOTAL, a zabrání dvojímu započítání.
Automatické číslování pouze viditelných řádků
Pomocí funkce SUBTOTAL můžeme automaticky číslovat pouze řádky, které zůstaly po filtrování viditelné. V tabulce chceme v prvním sloupci očíslovat řádky, nicméně chceme, aby číslování reagovalo na filtr. Pokud bychom do sloupce jednoduše napsali pořadová čísla, tak by po vyfiltrování zůstala tato čísla beze změny a číslování by nenavazovalo. Stejně tak, kdybychom k očíslování použili třeba funkci SEQUENCE.
Pro tento trik použijeme funkci SUBTOTAL. Ve funkci vybereme funkci 103, což je funkce POČET2 neboli funkce COUNTA, která umí počítat textové hodnoty. A ve druhém parametru funkce vytvoříme dynamické rozpětí. Označíme první buňku s textem ku druhé buňce a první buňku pevně zafixujeme. Funkci stáhneme dolů a tím se vytvořilo dynamické rozpětí, kde funkce SUBTOTAL čísluje řádky, jelikož počítá textové hodnoty postupně.
Zkusíme vyfiltrovat tabulku.
Takto zapsaná funkce SUBTOTAL ignoruje skryté řádky. Takže Excel vždy počítá, kolik neprázdných a zároveň viditelných hodnot se nachází od začátku seznamu po aktuální řádek. Nejde tedy o klasické číslo řádku, ale o pořadí mezi aktuálně viditelnými záznamy.
A proč jsme vybrali funkci Proč 103 a ne 3? Funkční čísla 1 až 11 ignorují řádky skryté filtrem, ale mohou zahrnovat ručně skryté řádky. Varianta 101 až 111 ignoruje i ručně skryté řádky. Takže 103 je bezpečnější, pokud chceme opravdu pracovat jen s viditelnými záznamy.
Obarvení každého druhého viditelného řádku
Další trik na první pohled vůbec nevypadá jako použití SUBTOTAL. Pomocí něj si ale můžeme vytvořit střídavé barvy řádků, které se po filtrování automaticky přepočítají. Řekněme, že se vám líbí formátování excelových tabulek, tedy situaci, kdy máme obarvený každý druhý řádek, nicméně abychom získali toto formátování, tak bychom data museli změnit na excelovou tabulku. Co když ale z nějakého důvodu tuto změnu provést nechcete, ale stejně byste rádi dosáhli efektu obarvených řádků? Opět, pokud ručně obarvíte řádky, tak se řádky po vyfiltrování samozřejmě nepřebarví.
Pro tento trik použijeme podmíněné formátování a dvě funkce.
Funkce SUBTOTAL počítá neprázdné viditelné buňky ve sloupci od začátku seznamu po aktuální řádek. Následně funkce MOD vezme číslo, které vrátí funkce SUBTOTAL a zjistí zbytek po vydělení čísla dvěma. U sudého čísla bude zbytek nula a u lichého čísla bude zbytek jedna. Tím se budou na řádcích vždy střídat jedničky a nuly. A jelikož podmíněné formátování reaguje na pravdy a v Excelu jednička znamená pravdu, tak se řádky, kde funkce MOD vrátí jedna obarví.
Zkusíme vyfiltrovat řádky a barevné řádky se přizpůsobí.
Možná si říkáte, proč k obarvení ob řádku nepoužít klasické MOD(ŘÁDEK();2)
Toto řešení sice obarví každý druhý fyzický řádek, ale po filtrování může nastat:
- první viditelný řádek barevný,
- další viditelný řádek také barevný,
- potom dva bez barvy.
Protože Excel stále vyhodnocuje číslo skutečného řádku. SUBTOTAL naopak počítá jen viditelné řádky.
Řádek souhrnu
Další trik je trochu jiný, protože tentokrát SUBTOTAL nemusíme psát ručně. Excel ho dokáže vytvořit za nás. Nejdřív musíme mít data seřazená podle sloupce, podle kterého chceme souhrny. V tomto příkladu máme tabulku seřazenou dle regionu.
Na kartě Data vybereme Souhrn a nastavíme souhrny. Souhrn chceme podle regionu a chceme sčítat tržby.
Po potvrzení se do tabulky vloží souhrny a ovládací tlačítka, pomocí kterých můžeme sbalovat a rozbalovat tabulku dle potřeby. Výpočet SUMA je v tabulce pomocí funkce SUBTOTAL.
Zjištění, zda je řádek viditelný
Samotný SUBTOTAL umí například sečíst všechny viditelné tržby. Co když ale chceme z viditelných řádků sečíst pouze určitou část? Třeba jen pro konkrétního obchodníka nebo konkrétní produkt. Řekněme, že chceme pro vybrané regiony sečíst tržby, ale chceme dát uživateli možnost filtrovat tabulku, jak bude chtít. Dopředu tedy neznáme filtr a kritéria, která uživatel použije. Cílem je, aby se vždy sečetli tržby pro regiony jen podle aktuálně viditelných řádků.
Do pomocného sloupce vložíme funkci SUBTOTAL.
Pokud je řádek viditelný a buňka A4 není prázdná, dostaneme 1. Pokud ho filtr schová, dostaneme 0. V souhrnné tabulce použijeme funkci SUMIFS se dvěma podmínkami.
Zkusíme vyfiltrovat tabulku podle stavu faktury a výpočet SUMIFS se přepočítá na základě aktivního filtru.
Toto řešení je praktické v situacích, kdy dopředu nevíme, podle jakých kritérií se bude tabulka filtrovat a nechceme třeba pro každé kritérium tvořit rozbalovací seznamy atd.
Dopředu tak nemusíme vědět, které řádky budou viditelné. Stačí, že po vyfiltrování pomocný sloupec vrátí do funkce pouze viditelné řádky.
