Wanneer uw Excel-werkblad verborgen rijen, gefilterde gegevens of gegroepeerde gegevens bevat, gebruikt u de Excel SUBTOTAAL-functie. De functie SUBTOTAAL kan verborgen waarden in berekeningen opnemen of uitsluiten. Naast het vinden van het totaal van een gegevensgroep, kan Excel het gemiddelde, maximum, minimum, standaarddeviatie en variantie van uw gegevens berekenen. Zo voegt u subtotalen in Excel in.
Instructies in dit artikel zijn van toepassing op Excel voor Microsoft 365, Excel 2019 en Excel 2016.
Syntaxis van de SUBTOTAL-functie
Gebruik de functie SUBTOTAAL in Excel om de waarden in een werkblad op verschillende manieren samen te vatten. Het is vooral handig wanneer uw werkblad verborgen rijen bevat die u in de berekening wilt opnemen.
De syntaxis van de SUBTOTAL-functie is: SUBTOTAL(function_num, ref1, ref2, …)
Het function_num argument is vereist en specificeert het type wiskundige bewerking dat moet worden gebruikt voor het subtotaal. De functie SUBTOTAAL kan getallen optellen, de gemiddelde waarde van geselecteerde getallen berekenen, de maximum- en minimumwaarden in een bereik vinden, het aantal waarden in een geselecteerd bereik tellen en meer.
De functie SUBTOTAAL negeert cellen die geen gegevens bevatten en cellen met niet-numerieke waarden.
Dit argument is een getal en hangt ervan af of u verborgen rijen in het resultaat wilt opnemen of verborgen rijen wilt uitsluiten van het resultaat. Deze rijen kunnen handmatig worden verborgen of verborgen door een filter.
De function_num argumenten omvatten:
| Functietaak | function_num | function_num |
|---|---|---|
| (inclusief verborgen waarden) | (sluit verborgen waarden uit) | |
| GEMIDDELDE | 1 | 101 |
| COUNT | 2 | 102 |
| COUNTA | 3 | 103 |
| MAX | 4 | 104 |
| MIN | 5 | 105 |
| PRODUCT | 6 | 106 |
| STDEV | 7 | 107 |
| STDEVP | 8 | 108 |
| SOM | 9 | 109 |
| VAR | 10 | 110 |
| VARP | 11 | 111 |
De function_num referentieargumenten 1 t/m 11 bevatten alleen waarden in verborgen rijen bij gebruik van het Verberg commando om rijen te verbergen. Als u de opdracht Filter gebruikt, bevatten SUBTOTAL-berekeningen geen verborgen filterresultaten.
Het ref1 argument is vereist. Dit zijn de cellen die worden gebruikt om de resultaten van het geselecteerde argument functie_num te berekenen. Dit argument kan een waarde, een enkele cel of een celbereik zijn.
De ref2, … argumenten zijn optioneel. Dit zijn extra cellen die in de berekening worden meegenomen.
Gebruik de SUBTOTAAL-functie met verborgen rijen
Excel-functies kunnen handmatig of met behulp van het dialoogvenster Functieargumenten worden ingevoerd. Om te illustreren hoe u de functie handmatig invoert met behulp van de formulebalk, wordt in het volgende voorbeeld het argument COUNT function_num gebruikt om het aantal waarden in zichtbare rijen en in zowel zichtbare als verborgen rijen te tellen.
De functie SUBTOTAAL gebruiken om het aantal rijen in een werkblad te tellen:
- Begin met een werkblad dat meerdere rijen gegevens bevat.
-
Selecteer de cel die het aantal zichtbare rijen zal bevatten.
-
Voer in de functiebalk =SUBTOTAL in. Terwijl u typt, stelt Excel een functie voor. Dubbelklik op de functie SUBTOTAL.
Als u het dialoogvenster Functieargumenten wilt gebruiken om de functie SUBTOTAAL in te voeren, gaat u naar Formules en selecteert u Math & Trig >SUBTOTAAL.
Image -
Dubbelklik in het vervolgkeuzemenu dat verschijnt op het argument 102 – COUNT function_num.
Image -
Typ een komma (,).
Image -
Selecteer in het werkblad de cellen die u in de formule wilt opnemen.
Image -
Druk op Enter om het resultaat te zien in de cel die u in stap 2 hebt geselecteerd.
Image - Selecteer de cel die het aantal zichtbare en verborgen rijen zal bevatten.
- Voer in de functiebalk =SUBTOTAL in. Terwijl u typt, stelt Excel een functie voor. Dubbelklik op de functie SUBTOTAL.
- In het vervolgkeuzemenu dat verschijnt, dubbelklikt u op het argument 2 – COUNT function_num en typt u een komma (,).
-
Selecteer in het werkblad de cellen die u in de formule wilt opnemen en druk vervolgens op Enter.
Image -
Verberg verschillende rijen gegevens. In dit voorbeeld waren rijen met alleen verkopen onder $ 100.000 verborgen.
Image
Gebruik de SUBTOTAAL-functie met gefilterde gegevens
Het gebruik van de functie SUBTOTAAL op gefilterde gegevens negeert gegevens in rijen die door het filter zijn verwijderd. Elke keer dat de filtercriteria veranderen, herberekent de functie om het subtotaal voor de zichtbare rijen weer te geven.
De functie SUBTOTAAL gebruiken om de verschillen in berekeningsresultaten te zien tijdens het filteren van gegevens:
-
Maak SUBTOTAAL formules. Maak bijvoorbeeld formules om het subtotaal en de gemiddelde waarden van de gefilterde gegevens te bepalen.
Het maakt niet uit of je het argument function_num gebruikt voor zichtbare of verborgen rijen. Beide argumenten geven hetzelfde resultaat in gefilterde gegevens.
Image - Selecteer een willekeurige cel in de dataset.
-
Ga naar Home en selecteer vervolgens Sorteren en filteren > Filter.
Image -
Gebruik de vervolgkeuzepijlen om de werkbladgegevens te filteren.
Image -
Merk op hoe de waarden veranderen telkens wanneer u verschillende filtercriteria selecteert.
Image
Gebruik de SUBTOTAAL-functie met gegroepeerde gegevens
Wanneer gegevens zijn gegroepeerd, is er een manier om de functie SUBTOTAAL op elke individuele groep toe te passen en vervolgens het eindtotaal voor de hele gegevensset te berekenen.
- Selecteer een willekeurige cel in de dataset.
-
Selecteer Data > Subtotal om het dialoogvenster Subtotal te openen.
Image - Selecteer de Bij elke wijziging in vervolgkeuzepijl en kies de groepering waarnaar elk subtotaal wordt berekend.
- Selecteer de Use function vervolgkeuzepijl en kies een function_num.
- Selecteer in de lijst Add subtotaal aan de kolom waarop de formule wordt toegepast.
-
Selecteer OK.
Image -
Subtotalen worden ingevoegd voor elke gegevensgroep en een eindtotaal wordt onderaan de gegevensset ingevoegd.
Image - Om het function_num te wijzigen, markeert u een willekeurige cel in de dataset en selecteert u Data > Subtotaal. Maak vervolgens uw keuzes in het Subtotaal dialoogvenster.
