Stiahnite si ukážkový zošit
Tento návod ukazuje, ako používať Funkcia SUBTOTAL Excel v Exceli na výpočet súhrnnej štatistiky.
SUBTOTAL Prehľad funkcií
Funkcia SUBTOTAL Vypočíta súhrnnú štatistiku pre sériu údajov. Dostupné štatistiky zahŕňajú, ale nie sú obmedzené na priemer, štandardnú odchýlku, počet, min a max. Úplný zoznam nájdete nižšie v časti vstupov funkcií:
Ak chcete použiť funkciu medzisúčtu pracovného hárka programu Excel, vyberte bunku a zadajte:
(Všimnite si, ako sa zobrazujú vstupy vzorcov)
SUBTOTAL Syntax a vstupy funkcií:
| 1 | = SUBTOTAL (function_num, REF1) |
function_num - Číslo predstavujúce, ktorú operáciu je potrebné vykonať.
REF1 - Rozsahy alebo referencie obsahujúce údaje na výpočet.
Čo je funkcia SUBTOTAL?
SUBTOTAL je jednou z jedinečných funkcií v tabuľkách, pretože dokáže rozpoznať rozdiel medzi skrytými bunkami a skrytými bunkami. To sa môže ukázať ako veľmi užitočné pri práci s filtrovanými rozsahmi alebo keď potrebujete nastaviť výpočty na základe rôznych výberov používateľov. Pretože vie zo svojich výpočtov ignorovať aj ďalšie SUBTOTÁLNE funkcie, môžeme ho použiť aj v rámci veľkých súhrnných údajov bez obáv z dvojitého počítania.
Základné zhrnutie s SUBTOTAL
Povedzme, že ste mali tabuľku triedených predajov produktov a chceli ste vytvoriť súčty pre každý produkt a tiež vytvoriť celkový súčet. Môžete použiť kontingenčnú tabuľku alebo môžete vložiť niektoré vzorce. Zvážte toto rozloženie:
Do buniek B5 a B8 som umiestnil niektoré SUBTOTÁLNE funkcie, ktoré vyzerajú
| 1 | = SÚČET (9, B2: B4) |
Zo syntaxe môžete pre prvý argument použiť rôzne čísla. V našom konkrétnom prípade používame 9 na označenie, že chceme urobiť súčet.
Zamerajme sa na bunku B9. Má tento vzorec, ktorý zahŕňa celý rozsah údajov stĺpca B, ale nezahŕňa ostatné medzisúčty.
| 1 | = SÚČET (9, B2: B8) |
POZNÁMKA: Ak nechcete písať všetky súhrnné vzorce sami, môžete prejsť na pás s údajmi a použiť sprievodcu Obrys - medzisúčet. Automaticky vloží riadky a umiestni vzorce za vás.
Rozdiel v prvých argumentoch
V prvom prípade sme použili 9 na označenie, že chceme urobiť súčet. Rozdiel medzi použitím 9 a 109 by bol v tom, ako chceme, aby funkcia zvládala skryté riadky. Ak použijete označenie 1XX, funkcia nebude zahŕňať riadky, ktoré boli manuálne skryté alebo filtrované.
Tu je náš stôl z minulosti. Funkcie sme posunuli, aby sme videli rozdiel medzi argumentmi 9 a 109. Pri všetkom viditeľnom sú výsledky rovnaké.
Ak použijeme filter na odfiltrovanie hodnoty 6 v stĺpci B, obe funkcie zostanú rovnaké.
Ak ručne skryjeme riadky, vidíme rozdiel. Funkcia 109 dokázala ignorovať skrytý riadok, zatiaľ čo funkcia 9 nie.
Zmeňte matematickú operáciu na SUBTOTAL
Možno budete chcieť niekedy dať svojmu používateľovi možnosť zmeniť, aký typ výpočtov sa vykonáva. Chcú napríklad získať súčet alebo priemer. Pretože SUBTOTAL riadi matematickú operáciu číslom argumentu, môžete to napísať do jedného vzorca. Tu je naše nastavenie:
Vytvorili sme rozbaľovaciu ponuku v D2, kde si používateľ môže vybrať buď „Sumu“ alebo „Priemer“. Vzorec v E2 je:
| 1 | = SÚČET (IF (D2 = "priemer", 1, IF (D2 = "súčet", 9)), B2: B4) |
Tu funkcia IF určí, ktorý číselný argument má byť zadaný SUBTOTAL. Ak je A5 „priemerný“, potom bude mať hodnotu 1 a SUBTOTAL uvedie priemer B2: B4. Alebo, ak sa A5 rovná „súčtu“, potom IF vydá 9 a dostaneme iný výsledok.
Túto schopnosť môžete rozšíriť pomocou vyhľadávacej tabuľky, v ktorej nájdete zoznam ešte ďalších typov operácií, ktoré chcete vykonať. Vaša vyhľadávacia tabuľka môže vyzerať takto
Potom môžete zmeniť vzorec v E2 na
| 1 | = SÚČET (VLOOKUP (A5, LookupTable, 2, 0), B2: B4) |
Podmienené vzorce so SUBTOTAL
Aj keď má SUBTOTAL mnoho operácií, ktoré môže vykonať, nemôže sám kontrolovať kritériá. Na vykonanie tejto operácie ho však môžeme použiť v pomocnom stĺpci. Keď máte stĺpec údajov, o ktorom viete, že bude vždy máte v sebe kus údajov, môžete použiť schopnosť SUBTOTALS na detekciu skrytých riadkov.
Tu je tabuľka, s ktorou budeme v tomto príklade pracovať. Nakoniec by sme chceli byť schopní zhrnúť hodnoty pre „Apple“, ale tiež nechať používateľa filtrovať stĺpec Množstvo.
Najprv vytvorte pomocný stĺpec, v ktorom bude umiestnená funkcia SUBTOTAL. V C2 má vzorec:
| 1 | = SÚČET (103, A2) |
Nezabudnite, že 103 znamená, že chceme urobiť COUNTA. Odporúčam použiť COUNTA, pretože potom môžete nechať vyplniť svoju referenčnú bunku A2 buď čísla alebo text. Teraz budete mať tabuľku, ktorá vyzerá takto:
To sa najskôr nezdá byť užitočné, pretože všetky hodnoty sú iba 1. Ak však skryjeme riadok 3, „1“ v C3 sa zmení na 0, pretože ukazuje na skrytý riadok. Aj keď nie je možné mať obrázok ukazujúci hodnotu konkrétnej skrytej bunky, môžete to skontrolovať skrytím riadka a následným napísaním základného vzorca na kontrolu.
| 1 | = C3 |
Teraz, keď máme stĺpec, ktorého hodnota sa zmení v závislosti od toho, či je skrytý alebo nie, sme pripravení napísať konečnú rovnicu. Naše SUMIFY budú vyzerať takto
V tomto vzorci budeme sumovať hodnoty zo stĺpca B iba vtedy, ak sa stĺpček A rovná „Apple“, a hodnota v stĺpci C je 1 (alias, riadok nie je skrytý). Povedzme, že náš používateľ chce filtrovať 600, pretože sa nám zdá abnormálne vysoký. Vidíme, že náš vzorec dáva správny výsledok.
S touto schopnosťou môžete použiť šek na COUNTIFS, SUMIFS alebo dokonca SUMPRODUCT. Pridáte možnosť nechať svojich používateľov ovládať niektoré krájače tabuliek a ste pripravení vytvoriť úžasný informačný panel.
SUBTOTAL v Tabuľkách Google
Funkcia SUBTOTAL funguje v Tabuľkách Google úplne rovnako ako v programe Excel:
SUBTOTAL Príklady vo VBA
Vo VBA môžete použiť aj funkciu MEDZISÚČET. Typ:application.worksheetfunction.subtotal (function_num, reh1)
Vykonanie nasledujúcich vyhlásení 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")) |
prinesie nasledujúce výsledky
Pokiaľ ide o argumenty funkcie (číslo_funkcie, atď.), Môžete ich buď zadať priamo do funkcie, alebo definovať premenné, ktoré sa namiesto toho použijú.
Vráťte sa do zoznamu všetkých funkcií v programe Excel
