Jak používat funkci Excel SUBTOTAL -

souhrn

Funkce Excel SUBTOTAL vrací agregovaný výsledek pro zadané hodnoty. SUBTOTAL může vrátit SUM, PRŮMĚR, COUNT, MAX a další (viz tabulka níže) a funkce SUBTOTAL může obsahovat nebo vyloučit hodnoty ve skrytých řádcích.

Účel

Získejte mezisoučet v seznamu nebo databázi

Návratová hodnota

Číslo představující konkrétní druh mezisoučtu

Syntax

= SUBTOTAL (function_num, ref1, (ref2),…)

Argumenty

  • function_num - číslo, které určuje, která funkce se použije při výpočtu mezisoučtů v seznamu. Úplný seznam najdete v tabulce níže.
  • ref1 - Pojmenovaný rozsah nebo odkaz na mezisoučet.
  • ref2 - (volitelně) Pojmenovaný rozsah nebo odkaz na mezisoučet.

Verze

Excel 2003

Poznámky k použití

Pomocí funkce SUBTOTAL získáte mezisoučet v seznamu nebo databázi. Navzdory jménu má SUBTOTAL schopnost provádět různé matematické funkce, včetně PRŮMĚR, POČET, MAX a mnoho dalších (úplný seznam viz tabulka níže). Ve výchozím nastavení SUBTOTAL vylučuje hodnoty v řádcích skrytých filtrem, což činí SUBTOTAL velmi užitečným v tabulkách Excel.

Funkce SUBTOTAL automaticky ignoruje ostatní SUBTOTAL vzorce, které existují v odkazech, aby se zabránilo dvojímu započítání.

Příklady

Níže uvádíme několik příkladů SUBTOTAL nakonfigurovaných na SUM, POČET A PRŮMĚRNÉ hodnoty v rozsahu. Všimněte si, že jediným rozdílem je hodnota použitá pro argument function_num :

=SUBTOTAL(109,range) // SUM =SUBTOTAL(103,range) // COUNT =SUBTOTAL(101,range) // AVERAGE

Dostupné výpočty

SUBTOTAL chování je řízeno argumentem function_num , který je poskytován jako číselná hodnota. K dispozici je 11 funkcí, každá se dvěma možnostmi, jak je vidět v tabulce níže. Všimněte si, že hodnoty jsou „spárovány“ (např. 1-101, 2-102, 3-103 atd.). To souvisí s tím, jak SUBTOTAL zachází s ručně skrytými řádky. Když function_num se pohybuje v rozmezí 1-11, SUBTOTAL obsahuje buňky, které byly ručně skryté. Když function_num se pohybuje v rozmezí 101-111, CELKEM vylučuje hodnoty v řádcích, které byly ručně skryty.

Funkce Zahrnout skryté Ignorovat skryté
PRŮMĚRNÝ 1 101
POČET 2 102
COUNTA 3 103
MAX 4 104
MIN 5 105
PRODUKT 6 106
STDEV 7 107
STDEVP 8 108
SOUČET 9 109
VAR 10 110
VARP 11 111

Poznámka: SUBTOTAL vždy ignoruje hodnoty v buňkách, které jsou skryty s filtrem. Hodnoty v řádcích, které byly „odfiltrovány“, nejsou nikdy zahrnuty, bez ohledu na číslo_funkce .

Mezisoučet v tabulkách Excel

Funkce SUBTOTAL se používá při zobrazení řádku Celkem v tabulce Excel. Excel vloží funkci SUBTOTAL automaticky a pomocí rozevírací nabídky můžete přepnout chování a zobrazit maximální, minimální, průměr atd. Důvod, proč aplikace Excel používá SUBTOTAL pro výpočty v řádku Celkem tabulky aplikace Excel, protože SUBTOTAL automaticky vylučuje řádky skryty ovládacími prvky filtru v horní části tabulky. To znamená, že při filtrování řádků v tabulce s řádkem Celkem se automaticky aktualizují výpočty, aby respektovaly filtr.

Mezisoučet s obrysy

Excel má funkci Mezisoučet, která automaticky vkládá SUBTOTÁLNÍ vzorce do seřazených dat. Tuto funkci najdete v části Data> Obrys> Mezisoučet. Takto vložené mezisoučty vzorců používají standardní čísla funkcí 1-11. To umožňuje, aby výsledky mezisoučtu zůstaly viditelné, i když jsou řádky skryty a zobrazeny, když je obrys sbalen a rozbalen.

Poznámka: Ačkoli funkce Obrys představuje „snadný“ způsob vkládání mezisoučtů do sady dat, kontingenční tabulka je lepší a pružnější způsob analýzy dat. Kontingenční tabulka navíc oddělí data od prezentace dat, což je osvědčený postup.

Poznámky

  • Když function_num se pohybuje v rozmezí 1-11, SUBTOTAL obsahuje hodnoty, které jsou skryté
  • Když function_num se pohybuje v rozmezí 101-111, CELKEM hodnoty vylučuje, že jsou skryty
  • Ve filtrovaných seznamech SUBTOTAL vždy ignoruje hodnoty ve skrytých řádcích, bez ohledu na číslo_funkce .
  • SUBTOTAL ignoruje další SUBTOTAL vzorce, které existují v odkazech, aby se zabránilo dvojímu započítání.
  • SUBTOTAL je navržen pro práci s vertikálními datovými hodnotami uspořádanými svisle. V horizontálních rozsazích jsou vždy zahrnuty hodnoty ve skrytých sloupcích.

Související videa

Jak přidat řádek součtu do tabulky V tomto videu se podíváme na to, jak přidat a nakonfigurovat řádek součtu do tabulky aplikace Excel. Řádek Celkem může automaticky zobrazit souhrnné výpočty pod tabulkou. Jak počítat položky ve filtrovaném seznamu V tomto videu vám ukážeme, jak přidat zprávu na začátek filtrovaného seznamu, která zobrazuje celkové a viditelné položky v seznamu. Vzorce pro dotazování na tabulku Protože tabulky podporují strukturované odkazy, můžete se o tabulce dozvědět mnoho jednoduchých vzorců. V tomto videu se podíváme na některé vzorce, které můžete použít k dotazování na tabulku.

Zajímavé články...