Prenesite primer delovnega zvezka
Ta vadnica prikazuje, kako uporabljati SUBTOTALNA funkcija Excel v Excelu za izračun zbirne statistike.
SUBTOTAL Pregled funkcije
Funkcija SUBTOTAL Izračuna zbirno statistiko za vrsto podatkov. Razpoložljivi statistični podatki vključujejo, vendar niso omejeni na povprečje, standardni odklon, štetje, min in maks. Oglejte si celoten seznam spodaj v razdelku vhodi funkcij:
Če želite uporabiti SUBTOTAL funkcijo delovnega lista Excel, izberite celico in vnesite:
(Upoštevajte, kako so prikazani vnosi formule)
SUBTOTAL Funkcija Sintaksa in vhodi:
| 1 | = SUBTOTAL (funkcija_številka, REF1) |
funkcija_številka - Številka, ki predstavlja, katero operacijo je treba izvesti.
REF1 - Obsegi ali reference, ki vsebujejo podatke za izračun.
Kaj je funkcija SUBTOTAL?
SUBTOTAL je ena od edinstvenih funkcij v preglednicah, ker lahko pokaže razliko med skritimi celicami in neskritimi celicami. To se lahko izkaže za zelo koristnega pri obravnavi filtriranih obsegov ali ko morate nastaviti izračune na podlagi različnih uporabniških izbir. Ker zna tudi zanemariti druge SUBTOTAL funkcije iz svojih izračunov, ga lahko uporabimo tudi v velikih povzetih podatkih brez strahu pred dvojnim štetjem.
Osnovni povzetek s SUBTOTAL
Recimo, da ste imeli tabelo razvrščenih prodaj izdelkov in ste želeli ustvariti skupne vrednosti za vsak izdelek ter ustvariti skupno skupno vrednost. Lahko uporabite vrtilno tabelo ali pa vstavite nekaj formul. Razmislite o tej postavitvi:
V celice B5 in B8 sem postavil nekaj SUBTOTAL funkcij, ki izgledajo tako
| 1 | = SUBTOTAL (9, B2: B4) |
Iz sintakse lahko za prvi argument uporabite različna števila. V našem konkretnem primeru uporabljamo 9, da pokažemo, da želimo zbrati vsoto.
Osredotočimo se na celico B9. Ima to formulo, ki vključuje celotno območje podatkov stolpca B, vendar ne vključuje drugih vmesnih seštevkov.
| 1 | = SUBTOTAL (9, B2: B8) |
OPOMBA: Če ne želite sami napisati vseh povzetkov, pojdite na trak Podatki in uporabite čarovnika Outline - Vmesni seštevek. Samodejno bo vstavil vrstice in namesto vas postavil formule.
Razlika v prvih argumentih
V prvem primeru smo uporabili 9, da označimo, da želimo vsoto. Razlika med uporabo 9 in 109 bi bila v tem, kako želimo, da funkcija obravnava skrite vrstice. Če uporabljate oznake 1XX, funkcija ne bo vključevala vrstic, ki so bile ročno skrite ali filtrirane.
Tukaj je naša miza od prej. Funkcije smo prestavili, tako da lahko vidimo razliko med argumenti 9 in 109. Z vsem vidnim so rezultati enaki.
Če uporabimo filter za filtriranje vrednosti 6 v stolpcu B, ostaneta obe funkciji enaki.
Če vrstice ročno skrijemo, vidimo razliko. Funkcija 109 je lahko prezrla skrito vrstico, funkcija 9 pa ne.
Spremenite matematično operacijo s SUBTOTAL
Morda boste želeli včasih dati uporabniku možnost, da spremeni vrsto izračuna, ki se izvaja. Ali na primer želijo dobiti vsoto ali povprečje. Ker SUBTOTAL nadzoruje matematično operacijo s številko argumenta, lahko to zapišete v eno samo formulo. Tu je naša nastavitev:
Ustvarili smo spustni meni v D2, kjer lahko uporabnik izbere »Vsota« ali »Povprečje«. Formula v E2 je:
| 1 | = SUBTOTAL (IF (D2 = "Povprečje", 1, IF (D2 = "Sum", 9)), B2: B4) |
Tu bo funkcija IF določila, kateri numerični argument podati SUBTOTALU. Če je A5 »Povprečje«, bo izpisalo 1, SUBTOTAL pa povprečje B2: B4. Ali pa, če je A5 enako "vsoti", IF daje 9 in dobimo drugačen rezultat.
To zmogljivost lahko razširite tako, da z iskalno tabelo navedete še več vrst operacij, ki jih želite izvesti. Vaša iskalna tabela bi lahko izgledala tako
Nato lahko formulo v E2 spremenite v
| 1 | = SUBTOTAL (VLOOKUP (A5, LookupTable, 2, 0), B2: B4) |
Pogojne formule s SUBTOTAL
Čeprav ima SUBTOTAL veliko operacij, ki jih lahko izvede, sam ne more preveriti meril. Lahko pa ga uporabimo v pomožnem stolpcu za izvedbo te operacije. Ko imate stolpec podatkov, za katerega veste, da bo nenehno če imate v sebi del podatkov, lahko uporabite SUBTOTALs za odkrivanje skritih vrstic.
Tu je tabela, s katero bomo delali v tem primeru. Sčasoma bi želeli sešteti vrednosti za »Apple«, uporabniku pa bi omogočili tudi filtriranje stolpca Količina.
Najprej ustvarite pomožni stolpec, v katerem bo funkcija SUBTOTAL. V C2 je formula naslednja:
| 1 | = SUBTOTAL (103, A2) |
Ne pozabite, da 103 pomeni, da želimo narediti COUNTA. Priporočam uporabo COUNTA, ker lahko nato izpolnite referenčno celico A2 bodisi številke ali besedilo. Zdaj boste imeli tabelo, ki izgleda tako:
To se sprva ne zdi koristno, ker so vse vrednosti le 1. Če pa skrijemo vrstico 3, se bo »1« v C3 spremenilo v 0, ker kaže na skrito vrstico. Čeprav je nemogoče imeti sliko, ki prikazuje vrednost določene skrite celice, jo lahko preverite tako, da skrijete vrstico in nato za preverjanje napišete osnovno formulo, kot je ta.
| 1 | = C3 |
Zdaj, ko imamo stolpec, ki se bo spremenil glede na to, ali je skrit ali ne, smo pripravljeni napisati končno enačbo. Naš SUMIFS bo videti tako
V tej formuli bomo seštevali vrednosti iz stolpca B le, če je stolpec A enak »Apple«, in vrednost v stolpcu C je 1 (tudi vrstica ni skrita). Recimo, da želi naš uporabnik filtrirati 600, ker se zdi nenormalno visok. Vidimo, da naša formula daje pravilen rezultat.
S to zmožnostjo lahko uporabite ček za COUNTIFS, SUMIFS ali celo za PODROČEK. Dodate možnost, da svojim uporabnikom omogočite nadzor nekaterih rezalnikov tabel in pripravljeni ste ustvariti čudovito nadzorno ploščo.
SUBTOTAL v Google Preglednicah
Funkcija SUBTOTAL v Google Preglednicah deluje popolnoma enako kot v Excelu:
SUBTOTALNI primeri v VBA
V VBA lahko uporabite tudi funkcijo SUBTOTAL. Vrsta:application.worksheetfunction.subtotal (število_funkcij, reh1)
Izvajanje naslednjih stavkov VBA
| 1234567891011121314151617 | Obseg ("C7") = Application.WorksheetFunction.Subtotal (1, Range ("C2: C5"))Obseg ("C8") = Application.WorksheetFunction.Subtotal (2, Range ("C2: C5"))Obseg ("C9") = Application.WorksheetFunction.Subtotal (4, Range ("C2: C5"))Obseg ("C10") = Application.WorksheetFunction.Subtotal (5, Range ("C2: C5"))Obseg ("C11") = Application.WorksheetFunction.Subtotal (9, Range ("C2: CE5"))Obseg ("D7") = Application.WorksheetFunction.Subtotal (1, Range ("D2: D5"))Obseg ("D8") = Application.WorksheetFunction.Subtotal (2, Range ("D2: D5"))Obseg ("D9") = Application.WorksheetFunction.Subtotal (4, Range ("D2: D5"))Obseg ("D10") = Application.WorksheetFunction.Subtotal (5, Range ("D2: D5"))Obseg ("D11") = Application.WorksheetFunction.Subtotal (9, Range ("D2: D5"))Obseg ("E7") = Application.WorksheetFunction.Subtotal (1, Range ("E2: E5"))Obseg ("E8") = Application.WorksheetFunction.Subtotal (2, Range ("E2: E5"))Obseg ("E9") = Application.WorksheetFunction.Subtotal (4, Range ("E2: E5"))Obseg ("E10") = Application.WorksheetFunction.Subtotal (5, Range ("E2: E5"))Obseg ("E11") = Application.WorksheetFunction.Subtotal (9, Range ("E2: E5")) |
bo prinesel naslednje rezultate
Za argumente funkcije (število_funkcije itd.) Jih lahko vnesete neposredno v funkcijo ali pa določite spremenljivke, ki jih želite uporabiti.
Vrnite se na seznam vseh funkcij v Excelu
