Ak váš excelový hárok obsahuje skryté riadky, filtrované údaje alebo zoskupené údaje, použite funkciu Excel SUBTOTAL. Funkcia SUBTOTAL môže zahrnúť alebo vylúčiť skryté hodnoty vo výpočtoch. Okrem nájdenia súčtu skupiny údajov môže Excel vypočítať priemer, maximum, minimum, štandardnú odchýlku a rozptyl vašich údajov. Tu je návod, ako vložiť medzisúčty do Excelu.
Pokyny v tomto článku platia pre Excel pre Microsoft 365, Excel 2019 a Excel 2016.
Syntax funkcie SUBTOTAL
Použite funkciu SUBTOTAL v Exceli na zhrnutie hodnôt v pracovnom hárku rôznymi spôsobmi. Je to užitočné najmä vtedy, keď váš pracovný hárok obsahuje skryté riadky, ktoré chcete zahrnúť do výpočtu.
Syntax funkcie SUBTOTAL je: SUBTOTAL(číslo_funkcie, ref1, ref2, …)
Argument function_num je povinný a určuje typ matematickej operácie, ktorá sa má použiť pre medzisúčet. Funkcia SUBTOTAL dokáže sčítať čísla, vypočítať priemernú hodnotu vybraných čísel, nájsť maximálne a minimálne hodnoty v rozsahu, spočítať počet hodnôt vo vybranom rozsahu a ďalšie.
Funkcia SUBTOTAL ignoruje bunky, ktoré neobsahujú údaje, a bunky s nečíselnými hodnotami.
Tento argument je číslo a závisí od toho, či chcete do výsledku zahrnúť skryté riadky alebo vylúčiť skryté riadky z výsledku. Tieto riadky môžu byť manuálne skryté alebo skryté filtrom.
Argumenty function_num zahŕňajú:
| Funkčná úloha | function_num | function_num |
|---|---|---|
| (zahŕňa skryté hodnoty) | (nezahŕňa skryté hodnoty) | |
| AVERAGE | 1 | 101 |
| COUNT | 2 | 102 |
| COUNTA | 3 | 103 |
| MAX | 4 | 104 |
| MIN | 5 | 105 |
| PRODUCT | 6 | 106 |
| STDEV | 7 | 107 |
| STDEVP | 8 | 108 |
| SUM | 9 | 109 |
| VAR | 10 | 110 |
| VARP | 11 | 111 |
Referenčné argumenty function_num 1 až 11 obsahujú iba hodnoty v skrytých riadkoch, keď na skrytie riadkov používate príkaz Hide. Pri použití príkazu Filter nezahŕňajú výpočty SUBTOTAL skryté výsledky filtra.
Argument ref1 je povinný. Toto sú bunky použité na výpočet výsledkov zvoleného argumentu číslo_funkcie. Tento argument môže byť hodnota, jedna bunka alebo rozsah buniek.
Argumenty ref2, … sú voliteľné. Toto sú ďalšie bunky, ktoré sú zahrnuté vo výpočte.
Použite funkciu SUBTOTAL so skrytými riadkami
Funkcie programu Excel je možné zadať manuálne alebo pomocou dialógového okna Argumenty funkcií. Na ilustráciu toho, ako manuálne zadať funkciu pomocou riadka vzorcov, nasledujúci príklad používa argument COUNT function_num na počítanie počtu hodnôt vo viditeľných riadkoch a vo viditeľných aj skrytých riadkoch.
Ak chcete použiť funkciu SUBTOTAL na počítanie počtu riadkov v pracovnom hárku:
- Začnite s pracovným hárkom, ktorý obsahuje viacero riadkov údajov.
- Vyberte bunku, ktorá bude obsahovať počet viditeľných riadkov.
-
Na paneli funkcií zadajte =MEDZISÚČET. Počas písania Excel navrhuje funkciu. Dvakrát kliknite na funkciu SUBTOTAL.
Ak chcete použiť dialógové okno Argumenty funkcie na zadanie funkcie SUBTOTAL, prejdite na Formulas a vyberte Math & Trig >SUBTOTAL.
Image -
V rozbaľovacej ponuke, ktorá sa zobrazí, dvakrát kliknite na argument 102 – COUNT argument function_num.
Image -
Napíšte čiarku (,).
Image -
V pracovnom hárku vyberte bunky, ktoré chcete zahrnúť do vzorca.
Image -
Stlačením Enter zobrazíte výsledok v bunke, ktorú ste vybrali v kroku 2.
Image - Vyberte bunku, ktorá bude obsahovať počet viditeľných a skrytých riadkov.
-
Na paneli funkcií zadajte =MEDZISÚČET. Počas písania Excel navrhuje funkciu. Dvakrát kliknite na funkciu SUBTOTAL.
- V rozbaľovacej ponuke, ktorá sa zobrazí, dvakrát kliknite na argument 2 – COUNT function_num a potom zadajte čiarku (,).
-
V pracovnom hárku vyberte bunky, ktoré chcete zahrnúť do vzorca, a potom stlačte Enter.
Image -
Skryť niekoľko riadkov údajov. V tomto príklade boli skryté riadky s predajom pod 100 000 USD.
Image
Použite funkciu SUBTOTAL s filtrovanými údajmi
Použitie funkcie SUBTOTAL na filtrované údaje ignoruje údaje v riadkoch, ktoré boli filtrom odstránené. Pri každej zmene kritérií filtra funkcia prepočíta, aby zobrazila medzisúčet pre viditeľné riadky.
Ak chcete použiť funkciu SUBTOTAL na zobrazenie rozdielov vo výsledkoch výpočtu pri filtrovaní údajov:
-
Vytvorte vzorce SUBTOTAL. Vytvorte napríklad vzorce na určenie medzisúčtu a priemerných hodnôt filtrovaných údajov.
Nezáleží na tom, či použijete argument function_num pre viditeľné alebo skryté riadky. Oba argumenty poskytujú rovnaký výsledok vo filtrovaných údajoch.
Image - Vyberte ľubovoľnú bunku v množine údajov.
-
Prejdite na Home, potom vyberte Sort & Filter > Filter.
Image -
Na filtrovanie údajov pracovného hárka použite šípky rozbaľovacej ponuky.
Image -
Všimnite si, ako sa hodnoty menia pri každom výbere iného kritéria filtra.
Image
Použite funkciu SUBTOTAL so zoskupenými údajmi
Keď sú údaje zoskupené, existuje spôsob, ako použiť funkciu SUBTOTAL na každú jednotlivú skupinu a potom vypočítať celkový súčet pre celú množinu údajov.
- Vyberte ľubovoľnú bunku v množine údajov.
-
Vyberte Údaje > Medzisúčet na otvorenie dialógového okna Medzisúčet.
Image - Vyberte Pri každej zmene v rozbaľovaciu šípku a vyberte zoskupenie, do ktorého sa bude počítať každý medzisúčet.
- Vyberte Use function šípku rozbaľovacej ponuky a zvoľte function_num.
- V zozname Pridať medzisúčet do vyberte stĺpec, v ktorom sa vzorec použije.
-
Vybrať OK.
Image -
Pre každú skupinu údajov sa vkladajú medzisúčty a na koniec množiny údajov sa vkladá celkový súčet.
Image - Ak chcete zmeniť function_num, zvýraznite ktorúkoľvek bunku v množine údajov a vyberte Data > Medzisúčet. Potom vykonajte svoje voľby v dialógovom okne Subtotal.
