13 Draaitabellen
Een draaitabel is een hulpmiddel om gegevens samen te vatten. Het is een interactieve dynamische tabel waarin je snel en gemakkelijk grote hoeveelheden gegevens kunt samenvatten, combineren en vergelijken. Het is wel noodzakelijk dat de gegevens goed geordend in een werkblad staan.
Een draaitabel is vooral erg geschikt wanneer variabelen uitgezet moet worden tegen andere variabelen, waardoor je antwoorden kunt krijgen op vragen als:
- Welk produkt levert de hoogste winstbijdrage?
- Welke verkoper maakt de hoogste omzet?
- Welke afdeling maakt de meeste kosten?
- …
Voorbeeld 13.1 Omzet per categorie
In de draaitabel in Figuur 13.1 kun je eenvoudig de omzet in de drie productcategorieën met elkaar vergelijken.
In een draaitabel worden minimaal twee soorten gegevens tegen elkaar uitgezet. Het ene gegevensoort zet je dan in het kolomveld, hier productcategorie, het andere in het rijveld, hier plaats. Daarnaast moet nog een gegevensoort waarvan je de resultaten wilt zien, in het waardenveld geplaatst worden, hier Omzet.
Omdat deze resultaten worden samengevat moet ook nog aangegeven worden welke bereking daarbij gebruikt moet worden, hier Som, de standaardkeuze.
Een draaitabel heeft vier gebieden waarin je velden kunt plaatsen:
| Gebied | Toelichting |
|---|---|
| Waarden | De velden met numerieke gegevens die je wilt samenvatten, meestal voor de berekening van totalen en gemiddeldes. |
| Rijen | Deze velden worden gebruikt om afzonderlijke groepen te maken. De informatie voor elke groep wordt in een aparte rij gezet. |
| Kolommen | Deze velden worden ook gebruikt om afzonderlijke groepen te maken. Ze worden meestal gebruikt wanneer de gegevens in de rijen wilt onderverdelen. Deze groepen worden in afzonderlijke kolommen weergegeven. Neem velden die niet teveel groepen geven, anders wordt de tabel erg breed. |
| Filters | Deze velden kunnen gebruikt worden om de gegevens te filteren. |
13.1 Draaitabellen maken
In het bestand bij deze taak staan de maandelijkse verkopen in 2016 van drie supermarkten voor verschillende producten, zie Figuur 13.2. Deze producten zijn in drie productgroepen verdeeld: agf (aardappelen, groente, fruit), vlees en zuivel. Deze gegevens worden geanalyseerd met behulp van een draaitabel.
Informatiebehoefte: Wat is de totale omzet per plaats per productcategorie?.
Taak 13.1 Bestand: Supermarkt.xlsx
Open het bestand.
Selecteer een cel ergens in de tabel, bijv. C3. Kies tab Invoegen > Draaitabel (groep Tabellen) > van Tabel/Bereik.
Het dialoogvenster Draaitabel maken verschijnt. Het bereik van de tabel is hierin reeds ingevuld.
- Bij deze oefening moet de draaitabel in het bestaande werkblad verschijnen. Selecteer Bestaand werkblad en klik in het werkblad op een lege plek, bijv. cel G3.
- Klik op OK.
Op het werkblad wordt een lege Draaitabel aangemaakt en aan de rechterkant verschijnt een taakvenster met de draaitabelvelden waarin je velden aan het rapport kunt toevoegen.
supermarket-pivot-empty
- Sleep in het Taakvenster veld plaats naar het vak Rijen, het veld productcategorie naar het vak Kolommen en veld omzet naar het vak Waarden. Bij de laatste actie wordt automatisch de berekeningsmethode
Somvan de omzet toegepast.
De draaitabel op het werkblad wordt met de gegevens gevuld.
Het draaitabelrapport is nu in principe gereed, maar kan nog wel een aantal verbeteringen voor de opmaak gebruiken.
Taak 13.2 omzettotalen opmaken met de getalnotatie financieel
- Selecteer een numerieke waarde in de draaitabel. Kies Tab Draaitabel Analyseren > Veldinstellingen (groep Actief veld.
Klik op de knop Getalnotatie. Het dialoogvenster Celeigenschappen verschijnt.
Kies Categorie Financieel > Decimalen: 0 > Symbool: €.
- Klik OK > OK.
De waardevelden in de draaitabel verschijnen in de gespecificeerde opmaak.
Taak 13.3 Kolom- en rijlabels in de draaitabel vervangen door de veldnaam
Selecteer een willekeurige cel in de draaitabel. Kies tab Ontwerpen > Rapportindeling (groep Indeling) > Overzichtsweergave.
Met de nu gemaakte draaitabel kun je snel en eenvoudig andere analyses maken. Zoals hoe is de totale omzet van elk product binnen de productcategorie per plaats.
Taak 13.4 Analyseren van de producten binnen elke productcategorie
- Sleep het veld productcategorie van Kolommen naar Rijen en het veld plaats van Rijen naar Kolommen. Sleep daarna het veld product naar Rijen en wel onder het veld productcategorie.
In plaats van omzettotalen kun je ook andere waarden berekenen, bijvoorbeeld de gemiddelde maandelijkse omzetten.
- Selecteer een numerieke waarde in de draaitabel en wijzig via het dialoogvenster Waardeveldinstellingm, zie Figuur 13.8 het berekeningstype in
Gemiddelde.
Taak 13.5 Top 3 producten
Probeer nu zelf met behulp van draaitabellen de volgende vraag te beantwoorden:
Welke drie producten zorgen voor de meeste omzet?
Gebruik product als rijvariabele. Een kolomvariabele is er in dit geval niet. Sorteer in het draaitabelrapport de omzet van hoog naar laag.
13.2 Brongegevens opsporen
Een draaitabel bevat samengevatte gegevens. In deze oefening leer je hoe je hiervan de brongegevens kunt vinden.
In een werkblad staan een aantal gegevens van buitendienstmedewerkers zoals omzet, reiskosten en provisie per maand en per regio. In een ander werkblad is van deze gegevens een draaitabel gemaakt met daarin de omzet per regio per medewerker.
Wanneer je wilt weten uit welke individuele waarden een resultaat is bepaald dan kun je die vrij eenvoudig vinden. Door dubbel te klikken op een resultaat in de draaitabel wordt een nieuw werkblad aangemaakt met daarin een tabel met de brongegevens.
Taak 13.6 Bestand: Omzet.xlsx
Open het bestand.
Selecteer het werkblad draaitabel en dubbelklik op de resultaten van Jansen in de regio Oost.
Een nieuw werkblad met daarin een lijst met alle gegevens van Jansen in de regio Oost wordt gemaakt.
13.3 Groeperen van gegevens
Een onoverzichtelijker lijst met gegevens kun je geschikter maken voor analyse door de gegevens te groeperen. Dat is met name het geval wanneerin de gegevens een datum zit. Datums kun je vaak groeperen tot jaren, kwartalen of maanden. In een draaitabel kun je dan de gegevens bekijken per jaar, per kwartaal of per maand.
Voorbeeld 13.2 Omzet per verkoper
Figuur 13.15 toont een deel van een draaitabel met de omzet per verkoper. In de linkerafbeelding de omzet op volgorde van datum (ongegroepeerd). In de rechterafbeelding is de omzet gegroepeerd per kwartaal en per maand.
Vanaf versie 2016 beschikt Excel over de functie tijdgroepering. Hiermee worden tijdgerelateerde velden automatisch ontdekt en gegroepeerd. Bij het toevoegen van zo’n veld aan een draaitabel worden automatisch nieuwe relevante velden aangemaakt, zoals Jaren, Kwartalen en Maanden.
Welke datum/tijd velden worden toegevoegd hangt af van het detailniveau van het datum-/tijdveld in de gegevenstabel. Bijvoorbeeld als de datumgegevens in dagen zijn en over meer dan een jaar gaan, dan worden er extra velden gemaakt voor maanden, kwartalen en jaren.
Zodra de velden zijn toegevoegd kun je beginnen met het analyseren van de gegevens over de verschillende tijdsperiodes met inzoom mogelijkheden. Op deze manier kun je soms extra inzichten vinden.
Voorbeeld 13.3 Kwartaalvergelijking per jaar
Zo kun je om een kwartaalvergelijking over meerdere jaren te krijgen het veld Jaren in het vak Kolommen plaatsen en het veld Kwartalen in het vak Rijen.
De + knop in de draaitabel duidt op een samengevouwen niveau. Wanneer je hierop klikt worden alle elementen in de draaitabel uitgevouwen tot het volgende niveau, hier Maanden. De + knop verandert dan in een - knop waarmee je de groep weer kunt samenvouwen. Dit heet ook wel inzoomen.
- Aangepast groeperen
- Het groeperen kun je aanpassen via een rechtermuisklik op een datum/tijdveld in de draaitabel en dan kiezen voor Groeperen. In het dialoogvenster Groeperen dat dan verschijnt, kun je andere tijdniveaus toevoegen of verwijderen.
- Groepering opheffen
- Een groepering kun je opheffen via een rechtermuisklik op een gegroepeerd veld in de draaitabel en dan kiezen voor Groep opheffen.
13.4 Voorbeeld Groeperen
Een voorbeeld van het groeperen van gegevens op basis van een datumveld.
Taak 13.7 Bestand: Facturen.xlsx
- Open het bestand.
Het bestand bevat een tabel met de tabelnaam Verkoopdata en de kolommen Regio, Verkoper, Factuurnr, Factuurdatum, Productcategorie en Factuurbedrag.
Allereerst wordt met een draaitabel de omzet per verkoper per productcategorie gemaakt.
- Selecteer een cel in de tabel. Kies tab Invoegen > Draaitabel (groep Tabellen) > van Tabel/Bereik.
Klik OK.
Maak een draaitabel volgens het ontwerp in Figuur 13.18 en geef de bedragen een valutanotatie. Het resultaat is te zien in Figuur 13.19.
- Sleep veld Factuurdatum naar het gebied Rijen en wel onder het veld Verkoper. De automatische tijdgroepering van Excel maakt nu twee berekende velden aan, Jaren en Kwartalen.
In de draaitabel (Figuur 13.21) zijn de bedragen nu gegroepeerd per jaar. Hierdoor is ook zien dat Brown als verkoper is begonnen in 2011 en niet in 2010, wat een mogelijke verklaring zou kunnen zijn voor de lagere bedragen.
Klik bij een willekeurige verkoper op de + (plus)knop voor het jaar 2010. Hierdoor wordt voor alle verkopers het jaar opengevouwen naar de kwartalen.
Klik bij een willekeurige verkoper op de + (plus)knop voor Kwrt1. Hierdoor wordt voor alle verkopers het kwartaal opengevouwen naar de maanden.
Experimenteer met het openvouwen en weer dichtvouwen van niveaus.
Sleep het veld Productcategorie uit het vak Kolommen, het veld Kwartalen van vak Rijen naar vak Kolommen en de velden Jaren en Factuurdatum uit het vak Rijen. Het ontwerp komt er nu uit te zien zoals in Figuur 13.23. Het resultaat zie je in Figuur 13.24.
Om de waarden gemakkelijker te vergelijken kun je er ook voor kiezen om ze weer te geven als percentages (van rijtotaal, kolomtotaal of eindtotaal)
- Selecteer een numerieke waarde in de draaitabel en kies tab Draaitabel Analyseren > Veldinstellingen (groep Actief veld). In het dialoogvenster kies je tab Waarden weergeven als en selecteer % van rijtotaal.
- Klik OK.
- Laat vervolgens de waarden weergeven als % van kolomtotaal.
Vragen
Gebruik de mogelijkheden van draaitabellen om de volgende vragen te beantwoorden. Er zijn meerdere wegen om de antwoorden te vinden.
- Welke verkoper heeft het meeste verkocht in december 2010?
Verkoper
- In welke maand van welk jaar was de verkoop van software het grootst?
Jaar Maand
- Bekijk de verkooppercentages voor hard- en software per regio. Wat is het percentage voor software in de regio Zuid?
Afgerond tot geheel getal:
- In welk kwartaal van welk jaar waren de verkopen van Anderson het laagst?
Jaar Kwartaal
- Bekijk de verkopen van Brown in kwartaal 2 van 2011. Valt je iets bijzonders op?
Alleen omzet in de maand juni. Niets in april en mei.
13.5 Filteren
Om de gegevens in een draaitabel te filteren kun je hiervoor in het taakvenster een veld (of meerdere velden) in het vak Filters plaatsen. Maar wanneer je op meerdere items filtert, dan kun je niet gemakkelijk zien waarop je filtert.
Een gebruikersvriendelijker manier is om slicers te gebruiken. Deze bevatten een aantal knoppen waarmee je snel de gegevens in een draaitabel filtert. En alleen voor het filteren van tijdgegevens kun je ook nog tijdlijnen gebruiken, welke identiek zijn aan slicers.
Het gebruik van slicers en tijdlijnen zie je veel in zogenaamde dashboards omdat je deze aan meerdere draaitabellen en draaigrafieken kunt koppelen.
In drie subtaken wordt het maken van rapportfilters, slicers en tijdlijnen besproken. Hierbij wordt gebruik gemaakt van het bestand [Supermarket.xlsx`, hetzelfde bestand als in Paragraaf 13.1, welke de maandelijkse verkoopgegevens in 2016 bevat van een aantal producten, welke in drie productgroepen zijn ondergebracht: agf, vlees en zuivel.
13.5.1 Rapportfilters
Taak 13.8 Bestand: Supermarket.xlsx
Open het bestand.
Voeg voor de gegevens een draaitabel in op een nieuw werkblad.
Plaats het veld productcategorie in Kolommen, het veld omzet in Waarden en het veld datum in Rijen.
Sleep het veld datum uit Rijen zodat hier alleen het veld Maanden komt te staan.
Plaats het veld plaats in het vak Filters. Hierdoor komt er een rapportfilter in de draaitabel.
- Klik op de keuzepijl en selecteer
Amsterdam.
- Klik op de keuzepijl, vink aan Meerdere items selecteren en selecteer dan
AmsterdamenRotterdam.
De gegevens voor beide plaatsen worden nu weergegeven. Helaas geeft het filter nu alleen maar aan dat er meerdere items geselecteerd zijn, maar niet welke items. Voor dit scenario zijn slicers een beter alternatief.
Klik op de keuzepijl en selecteer de optie (Alle).
Verwijder het veld plaats uit het vak Filters.
13.5.2 Slicers
Taak 13.9 Ga verder met het bestand uit Taak 13.8.
- Klik met de rechtermuisknop in de lijst met draaitabelvelden op veld plaats en kies Toevoegen als slicer.
Op het werkblad wordt vervolgens een slicer gemaakt.
- Experimenteer met de slicer door items te kiezen. Je kunt meerdere items selecteren met behulp van de Ctrl toets of via de knop
Een andere manier om slicers te maken is via het menu. Je kunt dan meerdere slicers tegelijk maken.
- Klik ergens in het draaitabelrapport en kies tab Draaitabel Analyseren > Slicer invoegen (groep Filter)
- Selecteer Maanden and click OK.
Via het lint tab Slicers) kun je de opmaak van een slicer wijzigen, zoals stijl, kleur, …
Experimenteer met het filteren van de gegevens met de twee slicers.
Verwijder alle slicers door eerst een slicer te selecteren en dan op de toets Delete te drukken.
13.5.3 Tijdlijnen
Tijdlijnen zijn als slicers. Hiermee kun je ook gegevens filteren maar ze zijn specifiek voor gebruik met datum-/tijdvelden. De datums verschijnen in een horizontale lijn van oud naar nieuw terwijl je van links naar rechts op de tijdlijn gaat.
Taak 13.10 Ga verder met het bestand uit Taak 13.9.
- Klik ergens in het draaitabelrapport en kies tab Draaitabel Analyseren > Tijdlijn invoegen (groep Filter)
Het dialoogvenster Tijdlijn invoegen toont alleen het veld datum omdat dit het enige datum/tijd veld is.
- Selecteer datum en klik OK.
- Een schuifbalk voor de zichtbare periode.
- Handgrepen om de periode te selecteren waarop gefilterd moet worden.
- Keuze voor de weer te geven tijdsperiode (Jaren, Kwartalen, Maanden, Dagen).
- Wanneer je een tijdlijn selecteert krijg je op de randen handgrepen waarmee je het gebied groter/kleiner kunt maken.
Selecteer een paar maanden en bekijk de resultaten.
Klik op de keuzepijl rechtsboven voor de tijdsperiode en selecteer Kwartalen.
Selecteer een paar kwartalen en bekijk de resultaten.
Verwijder de tijdlijn door deze te selecteren en dan op de toets Delete te drukken.
13.6 Draaigrafieken
Een draaigrafiek toont gegevensreeksen, categorieën en assen op dezelfde manier als een gewone grafiek. Maar je krijgt tevens mogelijkheden voor interactieve filtering direct op de grafiek zodat je snel een subset van de gegevens kunt bekijken. En je kunt ook slicers gebruiken voor de filtering.
Taak 13.11 Bestand: Omzet.xlsx
Open het bestand.
Selecteer in het werkblad draaitabel een cel in de draaitabel.
Kies tab Invoegen > Draaigrafiek (groep Grafieken) > Draaigrafiek > Gegroepeerde kolom > OK.
In de draaigrafiek zijn filters voor Regio en Vertegenwoordiger. Wanneer je een filter in de grafiek gebruikt, dan worden ook de gegevens in de draaitabel gefilterd.
Experimenteer met filtering op vertegenwoordiger en bekijk de resultaten. Eindig met het tonen van de gegevens voor alle vertegenwoordigers.
Voeg een slicer toe voor Maand en experimenteer hiermee.
13.7 Opties draaitabel
Er zijn diverse instelmogelijkheden voor draaitabellen. Kies hiervoor een willekeurige cel in een draaitabel en vervolgens tab Draaitabel Analyseren > Opties (groep Draaitabel). Via tabbladen (zie hierna) heb je meerdere instelmogelijkheden.
13.8 Opgaven
Oefening 13.1 Salaris gemiddeldes (pivo001)
Maak een draaitabelrapport om het gemiddelde salaris per afdeling en per divisie te bepalen. Het rapport moet er uitzien zoals hierna is aangegeven.
Bestand: Personeel.xlsx
Oefening 13.2 Salaris totalen (pivo002)
Maak een draaitabelrapport om het totale salaris per functie en per divisie te bepalen. Verder moet er op afdeling gefilterd kunnen worden. Het rapport moet er uitzien zoals hierna is aangegeven.
Bestand: Personeel.xlsx
Oefening 13.3 Omzet, reiskosten en provisie (pivo003)
In een werkblad zijn voor elke buitendienstmedewerker de volgende gegevens bijgehouden: Maand, Regio, Vertegenwoordiger, Omzet, Reiskosten en Provisie.
Maak één draaitabelrapport waarmee je de totale omzet, reiskosten en provisie kunt verkrijgen per maand, per vertegenwoordiger en per regio.
Bestand: Pivo003.xlsx
Oefening 13.4 Gemiddeld cijfer (pivo004)
In de volgende tabel staan de cijfers voor het vak informatica van een aantal studenten uit twee verschillende klassen.
Bepaal met behulp van een draaitabel het gemiddelde per klas en per geslacht.
Bestand: Pivo004.xlsx
Oefening 13.5 Frequentieverdeling cijfers (pivo005)
In de volgende tabel staan de cijfers van een aantal leerlingen.
Maak met behulp van een draaitabel een frequentieverdeling van de cijfers.
Bestand: Pivo005.xlsx
Oefening 13.6 Kledingverkoop per regio (pivo006)
In de volgende tabel staan de verkopen aan dames- en herenkleding in de vier regio’s voor de jaren 2009 en 2010.
Plaats de gegevens in een werkblad en bepaal met behulp van een draaitabel de totale verkopen per jaar in de vier regio’s, uitgesplitst naar dames- en herenkleding.
Bestand: Pivo006.xlsx
Oefening 13.7 Winkelsluiting (pivo007)
In opdracht van de winkeliersvereniging is een enquête uitgevoerd naar de avondopenstelling van winkels. De resultaten van de geïnterviewde personen zijn in de volgende tabel te zien.
Plaats de gegevens in een werkblad en bepaal met behulp van een draaitabel het aantal voor- en tegenstanders per leeftijdscategorie en deze weer uitgesplitst per geslacht.
Bestand: Pivo007.xlsx
Oefening 13.8 Eigen huis en gemiddelde waarde (pivo008)
In de volgende tabel staat van een aantal personen aangegeven hoeveel jaren studie ze na de basisschool gevolgd hebben, of ze al dan niet een eigen huis hebben en zoja wat daarvan de waarde is.
Plaats de gegevens in een werkblad en bepaal met behulp van een draaitabel het aantal personen met een eigen huis en de gemiddelde waarde ervan als functie van het aantal jaren studie.
Bestand: Pivo008.xlsx
Oefening 13.9 Vervoersonderzoek (pivo009)
Onder de bezoekers van een stadcentrum is een marktonderzoek uitgevoerd naar het gebruik van de auto en het gebruik van de bus. In de volgende tabel staan de gegevens van de geënquêteerden die ondervraagd zijn over hun manier van verplaatsen naar het centrum om te gaan winkelen. Gingen ze met de auto, met de bus, hoe ver ze wonen ze van het centrum (in km.)en de reistijd (in min.).
Plaats de gegevens in een werkblad en bepaal met behulp van een draaitabel de gemiddelde tijd met de auto voor inwoners die verder dan 15 km van het centrum wonen.
Maak een extra kolom waarin bepaald wordt of de afstand tot het centrum meer dan 15 km is.
Bestand: Pivo009.xlsx
28,5 min.
Oefening 13.10 Opbrengst koffie per regio (pivo010)
In een werkblad staan de opbrengsten van een aantal soorten koffie per maand per gebied. Het management wil graag op een eenvoudige wijze de totale opbrengst van de producten per regio kunnen bepalen. Maak een draaitabel waaruit per maand de opbrengst per koffiesoort berekend is. De regio moet met een rapportfilter gekozen kunnen worden.
Bestand: Koffie.xlsx
Oefening 13.11 Brandstofverbruik auto’s (pivo011)
Bepaal per merk en per brandstofsoort het minimum en het maximum verbruik met behulp van een draaitabel.
Bestand: Auto.xlsx
Oefening 13.12 Hobbyclub (pivo012)
In een werkblad staan in de laatste twee kolommen het aantal bezochte clubavonden in 2009 en 2010. Maak m.b.v. draaitabellen de volgende overzichten.
Het aantal leden van de club per woonplaats en per geslacht.
Het percentage leden per hobby en per geslacht.
Een overzicht van de aanwezigheid in 2010 per geslacht, waarbij het bezochte aantal clubavonden in drie groepen verdeeld is zoals in het volgende overzicht:
Bestand: Pivo012.xlsx
- Aantal leden per plaats en per geslacht
- Percentage leden per hobby en per geslacht
- Deelname leden in 2010 per geslacht