SUBTOTAAL-functie: Formule en hoe u de SUBTOTAAL-functie in Excel gebruikt

Wat is de SUBTOTAAL-functie in Excel ? Wat is de SUBTOTAAL-formule in Excel ? Laten we het uitzoeken met LuckyTemplates.com!

Microsoft Excel heeft veel functies die worden gebruikt om totalen te berekenen, en SUBTOTAAL is daar een van. Subtotaal verschilt echter van de SOM-functie. Het is geen normaal bedrag. In plaats daarvan wordt het gebruikt om subtotalen in lijsten of databases te berekenen, gemiddelden te berekenen en meer.

Hoofdinhoud over de SUBTOTAAL-functie

• Wat is de functie SUBTOTAAL en waarvoor wordt deze gebruikt?
• SUBTOTAAL-formule in Excel
• Hoe de SUBTOTAAL-functie in Excel te gebruiken
  • 1. Bereken het totaal van gefilterde rijen 
  • 2. Tel gefilterde cellen die niet leeg zijn
  • 3. Negeer waarden in geneste subtotaalformules
• Dingen om te overwegen voordat u de SUBTOTAAL-functie in Excel gebruikt
• Foutmeldingen bij het uitvoeren van de SUBTOTAAL-functie in Excel

Wat is de functie SUBTOTAAL en waarvoor wordt deze gebruikt?

Op het eerste gezicht vraagt ​​u zich misschien af: wat is het doel van de functie SUBTOTAAL? Het is immers gemakkelijk om een ​​subtotaal uit een reeks cellen te vinden met behulp van bestaande functies zoals SUMIF of AVERAGE .

Hoewel het waar is dat u niet noodzakelijkerwijs SUBTOTAAL hoeft te gebruiken, wordt het analyseren van gerichte gegevens een stuk eenvoudiger. U kunt met behulp van SUBTOTAAL een formule in een celbereik invoegen zonder het algehele totaal te beïnvloeden, omdat SUBTOTAAL andere cellen negeert die een SUBTOTAAL-formule bevatten.

SUBTOTAAL werkt met andere Excel-functies, zoals het filteren van cellen. Als u een tabel filtert op een waarde, wordt de formule SUBTOTAAL bijgewerkt, wordt het filter herkend en worden de overeenkomstige cellen uitgesloten. SUBTOTAAL is ook handig voor het negeren van verborgen waarden, wat andere functies (zoals SOM) niet kunnen doen.

Gelukkig kun je andere functies gebruiken als onderdeel van een SUBTOTAAL-formule. Er zijn 11 verschillende subtotaalmethoden beschikbaar, elk geschikt voor een bestaande Excel-functie. Als u een SOM-formule wilt die werkt met gefilterde cellen en andere SUBTOTAAL-formules negeert, kunt u het beste SUBTOTAAL gebruiken, hoewel een draaitabel op dezelfde manier kan werken.

SUBTOTAAL-formule in Excel

De functie SUBTOTAAL werkt door de totale waarde van een celbereik te berekenen, op basis van een andere wiskundige functie. SUBTOTAAL gebruikt maximaal 11 verschillende wiskundige functies (van GEMIDDELDE tot VAR.P) om de juiste totale waarde te vinden, terwijl er binnen de functieparameters wordt gewerkt (d.w.z. andere SUBTOTAAL-formules worden genegeerd).

De functie SUBTOTAAL heeft de syntaxis =SUBTOTAAL(function_num, ref1, [ref2],…) . Daarin:

• Functie_num: De getallen 1 tot en met 11 en 101 tot en met 111 geven aan welke functie wordt gebruikt om SUBTOTAAL te berekenen.
• Ref1, Ref2, ...: 1 of meer cellen, of celbereik om subtotalen te berekenen, maximaal 254.

Opmerking:

• De functie SUBTOTAAL is ontworpen om verticale kolommen met gegevens te berekenen.
• Als de argumenten ref1, ref2,... de functie SUBTOTAAL bevatten, worden deze genegeerd om dubbeltellingen te voorkomen.
• Als function_num tussen 1 en 11 ligt, berekent de functie SUBTOTAAL inclusief verborgen waarden in de dataset, omdat de rij met dat bereik verborgen is. Als function_num tussen 101 en 111 ligt, berekent de functie SUBTOTAAL alleen niet-verborgen waarden in de dataset (de waarden in verborgen rijen worden niet meegerekend).
• Voor gegevensgebieden die door Filter verborgen zijn, wordt SUBTOTAAL genegeerd.

Hoe de SUBTOTAAL-functie in Excel te gebruiken

1. Bereken het totaal van gefilterde rijen 

Om dit type som beter te begrijpen, raadpleegt u het artikel Bereken de som van gefilterde lijstwaarden in Excel . Kortom, de SUBTOTAAL-functie zal in dit geval zijn:

=SUBTOTAAL(9,_bereik)

pham_range hier is het gebied dat u wilt optellen na het filteren van de gegevens.

2. Tel gefilterde cellen die niet leeg zijn

We gebruiken SUBTOTAAL 3 of SUBTOTAAL 103. Als er echter verborgen rijen zijn, moet u SUBTOTAAL 103 gebruiken om de zichtbare niet-lege cellen nauwkeurig te tellen.

De bovenstaande gegevenstabel verbergt bijvoorbeeld twee rijen 4 en 5. Als u SUBTOTAAL 3 of SUBTOTAAL 103 gebruikt, levert dit twee verschillende resultaten op.

We voeren de formule in en Excel geeft automatisch de functieset weer waaruit u kunt kiezen zonder dat u deze hoeft te onthouden.

Het resultaat bij gebruik van SUBTOTAAL 3 is 3, inclusief verborgen cellen in de rij.

Wat SUBTOTAAL 103 betreft, het zal alleen de niet-lege cellen weergeven die we zien, waarbij verborgen rijen worden genegeerd.

3. Negeer waarden in geneste subtotaalformules

We berekenen bijvoorbeeld het gemiddelde van de totale kilogram stof in magazijnen A1 en A2.

De gemiddelde formule voor magazijn A2= SUBTOTAAL(1,C2:C4) en geeft het resultaat 19.

De gemiddelde formule voor magazijn A1 = SUBTOTAAL(1,C5:C7) heeft het resultaat 38.

Bij het berekenen van het gemiddelde van het totale aantal stoffen in 2 magazijnen worden de gemiddelde resultaten in de 2 magazijnen echter genegeerd. We hebben de formule =SUBTOTAAL(1,C2:C9) en het resultaat verwijdert automatisch het eerder berekende gemiddelde resultaat.

Dingen om te overwegen voordat u de SUBTOTAAL-functie in Excel gebruikt

Hoewel SUBTOTAAL zijn voordelen heeft, zijn er enkele zaken waarmee u rekening moet houden voordat u deze functie gaat gebruiken, waaronder de volgende:

• Zoals gezegd zorgt het gebruik van 1-11 voor het argument function_num ervoor dat SUBTOTAL verborgen waarden bevat, terwijl 101-111 deze negeert.
• Als u een andere waarde dan 1-11 of 101-111 gebruikt, retourneert Excel de fout #WAARDE! Dit zal ook gebeuren voor 3D-celverwijzingen (waarbij naar dezelfde cel, verspreid over meerdere bladen, in een bereik wordt verwezen).
• Wanneer horizontale celbereiken zoals A1:D1 worden gebruikt, worden verborgen waarden automatisch opgenomen (ongeacht de waarde van het function_num-argument). Dit is een beperking van de SUBTOTAAL-functie en kan niet worden overwonnen.
• Het gebruik van SUBTOTAAL op gefilterde gegevens zorgt ervoor dat verborgen waarden altijd worden genegeerd, ongeacht het gebruikte argument function_num.
• SUBTOTAAL kan worden gebruikt als geneste functie als onderdeel van andere functies, inclusief formules die een ALS-functie bevatten.
• Als een andere SUBTOTAAL-formule binnen het gegevensbereik ref1 valt, negeert SUBTOTAAL deze en sluit deze uit van de algemene berekening.
• In Excel kunnen maximaal 254 celbereiken worden gebruikt in een SUBTOTAAL-formule, hoewel er slechts 1 celbereik nodig is om de formule te laten werken.

Foutmeldingen bij het uitvoeren van de SUBTOTAAL-functie in Excel

Wanneer u de functie SUBTOTAAL in Excel berekent, zult u vaak enkele basisfouten tegenkomen:

• #WAARDE!: De functie-ID ligt niet in het bereik 1-11 of 101-111 of heeft een 3D-referentie.
• #DIV/0!: Treedt op wanneer een specifieke som moet worden gedeeld door 0 (bijvoorbeeld bij het berekenen van de gemiddelde of standaarddeviatie van een celbereik dat geen numerieke waarden bevat).
• #NAAM? De functienaam SUBTOTAAL is verkeerd gespeld.

Enkele tips over het gebruik van de SUBTOTAAL-functie in Microsoft Excel die u misschien niet kent

Tip 1: Stel dat u er zeker van wilt zijn dat alle T-shirtkleuren in elke maat in beide magazijnen beschikbaar zijn. U kunt deze stappen volgen:

Stap 1: Klik op Subtotaal . Houd er rekening mee dat deze stap bedoeld is om meer criteria toe te voegen aan de bestaande subtotaalgegevens.

Stap 2: Selecteer AANTAL in het vervolgkeuzemenu en Grootte in het veld ' Subtotaalveld toevoegen aan '. Schakel vervolgens Huidige subtotalen vervangen uit . Wanneer u op OK klikt , ontvangt u de volgende gegevenstabel:

Dit zorgt ervoor dat u over voldoende verschillende dimensies beschikt en dat u de gegevens kunt classificeren op een manier die geen herhaalde bewerkingen vereist.

Tip 2 : Het is altijd handig om gegevens per kolom te sorteren.

Tip 3: Helpt u bij het opnemen van een label in de eerste rij.

Tip 4: Als u gegevens wilt samenvatten, schakelt u het selectievakje ' Samenvatting onder de gegevens bij het invoegen van subtotaal ' uit. - Samenvatting onderstaande gegevens bij het invoegen van de SUBTOTAAL-functie.

Kortom, wat is de subtotaalfunctie in Excel?

Zoals de naam al doet vermoeden, wordt de functie SUBTOTAAL in Excel gebruikt om subtotalen te berekenen. De Excel SUBTOTAAL-functie retourneert geaggregeerde resultaten voor de opgegeven waarden. SUBTOTAAL kan de som, het gemiddelde, het aantal, het maximum berekenen… Deze Excel kan ook waarden in verborgen rijen opnemen of uitsluiten.

• Doel: Bereken het subtotaal in een lijst of database.
• Retourwaarde: een getal dat een type subtotaal vertegenwoordigt.
• Argument:
  • function_num- Een getal dat specificeert welke functie moet worden gebruikt om subtotalen in een lijst te berekenen.
  • ref1- Een benoemd bereik of verwijzing naar een subtotaal.
  • ref2- [optioneel] Een benoemd bereik of verwijzing naar een subtotaal.

Met de functie SUBTOTAAL kunt u snel een gegevensset analyseren zonder afhankelijk te zijn van complexere draaitabellen. Als u vastloopt, kunt u ook de functie Subtotaal gebruiken, te vinden op het tabblad Gegevens op het lint, om automatisch een SUBTOTAAL-formule te maken die u vervolgens elders kunt bewerken of kopiëren.

Bekijk meer:

• SUMPRODUCT-functie in Excel: Bereken het totale product van overeenkomstige waarden
• Hoe 2 kolommen Volledige naam en voornaam in Excel te combineren zonder inhoud te verliezen