Onlangs heeft Excel nieuwe samenvattingsfuncties geïntroduceerd die tot nu toe alleen met behulp van draaitabellen uitgevoerd konden worden, waarmee u snel totalen naar verschillende inzichten kunt creëren. In dit artikel zal ik de nieuwe aggregatiefuncties nader toelichten aan de hand van een praktisch voorbeeld.

Basisdata

We starten met de volgende Excel-tabel, genaamd DataOmzet:

Als u de omzet per product wenst te weten, zult u hiervoor een draaitabel aanmaken. Een nadeel van een draaitabel is, dat u deze moet vernieuwen indien data in de brontabel zich wijzigt.

GROEPEREN.PER (GROUPBY)

Met de functie GROEPEREN.PER (GROUPBY) kunt u een dynamische samenvattingstabel aanmaken. Deze functie kent 3 verplichte argumenten en 5 optionele argumenten. Met de volgende formule =GROEPEREN.PER(DataOmzet[product];DataOmzet[bedrag];SOM;3) maakt u de volgende dynamische matrixtabel aan:

Aan het blauwe kader kunt u herkennen dat we hier te maken hebben met een dynamische matrixformule. Het gehele gebied wordt automatisch aangevuld met de betrokken items en waarden. Met behulp van de functie-assistent kunt u de functie nader inspecteren.

Met de functie GROEPEREN.PER kunt u gegevens groeperen, aggregeren, sorteren en filteren op basis van de velden die u opgeeft.

Met de rijvelden worden unieke items gefilterd en met de waarden wordt de omzet geaggregeerd. Via de functie bepaalt u hoe de omzet wenst te aggregeren (SOM, PERCENTOF, GEMIDDELDE, AANTAL, enzovoort).

Via de 5 optionele argumenten kunt u:

• De kopteksten al dan niet weergeven
• De totalen al dan niet weergeven
• De wijze van sorteren bepalen
• De posten al dan niet filteren
• De relatievelden opgeven aan de row_fields

Als u de kopteksten wenst weer te geven dient u in selectie ook de kopteksten mee te nemen. De formule wordt dan:

=GROEPEREN.PER(DataOmzet[[#Alles];[product]];DataOmzet[[#Alles];[bedrag]];SOM;3)

De tabel ziet er dan als volgt uit:

Lees ook op CFO.nl: Expeditie CFO – Hans van Tuyll (CFO van NAM): “Als CFO richt ik mij volledig op wat wél binnen onze invloed ligt.”  

DRAAIEN.PER (PIVOTBY)

Met de functie DRAAIEN.PER (PIVOTBY) kunt u dynamische kruistabellen maken. De volgende formule:

=DRAAIEN.PER(DataOmzet[[#Alles];[product]];DataOmzet[[#Alles];[regio]];DataOmzet[[#Alles];[bedrag]];SOM;3)
leidt tot de volgende kruistabel:

Deze functie kent 3 verplichte argumenten en 7 optionele argumenten.

Evaluatie van de nieuwe aggregatiefuncties

• Draaitabellen moeten altijd worden vernieuwd na aanpassing van de brondata. Bij de nieuwe aggregatiefuncties geschiedt dat direct.
• De aggregatieformule bestaat slechts uit een formule waarmee een bereik wordt weergegeven en berekend.
• Draaitabellen hebben de beschikking over slicers en kunnen gekoppeld worden aan de databronnen door middel van Power Query die elders zijn opgeslagen. Bij de aggregatiefuncties dienen de tabellen wel in het actieve bestand aanwezig te zijn.