Download het voorbeeldwerkboek
Deze zelfstudie laat zien hoe u de Excel SUBTOTAAL Functie in Excel om samenvattende statistieken te berekenen.
SUBTOTAAL Functieoverzicht
De functie SUBTOTAAL Berekent een samenvattende statistiek voor een reeks gegevens. Beschikbare statistieken omvatten, maar zijn niet beperkt tot, gemiddelde, standaarddeviatie, aantal, min en max. Zie de volledige lijst hieronder in het gedeelte over functie-ingangen:
Om de SUBTOTAAL Excel-werkbladfunctie te gebruiken, selecteert u een cel en typt u:
(Let op hoe de formule-invoer verschijnt)
SUBTOTAAL Functiesyntaxis en ingangen:
| 1 | =SUBTOTAAL(functie_getal,REF1) |
function_num - Een getal dat aangeeft welke bewerking moet worden uitgevoerd.
REF1 - Bereiken of referenties die gegevens bevatten om te berekenen.
Wat is de SUBTOTAAL-functie?
Het SUBTOTAAL is een van de unieke functies in spreadsheets omdat het het verschil kan zien tussen verborgen cellen en niet-verborgen cellen. Dit kan erg handig zijn bij het omgaan met gefilterde bereiken of wanneer u berekeningen moet instellen op basis van verschillende gebruikersselecties. Omdat het ook andere SUBTOTAAL-functies van zijn berekeningen weet te negeren, kunnen we het ook gebruiken binnen grote samengevatte gegevens zonder angst voor dubbeltellingen.
Basisoverzicht met SUBTOTAAL
Stel dat u een tabel had met gesorteerde productverkopen en dat u totalen voor elk product wilde maken, evenals een algemeen totaal. U kunt een draaitabel gebruiken of u kunt enkele formules invoegen. Overweeg deze lay-out:
Ik heb enkele SUBTOTAAL-functies in de cellen B5 en B8 geplaatst die eruitzien als
| 1 | =SUBTOTAAL(9, B2:B4) |
Vanuit de syntaxis kunt u verschillende getallen gebruiken voor het eerste argument. In ons specifieke geval gebruiken we 9 om aan te geven dat we een som willen doen.
Laten we ons concentreren op cel B9. Het heeft deze formule, die het volledige gegevensbereik van kolom B bevat, maar niet de andere subtotalen.
| 1 | =SUBTOTAAL(9, B2:B8) |
OPMERKING: Als u niet alle samenvattingsformules zelf wilt schrijven, kunt u naar het Data-lint gaan en de wizard Overzicht - Subtotaal gebruiken. Het zal automatisch rijen invoegen en de formules voor u plaatsen.
Verschil in eerste argumenten
In het eerste voorbeeld gebruikten we een 9 om aan te geven dat we een som wilden doen. Het verschil tussen het gebruik van 9 en 109 is hoe we willen dat de functie verborgen rijen verwerkt. Als u de 1XX-aanduidingen gebruikt, bevat de functie geen rijen die handmatig zijn verborgen of gefilterd.
Hier is onze tafel van vroeger. We hebben de functies verplaatst zodat we het verschil tussen de 9 en 109 argumenten kunnen zien. Met alle zichtbare, de resultaten zijn hetzelfde.
Als we een filter toepassen om de waarde van 6 in kolom B eruit te filteren, blijven de twee functies hetzelfde.
Als we de rijen handmatig verbergen, zien we het verschil. De 109-functie kon de verborgen rij negeren, terwijl de 9-functie dat niet deed.
Wiskundige bewerking wijzigen met SUBTOTAL
Misschien wilt u uw gebruiker soms de mogelijkheid geven om te wijzigen welk type berekeningen wordt uitgevoerd. Willen ze bijvoorbeeld de som of het gemiddelde. Aangezien SUBTOTAL de wiskundige bewerking bestuurt met een argumentnummer, kunt u dit in een enkele formule schrijven. Dit is onze opstelling:
We hebben een vervolgkeuzelijst gemaakt in D2 waar de gebruiker "Som" of "Gemiddeld" kan selecteren. De formule in E2 is:
| 1 | =SUBTOTAAL(IF(D2="Gemiddelde",1,IF(D2="Som",9)),B2:B4) |
Hier gaat de ALS-functie bepalen welk numeriek argument aan het SUBTOTAAL moet worden gegeven. Als A5 "Gemiddeld" is, wordt een 1 uitgevoerd en geeft SUBTOTAAL het gemiddelde van B2:B4. Of, als A5 gelijk is aan "Sum", dan voert de IF een 9 uit en krijgen we een ander resultaat.
U kunt deze mogelijkheid uitbreiden door een opzoektabel te gebruiken om nog meer soorten bewerkingen op te sommen die u wilt uitvoeren. Uw opzoektabel kan er als volgt uitzien:
Dan zou je de formule in E2 kunnen veranderen in
| 1 | =SUBTOTAAL(VERT.ZOEKEN(A5, Opzoektabel, 2, 0), B2:B4) |
Voorwaardelijke formules met SUBTOTAAL
Hoewel SUBTOTAL veel bewerkingen heeft die het kan doen, kan het zelf geen criteria controleren. We kunnen het echter in een helperkolom gebruiken om deze bewerking uit te voeren. Wanneer u een kolom met gegevens heeft waarvan u weet dat deze zal altijd een stukje gegevens bevat, kunt u de mogelijkheid van SUBTOTAL gebruiken om verborgen rijen te detecteren.
Dit is de tabel waarmee we in dit voorbeeld zullen werken. Uiteindelijk willen we de waarden voor "Apple" kunnen optellen, maar de gebruiker ook de kolom Qty laten filteren.
Maak eerst een hulpkolom waarin de functie SUBTOTAAL zal staan. In C2 is de formule:
| 1 | =SUBTOTAAL(103, A2) |
Onthoud dat 103 betekent dat we een COUNTA willen doen. Ik raad aan COUNTA te gebruiken, omdat je dan je referentiecel van A2 kunt laten vullen met of cijfers of tekst. Je hebt nu een tabel die er als volgt uitziet:
Dit lijkt in eerste instantie niet handig omdat alle waarden slechts 1 zijn. Als we rij 3 echter verbergen, verandert die "1" in C3 in een 0 omdat deze naar een verborgen rij wijst. Hoewel het onmogelijk is om een afbeelding te hebben die de waarde van de specifieke verborgen cel laat zien, kunt u deze controleren door de rij te verbergen en vervolgens een basisformule zoals deze te schrijven om dit te controleren.
| 1 | =C3 |
Nu we een kolom hebben die in waarde verandert, afhankelijk van of deze verborgen is of niet, zijn we klaar om de laatste vergelijking te schrijven. Onze SUMIFS zullen er zo uitzien
In deze formule gaan we alleen waarden uit kolom B optellen als kolom A gelijk is aan "Apple", en de waarde in kolom C is 1 (ook bekend als de rij is niet verborgen). Laten we zeggen dat onze gebruiker de 600 eruit wil filteren, omdat deze abnormaal hoog lijkt. We kunnen zien dat onze formule het juiste resultaat geeft.
Met deze mogelijkheid zou je een vinkje kunnen toepassen op een AANTALLEN.ALS, SOMMEN, of zelfs een SOMPRODUCT. U voegt de mogelijkheid toe om uw gebruikers enkele table slicers te laten bedienen en u bent klaar om een geweldig dashboard te maken.
SUBTOTAAL in Google Spreadsheets
De SUBTOTAAL-functie werkt in Google Spreadsheets precies hetzelfde als in Excel:
SUBTOTAAL Voorbeelden in VBA
U kunt ook de SUBTOTAL-functie in VBA gebruiken. Type:applicatie.werkbladfunctie.subtotaal(function_num,reh1)
De volgende VBA-instructies uitvoeren:
| 1234567891011121314151617 | Range("C7") = Application.WorksheetFunction.Subtotal(1, Range("C2:C5"))Range("C8") = Application.WorksheetFunction.Subtotal(2, Range("C2:C5"))Range("C9") = Application.WorksheetFunction.Subtotal(4, Range("C2:C5"))Range("C10") = Application.WorksheetFunction.Subtotal(5, Range("C2:C5"))Range("C11") = Application.WorksheetFunction.Subtotal(9, Range("C2:CE5"))Range("D7") = Application.WorksheetFunction.Subtotal(1, Range("D2:D5"))Range("D8") = Application.WorksheetFunction.Subtotal(2, Range("D2:D5"))Range("D9") = Application.WorksheetFunction.Subtotal(4, Range("D2:D5"))Range("D10") = Application.WorksheetFunction.Subtotal(5, Range("D2:D5"))Range("D11") = Application.WorksheetFunction.Subtotal(9, Range("D2:D5"))Range("E7") = Application.WorksheetFunction.Subtotal(1, Range("E2:E5"))Range("E8") = Application.WorksheetFunction.Subtotal(2, Range("E2:E5"))Range("E9") = Application.WorksheetFunction.Subtotal(4, Range("E2:E5"))Range("E10") = Application.WorksheetFunction.Subtotal(5, Range("E2:E5"))Range("E11") = Application.WorksheetFunction.Subtotal(9, Range("E2:E5")) |
zal de volgende resultaten opleveren:
Voor de functieargumenten (functie_getal, enz.), kunt u ze ofwel rechtstreeks in de functie invoeren, of variabelen definiëren om in plaats daarvan te gebruiken.
Keer terug naar de lijst met alle functies in Excel
