Stáhněte si ukázkový sešit
Tento výukový program ukazuje, jak používat Funkce Excel SUBTOTAL v Excelu k výpočtu souhrnné statistiky.
SUBTOTAL Přehled funkcí
Funkce SUBTOTAL Vypočítává souhrnnou statistiku pro řadu dat. Dostupné statistiky zahrnují, ale nejsou omezeny na průměr, standardní odchylku, počet, min a max. Viz úplný seznam níže v sekci vstupů funkcí:
Chcete -li použít funkci SUBTOTAL Excel Worksheet, vyberte buňku a zadejte:
(Všimněte si, jak se zobrazují vstupy vzorců)
SUBTOTAL Syntaxe a vstupy funkcí:
| 1 | = SUBTOTAL (function_num, REF1) |
function_num - Číslo představující, kterou operaci provést.
REF1 - Rozsahy nebo reference obsahující data k výpočtu.
Co je funkce SUBTOTAL?
SUBTOTAL je jednou z jedinečných funkcí v tabulkách, protože dokáže rozeznat rozdíl mezi skrytými buňkami a skrytými buňkami. To se může ukázat jako velmi užitečné při práci s filtrovanými rozsahy nebo když potřebujete nastavit výpočty na základě různých výběrů uživatelů. Vzhledem k tomu, že ze svých výpočtů také umí ignorovat další SUBTOTÁLNÍ funkce, můžeme jej také použít v rámci velkých souhrnných dat bez obav z dvojího počítání.
Základní souhrn se SUBTOTAL
Řekněme, že jste měli tabulku seřazených prodejů produktů a chtěli jste vytvořit součty pro každý produkt a také vytvořit celkový součet. Můžete použít kontingenční tabulku nebo vložit nějaké vzorce. Zvažte toto rozložení:
Do buněk B5 a B8 jsem umístil některé SUBTOTAL funkce, které vypadají jako
| 1 | = SUBTOTAL (9, B2: B4) |
Ze syntaxe můžete pro první argument použít řadu čísel. V našem konkrétním případě používáme 9 k označení, že chceme provést součet.
Zaměřme se na buňku B9. Má tento vzorec, který zahrnuje celý rozsah dat sloupce B, ale nezahrnuje ostatní mezisoučty.
| 1 | = SUBTOTAL (9, B2: B8) |
POZNÁMKA: Pokud nechcete psát všechny souhrnné vzorce sami, můžete přejít na pás karet Data a použít průvodce Obrys - Mezisoučet. Automaticky vloží řádky a umístí vzorce za vás.
Rozdíl v prvních argumentech
V prvním příkladu jsme použili 9 k označení, že chceme udělat součet. Rozdíl mezi použitím 9 a 109 by byl v tom, jak chceme, aby funkce zpracovávala skryté řádky. Pokud použijete označení 1XX, funkce nebude zahrnovat řádky, které byly ručně skryty nebo filtrovány.
Tady je náš stůl z minulosti. Přepnuli jsme funkce, abychom viděli rozdíl mezi argumenty 9 a 109. Se všemi viditelnými jsou výsledky stejné.
Pokud použijeme filtr k odfiltrování hodnoty 6 ve sloupci B, zůstanou obě funkce stejné.
Pokud ručně skryjeme řádky, vidíme rozdíl. Funkce 109 dokázala ignorovat skrytý řádek, zatímco funkce 9 ne.
Změňte matematickou operaci na SUBTOTAL
Možná budete chtít někdy dát svému uživateli možnost změnit, jaký typ výpočtů se provádí. Chtějí například získat součet nebo průměr. Protože SUBTOTAL řídí matematickou operaci pomocí čísla argumentu, můžete to zapsat do jednoho vzorce. Zde je naše nastavení:
Vytvořili jsme rozevírací seznam v D2, kde si uživatel může vybrat buď „Součet“ nebo „Průměr“. Vzorec v E2 je:
| 1 | = SUBTOTAL (IF (D2 = "Průměr", 1, IF (D2 = "Součet", 9)), B2: B4) |
Zde funkce IF určí, který numerický argument má být zadán SUBTOTAL. Pokud je A5 „průměrný“, pak bude mít hodnotu 1 a SUBTOTAL bude dávat průměr B2: B4. Nebo pokud se A5 rovná „Součet“, pak IF vydá 9 a dostaneme jiný výsledek.
Tuto schopnost můžete rozšířit pomocí vyhledávací tabulky, která vypisuje ještě více typů operací, které chcete provést. Vaše vyhledávací tabulka může vypadat takto
Potom můžete změnit vzorec v E2 na
| 1 | = SUBTOTAL (VLOOKUP (A5, LookupTable, 2, 0), B2: B4) |
Podmíněné vzorce s SUBTOTAL
Přestože má SUBTOTAL mnoho operací, které může provádět, nemůže sám kontrolovat kritéria. Můžeme ji však použít v pomocném sloupci k provedení této operace. Když máte sloupec dat, o kterém víte, že bude vždy mít v sobě kus dat, můžete použít schopnost SUBTOTALs k detekci skrytých řádků.
Zde je tabulka, se kterou budeme v tomto příkladu pracovat. Nakonec bychom chtěli být schopni sečíst hodnoty pro „Apple“, ale také nechat uživatele filtrovat sloupec Množství.
Nejprve vytvořte pomocný sloupec, ve kterém bude umístěna funkce SUBTOTAL. V C2 je vzorec:
| 1 | = SUBTOTAL (103, A2) |
Pamatujte, že 103 znamená, že chceme udělat COUNTA. Doporučuji použít COUNTA, protože poté můžete nechat vyplnit referenční buňku A2 buď čísla nebo text. Nyní budete mít tabulku, která vypadá takto:
To se na první pohled nezdá užitečné, protože všechny hodnoty jsou pouze 1. Pokud však skryjeme řádek 3, „1“ v C3 se změní na 0, protože ukazuje na skrytý řádek. I když není možné mít obrázek ukazující hodnotu konkrétní skryté buňky, můžete to zkontrolovat tak, že skryjete řádek a poté napíšete základní vzorec, který chcete zkontrolovat.
| 1 | = C3 |
Nyní, když máme sloupec, jehož hodnota se změní v závislosti na tom, zda je skrytý nebo ne, jsme připraveni napsat konečnou rovnici. Naše SUMIFY budou vypadat takto
V tomto vzorci sečteme hodnoty ze sloupce B pouze tehdy, když se sloupec A rovná „Apple“, a hodnota ve sloupci C je 1 (alias řádek není skrytý). Řekněme, že náš uživatel chce filtrovat 600, protože se zdá neobvykle vysoký. Vidíme, že náš vzorec dává správný výsledek.
Díky této schopnosti můžete použít šek na COUNTIFS, SUMIFS nebo dokonce na SUMPRODUCT. Přidáte možnost nechat své uživatele ovládat některé kráječe tabulek a jste připraveni vytvořit úžasný řídicí panel.
SUBTOTAL v Tabulkách Google
Funkce SUBTOTAL funguje v Tabulkách Google úplně stejně jako v Excelu:
SUBTOTAL Příklady ve VBA
Ve VBA můžete také použít funkci SUBTOTAL. Typ:application.worksheetfunction.subtotal (function_num, reh1)
Provedení následujících příkazů VBA
| 1234567891011121314151617 | Rozsah ("C7") = Application.WorksheetFunction.Subtotal (1, Range ("C2: C5"))Rozsah ("C8") = Application.WorksheetFunction.Subtotal (2, Range ("C2: C5"))Rozsah ("C9") = Application.WorksheetFunction.Subtotal (4, Range ("C2: C5"))Rozsah ("C10") = Application.WorksheetFunction.Subtotal (5, Range ("C2: C5"))Rozsah ("C11") = Application.WorksheetFunction.Subtotal (9, Range ("C2: CE5"))Rozsah ("D7") = Application.WorksheetFunction.Subtotal (1, Range ("D2: D5"))Rozsah ("D8") = Application.WorksheetFunction.Subtotal (2, Range ("D2: D5"))Rozsah ("D9") = Application.WorksheetFunction.Subtotal (4, Range ("D2: D5"))Rozsah ("D10") = Application.WorksheetFunction.Subtotal (5, Range ("D2: D5"))Rozsah ("D11") = Application.WorksheetFunction.Subtotal (9, Range ("D2: D5"))Rozsah ("E7") = Application.WorksheetFunction.Subtotal (1, Range ("E2: E5"))Rozsah ("E8") = Application.WorksheetFunction.Subtotal (2, Range ("E2: E5"))Rozsah ("E9") = Application.WorksheetFunction.Subtotal (4, Range ("E2: E5"))Rozsah ("E10") = Application.WorksheetFunction.Subtotal (5, Range ("E2: E5"))Rozsah ("E11") = Application.WorksheetFunction.Subtotal (9, Range ("E2: E5")) |
přinese následující výsledky
Argumenty funkce (číslo_funkce atd.) Můžete buď zadat přímo do funkce, nebo definovat proměnné, které se místo toho použijí.
Zpět na seznam všech funkcí v Excelu
