Alternatief voor draaitabel?

Een draaitabel (pivot table in de Engelse versie) is een prima middel voor data-analyse. Vergeleken met oudere versies van Excel is het maken van een draaitabel een stuk eenvoudiger geworden. Gelukkig 😊.
Maar een nadeel blijft dat je eraan moet denken dat je de tabel moet vernieuwen! En soms moet je zelfs het cellenbereik aanpassen. Dat wordt helaas wel eens vergeten: en dan zit je met berekeningen die niet up-to-date zijn 🫢.
Maar er is een alternatief: er zijn sinds kort 2 functies die een variant opleveren voor een draaitabel. En omdat bij elke wijziging altijd alle formules worden herberekend, heb je geen last van het verschijnsel van hierboven!

Welke versie moet je hebben?

Deze nieuwe functies zijn momenteel alleen beschikbaar in Excel 365, de webversie van Excel en de Excel-versie voor de iPhone.

De voorbeelddata

Als voorbeeld voor de uitleg gebruik ik dit.

Altijd een goede basis

Van deze gegevens heb ik een tabel van gemaakt met de naam Verkoopdata.

Een tabel maak je door de cellen te selecteren > Invoegen > Tabel ①.
Het is ook altijd handig om de tabel een passende naam te geven via het speciale tabblad dat is verschenen: Tabelnaam ②.

Een tabel heeft vele voordelen, daar heb ik het al eerder over gehad in een blog (https://www.toels-pc.nl/2024/03/01/maak-altijd-een-tabel ) en in diverse YouTube-videotips (bijvoorbeeld https://youtu.be/YOGNJyaDIKU en https://youtu.be/zZmTwB_Pi90 ).

Nog niet juist vertaald

Ik maak dit blog met de Nederlandstalige Excel. Maar je ziet bij het gebruik van deze redelijk nieuwe functies, dat het nog niet helemaal Nederlands is.

Typ je de functie in een cel, dan zijn de omschrijvingen van de argumenten nog Engels ① en ②. In het dialoogvenster Functieargumenten staat er nog een foutmelding bij het 3^e argument ②.

Dynamisch bereik

Het resultaat van zowel GROEPEREN.PER als DRAAIEN.PER is een dynamisch bereik. Dat betekent dat de berekening in één cel staat en dat die ‘overloopt’ in andere cellen.
Je herkent een dynamisch bereik altijd aan de blauwe rand eromheen als een cel erin is geselecteerd ①.
Als er niet voldoende ruimte is om ‘over te lopen’ (zoals bij ②), dan krijg je een foutmelding #OVERLOPEN! (Engels: #SPILL!). Maak de cel(len) leeg en het probleem is opgelost!

Je ziet in het voorbeeld ook, dat de cellen nog wel opgemaakt moeten worden. Dat kun je op de gebruikelijke manier doen.

Groeperen.per = draaitabel met rijvelden

Met de functie GROEPEREN.PER (GROUPBY) is dit gemaakt: de orderbedragen opgeteld per land en daarbij het totale orderbedrag.

GROEPEREN.PER heeft 3 verplichte argumenten en enkele optionele. Dit zijn de verplichte argumenten.

1^e argument: rijvelden.
Hier geef je op welke kolom(men) je onder elkaar wilt hebben (in rijen dus). In dit voorbeeld is dat de kolom Land: Verkoopdata[Land].
2^e argument: welke Waarden wil je gebruiken. In dit voorbeeld wil je de kolom Orderbedrag gebruiken.
3^e argument: welke berekening wil je gebruiken. In dit voorbeeld optellen, dus SOM.

In het voorbeeld hierboven zijn de cellen met de bedragen voorzien van een passende getalnotatie en tekenopmaak.

Wanneer je extra argumenten toevoegt, kun je bijvoorbeeld nog deze varianten krijgen.

Omschrijvingen erboven.
Hier is het aantal orders geteld per land.
Let op: je moet dan bij de eerste 2 argumenten ook de kolomkoppen meenemen!
Totaal bovenaan.
Andere sorteervolgorde.

Jammer, moet nog worden verbeterd

Wat je voor de verschillende argumenten moet invullen is niet altijd duidelijk. Soms is het handiger om de functie zelf te maken in de cel (onderste deel van de afbeelding), omdat je dan aanwijzingen krijgt voor de optionele velden: je ziet dat niet in het dialoogvenster Functieargumenten.

Maar een andere keer is het beter om het dialoogvenster te gebruiken, want dan is door de aanwijzing te vinden. In de bovenste afbeelding zie je niet hoe je de sortering moet opgeven, in het dialoogvenster eronder wel.

Je kunt ook meer velden opgeven in het rijgebied. Bijvoorbeeld de Landen en de Verkopers met hun orderbedragen opgeteld. Hier zijn ook subtotalen gebruikt (Total depth = 2)

Draaien.per = draaitabel met rijvelden en kolomvelden

DRAAIEN.PER (PIVOTBY) heeft een vergelijkbare opbouw. Je kunt hier echter ook velden opgeven die in kolommen moeten komen.
Hier zie je de landen in dat kolommengebied, de verkopers in het rijgebied. Die orderbedragen zijn weer opgeteld.

1^e argument: Rijvelden
Hier staat de kolom Verkopers uit de tabel: de namen van de verkopers staan in rijen.
2^e argument: Kolomvelden
Hier staat de kolom Land uit de tabel: de namen van de landen staan in kolommen.
3^e argument: waarden
Welke kolom bevat de gegevens waarmee gerekend moet worden? Hier zijn dat de orderbedragen.
4^e argument: de berekening
Vul hier de gewenste berekening in. Net als bij GROEPEREN.PER geeft dit in de Nederlandse versie een foutmelding in het dialoogvenster, maar het werkt wel gewoon.

Wanneer je extra argumenten toevoegt, kun je bijvoorbeeld nog deze varianten krijgen.

Omschrijvingen erboven
Hier is het aantal orders geteld per land met omschrijvingen bij de rijen en kolommen.
Let op: je moet dan bij de eerste 2 argumenten ook de kolomkoppen meenemen!
Je kunt ook filteren, bijvoorbeeld zonder de gegevens van Luxemburg. Je voegt dan de kolom toe waarop je wilt filteren en zet het filter erbij: land <> Luxemburg.

Beperkingen

Het grote voordeel van deze functies is dus dat ze bij elke wijziging opnieuw berekend worden. Dat gebeurt bij een draaitabel dus niet automatisch.

Een nadeel: je moet de cellen opmaken en dat is bij een draaitabel weer automatisch geregeld.
Zeker als achteraf nieuwe items worden toegevoegd, kun je dus dit krijgen. Omdat er een extra land is toegevoegd, is de totaalrij lager gekomen. Die heeft nog niet de juiste (getal)opmaak. De rij waar het totaal eerst stond was vet, dus is hier nu Nederland vet.

Maar voor de basale vorm is het zeker een uitkomst!

Vergelijking draaitabel versus functies

Maken deze functies draaitabellen overbodig? Wat is beter?
Hieronder een klein overzicht.

	Draaitabel	Functies
Bijwerken	Handmatig	Automatisch
Opmaak	Tabelontwerp automatisch	Celopmaak handmatig
Slicers en tijdlijnen	Beschikbaar	Niet beschikbaar
Bij veel data	Sneller	Kan vertragen
Meer berekeningen	Mogelijk in één draaitabel	Slechts 1 mogelijk
Uitwisselbaar	Kan in alle versies van Excel	Alleen Excel 365, webversie

Wanneer tijdens een Excel-training “draaitabellen” aan de orde komen, verbaas ik me vaak over hoe weinig mensen hierover weten. Ook mensen die er al langer mee werken.
Daarom in deze blog een paar basisvaardigheden over draaitabellen die niet moeilijk, maar wel heel handig zijn.

De basis van een draaitabel

Een draaitabel is over het algemeen gebaseerd op een cellenbereik in de vorm van een tabel. Het is altijd een goed plan om van die gegevens EERST een Excel-tabel te maken en dan pas de draaitabel.
De reden: extra toegevoegde rijen en kolommen in dit cellenbereik worden dan automatisch meegenomen bij het vernieuwen van de draaitabel! Zo je er geen tabel van hebben gemaakt, dan moet je eraan denken dat je het cellenbereik handmatig moet aanpassen.

Je maakt een Excel-tabel van door het cellenbereik te selecteren en dan op de Tab Invoegen te kiezen voor Tabel ①. Excel vraagt dan of er kopteksten in de selectie zijn meegenomen ②. Excel vraagt hiermee dus of de eerste rij van dat cellenbereik de kolomomschrijvingen zijn.
Daarna is het cellenbereik een Excel-tabel geworden.
Als een cel in die Excel-tabel is geselecteerd herken je dat aan het extra tabblad in het lint met de naam Tabelontwerp. Daar kun je ook de naam van die tabel zien (over het algemeen Tabel<nr>) en kun je die naam aanpassen naar een zinvollere naam ③.

De rechtermuisknop is je BFF!

Meestal geeft men een opdracht voor een actie in een draaitabel via het speciale tabblad Draaitabel analyseren. Maar veel acties kun je uitvoeren via de rechtermuisknop op een cel in de draaitabel. En dat is vaak sneller en eenvoudiger.
Als je rechts klikt op een cel in het rijgebied krijg je het menu van ①. Bij het klikken op een van de resultaatcellen krijg jet het menu van ②.

TIP: niet alleen in draaitabellen is je rechtermuisknop je BFF: Best Friend Forever!

Meer berekeningen mogelijk met hetzelfde veld

Een veld waarmee je een berekening wilt maken zet je in het gebied Waarden. Bij een getal zal het dan worden opgesteld (Som van <veldnaam>).
Als je behalve de optelling ook bijvoorbeeld het gemiddelde en/of het aantal wilt berekenen, kun je dat veld vaker naar het gebied Waarden slepen. In eerste instantie zal het dan Som van <veldnaam>2/3/enz genoemd worden.
Maar via de rechtermuisknop in een van die resultaatcellen, kun je eenvoudig kiezen voor een andere berekening via Waarden samenvatten per.

Nog meer berekeningen direct mogelijk

Behalve de hierboven genoemde berekeningen, kun je de gegevens ook nog op een andere manier weergeven. Klik hiervoor ook weer op een resultaatcel en kies dan voor Waarden weergeven als. Je hebt nu een groot aantal berekeningen waaruit je kunt kiezen.

Kolombreedte niet meer automatisch aanpassen

Als je een draaitabel aanpast, zie je dat de kolombreedte zich automatisch aanpast. Dat kan handig zijn, maar niet altijd als er meer draaitabel op hetzelfde werkblad staan.
Je kunt dat uitzetten bij de instellingen van die draaitabel.

Klik met de rechtermuisknop in de draaitabel > Opties voor draaitabel.
Je krijgt dan een dialoogvenster met veel tabs. Op de tab Indeling & Opmaak kun je onder andere het automatisch aanpassen van de kolombreedte uitschakelen.

Volgorde aanpassen

De standaardinstelling is dat de items in het rijgebied in oplopende volgorde worden weergegeven ①. Maar … als er in het cellenbereik van de tabel een nieuw item bijkomt, zal die altijd onderaan geplaatst worden ②.
Je kunt die volgorde handmatig aanpassen door dat item te verslepen naar de gewenste positie ③of je klikt met de rechtermuisknop in een van de items > Sorteren > kies de sorteervolgorde ④.

Lay-out aanpassen

Draaitabel kun je in een andere layout zetten. Je regelt dat via de tab Ontwerpen > Rapportindeling. Er zijn 3 opties: de compacte, overzichts- en de tabelweergave.
Verder kun je daar met de knoppen Subtotalen en Eindtotalen aangeven of je die wel of niet wilt zien.
De standaardlay-out is de compacte weergave met subtotalen en eindtotalen.

Standaardontwerp zelf instellen

Gebruik je liever een andere lay-out als standaard, dan kun je dat instellen met Bestand > Opties > Gegevens > Standaardindeling bewerken.
Je kunt hier niet alleen het standaardontwerp aanpassen, maar ook de opties die je graag wilt zien voor nieuwe draaitabellen.

Let op: deze instelling geldt voor alle nieuwe draaitabellen die je maakt!

Tag Archief van: draaitabel