Kapitola 17 · 17.18
Sčítání pouze vyfiltrovaných řádků, i s další podmínkou
Vyfiltrujete seznam prodejů podle jedné kategorie, ale součet pod ním stále ukazuje částku za všechny řádky. To je správně: SUMA sčítá i řádky, které filtr skryje. Chcete-li sečíst jen to, co vidíte, použijte SUBTOTAL. A pokud chcete v rámci vyfiltrovaných řádků uplatnit ještě další podmínku nebo přeskočit chybové hodnoty, máte k dispozici dva další užitečné vzorce.
Jako příklad použijeme seznam prodejů firmy Bloemisterij Het Zonnetje: ve sloupci A je datum, v B produkt, v C kategorie a v D částka, od řádku 2 do řádku 300. Na řádku záhlaví je nastaven filtr (viz Řazení a filtrování).
Kam vzorce umístit?
Ne pod seznam: vzorec přímo pod seznamem se může při filtrování sám skrýt a při řazení se posunout. Ale ani jen tak vedle seznamu: filtr skrývá celé řádky, takže vzorec v buňce F3 zmizí také, když řádek 3 vyfiltrujete. Umístěte je proto do řádku záhlaví (řádek 1, vedle nadpisů) nebo na samostatný list. V příkladu jsou v řádku 1, od sloupce F.
Součet viditelných řádků
=SUBTOTAL(109;D2:D300)
První číslo určuje výpočet. 9 znamená součet, 109 také, ale s jedním rozdílem: 109 přeskakuje i řádky, které jste skryli ručně (pravé tlačítko myši, Skrýt). 9 přeskakuje jen řádky skryté filtrem. Pokud tedy chcete sečíst to, co vidíte, použijte 109.
| Výpočet | Vyfiltrované | I ručně skryté |
|---|---|---|
| SUMA | 9 | 109 |
| Počet vyplněných buněk | 3 | 103 |
| Střední bod | 1 | 101 |
| Maximum | 4 | 104 |
Počet viditelných řádků zjistíte pomocí =SUBTOTAL(103;B2:B300).

Viditelné řádky a k tomu další podmínka
Vyfiltrovali jste kategorii Boeketten a chcete v ní sečíst jen smuteční vazby. SUMIFS na filtr nebere ohled, SUBTOTAL zase nezná podmínky. Na internetu k tomu najdete vzorec s funkcemi SOUČIN.SKALÁRNÍ, SUBTOTAL a POSUN. V Euro-Office však vrací chybovou hodnotu #NUM!. S pomocným sloupcem to funguje, a navíc jednodušeji:
- Vedle seznamu vytvořte sloupec Může zobrazovat, zde sloupec E. Vyberte E2:E300, zadejte =SUBTOTAL(103;D2) a stiskněte Ctrl+Enter. Každý řádek dostane 1, dokud je viditelný, a 0, když ho filtr skryje.
- Sečtěte pomocí SUMIFS a pomocný sloupec přidejte jako další podmínku: =SUMIFS(D2:D300;B2:B300;"Rouwstuk";E2:E300;1).
Když filtr změníte, pomocný sloupec i součet se okamžitě přepočítají.
Přeskočení chybových hodnot
Pokud je někde ve sloupci #N/A nebo #DIV/0!, vrátí SUMA i SUBTOTAL také chybovou hodnotu. AGGREGATE umí chyby přeskočit:
=AGGREGATE(9;7;D2:D300)
Číslo 9 je opět součet, 7 znamená: přeskočit skryté řádky i chybové hodnoty.
Úskalí
- Skryté sloupce se do výpočtu započítávají: SUBTOTAL bere v úvahu jen skryté řádky.
