Last ned eksemplet på arbeidsbok
Denne opplæringen viser hvordan du bruker Excel SUBTOTAL Funksjon i Excel for å beregne sammendragsstatistikk.
SUBTOTAL Funksjonsoversikt
SUBTOTAL -funksjonen Beregner en sammendragsstatistikk for en serie data. Tilgjengelig statistikk inkluderer, men er ikke begrenset til, gjennomsnittlig standardavvik, antall, min og maks. Se hele listen nedenfor i funksjonsinngangsseksjonen:
For å bruke funksjonen SUBTOTAL Excel -regneark, velg en celle og skriv:
(Legg merke til hvordan formelinngangene vises)
SUBTOTAL Funksjon Syntaks og innganger:
| 1 | = SUBTOTAL (funksjonsnummer, REF1) |
funksjonsnummer - Et tall som representerer hvilken operasjon som skal utføres.
REF1 - Rekkevidder eller referanser som inneholder data å beregne.
Hva er SUBTOTAL -funksjonen?
SUBTOTALEN er en av de unike funksjonene i regneark fordi den kan se forskjellen mellom skjulte celler og ikke-skjulte celler. Dette kan vise seg å være ganske nyttig når du arbeider med filtrerte områder eller når du trenger å sette opp beregninger basert på forskjellige brukervalg. Siden den også vet å ignorere andre SUBTOTAL-funksjoner fra beregningene, kan vi også bruke den i store oppsummerte data uten frykt for dobbelttelling.
Grunnleggende oppsummering med SUBTOTAL
La oss si at du hadde en tabell med sortert produktsalg, og ønsket å opprette totaler for hvert produkt, samt lage en samlet sum. Du kan bruke en pivottabell, eller du kan sette inn noen formler. Vurder dette oppsettet:
Jeg har plassert noen SUBTOTAL -funksjoner i cellene B5 og B8 som ser ut
| 1 | = SUBTOTAL (9, B2: B4) |
Fra syntaksen kan du bruke en rekke tall for det første argumentet. I vårt spesifikke tilfelle bruker vi 9 for å indikere at vi vil gjøre en sum.
La oss fokusere på celle B9. Den har denne formelen, som inkluderer hele dataområdet for kolonne B, men ikke inkluderer de andre delsummene.
| 1 | = SUBTOTAL (9, B2: B8) |
MERK: Hvis du ikke vil skrive alle oppsummeringsformlene selv, kan du gå til Databåndet og bruke veiviseren Disposisjon - Delsum. Den vil automatisk sette inn rader og plassere formlene for deg.
Forskjell i de første argumentene
I det første eksemplet brukte vi en 9 for å indikere at vi ønsket å gjøre en sum. Forskjellen mellom å bruke 9 og 109 ville være hvordan vi vil at funksjonen skal håndtere skjulte rader. Hvis du bruker 1XX -betegnelsene, inkluderer funksjonen ikke rader som er skjult eller filtrert manuelt.
Her er vårt bord fra før. Vi har flyttet funksjonene over slik at vi kan se forskjell mellom 9 og 109 argumentene. Med alle synlige er resultatene de samme.
Hvis vi bruker et filter for å filtrere ut verdien av 6 i kolonne B, forblir de to funksjonene de samme.
Hvis vi skjuler radene manuelt, ser vi forskjellen. 109 -funksjonen var i stand til å ignorere den skjulte raden mens 9 -funksjonen ikke gjorde det.
Endre matematikkoperasjon med SUBTOTAL
Noen ganger kan det være lurt å gi brukeren muligheten til å endre hvilken type beregninger som utføres. Vil de for eksempel få summen eller gjennomsnittet. Siden SUBTOTAL styrer den matematiske operasjonen med et argumentnummer, kan du skrive dette i en enkelt formel. Her er oppsettet vårt:
Vi har opprettet en rullegardinmeny i D2 der brukeren kan velge enten "Sum" eller "Gjennomsnitt". Formelen i E2 er:
| 1 | = SUBTOTAL (HVIS (D2 = "Gjennomsnitt", 1, HVIS (D2 = "Sum", 9)), B2: B4) |
Her skal IF -funksjonen bestemme hvilket numerisk argument som skal gis til SUBTOTALEN. Hvis A5 er "Gjennomsnitt", vil den sende ut en 1 og SUBTOTAL vil gi gjennomsnittet av B2: B4. Eller, hvis A5 er lik “Sum”, gir IF en 9, og vi får et annet resultat.
Du kan utvide denne muligheten ved å bruke en oppslagstabell for å liste opp enda flere typer operasjoner du vil utføre. Oppslagstabellen din kan se slik ut
Deretter kan du endre formelen i E2 til å være
| 1 | = SUBTOTAL (VLOOKUP (A5, LookupTable, 2, 0), B2: B4) |
Betingede formler med SUBTOTAL
Selv om SUBTOTAL har mange operasjoner den kan utføre, kan den ikke sjekke kriteriene alene. Imidlertid kan vi bruke den i en hjelperkolonne for å utføre denne operasjonen. Når du har en kolonne med data som du vet vil bestandig har et stykke data i det, kan du bruke SUBTOTALs evne til å oppdage skjulte rader.
Her er tabellen vi skal jobbe med i dette eksemplet. Til slutt vil vi gjerne summere verdiene for "Apple", men også la brukeren filtrere Antall -kolonnen.
Opprett først en hjelperkolonne som vil inneholde SUBTOTAL -funksjonen. I C2 er formelen:
| 1 | = SUBTOTAL (103, A2) |
Husk at 103 betyr at vi vil gjøre et COUNTA. Jeg anbefaler å bruke COUNTA fordi du da kan få referansecellen til A2 fylt med enten tall eller tekst. Du får nå et bord som ser slik ut:
Dette virker ikke nyttig i begynnelsen fordi alle verdiene er bare 1. Men hvis vi gjemmer rad 3, vil "1" i C3 endres til en 0 fordi den peker på en skjult rad. Selv om det er umulig å ha et bilde som viser den spesifikke skjulte cellens verdi, kan du sjekke det ved å skjule raden og deretter skrive en grunnleggende formel som denne for å kontrollere.
| 1 | = C3 |
Nå som vi har en kolonne som vil endres i verdi avhengig av om den er skjult eller ikke, er vi klare til å skrive den siste ligningen. Våre SUMIFS vil se slik ut
I denne formelen skal vi bare summere verdier fra kolonne B når kolonne A er lik "Apple", og verdien i kolonne C er 1 (alias, raden er ikke skjult). La oss si at brukeren vår vil filtrere ut 600, fordi den virker unormalt høy. Vi kan se at formelen vår gir riktig resultat.
Med denne muligheten kan du bruke en sjekk på en COUNTIFS, SUMIFS eller til og med et SUMPRODUCT. Du legger til muligheten til å la brukerne kontrollere noen bordskiver, og du er klar til å lage et fantastisk dashbord.
SUBTOTAL i Google Regneark
SUBTOTAL -funksjonen fungerer nøyaktig det samme i Google Regneark som i Excel:
SUBTOTALE eksempler i VBA
Du kan også bruke SUBTOTAL -funksjonen i VBA. Type:application.worksheetfunction.subtotal (function_num, reh1)
Utfører følgende VBA -utsagn
| 1234567891011121314151617 | Range ("C7") = Application.WorksheetFunction.Subtotal (1, Range ("C2: C5"))Område ("C8") = Application.WorksheetFunction.Subtotal (2, Range ("C2: C5"))Range ("C9") = Application.WorksheetFunction.Subtotal (4, Range ("C2: C5"))Område ("C10") = Application.WorksheetFunction.Subtotal (5, Range ("C2: C5"))Range ("C11") = Application.WorksheetFunction.Subtotal (9, Range ("C2: CE5"))Range ("D7") = Application.WorksheetFunction.Subtotal (1, Range ("D2: D5"))Område ("D8") = Application.WorksheetFunction.Subtotal (2, Range ("D2: D5"))Range ("D9") = Application.WorksheetFunction.Subtotal (4, Range ("D2: D5"))Område ("D10") = Application.WorksheetFunction.Subtotal (5, Range ("D2: D5"))Range ("D11") = Application.WorksheetFunction.Subtotal (9, Range ("D2: D5"))Område ("E7") = Application.WorksheetFunction.Subtotal (1, Range ("E2: E5"))Område ("E8") = Application.WorksheetFunction.Subtotal (2, Range ("E2: E5"))Område ("E9") = Application.WorksheetFunction.Subtotal (4, Range ("E2: E5"))Område ("E10") = Application.WorksheetFunction.Subtotal (5, Range ("E2: E5"))Range ("E11") = Application.WorksheetFunction.Subtotal (9, Range ("E2: E5")) |
vil gi følgende resultater
For funksjonsargumentene (funksjonsnummer, etc.) kan du enten skrive dem inn direkte i funksjonen, eller definere variabler som skal brukes i stedet.
Gå tilbake til listen over alle funksjoner i Excel
