Excel funkcia SUBTOTAL + video
Funkcia SUBTOTAL sa využíva v Exceli pri tvorbe súčtových riadkov v tabuľkách. Ide o veľmi praktickú funkciu, ktorá zastupuje až 11 funkcií.
Zápis funkcie SUBTOTAL: “=SUBTOTAL(číslo_funkcie;odk1;…)”
Odk1 – predstavuje rozsah buniek, ktoré vyhodnocujeme
Číslo_funkcie – predstavuje číslo funkcie, ktorú si želáme použiť. Čísla 1-11 zahŕňajú skryté hodnoty a čísla 101-111 ignorujú skryté hodnoty
Číslo_funkcie (zahŕňa skryté hodnoty) | Číslo_funkcie (ignoruje skryté hodnoty) | Funkcia |
1 | 101 | AVERAGE |
2 | 102 | COUNT |
3 | 103 | COUNTA |
4 | 104 | MAX |
5 | 105 | MIN |
6 | 106 | PRODUCT |
7 | 107 | STDEV |
8 | 108 | STDEVP |
9 | 109 | SUM |
10 | 110 | VAR |
11 | 111 | VARP |
Súčtové riadky
Väčšina užívateľov Excelu robí pri tvorbe súčtového riadku dve chyby.
Súčtový riadok robia na konci tabuľky a nachádza sa hneď riadok za tabuľkou.
Nevýhodou takéhoto súčtového riadku je, že si po výsledky musíte vždy chodiť “niekam dole”, ak má tabuľka tisíce záznamov, je to veľmi nepraktické. To, že je súčtový riadok na spodnej časti tabuľky by až taká vážna chyba nebola.
Avšak to, že je hneď za tabuľkou už chybu predstavuje a to z toho dôvodu, že Excel ho považuje za súčasť tabuľky, takže napríklad pri tvorbe kontingenčnej tabuľky budú do tabuľky zarátané aj hodnoty súčtov. Dostaneme teda do tabuľky duplicitné súčty a výsledky budú nesprávne.
Ako prvé je potrebné oddeliť súčtový riadok od tabuľky, to znamená nechať jeden prázdny riadok medzi tabuľkou a súčtovým riadkom. Ja osobne preferujem súčtové riadky hore, takto máte vždy aktuálne súčty na očiach a nemusíte pre ne chodiť na koniec tabuľky.
Druhou veľmi zásadnou chybou je, že sa v súčtovom riadku používajú funkcie v základnom tvare napríklad: SUM, AVERAGE, MAX, MIN,… a pod. namiesto toho, aby použili funkciu SUBTOTAL a určili jej, akú funkciu má vykonávať.
Tento problém sa prejaví až pri filtrovaní. Súčtový riadok, kde sa nachádza priamo funkcia SUM bude rátať aj hodnoty, ktoré nevyhovujú filtračným kritériám.
Na obrázku “Súčtový riadok – SUM” je možné vidieť, že nami zvoleným kritériám vyhovujú iba tri záznamy, avšak súčet je stále rátaný zo všetkých buniek, aj skrytých. Problém nastáva pri tabuľkách kde sú stovky/tisícky záznamov, tam si nevšimnete, že súčty v súčtovom riadku nie sú vypočítané dobre.
Potrebujeme teda funkciu, ktorá vypočíta iba tie hodnoty, ktoré sú viditeľné. Práve na tieto problémy sa využíva funkcia SUBTOTAL, keďže chceme aby sa vyhodnocovali iba viditeľné záznamy musíme zadávať čísla funkcií zo stĺpca “Ignoruje skryté hodnoty”.
Na obrázku “Súčtový riadok – SUBTOTAL” si môžete všimnúť, že vyfiltrované hodnoty sú vypočítané správne. Práve preto, že sme použili na výpočet funkciu SUBTOTAL.
