Het Excel bestand dat hoort bij deze pagina over draaitabellen voor gevorderden staat onderaan de tekst en mag gratis worden gedownload voor eigen gebruik.
Let op: Voor het volgen van onderstaande instructies dien je te beschikken over Power Pivot in Excel. Deze functie wordt bij stap 2c geïntroduceerd. Je kunt hier checken of jouw Excel-versie over Power Pivot beschikt. Of kijk onderaan bij Tot slot nog enkele tips voor een snellere controle. Power Pivot is sowieso niet beschikbaar in Office (Excel) for Mac.
Inleiding
Draaitabellen bieden veel overzicht. Je kunt er grote hoeveelheden informatie eenvoudig mee groeperen en ordenen. Ook zijn draaitabellen flexibel. Door wat labels heen en weer te slepen verander je een bestaand overzicht en creëer je nieuwe inzichten. Beginnende gebruikers leren over het algemeen snel hoe je op basis van informatie uit één tabblad een draaitabel maakt (zie: Draaitabellen voor beginners). Maar het is ook mogelijk om informatie uit verschillende tabellen te combineren in één draaitabel. Met Power Pivot, in een handomdraai.
Voorbeeldbestand
Voor deze blog heb ik een bestand gemaakt voor ‘Garagebedrijf Janssen’. Dit bedrijf verkoopt zogenoemde Ster Occassions; gebruikte auto’s van het merk Mercedes. In het bestand staan vier tabbladen:
- Verkoopinformatie – Dit betreft een lijst met verkochte voertuigen. Hiervan zijn het kenteken, de (verkoop)maand, verkoopprijs en betaalmethode vastgelegd.
- Voertuiginformatie – Dit betreft een lijst met voertuiggegevens. Het kenteken, model, de carrosserievariant en transmissie zijn vastgelegd.
- Klantinformatie – Dit betreft een lijst met klantengegevens. Het kenteken, de voornaam, achternaam en woonplaats zijn vastgelegd.
- CombiDraaitabel – Dit betreft een draaitabel met de informatie uit de voorgaande drie tabbladen daarin gecombineerd. Deze instructie gaat over het maken van deze speciale draaitabel.
Stap 1: Gegevenslijsten omzetten naar tabellen
Om de gecombineerde draaitabel te kunnen maken, moeten de gegevenslijsten op de eerste drie tabbladen worden omgezet naar tabellen. Dit doe je als volgt.
Gegevenslijsten omzetten naar tabellen
1a) Selecteer een willekeurige cel in de lijst met gegevens
1b) Op tab Invoegen klik op de knop Tabel
1c) In het verschenen menu Tabel maken wordt het tabelbereik automatisch ingevuld (hier =$A$4:$D$16).
1d) Vink aan dat de tabel reeds kopteksten bevat en druk op OK
1e) Nadat je op OK hebt gedrukt, wordt de tabel automatisch opgemaakt in een wisselend strepenpatroon
1f) Geef de tabel een logische naam in het vak Tabelnaam:
1g) Herhaal stap 1a t/m 1f voor alle drie de tabbladen met gegevenslijsten
Ik noem mijn drie tabellen Verkooptabel, Voertuigtabel en Klanttabel
Stap 2: Een draaitabel maken
De volgende stap is het maken van een draaitabel; dit is nog niet de uiteindelijke CombiDraaitabel. Vooral één specifieke instellingskeuze bij het maken van de draaitabel is van belang. Ga als volgt te werk.
Draaitabel maken
2a) Selecteer een willekeurige cel in een van de aangemaakte tabellen
2b) Op tab Invoegen klik op de knop Draaitabel
2c) In het verschenen menu Draaitabel maken vink je de optie Deze gegevens toevoegen aan het gegevensmodel aan
2d) Klik op OK
2e) Er wordt een nieuw tabblad met een lege draaitabel aangemaakt
2f) Geef het nieuwe tabblad een naam (in mijn geval CombiDraaitabel)
2g) Selecteer een willekeurige cel in de nieuwe draaitabel en selecteer in het Draaitabelvelden menu aan de rechterkant van het scherm de tab Alle
Je ziet dat Excel alle drie de aangemaakte tabellen bij stap 1 herkent in de draaitabel. Echter, je kunt ze nog niet gebruiken.
Stap 3: Relaties tussen tabellen leggen
De drie eerder aangemaakte tabellen hebben één ding gemeen: ze bevatten allemaal een kolom met unieke kentekengegevens. Deze unieke gegevens heb ik nodig om een relatie tussen de tabellen te kunnen leggen. Ter illustratie het volgende.
Door een relatie te leggen tussen de verkooptabel en voertuigtabel en daarna tussen de verkooptabel en klanttabel zijn de voertuigtabel en klanttabel automatisch ook verbonden. Doe hiertoe het volgende.
Relaties tussen tabellen leggen
3a) Selecteer een willekeurige cel in de aangemaakte lege draaitabel
3b) Op tab Analyseren klik op de knop Relaties
3c) Klik op Nieuw… om de eerste nieuwe relatie te kunnen leggen
3d) Kies het verschenen Relatie maken scherm bij Tabel: voor de Verkooptabel en bij Kolom (extern): voor Kenteken
3e) Kies bij Gerelateerde tabel: voor de Voertuigtabel en bij Gerelateerde kolom (primair): voor Kenteken
3f) Klik op OK en kies opnieuw voor Nieuw… om ook de tweede nieuwe relatie te kunnen leggen
3g) Kies in het opnieuw verschenen Relatie maken scherm bij Tabel: voor de Verkooptabel en bij Kolom (extern): voor Kenteken
3h) Kies bij Gerelateerde tabel: voor de Klanttabel en bij Gerelateerde kolom (primair): voor Kenteken
Klik op OK en daarna op Sluiten
Stap 4: Het gewenste overzicht maken
Nu de drie tabellen aan elkaar gerelateerd zijn, kunnen met de nieuwe CombiDraaitabel overzichten worden getoond die een combinatie van gegevens uit alle tabellen bevatten. Daarbij hoef je de Kenteken data niet te gebruiken (maar het mag wel). Deze unieke data waren alleen nodig voor het leggen van de relaties tussen de individuele tabellen.
Het gewenste overzicht maken
4a) Selecteer een willekeurige cel in de nieuwe, nog lege draaitabel
4b) Selecteer in het verschenen Draaitabelvelden menu aan de rechterkant van het scherm de tab Alle
4c) Klik alle tabellen open om de beschikbare veldnamen voor de CombiDraaitabel te zien
4d) Stel het gewenste overzicht samen
In mijn voorbeeld heb ik velden uit alle drie de tabellen in één overzicht verwerkt. Zonder het leggen van de relaties tussen de tabellen was dit onmogelijk geweest:
- Voertuigtabel – De velden Model en Kenteken zijn ondergebracht bij Rijen in de draaitabel.
- Klanttabel – Het veld Woonplaats is ondergebracht bij Kolommen in de draaitabel.
- Verkooptabel – Het veld Prijs is ondergebracht bij Waarden in de draaitabel.
Tot slot een aantal tips
- Controleer hier of jouw Excel-versie Power Pivot functionaliteit bevat.
- Een snellere controle: Kies een willekeurige cel in Excel, klik op tab Invoegen en druk op de knop Draaitabel. Toont het verschenen menu Draaitabel maken de optie Deze gegevens toevoegen aan het gegevensmodel dan heb je Power Pivot functionaliteit tot je beschikking.
- Als je goed weet hoe je een eenvoudige draaitabel maakt, is een draaitabel gebaseerd op Power Pivot ‘een makkie’.
- Een Power Pivot draaitabel functioneert pas optimaal als de onderliggende tabellen gekoppeld zijn op basis van unieke gegevens zoals bijvoorbeeld ordernummers, kentekens, bankrekeningnummers, personeelsnummers, BTW-nummers, enzovoort.