Search
Close this search box.

Excel tip: Hoe maak je een draaitabel in MS Excel

 

Een korte uitleg van data-analyse met behulp van een tabel en een draaitabel

 

De tabel en de draaitabel

 

Met gewoon tellen, filters of som/ aantal-formules of kun je de gegevens uit een tabel gebruiken voor verslaglegging in een periodiek overzicht. Maar dit kan veel eenvoudiger met een draaitabel. Je kunt met een draaitabel de gegevens uit een tabel samenvatten, ordenen of analyseren op de manier zoals je zelf wilt. Het grootste voordeel van een draaitabel is het dynamische karakter daarvan. Je kunt de kolomvelden met de rijvelden blijven wisselen (lees draaien) of vervangen.Bovendien kun je de data nog eens overzichtelijker maken door in de draaitabel selecties toe te passen.

 

Velen vinden het maken van een draaitabel te ingewikkeld. En toch! Een draaitabel is zo gemaakt. In het onderstaande voorbeeld vertel ik eerst stap voor stap hoe de tabelfuncties van MS Excel werken, omdat daarmee al een winst voor het gebruik van data is te halen. Daarna laat ik zien hoe je van deze tabel een draaitabel maakt.

 

De tabel

 

Je hebt gegevens uit een grote database op je Excel-blad binnengekregen over kinderopvangcentra in Amsterdam. Het management wil weten hoeveel kinderen in diverse soorten kinderopvangen aanwezig zijn en dan ook nog eens per stadsdeel. Om het eenvoudig te houden laat ik in mijn voorbeeld maar enkele rijen en kolommen zien.

 

Op je werkblad staan onder meer de volgende gegevens:

 

draaitabel

 

De voorbereiding en een analyse zonder draaitabel

 

knoppenbalktabelKies op de menubalk voor Invoegen en vervolgens voor Tabel. Met een tussenschermpje wordt het bereik voorgesteld en na een klik op Ok, heeft MS Excel meteen al een aantal wijzigingen aangebracht. Niet alleen hebben de kolomnamen een selectiepijltje gekregen, maar ook de knoppenbalk is gewijzigd.

 

 

 

 

 

tabelnaamVul links bovenin het woord ‘Gegevens’ in, waardoor je selectie een naam heeft gekregen. Hiermee kun je een volgende keer eenvoudig je tabel geheel selecteren.

 

In mijn voorbeeld kun je in het vervolgschermpje kiezen voor kinderdagverblijf of voorschool. Het keuzepijltje is vervolgens in een trechtertje gewijzigd en je krijgt alleen maar de Kind_id’s van de kinderdagverblijven te zien.

 

Een nieuwe knoppenbalk

 

Bij de tabel behoort een nieuwe knoppenbalk Hulpmiddelen voor tabellen (op de menubalk boven het menu Ontwerpen), waarmee je een aantal relevante opties krijgt voor data-analyse. Mocht je de nieuwe werkbalk niet meer zien, dan is je cursor buiten je gegevensbereik geplaatst. Zodra hij weer in de tabel staat, komt ook de werkbalk weer tevoorschijn.

 

tabelknoppenbalk

 

De meeste knoppen van de nieuwe knoppenbalk spreken voor zich. Het is natuurlijk even leuk om te zien hoe je opmaak zich aanpast als je kiest voor een van de rechtse knoppen, maar veel interessanter is bijvoorbeeld het vinkje Totaalrij. Als je deze optie kiest, komt er onderaan je tabel een rij met totalen. Hierin zie je meteen het aantal van je gemaakte keuze kinderdagverblijf. Maak je gebruik van het keuzepijltje van het nieuwe veld Totaal dan volgt er een schermpje waarmee je onder meer voor de som, de laagste, hoogste, etc. kunt kiezen.

 

 

Tip
rijblokkerenOm het jezelf wat makkelijker te maken, raad ik je aan de eerste rij met veldnamen vast te zetten. Zet de cursor onder de rij met namen en kies dan in de knoppenbalk Beeld voor Bovenste rij blokkeren.

 

 

 


De heattable

 

Iheattablen mijn voorbeeld heeft een ‘heattable’ niet veel zin, maar je kunt je voorstellen dat je de getallen boven en onder een bepaalde waarde snel wilt kunnen traceren. De programmeurs van Microsoft bedachten hiervoor een kleurenschema op basis van de waarden in de tabel. De kleuren nemen toe van blauw naar rood. Je ziet meteen de buitengemiddelde getallen.

 

Het instellen van een heattable voor je selectie doe je door in het Startmenu te kiezen voor Voorwaardelijke opmaak van de groep Stijlen.

 

Hier kun je een kleurenopmaak kiezen, die voor jou het meest duidelijk de verschillen accentueert.

 

Op internet zijn veel voorbeelden en instructies hierover te vinden.

 

Duplicaten verwijderen

dubbelerecordsAls we even terugkeren naar de nieuwe knoppenbalk Hulpmiddelen van tabellen dan zien we daar een prachtige, maar tevens gevaarlijke optie staan: Duplicaten verwijderen. Voordat je deze optie gaat gebruiken, raad ik je ten zeerste aan een kopie van je spreadsheet te maken. Uiteraard wil je dubbele tellingen voorkomen en ook van deze optie gebruik maken, maar MS Excel verwijdert wel alle gekozen dubbelen. Wees voorzichtig.

 

In mijn voorbeeld koos ik ervoor alle dubbele records van het soort Kinderopvang te verwijderen. Daarmee verdwenen de Kind_id’s van de dubbele waarden en hield ik alleen drie records met Voorschool, Kinderdagverblijf en Peuterspeelzaal over.

 

Dubbele records en gekoppelde tabellen

Als je gebruik maakt van een tabel die aan een database of een ander spreadsheet is gekoppeld, dan valt het mee. De gegevens verdwijnen niet uit je oorspronkelijke tabel. Met de knop Alles vernieuwen van de knoppenbalk Gegevens heb je de verwijderde records weer terug.

 

De draaitabel

draaitabelmakenWe gaan nu naar de Draaitabel (Pivot in de Engelse versie). In de knoppenbalk Hulpmiddelen voor tabellen zie je linksboven de optie Samenvatten met draaitabel.

 

Als je hiervoor kiest, stelt het vervolgscherm al een selectie voor. Heb je je tabel ook zoals ik voorstelde ‘Gegevens’ genoemd, dan kun je beter die naam hier invullen.

 

Als je weinig kolommen hebt, zoals in mijn voorbeeld, dan bevordert het de overzichtelijkheid door de tabel ernaast te zetten. Met veel kolommen, raad ik je aan te kiezen voor een draaitabel op een nieuw werkblad.

 


De Besturingspanelen van de draaitabel

 

draaitabelveldenNa de bevestiging krijg je de besturingspanelen voor je draaitabel en net als ik eerder aangaf krijg je ook hier op de bovenste menubalk nog een extra werkbalk met de naam Hulpmiddelen voor draaitabellen.

 

Sleep het veld Stadsdeel (bovenin) naar de plek Kolomlabels, de Opvangsoort naar de rijlabels en tenslotte het veld Kind_id naar de Waarden.

 

Het resultaat zie je ontstaan, zodra je een sleepactie voltooid hebt.

 

draaitabelslepenWaarschijnlijk heeft Excel een som gemaakt van de Kind_id waarden, maar met het pijltje en vervolgens de keuze voor Waarde instellingen zie je dat je ook het aantal kunt opvragen.

 

 

 

 

 

 

 

De draaitabel gecombineerd met de heattable

Op de knoppenbalk Hulpmiddelen voor draaitabellen staat de optie Selecteren en vervolgens Waarden. Als je deze keuze maakt en daarna een heattable toepast komt het verschil in de getallen goed in beeld. Mijn draaitabel geeft het volgende resultaat.

 

draaitabelresultaat

Dit voldoet precies aan de vraag van het management om de aantallen per Stadsdeel en opvangsoort in beeld te krijgen. Omdat ik eerder de dubbele Kind_id’s verwijderde, ben ik ervan verzekerd dat er geen Cartesische verschillen ontstaan.

 

Geïnteresseerd?

 

In het bovenstaande voorbeeld heb ik bewust gekozen voor een eenvoudige tabel. Maar natuurlijk werkt deze methode op veel ingewikkeldere tabellen waar meer kolommen naar de rijlabels en de kolomlabels te slepen zijn. Als je deze stappen exact volgt moet ook dat geen enkel probleem zijn. Kom je er toch niet uit? Neem dan vrijblijvend contact met ons op via: info@florijn.com zodat een van onze MS Excel programmeurs je persoonlijk komt helpen.

Add Your Heading Text Here

Lorem ipsum dolor sit amet, consectetur adipiscing elit. Ut elit tellus, luctus nec ullamcorper mattis, pulvinar dapibus leo.

Add Your Heading Text Here

Lorem ipsum dolor sit amet, consectetur adipiscing elit. Ut elit tellus, luctus nec ullamcorper mattis, pulvinar dapibus leo.

Add Your Heading Text Here

Lorem ipsum dolor sit amet, consectetur adipiscing elit. Ut elit tellus, luctus nec ullamcorper mattis, pulvinar dapibus leo.

Add Your Heading Text Here

Lorem ipsum dolor sit amet, consectetur adipiscing elit. Ut elit tellus, luctus nec ullamcorper mattis, pulvinar dapibus leo.

Add Your Heading Text Here

Lorem ipsum dolor sit amet, consectetur adipiscing elit. Ut elit tellus, luctus nec ullamcorper mattis, pulvinar dapibus leo.

Add Your Heading Text Here

Lorem ipsum dolor sit amet, consectetur adipiscing elit. Ut elit tellus, luctus nec ullamcorper mattis, pulvinar dapibus leo.

Add Your Heading Text Here

Lorem ipsum dolor sit amet, consectetur adipiscing elit. Ut elit tellus, luctus nec ullamcorper mattis, pulvinar dapibus leo.

Add Your Heading Text Here

Lorem ipsum dolor sit amet, consectetur adipiscing elit. Ut elit tellus, luctus nec ullamcorper mattis, pulvinar dapibus leo.

Add Your Heading Text Here

Lorem ipsum dolor sit amet, consectetur adipiscing elit. Ut elit tellus, luctus nec ullamcorper mattis, pulvinar dapibus leo.

Add Your Heading Text Here

Lorem ipsum dolor sit amet, consectetur adipiscing elit. Ut elit tellus, luctus nec ullamcorper mattis, pulvinar dapibus leo.

Add Your Heading Text Here

Lorem ipsum dolor sit amet, consectetur adipiscing elit. Ut elit tellus, luctus nec ullamcorper mattis, pulvinar dapibus leo.

Add Your Heading Text Here

Lorem ipsum dolor sit amet, consectetur adipiscing elit. Ut elit tellus, luctus nec ullamcorper mattis, pulvinar dapibus leo.