Snad každý mírně pokročilý, kdo pracuje s Excelem, umí používat na součet funkci SUMA(). Funguje to jednoduše a spolehlivě, ale pouze do chvíle, kdy je potřeba sečíst jen vyfiltrované buňky v nějakém výběru (tj. ty, co se mají sečíst, se nevymazávají, jsou jen skryty, ať již zmíněným filtrem, nebo třeba s pomocí skrývání řádků). Funkce SUMA ale sčítá všechny buňky v klasickém sloupcovém či řádkovém výběru, včetně skrytých. Tedy samozřejmě lze v rámci zadávaných parametrů dávat do součtů vybrané samostatné oblasti či jednotlivé buňky, ale to je u filtrovaných výběrů značně neefektivní postup.

Výběr nesousedících buněk, nebo oblastí v Excelu

 

Než přejdu k řešení nastíněného součtu jen viditelných buněk v rámci filtrování sloupců, nebo řádků, zmíním ještě jednu užitečnou věc. Jak se v Excelu provádí výběr nesousedících buněk, nebo oblastí? Třeba pokud v rámci sčítání do vzorce =SUMA() chceme sčítat různé buňky a výběry oblastí, podržíme při výběru tlačítko CRTL a souběžně provádíme výběry nesousedících oblastí/buněk myší, a tlačítko CTRL pustíme až, když máme vybrané vše, co chceme. Když se při výběru spleteme, stačí do chybně vybrané oblasti či buňky kliknout za souběžného držení CTRL znovu a oblast se z výběru zase odznačí.

 

Výpočet součtu pouze vyfiltrovaných buněk (bez skrytých)

 

Pokud chceme, aby (třeba dynamicky) Excel sčítal vždy jen viditelné buňky sloupcového výběru, kde byl použitý filtr (a při změně filtru zase sečetl nové/jiné viditelné, tj. vždy pouze viditelné), pak se místo funkce SUMA() používá funkce SUBTOTAL().

Funkce se zapisuje jako SUBTOTAL(konstanta;[odkaz1];[odkaz2];…)

 

Pod konstantou, jejíž zadání je povinné, se skrývají čísla 1 až 11, anebo 101 až 111, jakou funkci označují a jakým způsobem s ní počítají, to je vidět v tabulce níže. Nás aktuálně zajímá SOUČET, tedy použijeme jako konstantu číslo 9. Ukázkový zápis funkce SUBTOTAL může být: =SUBTOTAL(9;A2:A16) nebo =SUBTOTAL(109;A2:A16)

 

A ještě pozor na to, proč se rozlišuje, zda součet (SUMA) v rámci funkce SUBTOTAL označíme číslem 9, nebo naopak 109. Rozdíl je v tom, že parametr 9 se používá v případě, kdy do takto definovaného součtu jsou zahrnuty ručně skryté řádky a 109 vyloučí ze součtu i tyto řádky skryté ručně. Kdo si tudíž chce být jistý, že se mu sčítá jen to, co opravdu je vidět, použije parametr 109. Vyfiltrované buňky jsou vyloučené pokaždé.

Jak si lze vyzkoušet, při změně filtru se hodnota výpočtu dynamicky mění. Pro různé sčítání a vytváření souhrnů se používají třeba i volby Data/Souhrny, samostatnou kapitolou jsou pak kontingenční tabulky, k tomu se, předpokládám, ještě někdy v budoucnu vrátím.

 

Konstanta funkce (počítá i s ručně skrytými hodnotami, ale vylučuje hodnoty skryté datovým filtrem)	Konstanta fce (ignoruje všechny skryté hodnoty, jak filtrované, tak skryté ručně)	Funkce
1	101	PRŮMĚR
2	102	POČET
3	103	POČET2
4	104	MAX
5	105	MIN
6	106	SOUČIN
7	107	SMODCH.VÝBĚR
8	108	SMODCH
9	109	SUMA
10	110	VAR.VÝBĚR
11	111	VAR