Download eksemplet på projektmappe
Denne vejledning viser, hvordan du bruger Excel SUBTOTAL funktion i Excel for at beregne oversigtsstatistik.
SUBTOTAL Funktionsoversigt
SUBTOTAL -funktionen Beregner en oversigtsstatistik for en række data. Tilgængelig statistik inkluderer, men er ikke begrænset til, gennemsnitlig standardafvigelse, antal, min og maks. Se hele listen herunder i afsnittet om funktionsindgange:
For at bruge funktionen SUBTOTAL Excel -regneark skal du vælge en celle og skrive:
(Bemærk hvordan formelindgangene vises)
SUBTOTAL funktion Syntaks og input:
| 1 | = SUBTOTAL (funktionsnummer, REF1) |
funktionsnummer - Et nummer, der repræsenterer, hvilken handling der skal udføres.
REF1 - Områder eller referencer, der indeholder data til beregning.
Hvad er SUBTOTAL -funktionen?
SUBTOTAL er en af de unikke funktioner i regneark, fordi den kan fortælle forskellen mellem skjulte celler og ikke-skjulte celler. Dette kan vise sig at være ganske nyttigt, når det drejer sig om filtrerede intervaller, eller når du skal konfigurere beregninger baseret på forskellige brugervalg. Da den også ved at ignorere andre SUBTOTAL-funktioner fra sine beregninger, kan vi også bruge den inden for store opsummerede data uden frygt for dobbelttælling.
Grundlæggende oversigt med SUBTOTAL
Lad os sige, at du havde en tabel med sorteret produktsalg og ønskede at oprette totaler for hvert produkt samt oprette en samlet sum. Du kan bruge en pivottabel, eller du kan indsætte nogle formler. Overvej dette layout:
Jeg har placeret nogle SUBTOTAL -funktioner i celler B5 og B8, der ligner
| 1 | = SUBTOTAL (9, B2: B4) |
Fra syntaksen kan du bruge en række tal til det første argument. I vores specifikke tilfælde bruger vi 9 til at angive, at vi vil lave et beløb.
Lad os fokusere på celle B9. Den har denne formel, som omfatter hele kolonne B -dataområdet, men ikke inkluderer de andre subtotaler.
| 1 | = SUBTOTAL (9, B2: B8) |
BEMÆRK: Hvis du ikke selv vil skrive alle resuméformlerne, kan du gå til databåndet og bruge guiden Disposition - Subtotal. Det vil automatisk indsætte rækker og placere formlerne for dig.
Forskel på de første argumenter
I det første eksempel brugte vi en 9 til at angive, at vi ville lave et beløb. Forskellen mellem at bruge 9 og 109 ville være, hvordan vi vil have funktionen til at håndtere skjulte rækker. Hvis du bruger 1XX -betegnelserne, inkluderer funktionen ikke rækker, der er blevet skjult eller filtreret manuelt.
Her er vores bord fra før. Vi har flyttet funktionerne over, så vi kan se forskel mellem de 9 og 109 argumenter. Med alle synlige er resultaterne de samme.
Hvis vi anvender et filter til at filtrere værdien af 6 i kolonne B, forbliver de to funktioner de samme.
Hvis vi manuelt skjuler rækkerne, ser vi forskellen. Funktionen 109 kunne ignorere den skjulte række, mens 9 -funktionen ikke gjorde det.
Skift matematikoperation med SUBTOTAL
Nogle gange vil du måske gerne give din bruger mulighed for at ændre, hvilken type beregninger der udføres. Vil de f.eks. Få summen eller gennemsnittet. Da SUBTOTAL styrer matematikoperationen med et argumentnummer, kan du skrive dette i en enkelt formel. Her er vores setup:
Vi har oprettet en rullemenu i D2, hvor brugeren kan vælge enten "Sum" eller "Gennemsnit". Formlen i E2 er:
| 1 | = SUBTOTAL (HVIS (D2 = "Gennemsnit", 1, HVIS (D2 = "Sum", 9)), B2: B4) |
Her vil IF -funktionen bestemme, hvilket numerisk argument der skal gives til SUBTOTALEN. Hvis A5 er "Gennemsnit", udsender den en 1, og SUBTOTAL giver gennemsnittet B2: B4. Eller, hvis A5 er lig med "Sum", så sender IF en 9, og vi får et andet resultat.
Du kan udvide denne mulighed ved at bruge en opslagstabel til at liste endnu flere typer operationer, du vil udføre. Din opslagstabel kan se sådan ud
Derefter kan du ændre formlen i E2 til at være
| 1 | = SUBTOTAL (VLOOKUP (A5, LookupTable, 2, 0), B2: B4) |
Betingede formler med SUBTOTAL
Selvom SUBTOTAL har mange operationer, den kan udføre, kan den ikke kontrollere kriterierne alene. Vi kan dog bruge den i en hjælperkolonne til at udføre denne operation. Når du har en kolonne med data, som du ved vil altid har et stykke data i det, kan du bruge SUBTOTALs evne til at registrere skjulte rækker.
Her er tabellen, vi vil arbejde med i dette eksempel. Til sidst vil vi gerne kunne opsummere værdierne for "Apple", men også lade brugeren filtrere kolonnen Antal.
Opret først en hjælperkolonne, der skal rumme SUBTOTAL -funktionen. I C2 er formlen:
| 1 | = SUBTOTAL (103, A2) |
Husk, at 103 betyder, at vi vil lave et COUNTA. Jeg anbefaler at bruge COUNTA, fordi du derefter kan få din referencecelle på A2 fyldt med enten tal eller tekst. Du får nu en tabel, der ser sådan ud:
Dette virker ikke nyttigt i første omgang, fordi alle værdierne bare er 1. Hvis vi skjuler række 3, ændres imidlertid "1" i C3 til et 0, fordi det peger på en skjult række. Selvom det er umuligt at have et billede, der viser den specifikke skjulte celles værdi, kan du kontrollere det ved at skjule rækken og derefter skrive en grundlæggende formel som denne for at kontrollere.
| 1 | = C3 |
Nu hvor vi har en kolonne, der vil ændre sig i værdi afhængigt af, om den er skjult eller ej, er vi klar til at skrive den sidste ligning. Vores SUMIFS vil se sådan ud
I denne formel vil vi kun opsummere værdier fra kolonne B, når kolonne A er lig med "Apple", og værdien i kolonne C er 1 (alias, rækken er ikke skjult). Lad os sige, at vores bruger vil filtrere de 600 ud, fordi den virker unormalt høj. Vi kan se, at vores formel giver det korrekte resultat.
Med denne mulighed kan du anvende en check på et COUNTIFS, SUMIFS eller endda et SUMPRODUCT. Du tilføjer muligheden for at lade dine brugere styre nogle bordskærer, og du er klar til at oprette et fantastisk dashboard.
SUBTOTAL i Google Sheets
SUBTOTAL -funktionen fungerer nøjagtig det samme i Google Sheets som i Excel:
SUBTOTALE eksempler i VBA
Du kan også bruge funktionen SUBTOTAL i VBA. Type:application.worksheetfunction.subtotal (function_num, reh1)
Udførelse af følgende VBA -erklæringer
| 1234567891011121314151617 | Range ("C7") = Application.WorksheetFunction.Subtotal (1, Range ("C2: C5"))Range ("C8") = Application.WorksheetFunction.Subtotal (2, Range ("C2: C5"))Range ("C9") = Application.WorksheetFunction.Subtotal (4, Range ("C2: C5"))Range ("C10") = Application.WorksheetFunction.Subtotal (5, Range ("C2: C5"))Range ("C11") = Application.WorksheetFunction.Subtotal (9, Range ("C2: CE5"))Område ("D7") = Application.WorksheetFunction.Subtotal (1, Range ("D2: D5"))Range ("D8") = Application.WorksheetFunction.Subtotal (2, Range ("D2: D5"))Range ("D9") = Application.WorksheetFunction.Subtotal (4, Range ("D2: D5"))Range ("D10") = Application.WorksheetFunction.Subtotal (5, Range ("D2: D5"))Range ("D11") = Application.WorksheetFunction.Subtotal (9, Range ("D2: D5"))Range ("E7") = Application.WorksheetFunction.Subtotal (1, Range ("E2: E5"))Range ("E8") = Application.WorksheetFunction.Subtotal (2, Range ("E2: E5"))Range ("E9") = Application.WorksheetFunction.Subtotal (4, Range ("E2: E5"))Range ("E10") = Application.WorksheetFunction.Subtotal (5, Range ("E2: E5"))Range ("E11") = Application.WorksheetFunction.Subtotal (9, Range ("E2: E5")) |
vil frembringe følgende resultater
For funktionsargumenterne (funktionsnummer osv.) Kan du enten indtaste dem direkte i funktionen eller definere variabler, der skal bruges i stedet.
Tilbage til listen over alle funktioner i Excel
