Subtotalen met formules
- 17/08/2019
 | Voorbeeldbestanden bij dit artikel: |
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
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. Foutwaarden kunnen ook het gevolg zijn van formules uit invoegtoepassingen, bvb.
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:
- selecteer een heel bereik, druk F5 > Speciaal... > Constanten > OK
- 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.
| Functienummer | Functie |
| 1 | GEMIDDELDE |
| 2 | AANTAL |
| 3 | AANTALARG |
| 4 | MAX |
| 5 | MIN |
| 6 | PRODUCT |
| 7 | STDEV.S |
| 8 | STDEV.P |
| 9 | SOM |
| 10 | VAR.S |
| 11 | VAR.P |
| 12 | MEDIAAN |
| 13 | MODUS.ENKELV |
| 14 | GROOTSTE |
| 15 | KLEINSTE |
| 16 | PERCENTIEL.INC |
| 17 | KWARTIEL.INC |
| 18 | PERCENTIEL.EXC |
| 19 | KWARTIEL.EXC |
| Optie | Gedrag |
| 0 of leeg | Negeer 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 |
| 1 | 101 | GEMIDDELDE |
| 2 | 102 | AANTAL |
| 3 | 103 | AANTALARG |
| 4 | 104 | MAX |
| 5 | 105 | MIN |
| 6 | 106 | PRODUCT |
| 7 | 107 | STDEV.S |
| 8 | 108 | STDEV.P |
| 9 | 109 | SOM |
| 10 | 110 | VAR.S |
| 11 | 111 | VAR.P |
(1): negeer verborgen waarden niet
(2): negeer verborgen waarden
Microsoft hulppagina
over SUBTOTAAL
Microsoft hulppagina
over AGGREGAAT
