Kapitola 17 · 17.18
Sčítanie iba filtrovaných riadkov, aj s ďalšou podmienkou
Zoznam predajov vyfiltrujete podľa jednej kategórie, ale súčet pod ním stále ukazuje sumu za všetky riadky. Je to správne: SUM sčíta aj riadky, ktoré filter skrýva. Ak chcete sčítať iba to, čo vidíte, použite SUBTOTAL. A ak chcete v rámci filtrovaných riadkov uplatniť ďalšiu podmienku alebo vynechať chybové hodnoty, poslúžia vám ďalšie dva praktické vzorce.
Ako príklad použijeme zoznam predajov kvetinárstva Bloemisterij Het Zonnetje: v stĺpci A je dátum, v B produkt, v C kategória a v D suma, od riadka 2 po riadok 300. Na hlavičkovom riadku je nastavený filter (pozrite Zoradenie a filtrovanie).
Kam umiestniť vzorce?
Nie pod zoznam: vzorec priamo pod zoznamom sa môže pri filtrovaní sám skryť a pri zoraďovaní sa posunúť. Ale ani len tak vedľa zoznamu: filter skrýva celé riadky, takže vzorec v bunke F3 zmizne spolu s riadkom 3, ak ho filter vyradí. Preto ich umiestnite do hlavičkového riadka (riadok 1, vedľa nadpisov) alebo na samostatný hárok. V príklade sú v riadku 1, od stĺpca F.
Súčet viditeľných riadkov
=SUBTOTAL(109;D2:D300)
Prvé číslo určuje výpočet. 9 znamená súčet, 109 tiež, s jedným rozdielom: 109 vynechá aj riadky, ktoré ste skryli ručne (pravé tlačidlo myši, Skryť). 9 vynechá iba riadky skryté filtrom. Ak teda chcete sčítať to, čo vidíte, použite 109.
| Výpočet | Filtrované | Aj ručne skryté |
|---|---|---|
| SÚČET | 9 | 109 |
| Počet vyplnených buniek | 3 | 103 |
| Stredný bod | 1 | 101 |
| Maximum | 4 | 104 |
Počet viditeľných riadkov zistíte vzorcom =SUBTOTAL(103;B2:B300).

Viditeľné riadky a ďalšia podmienka
Vyfiltrovali ste kategóriu Boeketten a v rámci nej chcete sčítať iba smútočné aranžmány. SUMIFS na filter neprihliada a SUBTOTAL nepozná podmienky. Na internete nájdete na tento účel vzorec so SUMPRODUCT, SUBTOTAL a OFFSET. V Euro-Office však vráti chybovú hodnotu #NUM!. S pomocným stĺpcom to funguje, a navyše jednoduchšie:
- Vedľa zoznamu vytvorte stĺpec Môže zobraziť, tu stĺpec E. Vyberte rozsah E2:E300, napíšte =SUBTOTAL(103;D2) a stlačte Ctrl+Enter. Každý riadok dostane hodnotu 1, kým je viditeľný, a 0, keď ho filter skryje.
- Sčítajte pomocou SUMIFS a pomocný stĺpec zahrňte ako ďalšiu podmienku: =SUMIFS(D2:D300;B2:B300;"Rouwstuk";E2:E300;1).
Keď filter zmeníte, pomocný stĺpec aj súčet sa okamžite prepočítajú.
Vynechanie chybových hodnôt
Ak sa niekde v stĺpci nachádza #N/A alebo #DIV/0!, aj SUM a SUBTOTAL vrátia chybovú hodnotu. AGGREGATE dokáže chyby vynechať:
=AGGREGATE(9;7;D2:D300)
Číslo 9 opäť znamená súčet, 7 znamená: vynechať skryté riadky aj chybové hodnoty.
Na čo si dať pozor
- Skryté stĺpce sa do výpočtu započítavajú: SUBTOTAL berie do úvahy iba skryté riadky.
