Subtotalen in Excel

Met de functie "Subtotaal" is in Excel veel mogelijk. Het kan gebruikt worden om deel optellingen te maken en een totaal. Het kan een hulpmiddel zijn bij het gebruik van het automatisch filter en kan in een database, cijfers groeperen en dienen als hulpmiddel bij het presenteren van gegevens. Het subtotaal hoeft niet altijd te bestaan uit een optelling, het kan ook een gemiddelde zijn of het aantal argumenten. Hieronder voorbeelden voor het gebruik van de functie subtotaal.

Kennismaking met de functie subtotaal

De functie "Subtotaal" is een zeer veelzijdige functie en toch kennen velen deze functie niet. In dit artikel een drietal voorbeelden waarin subtotalen worden gebruikt en op verschillende manieren toe wordt gepast. We beginnen eenvoudig en het laatste voorbeeld is wat lastiger... maar met onderstaande uitleg zeker te doen.
Deze uitleg is geschikt voor zowel gebruikers van oudere versies van Excel als voor gebruikers van Excel 2007 (of hoger).

Subtotalen maken met behulp van de knop "Autosom"

Een eenvoudige manier op kennis te maken met de functie subtotaal is het gebruik van de knop "Autosom"(Σ). De functie autosom gaat namelijk, als voldaan wordt aan bepaalde voorwaarden, vanzelf over op een subtotaal. Dit is het subtotaal dat de meeste mensen wel herkennen van school of een economische opleiding. Een optelling van cijfers behorend bij een bepaalde afdeling voor elke afdeling apart (subtotalen) en daaronder een optelling van de resultaten van al deze afdelingen gezamenlijk (groot totaal).

Voor het gebruik van deze mogelijkheid van het subtotaal selecteer je de vakjes waar een optelling moet worden gemaakt en twee cellen onder de cijfers (één voor het laatste subtotaal en de tweede voor het echte totaal). Dit selecteren kan met de "CTRL" toets, houd deze toets ingedrukt bij elke nieuwe selectie van een cel (klik met de linkermuisknop op de te selecteren cel).

Selecteer tegelijk de vakjes B5, B9 en B10 met behulp van de "CTRL" toets.

• Klik nu op de knop "Autosom" (de knop met het sigma teken Σ).
• Je ziet dat er nu in plaats van de gewone functie "Som" gebruik gemaakt wordt van de functie "Subtotaal".
• Er staat in B10 de volgende functie; =SUBTOTAAL(9;B9;B5)

In Excel 2007 zal toch de functie "Som" worden gebruikt maar ook in dit geval is het resultaat de berekening van subtotalen en groot totaal.

Syntaxis van de functie "Subtotaal"

Laten we gaan kijken naar de functie subtotaal. Wat staat er en wat zijn de mogelijkheden van deze functie.

Hieronder de syntaxis, een beschrijving van de basisregel voor deze functie;

• =SUBTOTAAL(9;B9;B5) of
• =SUBTOTAAL(Functie-getal;Verwijzing1;Verwijzing2)

De functie "Subtotaal" heeft twee soorten argumenten. Argumenten worden door ";" van elkaar gescheiden. In het bovenstaande voorbeeld zie je als eerste argument een 9 staan. Dit cijfer staat voor het soort berekening, de "9" is een optelling. Dit argument wordt ook wel het "Functie-getal" genoemd. Het tweede soort argument dat gebruikt wordt is het aangeven van het bereik. Waarover moet een subtotaal worden berekend. Excel noemt dit argument de "Verwijzing" (er zijn meerdere verwijzingen mogelijk). In het bovenstaande voorbeeld een optelling (9) van B9 (verwijzing1) en B5 (verwijzing2).

Het eerste argument (9) impliceert dat er meer mogelijkheden zijn voor het berekenen van een subtotaal. En ja dat is zo. Er zijn 11 verschillende mogelijkheden, ze volgen hieronder.

• 1 staat voor het uitrekenen van het GEMIDDELDE
• 2 staat voor het uitrekenen van een AANTAL
• 3 staat voor het uitrekenen van een AANTALARG (het aantal cellen wat is ingevuld met tekst tellen)
• 4 staat voor het uitrekenen van een MAX (hoogste waarde)
• 5 staat voor het uitrekenen van een MIN (laagste waarde)
• 6 staat voor het uitrekenen van een PRODUCT (vermenigvuldiging)
• 7 staat voor het uitrekenen van een STDEV (standaarddeviatie)
• 8 staat voor het uitrekenen van een STDEVP
• 9 staat voor het uitrekenen van een SOM (optelling)
• 10 staat voor het uitrekenen van een VAR
• 11 staat voor het uitrekenen van een VARP

Subtotalen gebruiken bij het "Autofilter"

Veel mensen kennen de functie "Autofilter" wel maar niet de combinatie hiervan met subtotalen. Dit terwijl het toevoegen van sutotalen aan het autofilter een duidelijke verbetering kan geven aan deze gegevens.

Het autofilter is te gebruiken voor grote lijsten met gegevens, een database, om gegevens die voldoen aan bepaalde criteria te kunnen laten zien. Ik wil in mijn verkoopcijfers kunnen zien wat er in de afgelopen maand is verkocht of uit deze database alleen gegevens zien van één product. Met behulp van de functie subtotaal is in dit geval direct een optelling te zien van welke selectie dan ook. De waarde (uitkomst) van de functie zal automatisch aangepast zijn aan de gekozen voorwaarde via het autofilter en de weergave van de gegevens uit de database.

Om een indruk te krijgen van deze oefening hieronder een voorbeeld van een kleine database waar de functie "Subtotaal" te gebruiken is.

Type de gegevens in Excel.
 
Zet nu het "Autofilter" aan. Dit kan door in Excel 7 (en hoger) te kiezen voor "Sorteren en filteren" uit het lint "Start" en daarna de optie "Filter". In oudere versies van Excel is dit de optie uit het menu "Data","Filter", "Autofilter".
Er komen rechts naast de titels driehoekjes te staan. Hiermee kun je de gegevens uit de desbetreffende kolom gaan selecteren (of sorteren). Selecteer nu bijvoorbeeld alle gegevens met de waarde "Hbo" (laatste kolom) met behulp van het driehoekje bij deze kolom. Klik nu op de knop "Autosom" (Σ) in de cel direct onder de lijst. Dit geeft een subtotaal.
Verander het functie-getal "9" in een "3" om het aantal argumenten te kunnen tellen dat aan de net gekozen selectie voldoet. In het voorbeeld zal dit een waarde "6" opleveren.

Kies nu via het filter de voorwaarde "Mbo" in plaats van "Hbo". Dit zal automatisch resulteren in het cijfer "5". En er zijn ook inderdaad vijf mensen die voldoen aan deze voorwaarde.
Via het autofilter is nu elke selectie te maken en zal het subtotaal van deze selectie het juiste aantal ("3" als functie-getal) of een optelling (gebruik als functie-getal "9") opleveren. Een voorbeeld voor het gebruik van het functie-getal "9" zou kunnen zijn het optellen van de lonen van mensen die 36 uur werken. Of het gemiddelde (gebruik functie-getal "1") loon van vrouwen.

Subtotalen bij database gegevens

Dit laatste voorbeeld van het gebruik van de functie subtotaal is wat lastiger. We kunnen de kleine database gebruiken die in het vorige hoofdstuk is gemaakt. Om met deze laatste mogelijkheid te kunnen werken moet de database aan twee voorwaarden voldoen.

1. Een autofilter bevatten (zie vorige hoofdstuk).
2. Gesorteerd zijn op minimaal één kolom via de knop "A→Z" (in Excel 2007 bevindt deze knop zich onder de optie "Sorteren en filteren" uit het lint "Gegevens" maar ook via het autofilter aanwezig).

Verwijder de laatste regel waar het subtotaal uit de vorige oefening stond. Sorteer de database gegevens uit de vorige oefening op de kolom "Opleiding". Kies nu uit het menu "Data" voor de optie "Subtotalen" of in Excel 7 (en hoger) voor de optie "Subtotalen" uit het onderdeel "Overzicht" in het lint "Gegevens". Kies uit het venster wat nu verschijnt de onderstaande mogelijkheden; Bij iedere wijziging in Opleiding, een Aantal en toevoegen aan Opleiding.
Je ziet nu dat de subtotalen tussen de database worden geplaatst. Daarnaast zie je aan de linkerkant van het scherm dat de gegevens automatisch worden gegroepeerd. De cijfers boven aan de groepering geven het niveau aan dat zichtbaar is. Niveau 2 zal alleen de subtotalen laten zien. Niveau 3 laat alles zien en niveau 1 alleen het totaal van de hele database.

Probeer hier nu zelf wat mee te stoeien. Indien je dit gaat doen, verwijder dan eerst het bestaande subtotaal door de knop "Alles verwijderen" rechts-onderin het venster bij het maken van het subtotaal (zie ook het vorige plaatje). Bereken bijvoorbeeld het gemiddelde loon van alle mensen op basis van het aantal uur dat men werkt (sorteer op de kolom "Werktijden", maak een subtotaal op kolom "Werktijden" gebruik de functie "Gemiddelde" en zet het resultaat in de kolom "Loon").

Aan de hand van een simpel voorbeeld hebben we de functie "Subtotalen" bekeken. Deze functie heeft vele mogelijkheden en probeer het eens in de databases waarmee je in de praktijk werkt. Succes gegarandeerd.