Functie SUBTOTAAL in Excel

Met de functie SUBTOTAAL kun je meerdere typen berekeningen uitvoeren op een bereik met gegevens, zoals het gemiddelde, het maximum of de som. Deze functie lijkt dan ook op functies zoals SOM, GEMIDDELDE, AANTAL, MAX en MIN.

Het verschil is dat de functie SUBTOTAAL rekening houdt met gefilterde gegevens. Rijen die door een filter zijn verborgen, worden automatisch niet meegenomen in de berekening. Daarnaast voorkomt deze functie dubbele berekeningen, omdat waarden uit andere ‘SUBTOTAAL formules’ worden genegeerd.

In dit artikel komen meerdere voorbeelden aan bod, zodat je goed kunt zien hoe deze functie precies verschilt ten opzichte van bijvoorbeeld SOM, en wanneer je beter voor SUBTOTAAL kunt kiezen.

Schrijfwijze formule

SUBTOTAAL(functie_getal;verw1;[verw2];…)

Argumenten

Functie_getal

Met dit argument bepaal je welke berekening SUBTOTAAL moet uitvoeren. In plaats van een functienaam (zoals SOM of GEMIDDELDE) voer je hier een getal in.

• 1 – GEMIDDELDE
• 2 – AANTAL
• 3 – AANTALARG
• 4 – MAX
• 5 – MIN
• 6 – PRODUCT
• 7 – STDEV
• 8 – STDEVP
• 9 – SOM
• 10 – VAR
• 11 – VARP

Daarnaast bestaan er ook functie_getallen van 101 t/m 111. Deze werken hetzelfde, maar negeren ook handmatig verborgen rijen (niet alleen gefilterde rijen):

• 101 – GEMIDDELDE
• 102 – AANTAL
• 103 – AANTALARG
• 104 – MAX
• 105 – MIN
• 106 – PRODUCT
• 107 – STDEV
• 108 – STDEVP
• 109 – SOM
• 110 – VAR
• 111 – VARP

Dit klinkt lastiger dan het is. Hoe het werkt, en wat de verschillen zijn, zal duidelijk worden in de voorbeelden.

verw1

Dit is het eerste bereik waarop de berekening wordt toegepast. Dit is vergelijkbaar met hoe je bijvoorbeeld met de functie SOM een bereik met getallen wilt optellen.

Optioneel kun je extra bereiken opgeven, die je kunt meenemen in dezelfde berekening. In de praktijk komt dit minder vaak voor.

Voorbeeld

In dit voorbeeld hebben we een overzicht met gewerkte uren:

• Kolom A: Naam medewerker
• Kolom B: Afdeling
• Kolom C: Aantal gewerkte uren

Aan de kolomkoppen zijn filters toegevoegd via Gegevens > Filter.

We willen het totaal aantal gewerkte uren berekenen en vergelijken daarbij de functies SUBTOTAAL en SOM.

Berekening zonder filter

In cel B8 voeren we de volgende formule in:

=SUBTOTAAL(9;C2:C6)

Het functiegetal 9 staat voor SOM.

Ter vergelijking voeren we in cel B9 de formule in:

=SOM(C2:C6)

Beide formules retourneren hetzelfde resultaat, namelijk 40 gewerkte uren.

Zolang er geen filter actief is, werken SUBTOTAAL en SOM dus hetzelfde.

Berekening met een filter

We willen nu alleen de gewerkte uren zien van de afdeling Verkoop.

Via de filter in kolom B selecteren we daarom alleen Verkoop:

In het overzicht blijven nu alleen de rijen van deze afdeling zichtbaar, maar het resultaat van de formules is nu niet langer hetzelfde.

• De functie SUBTOTAAL retourneert 27 (8 + 9 + 10).
• De functie SOM blijft echter 40 tonen.

De functie SOM houdt namelijk geen rekening met gefilterde rijen, terwijl SUBTOTAAL dat wel doet. Dit is een van de belangrijkste verschillen tussen beide functies.

Handmatig verborgen rijen

In dit voorbeeld gebruiken we geen filter, maar verbergen we zelf een rij, bijvoorbeeld rij 4 (rij selecteren en daarna rechtermuisklik > Verbergen).

Het resultaat van beide formules blijft nu 40.

Dit komt doordat we in SUBTOTAAL het functiegetal 9 gebruiken. Deze variant negeert alleen gefilterde rijen, niet handmatig verborgen rijen.

Wil je dat handmatig verborgen rijen ook worden genegeerd, dan gebruik je de ‘100 variant’. Voor de som wordt het functiegetal dan 109:

=SUBTOTAAL(109;C2:C6)

Nu retourneert SUBTOTAAL wel het juiste totaal van alleen de zichtbare rijen, namelijk 31.

Andere berekeningen dan SOM

In deze voorbeelden hebben we de som berekend, maar hetzelfde principe geldt dus voor andere berekeningen:

• Gemiddelde – functiegetal 1 (of 101)
• Aantal – functiegetal 2 (of 102)
• Maximum – functiegetal 4 (of 104)

Welke variant je gebruikt, hangt af van de vraag of je handmatig verborgen rijen wilt meenemen in de berekening of niet.

SUBTOTAAL negeert andere SUBTOTAAL-formules

De functie SUBTOTAAL heeft nog een ander handig voordeel, dat bij veel mensen onbekend is. Deze functie negeert namelijk automatisch andere ‘subtotaal formules’ die zich binnen hetzelfde bereik bevinden. Hiermee voorkom je dat totalen per ongeluk dubbel worden meegenomen.

In de praktijk is dit vooral handig wanneer je werkt met subtotalen per groep en daaronder nog een eindtotaal wilt berekenen.

Het voorbeeld is een beetje aangepast, zodat het aantal gewerkte uren nu per afdeling een subtotaal heeft gekregen.

Daaronder willen we in cel C10 het totaal berekenen van alle uren, met de formule =SOM(C2:C8).

Excel telt nu alle losse uren en de subtotaalregels (rij 5 en 8), waaronder het totaal dubbel wordt geteld.

Gebruik je in plaats daarvan de functie SUBTOTAAL, dan worden alle onderliggende ‘subtotaal formules’ genegeerd en krijg je wel het juiste eindtotaal:

SUBTOTAAL gebruiken via een tabel (zonder formule)

Je hoeft niet per se zelf een formule te maken met de functie SUBTOTAAL. Als je je gegevens namelijk omzet naar een tabel, gebruikt Excel deze functie automatisch.

Dit werkt als volgt:

1. Selecteer je gegevens
2. Ga in het lint naar Invoegen > tabel
3. Zet een vinkje bij ‘Mijn tabel bevat kopteksten’
4. Zet een vinkje in het lint in het tabblad Tabelontwerp bij de optie Totaalrij

In de totaalrij kun je vervolgens zelf kiezen welk type berekening je wilt uitvoeren door op het pijltje naar beneden te klikken.

In bovenstaand voorbeeld is gekozen voor MAX. En je ziet in de formulebalk dat Excel nu de functie SUBTOTAAL zelf heeft gebruikt, met het functiegetal 104.

Als je een overzicht met filterknoppen wilt, is het gebruiken van tabellen vaak de beste keuze. Je hoeft dan dus zelf geen formules te maken om snel berekeningen uit te voeren.

Je wilt echter niet altijd een tabel gebruiken. Je kunt dan de ‘gewone’ functies gebruiken zoals SOM en GEMIDDELDE. Maar als je wel rijen wilt verbergen (handmatig of met filters), dan is de functie SUBTOTAAL een ideale oplossing.