SUBTOTAL i Excel (formel, eksempler) - Brug af SUBTOTAL-funktion

Indholdsfortegnelse:

Anonim

Excel SUBTOTAL-funktion (indholdsfortegnelse)

  • SUBTOTAL i Excel
  • SUBTOTAL formel i Excel
  • Hvordan bruges SUBTOTAL-funktion i Excel?

SUBTOTAL i Excel

  • En subtotal funktion er en indbygget funktion i Excel, der er kategoriseret under matematik / trig-funktion
  • Subtotal summerer ikke blot antallet i et defineret celleområde, det hjælper ved at udføre forskellige aritmetiske operationer
  • SUBTOTAL er alsidig. Du kan bruge det til at gennemsnit, opsummere, tælle en masse celler & andre beregninger

Definition

Returnerer et subtotal i en database eller liste, dvs At finde ud af subtotal for et givet celleområde. Den udfører en specificeret beregning for et givet sæt værdier.

SUBTOTAL formel i Excel

Formlen til SUBTOTAL-funktionen er som følger:

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

Funktionsnummer eller betjeningskode: Det angiver, hvilken funktion der skal bruges til beregning af subtotalerne i en given liste, eller det er en type matematisk operation, som du gerne vil udføre

Et funktionsnummer-argument anvendes på baggrund af nedenstående to kriterier eller metode

  1. Inkluder skjulte værdier Når funktionsnummer-argumentet er mellem 1-11, inkluderer SUBTOTAL-funktionen de skjulte værdier.
  2. Ignorer skjulte værdier Når funktionsnummer-argumentet er mellem 101-111, vil SUBTOTAL-funktionen ekskludere eller ignorere de skjulte værdier. Det finder kun værdier, der er usynlige rækker, der ignorerer skjulte værdier i beregningen.
Fun_num
Inkluderer skjulte værdier		 Fun_num
Ekskluderer skjulte værdier		 Fungere Beskrivelse
1 101 GENNEMSNIT Det beregner gennemsnittet af et specificeret interval.
2 102 TÆLLE Det tæller antallet af celler, der indeholder numeriske værdier.
3 103 TÆLV Det tæller ikke-tomme celler i det specificerede interval.
4 104 MAX Det finder ud af den største værdi i det specificerede interval.
5 105 MIN Det finder ud af den mindste værdi i det specificerede interval.
6 106 PRODUKT Det beregner produktet fra celler i det specificerede interval.
7 107 STDEV Det estimerer standardafvigelse i det specificerede interval.
8 108 STDEVP Det beregner standardafvigelse baseret på hele befolkningen.
9 109 SUM Det beregner summen af ​​det specificerede interval.
10 110 VAR Det estimerer varians i det specificerede interval.
11 111 VARP Det estimerer varians baseret på hele befolkningen.

ref1, (ref2): En eller flere henvisninger til celler, der indeholder de værdier, som beregningen skal udføres for subtotal.

Hvordan bruges SUBTOTAL-funktion i Excel?

Lad os se hvordan SUBTOTAL-funktionen fungerer i Excel.

Du kan downloade denne SUBTOTAL Funktion Excel-skabelon her - SUBTOTAL Funktion Excel-skabelon

Eksempel # 1 - SUBTOTAL formel for værdier skjult ved hjælp af Excel Autofilter eller rækker skjult af et filter

I nedenstående eksempel indeholder tabellen kvartalsvise salgsdata, hvor de enkelte kvartaler i kolonne E & salgsdata i kolonne F.

Sumfunktionen anvendes i celle “F16” dvs. = SUM (F4: F15).

Det returnerer eller resulterer i værdi 1184 .

SUM-funktionen returnerer den samme værdi. dvs. 1184 i stedet for 194 for Q4-data, hvis rækker er skjult af et filter (Nedenfor nævnte skærmbillede).

Hvis jeg kun ønsker den korrekte SUM-værdi for Q4-data, skal jeg bruge SUBTOTAL-funktionen.

Før du anvender SUBTOTAL-funktionen, skal du anvende et filter på overskriftsraden under dataværktøjslinjen.

Lad os nu beregne specifikt Q1 eller Q2 eller Q3 eller Q4 salgsdata separat for filtrerede data.

Brug 109 som funktionsnummer-argument i SUBTOTAL-funktionen til at opsummere dataene. ie = SUBTOTAL (109, C17: C28)

Funktionen SUBTOTAL ignorerer rækker skjult af et filter og beregner det korrekte resultat.

Her ændres subtotalværdien dynamisk i henhold til filteret, den finder kun en sum af værdier i synlige rækker; skjulte rækker forsømmes af funktionen SUBTOTAL. (Nedenfor nævnt f.eks.)

Q4

Q3

Q2

Q1

Eksempel # 2 - SUBTOTAL Funktion til manuelt skjulte rækker

I excelarket er række nr. 15, 16, 19 og 20 skjult ved hjælp af rækkeformatindstillingen ved at fremhæve disse rækker, højreklikke med musen og vælge Skjul.

En subtotal funktion bruges i celle C23 i regnearket til at beregne summen af ​​synlige celler.

Vi er nødt til at bruge 109 som et funktionsnummer-argument i SUBTOTAL-funktionen for at opsummere dataene.

ie = SUBTOTAL (109, C14: C21)

Her ignorerer SUBTOTAL-funktion manuelt rækker skjult og beregner det korrekte resultat.

Den finder kun en sum af værdier i synlige rækker; skjulte rækker forsømmes af funktionen SUBTOTAL

Eksempel 3 - SUBTOTAL Funktion til normal værdi

Da alle celler i intervallet af kvartalsvise salgstal er synlige, er ingen af ​​dem filtreret, eller rækker er skjult, det inkluderer alle værdier.

I det nedenstående eksempel anvendes flere subtotale funktioner.

I cellen anvendes C14, C15 og C16 SUBTOTAL funktion.

Til beregning af SUM-værdien bruges funktion nummer 9 eller 109 i en subtotal funktion, hvor den beregner summen af ​​det specificerede interval, dvs. = SUBTOTAL (9, C4: C11)

Det giver os summen af ​​alle værdier i C4: C11, forudsat at ingen filtreres, og rækker er skjult i det specificerede interval.

For at beregne MIN-værdi bruges funktion nummer 5 eller 105 i en subtotal funktion, hvor den finder ud af den mindste værdi i det specificerede interval. I en celle, C15, anvendes en SUBTOTAL-funktion, dvs. = SUBTOTAL (5, C4: C11)

Det finder ud af den mindste værdi i det specificerede interval, dvs. C4: C11

Til beregning af MAX-værdi bruges funktion nummer 4 eller 104 i en subtotal funktion, hvor den finder ud af den største værdi i det specificerede område. I en celle, C16, anvendes en SUBTOTAL-funktion, dvs. = SUBTOTAL (4, C4: C11)

Den finder ud af den største værdi i det specificerede interval, dvs. C4: C11.

Ting at huske på SUBTOTAL-funktionen

  • En SUBTOTAL funktion kan anvendes til at finde en sum af filtrerede værdier.
  • En SUBTOTAL-funktion bruges til at ignorere værdier i skjulte rækker.
  • Funktion _ antal argumentværdi bør ikke være større end 11 eller mindre end 1, ligeledes bør ikke være større end 111 eller mindre end 101, ellers giver det #VÆRDE! fejl.
  • En SUBTOTAL-funktion er kun anvendelig for den numeriske værdi.
  • Hvis der er nogle undertotaler til stede i SUBTOTAL-området, vil de blive forsømt, dvs. hvis der allerede findes eksisterende SUBTOTAL-formler i inputområdet for en anden SUBTOTAL-formel, forsømmes disse værdier ved subtotal funktion under beregningen, så dobbeltoptælling undgås & det forhindrer fejlen ved dobbeltoptælling.
  • En SUBTOTAL funktion kan bruges til dynamisk opsummering af data.
  • Blanke celler og celler, der indeholder ikke-numeriske værdier, ignoreres af subtotal funktion under beregningen.

Anbefalede artikler

Dette har været en guide til SUBTOTAL. Her diskuterer vi SUBTOTAL-formlen, og hvordan man bruger SUBTOTAL-funktionen sammen med Excel-eksempel og downloadbare Excel-skabeloner. Du kan også se på disse nyttige funktioner i excel -

  1. SØG Funktion i Excel
  2. FIND-funktion i Excel
  3. IKKE i Excel
  4. TRANSPOSE i Excel