Výpočet součtu jen u vyfiltrovaných buněk, tj. část buněk může být skryta

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í CRTL 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

 

Upozorňujeme v souladu s legislativou EU, že tento web využívá cookies, jako naprostá většina všech stránek na internetu. Dalším používáním webu s jejich využitím souhlasíte. Více informací

Cookies na této webové stránce jsou povoleny, aby vám umožnily co nejsnažší procházení obsahu. Chcete-li i nadále používat tuto webovou stránku je třeba mít cookies povolené v prohlížeči a klepnout na tlačítko "Souhlasím", čímž si zpřístupníte obsah.

Zavřít