Hvordan bruke Excel SUBTOTAL-funksjonen

Sammendrag

Excel SUBTOTAL-funksjonen returnerer et samlet resultat for oppgitte verdier. SUBTOTAL kan returnere SUM, GJENNOMSNITT, ANTALL, MAKS og andre (se tabell nedenfor), og SUBTOTAL-funksjonen kan enten inkludere eller ekskludere verdier i skjulte rader.

Hensikt

Få en delsum i en liste eller database

Returverdi

Et tall som representerer en bestemt type delsum

Syntaks

= SUBTOTAL (funksjonsnummer, ref1, (ref2),…)

Argumenter

  • function_num - Et tall som spesifiserer hvilken funksjon som skal brukes til å beregne delsummer i en liste. Se tabellen nedenfor for full liste.
  • ref1 - Et navngitt område eller referanse til delsum.
  • ref2 - (valgfritt) Et navngitt område eller referanse til delsum.

Versjon

Excel 2003

Bruksanvisninger

Bruk SUBTOTAL-funksjonen for å få en delsum i en liste eller database. Til tross for navnet har SUBTOTAL muligheten til å utføre en rekke matematiske funksjoner, inkludert GJENNOMSNITT, TELLING, MAKS og mange andre (se tabellen nedenfor for den komplette listen). Som standard ekskluderer SUBTOTAL verdier i rader skjult av et filter, noe som gjør SUBTOTAL veldig nyttig i Excel-tabeller.

SUBTOTAL-funksjonen ignorerer automatisk andre SUBTOTAL-formler som finnes i referanser for å forhindre dobbelttelling.

Eksempler

Nedenfor er noen eksempler på SUBTOTAL konfigurert til SUM, COUNT og GJENNOMSNITT verdiene i et område. Legg merke til at den eneste forskjellen er verdien som brukes for argumentet function_num :

=SUBTOTAL(109,range) // SUM =SUBTOTAL(103,range) // COUNT =SUBTOTAL(101,range) // AVERAGE

Tilgjengelige beregninger

SUBTOTAL atferd styres av argumentet function_num , som er gitt som en numerisk verdi. Det er 11 funksjoner tilgjengelig, hver med to alternativer, som vist i tabellen nedenfor. Legg merke til at verdiene er "paret" (f.eks. 1-101, 2-102, 3-103 og så videre). Dette er relatert til hvordan SUBTOTAL håndterer manuelt skjulte rader. Når function_num er mellom 1-11, inkluderer SUBTOTAL celler som er skjult manuelt. Når function_num er mellom 101-111, ekskluderer SUBTOTAL verdier i rader som er skjult manuelt.

Funksjon Inkluder skjult Ignorer skjult
GJENNOMSNITT 1 101
TELLE 2 102
COUNTA 3 103
MAX 4 104
MIN 5 105
PRODUKT 6 106
STDEV 7 107
STDEVP 8 108
SUM 9 109
VAR 10 110
VARP 11 111

Merk: SUBTOTAL ignorerer alltid verdier i celler som er skjult med et filter. Verdier i rader som er "filtrert ut" er aldri inkludert, uavhengig av funksjonsnummer .

SUBTOTAL i Excel-tabeller

SUBTOTAL-funksjonen brukes når du viser en total rad i en Excel-tabell. Excel setter inn SUBTOTAL-funksjonen automatisk, og du kan bruke en rullegardinmeny til å bytte oppførsel og vise maks, min, gjennomsnitt osv. Årsaken til at Excel bruker SUBTOTAL for beregninger i den totale raden i en Excel-tabell er fordi SUBTOTAL automatisk ekskluderer rader skjult av filterkontrollene øverst i tabellen. Når du filtrerer rader i en tabell med en total rad, ser du beregningene oppdateres automatisk for å respektere filteret.

SUBTOTAL med konturer

Excel har en delsumfunksjon som automatisk setter inn SUBTOTALE formler i sorterte data. Du finner denne funksjonen på Data> Outline> Subtotal. SUBTOTALE formler satt inn på denne måten bruker standardfunksjonstallene 1-11. Dette gjør at delsumresultatene forblir synlige selv når rader er skjult og vises når omrisset er kollapset og utvidet.

Merk: Selv om omrissfunksjonen er en "enkel" måte å sette inn delsummer i et datasett, er en pivottabell en bedre og mer fleksibel måte å analysere data på. I tillegg vil en pivottabell skille dataene fra presentasjonen av dataene, noe som er en god praksis.

Merknader

  • Når function_num er mellom 1-11, inkluderer SUBTOTAL verdier som er skjult
  • Når function_num er mellom 101-111, ekskluderer SUBTOTAL verdier som er skjult
  • I filtrerte lister ignorerer SUBTOTAL alltid verdier i skjulte rader, uavhengig av funksjonsnummer .
  • SUBTOTAL ignorerer andre SUBTOTAL formler som finnes i referanser for å forhindre dobbelttelling.
  • SUBTOTAL er designet for å fungere med vertikale dataverdier ordnet vertikalt. I horisontale områder er verdier i skjulte kolonner alltid inkludert.

Relaterte videoer

Slik legger du til en totalrad i en tabell I denne videoen vil vi se på hvordan du legger til og konfigurerer en total rad til en Excel-tabell. Totalraden kan automatisk vise sammendragsberegninger under en tabell. Slik teller du varer i en filtrert liste I denne videoen viser vi deg hvordan du legger til en melding øverst på en filtrert liste som viser totalt og synlige elementer i listen. Formler for å spørre om en tabell Fordi tabeller støtter strukturerte referanser, kan du lære mye om en tabell med enkle formler. I denne videoen vil vi se på noen formler du kan bruke til å spørre en tabell.

Interessante artikler...