SUBTOTAL i Excel (formel, eksempler) - Bruk av SUBTOTAL funksjon

Innholdsfortegnelse:

Anonim

Excel SUBTOTAL-funksjon (innholdsfortegnelse)

  • SUBTOTAL i Excel
  • SUBTOTAL Formel i Excel
  • Hvordan bruke SUBTOTAL-funksjonen i Excel?

SUBTOTAL i Excel

  • En subtotal funksjon er en innebygd funksjon i excel som er kategorisert under matte / trig-funksjon
  • Subtotal vil ikke bare summen av tallene i et definert område av celler, det vil hjelpe ved å utføre forskjellige aritmetiske operasjoner
  • SUBTOTAL er allsidig. Du kan bruke den til å gjennomsnittlig, oppsummere, telle en haug celler og andre beregninger

Definisjon

Returnerer et subtotal i en database eller liste, dvs. for å finne subtotal for et gitt område med celler. Den utfører en spesifisert beregning for et gitt sett med verdier.

SUBTOTAL Formel i Excel

Formelen for SUBTOTAL-funksjonen er som følger:

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

Funksjonsnummer eller operasjonskode: Den indikerer hvilken funksjon som skal brukes til beregning av subtotalene i en gitt liste, eller det er en type matematisk operasjon du ønsker å utføre

Et funksjonsnummer argument brukes på grunnlag av de to nevnte to kriteriene eller metoden

  1. Inkluder skjulte verdier Når funksjonsnummer-argumentet er mellom 1-11, inkluderer SUBTOTAL-funksjonen de skjulte verdiene.
  2. Ignorer skjulte verdier Når funksjonsnummer-argumentet er mellom 101-111, vil SUBTOTAL-funksjonen ekskludere eller ignorere de skjulte verdiene. Det vil finne verdier bare usynlige rader ignorerer skjulte verdier i beregningen.
Fun_num
Inkluderer skjulte verdier		 Fun_num
Ekskluderer skjulte verdier		 Funksjon Beskrivelse
1 101 GJENNOMSNITT Den beregner gjennomsnittet av et spesifisert område.
2 102 TELLE Den vil telle antall celler som inneholder tallverdier.
3 103 COUNTA Det teller ikke-tomme celler i det angitte området.
4 104 MAX Den vil finne den største verdien i det spesifiserte området.
5 105 MIN Den vil finne den minste verdien i det spesifiserte området.
6 106 PRODUKT Den beregner produktet fra celler i det spesifiserte området.
7 107 STDEV Den estimerer standardavvik i det spesifiserte området.
8 108 STDEVP Den beregner standardavvik basert på hele befolkningen.
9 109 SUM Den beregner summen av det spesifiserte området.
10 110 VAR Den estimerer varians i det spesifiserte området.
11 111 VARP Den estimerer varians basert på hele befolkningen.

ref1, (ref2): En eller flere referanser til celler som inneholder verdiene som beregningen skal utføres for subtotal.

Hvordan bruke SUBTOTAL-funksjonen i Excel?

La oss se hvordan SUBTOTAL-funksjonen fungerer i Excel.

Du kan laste ned denne SUBTOTAL Funksjonen Excel-malen her - SUBTOTAL Funksjonen Excel-malen

Eksempel # 1 - SUBTOTAL Formel for verdier skjult ved hjelp av Excel autofilter eller rader skjult av et filter

I eksemplet nedenfor inneholder tabellen kvartalsvise salgsdata, der individuelle kvartaler i kolonne E & salgsdata i kolonne F.

Sumfunksjon brukes i celle “F16” dvs. = SUM (F4: F15).

Det returnerer eller resulterer i verdi 1184 .

SUM-funksjonen returnerer den samme verdien. dvs. 1184, i stedet for 194 for Q4-data, hvis rader er skjult av et filter (nedenfor nevnte skjermdump).

Hvis jeg bare vil ha den korrekte SUM-verdien for Q4-data, må jeg bruke SUBTOTAL-funksjonen.

Før du bruker SUBTOTAL-funksjonen, bruker du et filter på overskriftsraden under dataverktøylinjen.

La oss nå beregne spesifikt salgsdata for Q1 eller Q2 eller Q3 eller Q4 hver for seg for filtrerte data.

Bruk 109 som funksjonsnummer-argument i SUBTOTAL-funksjonen for å oppsummere dataene. ie = SUBTOTAL (109, C17: C28)

SUBTOTAL-funksjonen ignorerer rader som er skjult av et filter og beregner riktig resultat.

Her endres subtotalverdien dynamisk i henhold til filteret, den vil finne en sum av verdier bare i synlige rader; skjulte rader blir neglisjert av SUBTOTAL-funksjonen. (Nedenfor nevnt f.eks.)

Q4

Q3

Q2

Q1

Eksempel 2 - SUBTOTAL funksjon for manuelt skjulte rader

I excelarket er rad nr. 15, 16, 19 og 20 skjult ved hjelp av radformateringsalternativ, ved å markere disse radene, høyreklikke med musen og velge Skjul.

En subtotal funksjon brukes i celle C23 i regnearket for å beregne summen av synlige celler.

Vi må bruke 109 som funksjonsnummer-argument i SUBTOTAL-funksjonen for å oppsummere dataene.

ie = SUBTOTAL (109, C14: C21)

Her ignorerer SUBTOTAL-funksjon manuelt rader som er skjult og beregner riktig resultat.

Den vil finne en sum av verdier bare i synlige rader; skjulte rader blir neglisjert av SUBTOTAL-funksjonen

Eksempel # 3 - SUBTOTAL Funksjon for normal verdi

Ettersom alle cellene i området kvartalsvise salgstall er synlige, er ingen av dem filtrert eller rader er skjult, det inkluderer alle verdiene.

I det nedenfor nevnte eksemplet brukes flere subtotale funksjoner.

I celle brukes C14, C15 og C16 SUBTOTAL-funksjonen.

For å beregne SUM-verdi brukes funksjon nummer 9 eller 109 i en subtotal funksjon der den beregner summen av spesifisert område dvs. = SUBTOTAL (9, C4: C11)

Det vil gi oss summen av alle verdiene i C4: C11, forutsatt at ingen er filtrert og radene er skjult i det angitte området.

For å beregne MIN-verdi brukes funksjon nummer 5 eller 105 i en subtotal funksjon der den finner ut minste verdi i det spesifiserte området. I en celle, C15, brukes en SUBTOTAL-funksjon, dvs. = SUBTOTAL (5, C4: C11)

Den vil finne den minste verdien i det spesifiserte området, dvs. C4: C11

For å beregne MAX-verdi brukes funksjon nummer 4 eller 104 i en subtotal funksjon der den finner ut den største verdien i det spesifiserte området. I en celle, C16, brukes en SUBTOTAL-funksjon, dvs. = SUBTOTAL (4, C4: C11)

Den vil finne ut den største verdien i det spesifiserte området, dvs. C4: C11.

Ting å huske på SUBTOTAL-funksjonen

  • En SUBTOTAL-funksjon kan brukes for å finne en sum av filtrerte verdier.
  • En SUBTOTAL-funksjon brukes til å ignorere verdier i skjulte rader.
  • Funksjon _ nummer-argumentverdien skal ikke være større enn 11 eller mindre enn 1, på samme måte skal ikke være større enn 111 eller mindre enn 101, ellers vil den gi #VALUE! feil.
  • En SUBTOTAL-funksjon gjelder bare for den numeriske verdien.
  • Hvis det er noen subtotaler til stede i SUBTOTAL-området, vil de bli neglisjert, dvs. hvis noen allerede eksisterende SUBTOTAL-formler er til stede i inputområdet til en annen SUBTOTAL-formel, blir disse verdiene neglisjert av subtotalfunksjon under beregningen, slik at dobbelttelling unngås & det forhindrer feilen ved dobbeltregning.
  • En SUBTOTAL-funksjon kan brukes til å oppsummere data dynamisk.
  • Blanke celler og celler som inneholder ikke-numeriske verdier blir ignorert av subtotal funksjon under beregningen.

Anbefalte artikler

Dette har vært en guide til SUBTOTAL. Her diskuterer vi SUBTOTAL-formelen og hvordan du bruker SUBTOTAL-funksjonen sammen med Excel-eksempel og nedlastbare Excel-maler. Du kan også se på disse nyttige funksjonene i excel -

  1. SØK Funksjon i Excel
  2. FINN funksjon i Excel
  3. IKKE i Excel
  4. TRANSPOSE i Excel