Når dit Excel-regneark indeholder skjulte rækker, filtrerede data eller grupperede data, skal du bruge funktionen Excel SUBTOTAL. Funktionen SUBTOTAL kan inkludere eller ekskludere skjulte værdier i beregninger. Udover at finde totalen af en datagruppe, kan Excel beregne gennemsnittet, maksimum, minimum, standardafvigelse og varians af dine data. Sådan indsætter du subtotaler i Excel.
Instruktionerne i denne artikel gælder for Excel til Microsoft 365, Excel 2019 og Excel 2016.
Syntaks for SUBTOTAL-funktionen
Brug SUBTOTAL-funktionen i Excel til at opsummere værdierne i et regneark på forskellige måder. Det er især nyttigt, når dit regneark indeholder skjulte rækker, som du vil inkludere i beregningen.
Syntaksen for SUBTOTAL-funktionen er: SUBTOTAL(funktion_num, ref1, ref2, …)
Argumentet function_num er påkrævet og specificerer typen af matematisk operation, der skal bruges til subtotalen. Funktionen SUBTOTAL kan tilføje tal, beregne gennemsnitsværdien af valgte tal, finde maksimum- og minimumværdierne i et område, tælle antallet af værdier i et valgt område og mere.
Funktionen SUBTOTAL ignorerer celler, der ikke indeholder data, og celler med ikke-numeriske værdier.
Dette argument er et tal og afhænger af, om du vil inkludere skjulte rækker i resultatet eller ekskludere skjulte rækker fra resultatet. Disse rækker kan være manuelt skjult eller skjult af et filter.
Argumenterne funktion_num omfatter:
| Funktionsopgave | function_num | function_num |
|---|---|---|
| (inkluderer skjulte værdier) | (ekskluderer skjulte værdier) | |
| AVERAGE | 1 | 101 |
| COUNT | 2 | 102 |
| COUNTA | 3 | 103 |
| MAX | 4 | 104 |
| MIN | 5 | 105 |
| PRODUCT | 6 | 106 |
| STDEV | 7 | 107 |
| STDEVP | 8 | 108 |
| SUM | 9 | 109 |
| VAR | 10 | 110 |
| VARP | 11 | 111 |
Referenceargumenterne funktion_num 1 til 11 inkluderer kun værdier i skjulte rækker, når du bruger kommandoen Skjul til at skjule rækker. Når du bruger kommandoen Filter, inkluderer SUBTOTAL-beregninger ikke skjulte filterresultater.
Argumentet ref1 er påkrævet. Disse er cellerne, der bruges til at beregne resultaterne af det valgte funktion_num-argument. Dette argument kan være en værdi, en enkelt celle eller et celleområde.
ref2, … argumenterne er valgfrie. Dette er yderligere celler, der er inkluderet i beregningen.
Brug SUBTOTAL-funktionen med skjulte rækker
Excel-funktioner kan indtastes manuelt eller ved hjælp af dialogboksen Funktionsargumenter. For at illustrere, hvordan man indtaster funktionen manuelt ved hjælp af formellinjen, bruger følgende eksempel argumentet COUNT function_num til at tælle antallet af værdier i synlige rækker og i både synlige og skjulte rækker.
For at bruge funktionen SUBTOTAL til at tælle antallet af rækker i et regneark:
- Start med et regneark, der indeholder flere rækker med data.
- Vælg den celle, der skal indeholde antallet af synlige rækker.
-
I funktionslinjen skal du indtaste =SUBTOTAL. Mens du skriver, foreslår Excel en funktion. Dobbeltklik på funktionen SUBTOTAL.
For at bruge dialogboksen Funktionsargumenter til at indtaste SUBTOTAL-funktionen skal du gå til Formler og vælge Math & Trig >SUBTOTAL.
Image -
I rullemenuen, der vises, skal du dobbeltklikke på 102 – COUNT function_num argument.
Image -
Skriv et komma (,).
Image -
I regnearket skal du vælge de celler, der skal medtages i formlen.
Image -
Tryk på Enter for at se resultatet i den celle, du valgte i trin 2.
Image - Vælg den celle, der skal indeholde antallet af synlige og skjulte rækker.
-
I funktionslinjen skal du indtaste =SUBTOTAL. Mens du skriver, foreslår Excel en funktion. Dobbeltklik på funktionen SUBTOTAL.
- I rullemenuen, der vises, skal du dobbeltklikke på 2 – COUNT function_num-argumentet, og derefter indtaste et komma (,).
-
I regnearket skal du vælge de celler, der skal medtages i formlen, og derefter trykke på Enter.
Image -
Skjul flere rækker med data. I dette eksempel blev rækker med kun salg under 100.000 USD skjult.
Image
Brug SUBTOTAL-funktionen med filtrerede data
Brug af SUBTOTAL-funktionen på filtrerede data ignorerer data i rækker, der er blevet fjernet af filteret. Hver gang filterkriterierne ændres, genberegner funktionen for at vise subtotalen for de synlige rækker.
For at bruge funktionen SUBTOTAL til at se forskellene i beregningsresultater, mens data filtreres:
-
Opret SUBTOTAL-formler. Opret f.eks. formler for at bestemme subtotalen og gennemsnitsværdierne for de filtrerede data.
Det er lige meget, om du bruger argumentet function_num til synlige eller skjulte rækker. Begge argumenter giver det samme resultat i filtrerede data.
Image - Vælg en hvilken som helst celle i datasættet.
-
Gå til Hjem, og vælg derefter Sortér og filtrer > Filter.
Image -
Brug rullepilene til at filtrere regnearkets data.
Image -
Bemærk, hvordan værdierne ændres, hver gang du vælger forskellige filterkriterier.
Image
Brug SUBTOTAL-funktionen med grupperede data
Når data er grupperet, er der en måde at anvende SUBTOTAL-funktionen på hver enkelt gruppe og derefter beregne totalen for hele datasættet.
- Vælg en hvilken som helst celle i datasættet.
-
Vælg Data > Subtotal for at åbne Subtotal dialogboksen.
Image - Vælg Ved hver ændring i rullemenuen, og vælg den gruppering, som hver subtotal skal beregnes til.
- Vælg Brug funktion rullemenu, og vælg et function_num.
- I listen Tilføj subtotal til skal du vælge den kolonne, som formlen skal anvendes på.
-
Vælg OK.
Image -
Subtotaler indsættes for hver datagruppe, og en totalsum indsættes i bunden af datasættet.
Image - For at ændre funktionsnummeret skal du fremhæve en hvilken som helst celle i datasættet og vælge Data > Subtotal. Foretag derefter dine valg i dialogboksen Subtotal.
