Pokud váš excelový list obsahuje skryté řádky, filtrovaná data nebo seskupená data, použijte funkci Excel SUBTOTAL. Funkce SUBTOTAL může zahrnout nebo vyloučit skryté hodnoty ve výpočtech. Kromě zjištění součtu skupiny dat může Excel vypočítat průměr, maximum, minimum, směrodatnou odchylku a rozptyl vašich dat. Zde je návod, jak vložit mezisoučty do Excelu.
Pokyny v tomto článku platí pro Excel pro Microsoft 365, Excel 2019 a Excel 2016.
Syntaxe funkce SUBTOTAL
Použijte funkci SUBTOTAL v Excelu k sumarizaci hodnot v listu různými způsoby. Je zvláště užitečné, když váš list obsahuje skryté řádky, které chcete zahrnout do výpočtu.
Syntaxe funkce SUBTOTAL je: SUBTOTAL(číslo_funkce, ref1, ref2, …)
Argument function_num je povinný a určuje typ matematické operace, která se má použít pro mezisoučet. Funkce SUBTOTAL dokáže sčítat čísla, vypočítat průměrnou hodnotu vybraných čísel, najít maximální a minimální hodnoty v rozsahu, spočítat počet hodnot ve vybraném rozsahu a další.
Funkce SUBTOTAL ignoruje buňky, které neobsahují data, a buňky s nečíselnými hodnotami.
Tento argument je číslo a závisí na tom, zda chcete do výsledku zahrnout skryté řádky nebo z výsledku vyloučit skryté řádky. Tyto řádky mohou být ručně skryty nebo skryty filtrem.
Argumenty function_num zahrnují:
| Function Task | function_num | function_num |
|---|---|---|
| (zahrnuje skryté hodnoty) | (nezahrnuje skryté hodnoty) | |
| PRŮMĚR | 1 | 101 |
| COUNT | 2 | 102 |
| COUNTA | 3 | 103 |
| MAX | 4 | 104 |
| MIN | 5 | 105 |
| PRODUCT | 6 | 106 |
| STDEV | 7 | 107 |
| STDEVP | 8 | 108 |
| SUM | 9 | 109 |
| VAR | 10 | 110 |
| VARP | 11 | 111 |
Referenční argumenty function_num 1 až 11 zahrnují pouze hodnoty ve skrytých řádcích, když ke skrytí řádků používáte příkaz Hide. Při použití příkazu Filtr nezahrnují výpočty SUBTOTAL výsledky skrytých filtrů.
Argument ref1 je povinný. Toto jsou buňky používané k výpočtu výsledků vybraného argumentu číslo_funkce. Tento argument může být hodnota, jedna buňka nebo rozsah buněk.
Argumenty ref2, … jsou nepovinné. Toto jsou další buňky, které jsou zahrnuty do výpočtu.
Použijte funkci SUBTOTAL se skrytými řádky
Funkce Excelu lze zadat ručně nebo pomocí dialogového okna Argumenty funkcí. Abychom ilustrovali, jak ručně zadat funkci pomocí řádku vzorců, následující příklad používá argument COUNT function_num k počítání počtu hodnot ve viditelných řádcích a ve viditelných i skrytých řádcích.
Použití funkce SUBTOTAL k počítání počtu řádků v listu:
- Začněte s listem, který obsahuje více řádků dat.
- Vyberte buňku, která bude obsahovat počet viditelných řádků.
-
Na panelu funkcí zadejte =MEDZISČET. Při psaní Excel navrhuje funkci. Dvakrát klikněte na funkci SUBTOTAL.
Chcete-li použít dialogové okno Argumenty funkcí ke vstupu do funkce SUBTOTAL, přejděte na Formulas a vyberte Math & Trig >SUBTOTAL.
Image -
V rozbalovací nabídce, která se zobrazí, poklepejte na argument 102 – COUNT argument function_num.
Image -
Napište čárku (,).
Image -
V listu vyberte buňky, které chcete zahrnout do vzorce.
Image -
Stisknutím Enter zobrazíte výsledek v buňce, kterou jste vybrali v kroku 2.
Image - Vyberte buňku, která bude obsahovat počet viditelných a skrytých řádků.
- Na panelu funkcí zadejte =MEDZISČET. Při psaní Excel navrhuje funkci. Dvakrát klikněte na funkci SUBTOTAL.
- V rozbalovací nabídce, která se zobrazí, poklepejte na argument 2 – COUNT argument function_num a poté zadejte čárku (,).
-
V listu vyberte buňky, které chcete zahrnout do vzorce, a stiskněte Enter.
Image -
Skrýt několik řádků dat. V tomto příkladu byly skryty řádky s prodejem pod 100 000 $.
Image
Použijte funkci SUBTOTAL s filtrovanými daty
Použití funkce SUBTOTAL na filtrovaná data ignoruje data v řádcích, které byly filtrem odstraněny. Pokaždé, když se změní kritéria filtru, funkce přepočítá, aby zobrazila mezisoučet pro viditelné řádky.
Použití funkce SUBTOTAL k zobrazení rozdílů ve výsledcích výpočtu při filtrování dat:
-
Vytvořte vzorce SUBTOTAL. Vytvořte například vzorce k určení mezisoučtu a průměrných hodnot filtrovaných dat.
Nezáleží na tom, zda použijete argument function_num pro viditelné nebo skryté řádky. Oba argumenty poskytují stejný výsledek ve filtrovaných datech.
Image - Vyberte libovolnou buňku v sadě dat.
-
Přejděte na Home a vyberte Sort & Filter > Filter.
Image -
Pomocí rozbalovacích šipek filtrujte data listu.
Image -
Všimněte si, jak se hodnoty mění pokaždé, když vyberete jiná kritéria filtru.
Image
Použijte funkci SUBTOTAL se seskupenými daty
Když jsou data seskupena, existuje způsob, jak aplikovat funkci SUBTOTAL na každou jednotlivou skupinu a poté vypočítat celkový součet pro celý soubor dat.
- Vyberte libovolnou buňku v sadě dat.
-
Vyberte Data > Mezisoučet pro otevření dialogového okna Mezisoučet.
Image - Vyberte Při každé změně v rozevírací šipku a zvolte seskupení, do kterého se bude každý mezisoučet počítat.
- Vyberte rozbalovací šipku Použít funkci a zvolte číslo funkce.
- V seznamu Přidat mezisoučet do vyberte sloupec, na který bude vzorec použit.
-
Vybrat OK.
Image -
Pro každou skupinu dat se vloží mezisoučty a na konec souboru dat se vloží celkový součet.
Image - Chcete-li změnit funkci_číslo, zvýrazněte libovolnou buňku v sadě dat a vyberte Data > Mezisoučet. Poté proveďte své volby v dialogovém okně Subtotal.
