• Úvod
  • Kurzy
  • Individuálne
  • Online
  • pre Firmy
    • Kurzy a školenia na mieru
    • PPC reklama v Google Ads
    • Tvorba webových stránok
  • Referencie
  • Kontakt
  • Návody
    • Video – Návody
    • Excel
    • VBA programovanie – Excel
    • Microsoft Word
    • WordPress
    • SEO a PPC
    • ECDL
    • Iné

SUBTOTAL funkcia - robte súčotvý riadok správne

10
SEP
2020

Excel funkcia SUBTOTAL + video

Posted By : Ing. Michal Šurina - IT lektor
Comments : Off

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 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 a 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.

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 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” inak povedané tie stovkové čísla pre funkcie. Pre funkciu SUM (súčet) je to číslo 109.

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.

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átal.

Pozor, teda na to, že pri SUBTOTAL funkcii treba používať čísla “stovkové”.

Viac informácií...
Šurina Michal : Excel guru a lektor - Svojim klientom ponúkam 14 ročné vedomosti a know-how s prácou v Exceli a 9 ročné skúsenosti z programovania vo VBA. Naše kurzy a školenia z Excelu a VBA sú primárne zamerané na vzdelávanie firiem, organizácií, podnikov a štátnych alebo neštátnych inštitúcií. Mám dlhoročné skúsenosti s organizovaním školení a kurzov pre jednotlivcov alebo skupiny. Uvedomujem si, že vzdelávanie firemných zamestnancov je od vzdelávania širokej verejnosti odlišné. Preto ku každému klientovi/firme pristupujem individuálne. Zhodnotím Vaše potreby, firemné zameranie a pripravím osnovy presne podľa Vašich potrieb a predstáv.

Lajkujte a zdieľajte

Potrebujete pomôcť s Excelom?

+421 903 673 463
lektor@itlektor.eu

O nás

Ponúkame firemné kurzy a školenia v oblasti Microsoft Office (Excel, VBA - Programovanie v Exceli, Power BI, Word, PowerPoint, Access, Outlook...).
Kurzy a školenia grafických programov, PPC reklám a tvorbou webových stránok v CMS WordPress.
Pre verejnosť, ale aj firmy ponúkame online video kurzy z Excelu, VBA, Power BI, Accessu, Wordu, PowerPointu, Outlooku, Canvy, WordPressu.

Informácie

Zásady používania cookies
Obchodné podmienky

Kontaktné údaje

Ing. Michal Šurina
Cukrová 14,
811 08 Bratislava

+421 903 673 463
lektor@itlektor.eu

Počítačové kurzy a školenia pre zamestnancov, firmy, organizácie a korporácie.