Subtotalen met formules

Voorbeeldbestanden bij dit artikel:
  • Subtotalen
  • Inleiding

    Wie kent er de formule =SUBTOTAAL( ) ? En de formule =AGGREGAAT( ) ? Die functies laten ons toe om op een gemakkelijke manier een dataset samen te vatten. Het zijn hele generieke functies. Zo kan je de functies gebruiken om de som van data te nemen, maar even goed het gemiddelde, het maximum/minimum, enz. De naam van de functies verandert niet, wel een argument in de functie die je gebruikt in de cel. Daarnaast is het ook zeer handig dat deze functies geschikt zijn voor het negeren van verborgen rijen, of foutmeldingen, of cellen die reeds zelf een subtotaal bevatten. Met andere woorden, het zijn zeer veelzijdige functies die we eens van naderbij bekijken.

    Het voorbeeld waar ik dit onlangs toegepast heb, gaat over het inschatten van de inspanningen voor een project. Het project is onderverdeeld in taken en subtaken. Elk van de deeltaken moet een schatting krijgen, wat uiteindelijk uitmondt in een schatting voor taken en, finaal, het gehele project. Merk op dat Excel ook een SUBTOTAAL functionaliteit heeft die ingebouwd is, echter, de layout kan dan wel eens ernstig gecompromitteerd worden. Wens je dit toch te bekijken, je vindt dit in het menu 'Gegevens'.

    De data ziet er (geanonimiseerd) uit als in bovenstaande afbeelding. De lege cellen in kolom G en H moeten opgevuld worden met het totaal van de onderliggende lijnen van de rubriek. Denk voor het voorbeeld dat kolom G uren of dagen/dagdelen bevat, en kolom H bevat de kosten door G te vermenigvuldigen met 400. Uiteraard werken we in Excel... we willen geen harde waarden in die (nu) lege cellen, maar formules. Formules die uitbreiden of inkrimpen met het aantal lijnen binnen de rubriek. Tevens, op het niveau van het project willen we geen dubbeltellingen. Hiermee bedoelen we dat een taak de som is van deeltaken, maar als we het project totaliseren als som van cellen/taken moeten we de waarden voor de deeltaken uitsluiten om dubbeltellingen te vermijden. Dit kan allemaal met de functies =SUBTOTAAL( ) en =AGGREGAAT( ).

    Uitwerking

    Uiteindelijk ben ik nog een stap verder gegaan. Ik heb VBA code geschreven om het zetten van de formules te automatiseren. Zo zijn we in staat om gewoon cellen te selecteren, op een knop te drukken, en VBA zet de gewenste totalen met de formules.

    Selecteer de cellen G4:G7,G9,G11:G14,G17:G20, enz. (dus houd de Ctrl toets ingedrukt). Druk dan op de groene knop en vervolgens op OK. Het resultaat is dat er sommen gemaakt worden in de lege cellen boven de geselecteerde cellen. De cellen hebben een formule gekregen zoals aangegeven in het gebruikersformulier.

    Als we naar de AGGREGAAT formule van cel G3 kijken zien we inderdaad het bereik G4:G7 staan. Opgeteld geeft dat 17 uren/dagen/... Daarnaast bevat de AGGREGAAT formule ook nog 2 andere argumenten. De functie moet namelijk weten of het totaal een som/gemiddelde/maximum/minimum/... is. Tevens kunnen we kiezen wat we met eventueel verborgen rijen of cellen met foutwaarden of cellen met een AGGREGAAT/SUBTOTAAL formule willen doen. Verderop in het artikel volgt een oplijsting van de mogelijke waarden van deze argumenten.

    Onder het negeren van verborgen rijen moet je verstaan de onderstaande mogelijkheden:

    • rijen verborgen door het manueel verbergen van de rijen in het werkblad
    • rijen verborgen door de rijhoogte op 0 te zetten
    • rijen verborgen door een autofilter of uitgebreide filter die actief is
    • rijen verborgen door een filter in een tabel (ListObject)
    • rijen verborgen door het toepassen van een groepering op rijen
    Merk op dat het gaat over verborgen rijen (verticale bereiken) en niet verborgen kolommen (horizontale bereiken) !

    Hoe heb ik dit opgelost ? Wel, ik maakte een aantal namen aan in het werkboek, zoals AGGR_FUN en AGGR_OPT. Als je wil sommeren is de waarde voor AGGR_FUN die je nodig hebt, 9. Kijk even in de namen via Ctrl-F3. Wens je gemiddelden, dan neem je 1 in plaats van 9. Neem 2 voor aantallen, enz. Met de waarde 0 voor AGGR_OPT sluit je geneste SUBTOTAAL en AGGREGAAT functies uit, maar geen cellen met foutwaarden of cellen van verborgen rijen.

    Wat is die nFACTOR dan nog in de formule ? Dat is ook een naam in het werkboek, en standaard staat de waarde op 1. Wil je snel alle waarden bvb. halveren, zet het dan op 0,5. Of alle waarden verhogen met een marge van 5% kies dan 1,05 als factor.

    Tot slot vermeld ik nog 2 tips om niet te veel cellen manueel te moeten selecteren, zoals hierboven wel manueel gedaan werd:

    1. selecteer een heel bereik, druk F5 > Speciaal... > Constanten > OK
    2. selecteer een heel bereik, maak de keuzes in het gebruikersformulier en net als je OK wil drukken, houd de Shift toets ingedrukt. Ik heb dat nl. mee geprogrammeerd.

    Wil je in dit bestand gemakkelijk taken en subtaken toevoegen door een rij te dupliceren? lees hier een eerder artikel over mijn sneltoetscombinatie Ctrl + e die precies dat doet.

    Hieronder volgen als uitsmijter nog 2 tabellen met de mogelijke waarden voor de argumenten in de functie AGGREGAAT, en 1 tabel voor de SUBTOTAAL.

    FunctienummerFunctie
    1GEMIDDELDE
    2AANTAL
    3AANTALARG
    4MAX
    5MIN
    6PRODUCT
    7STDEV.S
    8STDEV.P
    9SOM
    10VAR.S
    11VAR.P
    12MEDIAAN
    13MODUS.ENKELV
    14GROOTSTE
    15KLEINSTE
    16PERCENTIEL.INC
    17KWARTIEL.INC
    18PERCENTIEL.EXC
    19KWARTIEL.EXC

    OptieGedrag
    0 of leegNegeer geneste SUBTOTAAL en AGGREGAAT functies
    1 Negeer geneste SUBTOTAAL, AGGREGAAT functies, en verborgen rijen
    2 Negeer geneste SUBTOTAAL, AGGREGAAT functies, en foutwaarden
    3 Negeer geneste SUBTOTAAL, AGGREGAAT functies, verborgen rijen, en foutwaarden
    4 Negeer niets
    5 Negeer verborgen rijen
    6 Negeer foutwaarden
    7 Negeer verborgen rijen en foutwaarden

    Functienummer (1)Functienummer (2)Functie
    1101GEMIDDELDE
    2102AANTAL
    3103AANTALARG
    4104MAX
    5105MIN
    6106PRODUCT
    7107STDEV.S
    8108STDEV.P
    9109SOM
    10110VAR.S
    11111VAR.P

    (1): negeer verborgen waarden niet
    (2): negeer verborgen waarden

    Microsoft hulppagina over SUBTOTAAL
    Microsoft hulppagina over AGGREGAAT




    Over Wim

    Wim Gielis is Business Intelligence consultant en Excel expert

    Andere links