Hoewel steeds meer Excel-gebruikers weten, dat draaitabellen ontzettend handig zijn bij de analyse van gegevens, het maken van overzichten etc. en deze mogelijkheden ook in hun dagelijkse werk gebruiken, is het groeperen binnen draaitabellen nog minder bekend: deze keer dus de focus op Groeperen in draaitabellen.

Oplettende lezers hebben ongetwijfeld gezien, dat ik dit in mijn vorige artikel (Top-2000 en draaitabellen) heb gebruikt zonder verdere uitleg.

Basis

Om een draaitabel te kunnen maken hebben we wat basisgegevens nodig. Ik heb in het Voorbeeldbestand op het tabblad Basis de fictieve productie van een simpele winkel gezet: een datum, het artikel en het verkochte aantal (soms dubbel).
Uiteraard (zie Kunst en Excel; 10 voordelen van Tabellen) zijn die gegevens in de vorm van een Tabel opgeslagen (met als naam tblVerkopen).
Ze waren eerst niet gesorteerd, maar om gemakkelijk de resultaten van de draaitabellen te beoordelen, heb ik dat nu wel even gedaan (klik op het vinkje achter Datum).

Overzicht per dag

Laten we snel even een overzicht maken van deze gegevens:

1. Plaats de cursor ergens in de tabel tblVerkopen
2. kies in de menutab Invoegen in het blok Tabellen de optie Draaitabel
3. in het vervolgscherm staat alles goed (Excel heeft automatisch de hele tabel als bron gekozen en het resultaat moet op een nieuw werkblad komen), dus klik op OK
4. op het nieuwe werkblad krijgt u aan de rechterkant de mogelijkheid om de draaitabel in te delen zoals u wilt: sleep Datum naar de Rijlabels, Art naar de Kolomlabels en Aantal naar het waardengebied
5. eigenlijk is de draaitabel klaar, maar ik vind de standaard-layout niet handig, dus heb ik in de menutab Hulpmiddelen voor draaitabellen de tab Ontwerpen gekozen en bij Rapportindeling de optie Tabelweergave.

Het resultaat staat op het tabblad Draai van het Voorbeeldbestand: een overzicht van de totale verkoopaantallen per dag en gesplitst naar artikel.

Overzicht per maand

Vaak willen we in rapportages niet alle details zien, maar bijvoorbeeld in plaats van dag-resultaten de verkopen per maand.

In dit geval zou je de basisgegevens daartoe kunnen uitbreiden met een zogenaamde hulp-kolom waarin met behulp van de functie MAAND de maand wordt vastgelegd. Als we ook nog het jaar willen weten, moeten we nog een extra hulp-kolom toevoegen (met daarin de functie JAAR).

Maar die hulpkolommen zijn niet nodig:

1. klik met de rechtermuisknop op één van de datums in de hiervoor gemaakte draaitabel
2. het zogenaamde context-gevoelige submenu komt dan tevoorschijn. Kies daar de optie Groeperen
3. Excel heeft de kolom, waarin geklikt is, geanalyseerd en heeft gezien dat dit allemaal datums zijn.
   We willen een maand-overzicht dus het voorstel van Excel kunnen we overnemen; klik op OK

LET OP als in de basisgegevens ergens in de kolom Datum een cel niet is gevuld of gevuld met een niet-bestaande datum of een getal of een tekst, dan zal het Groeperen-scherm er anders uitzien. De datum-opties zijn dan niet beschikbaar.
Nog erger: ook al corrigeert u de basisgegevens, Excel zal deze kolom nooit meer als een datum ‘zien’. Er zit dan niets anders op dan het werkblad met de draaitabel te verwijderen en het overzicht opnieuw te maken!

NB In het blok Lijst met draaitabelvelden aan de rechterkant van het scherm staat bij de te kiezen velden nog steeds Datum, maar Excel zal in de draaitabel alleen nog maar de maanden laten zien.

Overzicht per maand en jaar

Het overzicht per maand is in dit geval prima omdat Basis alleen maar datums uit 2016 bevat. Voegen we echter onderaan (of tussenin) de volgende regel toe:
1-1-2017, Servetten, 10
dan komen die in het Overzicht per maand uiteraard ook in de januari-regel tevoorschijn (wel even de draaitabel Vernieuwen door ergens rechts te klikken!).

We moeten zorgen dat de draaitabel ook per jaar groepeert:

1. klik met de rechtermuisknop op één van de maanden in de datum-kolom van de draaitabel
2. kies opnieuw Groeperen
3. en klik nu ook de Jaren aan en OK

NB1 in de Lijst met draaitabelvelden is een er een nieuw veld bijgekomen, Jaren. Dit veld kunt u op dezelfde manier in de draaitabel plaatsen als andere velden. Bijvoorbeeld in het Rapportfilter zodat u overzichten per jaar kunt maken.

NB2 wanneer u een draaitabel maakt, dan legt Excel de brongegevens apart op een eigen manier in het geheugen vast. Zo ook de consequenties van groeperingen. Maakt u nu met dezelfde bron (in dit geval tblVerkopen) een andere draaitabel, dan gebruikt Excel, om geheugenruimte te sparen, dezelfde interne bron. Dus ook eventuele groeperingen zijn in de tweede draaitabel direct zichtbaar.

NB3 hebt u bij een analyse/overzicht in dezelfde draaitabel (of een andere; zie NB2) de datums zelf nodig, klik dan bij het Groeperen ook de Dagen aan.
Hebt u de dagen in het huidige overzicht niet (meer) nodig, verwijder dan het vinkje voor de Datum in Lijst met draaitabelvelden.

Overzicht per kwartaal

Ongetwijfeld hebt u ondertussen ook gezien dat u op deze manier heel gemakkelijk kwartaaloverzichten kunt maken.
In het overzicht hiernaast is de groepering per kwartaal aangezet (zoals te zien is heb ik ook de 2017-regel toegevoegd en de draaitabel vernieuwd).
Nog een paar kleine aanpassingen:

1. met de rechtermuisknop klikken op een jaar en dan Subtotaal Jaren aanvinken
2. met de rechtermuisknop klikken op een kwartaal en dan Subtotaal Kwartalen aanvinken
3. de eerste drie kwartalen ‘dichtklappen’ door op het min-teken vóór het betreffende kwartaal te klikken
4. grafiek nodig? Klik ergens in de draaitabel, kies het tabblad Opties in de menutab Hulpmiddelen voor draaitabellen en binnen het blok Extra de optie Draaigrafiek. We willen een Kolomgrafiek, dus het klikken op OK volstaat.

Artikelen groeperen

Voordat we verder gaan is het het makkelijkst om de draaitabel op het tabblad Draai van het Voorbeeldbestand eerst aan te passen:

1. klik het vinkje voor Datum weg in de Lijst met draaitabelvelden
2. verplaats Art van Kolomlabels naar Rijlabels

Zoals we gezien hebben weet Excel bij datums hoe hij kan groeperen; bij teksten is dat natuurlijk een stuk lastiger.
Daarom gaan we als volgt te werk:

1. selecteer met de muis, die cellen die u wilt groeperen. In dit geval liggen die niet tegen elkaar, dus moeten we de Ctrl-toets gebruiken.
   Klik op Broodrooster
2. hou Ctrl ingedrukt en klik op Strijkijzer
3. klik met de rechtermuisknop op één van die 2 artikelen en kies Groeperen
4. doe hetzelfde met Servetten en Tafelkleed
5. verander de nieuwe Groep1 in Hard door de tekst ‘er overheen’ te tikken
6. Groep2 wordt op die manier Soft
7. de nieuwe kolomkop Art2 wordt ArtGroep
8. zet nog de subtotalen voor ArtGroep aan

Nu weer een kwartaaloverzicht maken:

1. klik het vinkje voor Art weg
2. verplaats ArtGroep van Rijlabels naar Kolomlabels
3. plaats Kwartalen en Jaren in de juiste volgorde in Rijlabels

Getallen groeperen

Om dit toe te lichten maken we een nieuwe draaitabel:

1. plaats de cursor ergens in de tabel tblVerkopen
2. kies in de menutab Invoegen in het blok Tabellen de optie Draaitabel
3. in het vervolgscherm staat alles goed (Excel heeft automatisch de hele tabel als bron gekozen en het resultaat moet op een nieuw werkblad komen), dus klik op OK
4. op het nieuwe werkblad krijgt u aan de rechterkant de mogelijkheid om de draaitabel in te delen zoals u wilt: sleep Aantal naar de Rijlabels, Art naar de Kolomlabels en Datum naar het waardengebied

We zien op die manier het aantal keren, dat een bepaald aantal verkopen voorkomt, ook nog gesplitst naar artikel.

LET OP Aantal van Datum is wat misleidend: we turven niet het aantal dagen, maar het aantal keer dat het veld Datum gevuld is. We hadden ook Aantal van Art of zelfs Aantal van Aantal kunnen kiezen.

NB de basis-gegevens zijn random gevuld. Je zou dus overal exact 50 verwachten. Als ik 100.000 records had aangemaakt zou de spreiding ook minder groot zijn geweest!

Maar nu groeperen:

1. klik met de rechtermuisknop op één van de cijfers 2, 3, 4, 5 of 6 en kies Groeperen
2. we krijgen nu een heel ander groepeer-scherm; we kunnen een eigen indeling maken.
   Verander de 2 in 1 en de 1 achter Op: in 3.
   Ofetwel begin bij 1 en groepeer in blokken van 3.
3. klik op OK.

Als we ook nog de artikelen groeperen naar Hard en Soft dan krijgen we het volgende overzicht:

---