Obsah článku:
Čo robí funkcia SUBTOTAL
Funkcia SUBTOTAL sa používa v Exceli pri tvorbe súčtových riadkov v tabuľkách. Ide o veľmi praktickú funkciu, ktorá zastupuje až 11 funkcií.
Syntax: Funkcia SUBTOTAL
Obsahuje nasledovné argumenty:
- Číslo_funkcie – Povinný argument. Číslo 1-11 alebo 101-111, ktoré určuje funkciu, ktorá sa má použiť pre medzisúčet. 1 až 11 obsahuje manuálne skryté riadky, zatiaľ čo čísla 101 až 111 ich vylúčia. Filtrované bunky sú vždy vylúčené.
- Odk1 – Povinný argument. Prvý menovaný rozsah alebo odkaz, ktorého medzisúčet chcete zistiť.
- Odk2;… – Voliteľné argumenty. Menované rozsahy alebo odkazy 2 až 254, ktorých medzisúčet chcete zistiť.
|
Číslo_funkcie |
Číslo_funkcie |
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 |
Potrebujete pomôcť s Excelom?
Súčtové riadky v Exceli
Väčšina používateľov Excelu robí pri tvorbe súčtového riadku tri chyby.
1. chyba: Súčty hneď za tabuľkou
Súčtový riadok robia na hneď na konci tabuľky, čiže riadok sa nachádza hneď 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.
Rovnako ako aj pri filtrovaní, Excel bude brať tento riadok ako súčasť tabuľky. V prípade, ak vyfiltrujeme určité údaje, Excel daný riadok skryje.
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.
Na obrázku 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.
2. chyba: Používa sa funkcia SUM
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ú filtrovaným kritériám.
⚠️ POZOR: Zjednodušene povedané, aj keď budete filtrovať, Excel Vám stále bude ukazovať súčty za celú tabuľku.
Na obrázku je možné vidieť, že nami zvoleným kritériám vyhovujú iba štyri 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 použí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“, inak povedané tie stovkové čísla pre funkcie. Pre funkciu SUM (súčet) je to číslo 109.
3. chyba: Zadáva sa hodnota 1, 2, 3 … 9 a nie 101, 102 … 109
V prípade, ak sa ľudia dozvedia o funkcii SUBOTAL, či už z internetu alebo od kolegov zvyknú robiť 3. chybu a to tú, že zadávajú pre súčet (SUM) číslo 9.
V tomto prípade je problém, že ak by sme v tabuľke skryli nejaké hodnoty ručne, čiže cez pravé tlačidlo a skryť riadok – funkcia SUBTOTAL by aj tieto nami ručne skryté riadky do súčtových riadkov rátala.
⚠️ Pozor, teda na to, že pri SUBTOTAL funkcii treba používať čísla „stovkové“. Funkcia SUM má číslo 109.
Video: Funkcia SUBTOTAL a súčtové riadky
