Werken met draaitabellen in Excel

Praktijkvoorbeeld 

In deze tutorial gebruiken we een dataset over wereldwijde fietsverkopen van 2011 tot 2016. De demografische informatie over klanten en producten in deze dataset is gerangschikt met kolommen voor winst, kosten en omzet. Je maakt een draaitabel van deze dataset, zodat je de gegevens kunt analyseren. 

De data is hier beschikbaar zodat je kunt meekijken. 

De data bekijken 

Laten we beginnen met het bekijken van een voorbeeld van de data voor deze tutorial. We hebben een tabel met 11 kolommen, waaronder datum-, tekst- en numerieke veldtypen. In deze subset zien we veel manieren waarop we met de gegevens kunnen werken om onze analyse uit te voeren en waardevolle inzichten te vinden. 

Je eerste draaitabel maken 

Om je eerste draaitabel te maken, selecteer je de tabel waarmee je wilt werken, ga je naar het tabblad Invoegen en kies je hieronder Draaitabel.

Daarna verschijnt een pop-upvenster waarin wordt gevraagd of je het gegevensbereik wilt wijzigen waarvan je een draaitabel wilt maken en of je de draaitabel in een nieuw werkblad of een bestaand werkblad wilt plaatsen. Voor deze tutorial volstaan de standaardopties. Klik op OK en maak een draaitabel in het nieuwe werkblad hieronder. 

Ons nieuwe blad toont de lege opzet van de draaitabel die automatisch voor je is aangemaakt. Aan de linkerkant van het scherm zie je een omlijning waar de draaitabel verschijnt zodra hij is opgebouwd. Aan de rechterkant zie je het deelvenster voor draaitabellen, waar je het meeste werk in zult doen. 

Als je gegevens al in een tabel in Excel staan, neemt de draaitabel automatisch alles daarin mee. Het is echter belangrijk om te weten dat je, als je gegevens niet in een tabelelement staan, handmatig de gegevens kunt selecteren die je wilt opnemen door ze te markeren en op dezelfde manier als hiervoor een draaitabel te maken. 

Je gegevens toevoegen 

We gaan ervan uit dat elke rij aan een individuele klant is gekoppeld; er zijn dus geen herhaalde bestellingen. Dit is een sterk vereenvoudigde weergave van de dataset, maar omdat er geen unieke klant-ID aanwezig is, is het voor deze tutorial makkelijker om van één klant per rij uit te gaan. 

We beginnen met het toevoegen van een categorisch veld aan het gedeelte Rijen. We gebruiken Country als categorie; zoek dit in de lijst met velden in de draaitabeleditor en sleep het naar het gedeelte Rijen. Vervolgens kijken we naar de waarde die we willen evalueren—in dit geval willen we klanten tellen, maar omdat we geen ID-veld hebben, kunnen we het veld Customer_Age gebruiken en dit naar het gedeelte Waarden slepen. Standaard is de aggregatie SOM, maar we moeten dit wijzigen naar AANTAL. Dat doe je door met de rechtermuisknop op Customer_Age in het gedeelte Waarden te klikken, Veldinstellingen te selecteren, Samenvatten te wijzigen van Som naar Aantal en op OK te klikken.

Goed gedaan! We zien nu een telling van klanten per Country waaruit ze hebben besteld. Dat is nuttig, maar laten we het ordenen op Country met de meeste klanten. Klik hiervoor met de rechtermuisknop op een waarde in de kolom Count of Customer_Age, kies Sorteren en vervolgens Van groot naar klein sorteren. We zien dat de Verenigde Staten de meeste klanten hadden, met 39.206.

Een stap verder 

Nu we weten hoe we een basisdraaitabel opzetten, gaan we een stap verder en kijken we naar het toevoegen van filters en kolommen en het vernieuwen van draaitabellen. Laten we de rijen en waarden die we in de vorige stap hebben toegevoegd verwijderen door er met de rechtermuisknop op te klikken en Veld verwijderen te selecteren. We hebben nu een volledig leeg canvas om mee te werken. 

Voor deze draaitabel proberen we de vraag te beantwoorden: "Welke kwartaal leverde in 2015 de hoogste omzet op, en tot welk product/subcategorie behoorde dit?" 

Laten we beginnen met het toevoegen van de rijen waarlangs we deze data willen analyseren; in ons geval zijn dat Product_Category en Sub_Category. Voor onze kolommen willen we een uitsplitsing van Quarters zien. Maar wacht, we hebben geen kolom Quarters… geen probleem: Excel heeft automatisch een datum in onze data herkend, dus wanneer je Date naar de kolommen sleept, verschijnen er twee nieuwe velden: Years en Quarters. Omdat we de data niet op individueel datumniveau hoeven te bekijken, kunnen we dat veld uit ons gedeelte Kolommen verwijderen. 

Omdat we voor onze vraag willen filteren op 2015, kunnen we het veld Years naar het gedeelte Filter verplaatsen. Je draaitabel zou er nu ongeveer zo uit moeten zien:

Nu we de structuur van onze tabel hebben, kunnen we het jaar vooraf selecteren waar we op willen filteren door op de dropdown voor Years te klikken en 2015 te kiezen. Zo ziet onze draaitabel er nu uit. 

Momenteel zijn onze datapunten op een enkel niveau, dus de stukprijs en stukkosten zijn gekoppeld aan één item. We willen de informatie correct samenvatten en moeten daarom even weg uit de draaitabel en terug naar onze datatabel.

In het tabblad met data moeten we een nieuwe berekende kolom maken om de omzet per rij te berekenen. Voor onze berekening moeten we Order Quantity vermenigvuldigen met Unit Price. 

Om een nieuwe kolom aan onze data toe te voegen, ga je terug naar het gegevensblad en naast onze kolom Unit Price in rij L2 voegen we onze nieuwe berekening toe; onze formule wordt:

[@[Order_Quantity]]*[@[Unit_Price]]

We hebben nu een nieuwe kolom die we in onze draaitabel kunnen gebruiken. Ga terug naar het blad met je draaitabel, en je ziet dat het veld nog niet verschijnt. Om onze draaitabel te vernieuwen, klik je met de rechtermuisknop op een willekeurig veld en selecteer je Vernieuwen.

Onze nieuwe kolom is toegevoegd en we kunnen deze naar het gedeelte Waarden slepen. Als je meer kolommen wilt toevoegen, kun je onze Excel-cheatsheet gebruiken om andere soorten berekeningen te bekijken die je kunt toepassen. 

Het oogt rommelig, dus laten we het opschonen door alle totalen te verwijderen. Dat kun je doen via het tabblad Ontwerpen. Open nu de dropdown Grand Totals en selecteer Uit voor rijen en kolommen. Werk vervolgens de cijfers bij zodat ze in valuta worden weergegeven. Markeer de waarden in de tabel en stel op het tabblad Start de notatie in op Valuta. 

Mooi, nu hebben we onze definitieve draaitabel. Hieruit zien we dat Fietsen in 2015 de hoogste omzet genereerden, vooral in Q4. Daarnaast zien we dat, van de fietsen die het bedrijf verkoopt, racefietsen het populairst zijn en de meeste omzet opleveren voor de organisatie.

Ga verder!

Deze tutorial was een goede introductie tot draaitabellen in Excel; als je makkelijk kon meekomen: goed gedaan! Als je ergens vastliep, kun je het oplossingbestand hier vinden. 

Experimenteer eens met een complexere dataset, pas verschillende attributen toe, speel ermee en kijk of je de data kunt doorgronden.

Als je meer van je Excel-vaardigheden wilt testen, overweeg dan onze cursus Data Analysis in Excel, als je die nog niet hebt gevolgd.