SUBTOTAL v Excelu (vzorec, příklady) Použití funkce SUBTOTAL

Funkce Excel SUBTOTAL (obsah)

• SUBTOTAL v Excelu
• SUBTOTAL Formula v Excelu
• Jak používat funkci SUBTOTAL v Excelu?

SUBTOTAL v Excelu

• Funkce mezisoučtu je vestavěná funkce v Excelu, která je roztříděna do funkce matematika / trigger
• Mezisoučet nejenže spočítá čísla v definovaném rozsahu buněk, ale pomůže při provádění různých aritmetických operací
• SUBTOTAL je univerzální. Můžete jej použít k průměrování, sčítání, počítání partií buněk a dalších výpočtů

Definice

Vrací mezisoučet v databázi nebo seznamu, tj. K nalezení mezisoučtu pro daný rozsah buněk. Provede zadaný výpočet pro danou sadu hodnot.

SUBTOTAL Formula v Excelu

Vzorec pro funkci SUBTOTAL je následující:

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

Function_num nebo kód operace: Označuje, která funkce by měla být použita pro výpočty mezisoučtů v daném seznamu nebo je to typ matematické operace, kterou chcete provést

Argument function_num se použije na základě níže uvedených dvou kritérií nebo metody

1. Zahrnout skryté hodnoty Pokud je argument function_num mezi 1-11, funkce SUBTOTAL bude zahrnovat skryté hodnoty.
2. Ignorovat skryté hodnoty Pokud je argument function_num mezi 101-111, funkce SUBTOTAL vyloučí nebo ignoruje skryté hodnoty. Nalezne hodnoty pouze neviditelné řádky, které ve výpočtu ignorují skryté hodnoty.

Fun_num Zahrnuje skryté hodnoty		Fun_num Nezahrnuje skryté hodnoty		Funkce		Popis
1		101		PRŮMĚRNÝ		Vypočítá průměr zadaného rozsahu.
2		102		POČET		Bude spočítat počet buněk, které obsahují číselné hodnoty.
3		103		COUNTA		Bude počítat neprázdné buňky ve specifikovaném rozsahu.
4		104		MAX		Zjistí největší hodnotu v zadaném rozsahu.
5		105		MIN		Zjistí nejmenší hodnotu v zadaném rozsahu.
6		106		PRODUKT		Vypočítá součet buněk ve specifikovaném rozsahu.
7		107		STDEV		Odhaduje směrodatnou odchylku v zadaném rozsahu.
8		108		STDEVP		Vypočítá směrodatnou odchylku na základě celé populace.
9		109		SOUČET		Vypočítá součet zadaného rozsahu.
10		110		VAR		Odhaduje rozptyl v zadaném rozsahu.
11		111		VARP		Odhaduje rozptyl na základě celé populace.

ref1, (ref2): Jeden nebo více odkazů na buňky obsahující hodnoty, které se mají pro mezisoučet provést výpočet.

Jak používat funkci SUBTOTAL v Excelu?

Podívejme se, jak funguje funkce SUBTOTAL v Excelu.

Tuto šablonu SUBTOTAL Function Excel si můžete stáhnout zde - SUBTOTAL Template Excel Function

Příklad 1 - SUBTOTAL vzorec pro hodnoty skryté pomocí Excel Excel nebo řádky skryté filtrem

V níže uvedeném příkladu obsahuje tabulka čtvrtletní údaje o prodeji, kde jednotlivé čtvrtletí ve sloupci E a údaje o prodeji ve sloupci F.

Funkce Sum je použita v buňce „F16“, tj. = SUM (F4: F15).

Vrací nebo vede k hodnotě 1184 .

Funkce SUM vrací stejnou hodnotu. tj. 1184, namísto 194 pro data Q4, pokud jsou řádky skryté filtrem (níže uvedený snímek obrazovky).

Pokud chci pouze správnou hodnotu SUM pro data Q4, musím použít funkci SUBTOTAL.

Před použitím funkce SUBTOTAL použijte pod panelem dat filtr pro řádek záhlaví.

Nyní si vypočtěte konkrétně prodejní data Q1 nebo Q2 nebo Q3 nebo Q4 jednotlivě pro filtrovaná data.

Použijte 109 jako argument function_num ve funkci SUBTOTAL pro sčítání dat. tj = SUBTOTAL (109, C17: C28)

Funkce SUBTOTAL ignoruje řádky skryté filtrem a vypočítá správný výsledek.

Zde se hodnota mezisoučtu dynamicky mění podle filtru, najde součet hodnot pouze ve viditelných řádcích; skryté řádky jsou zanedbány funkcí SUBTOTAL. (Níže uvedené např.)

Q4

Q3

Q2

Q1

Příklad # 2 - SUBTOTAL Funkce pro ručně skryté řádky

V listu Excel se řádky č. 15, 16, 19 a 20 skryly pomocí možnosti formátování řádků, zvýrazněním těchto řádků, kliknutím pravým tlačítkem myši a výběrem Skrýt.

V buňce C23 tabulky se používá mezisoučet k výpočtu součtu viditelných buněk.

Abychom mohli shrnout data, musíme použít 109 jako argument function_num ve funkci SUBTOTAL.

tj = SUBTOTAL (109, C14: C21)

Funkce SUBTOTAL zde ignoruje ručně skryté řádky a vypočítá správný výsledek.

Součet hodnot najde pouze ve viditelných řádcích; skryté řádky jsou zanedbány funkcí SUBTOTAL

Příklad # 3 - SUBTOTAL Funkce pro normální hodnotu

Protože jsou viditelné všechny buňky v rozmezí čtvrtletních údajů o prodeji, žádná z nich není filtrována nebo jsou řádky skryté, zahrnuje všechny hodnoty.

V níže uvedeném příkladu jsou použity více mezisoučtů.

V buňce je použita funkce C14, C15 a C16 SUBTOTAL.

Pro výpočet hodnoty SUM se číslo funkce 9 nebo 109 používá ve funkci mezisoučtu, kde vypočítá součet zadaného rozsahu, tj. = SUBTOTAL (9, C4: C11)

Dá nám to součet všech hodnot v C4: C11 za předpokladu, že žádné nejsou filtrovány a řádky jsou skryty v daném rozsahu.

Pro výpočet MIN hodnoty se číslo funkce 5 nebo 105 používá ve funkci mezisoučtu, kde zjistí nejmenší hodnotu ve specifikovaném rozsahu. V buňce C15 je použita funkce SUBTOTAL, tj. = SUBTOTAL (5, C4: C11)

Najde nejmenší hodnotu ve specifikovaném rozsahu, tj. C4: C11

Pro výpočet hodnoty MAX se číslo funkce 4 nebo 104 používá ve funkci mezisoučtu, kde zjistí největší hodnotu v zadaném rozsahu. V buňce C16 je použita funkce SUBTOTAL, tj. = SUBTOTAL (4, C4: C11)

Zjistí největší hodnotu ve specifikovaném rozsahu, tj. C4: C11.

Důležité informace o funkci SUBTOTAL

• Pro nalezení součtu filtrovaných hodnot lze použít funkci SUBTOTAL.
• Funkce SUBTOTAL se používá k ignorování hodnot ve skrytých řádcích.
• Hodnota argumentu funkce _num by neměla být větší než 11 nebo menší než 1, podobně by neměla být větší než 111 nebo menší než 101, jinak poskytne # HODNU! chyba.
• Funkce SUBTOTAL je použitelná pouze pro číselnou hodnotu.
• Pokud jsou v mezisoučtu SUBTOTAL nějaké mezisoučty, budou zanedbány, tj. Pokud jsou ve vstupním rozsahu jiného vzorce SUBTOTAL přítomny již existující SUBTOTAL vzorce, pak jsou tyto hodnoty během výpočtu zanedbávány mezisoučtem, takže se zamezí dvojitému počítání & zabraňuje tak chybám dvojitého počítání.
• K dynamickému shrnutí dat lze použít funkci SUBTOTAL.
• Prázdné buňky a buňky obsahující nečíselné hodnoty jsou při výpočtu ignorovány funkcí mezisoučtu.

Doporučené články

Toto byl průvodce SUBTOTAL. Zde diskutujeme SUBTOTAL vzorec a jak používat SUBTOTAL Function spolu s vynikajícím příkladem a stahovatelnými šablonami Excel. Můžete se také podívat na tyto užitečné funkce v Excelu -

1. Funkce SEARCH v Excelu
2. Najít funkci v Excelu
3. NENÍ v Excelu
4. PŘEPRAVA v Excelu