Iedereen die met Excel werkt, kent wel de situatie waarbij je verschillende tabellen hebt en dat je graag gegevens zou gebruiken uit die verschillende tabellen. De meeste Excel-gebruikers denken dat een PivotTable of draaitabel altijd gebaseerd is op één tabel. Toch kan je in Excel vrij eenvoudig een PivotTable maken met gegevens uit meerdere tabellen. In deze blogpost beschrijf ik de verschillende stappen die je nodig hebt.
Een PivotTable op basis van twee tabellen: een praktijkvoorbeeld
Sowieso heb je altijd minstens twee tabellen nodig. In dit voorbeeld vertrekken we ook van twee tabellen:
- Klanten (met de achternaam, de voornaam, het geslacht en de postcode) (Er zijn 82 klanten)
- Gemeentes (met de naam van de gemeente en de postcode) (Deze heb ik gedownload op de site van De Post: http://www.bpost.be/site/nl/verzenden/adressering/zoek-een-postcode
We zouden graag per gemeente het aantal inwoners per geslacht willen kennen.
Normaal zouden de meeste mensen een extra kolom ‘Gemeente’ aan de tabel met de klanten toevoegen om dan met een VLOOKUP-functie de gegevens op te zoeken in de andere tabel. Initieel kan je misschien zeggen dat dit niet zoveel werk is, maar stel dat je niet 1 kolom wil toevoegen aan de tabel klanten, maar dat je 10 kolommen moet toevoegen met telkens een VLOOKUP. Dat vraagt niet alleen veel tijd, het is ook belastend op vlak van de performantie van jouw Excel.
Bovendien kun je in ons voorbeeld niet zomaar een VLOOKUP-functie gebruiken. Excel zal immers altijd in de 1e kolom van de tabel met de gemeentes zoeken. In de eerste kolom staan in ons voorbeeld de gemeentes in plaats van de postcodes. Dus je zou ook al die kolommen van plaats moeten veranderen.
Het kan dus veel eenvoudiger:
- Klik in de tabel van de postcodes.
- Creëer een PivotTable via Insert – PivotTable
- In de popup activeer je de optie Add this data to the Data Model en je klikt op OK.
- In de PivotTable slepen we het veld Code naar de Rows.
- Aan de rechterkant zie je de PivotTable Field list. Momenteel wordt enkel de tabel getoond die gebruikt is in de PivotTable (Gemeentes), maar je hebt bovenaan een optie All waardoor je alle tabellen in het werkboek te zien krijgt. Klik op All en sleep het veld Achternaam van de tabel Klanten naar de Values.
- Je ziet als resultaat bij elke gemeente hetzelfde aantal personen. Dit is normaal. Excel weet immers niet hoe de tabel met de klanten kan gelinkt worden aan de tabel met de postcodes. Dit zie je ook aan de foutmelding in de Fields List.
- Je kan proberen om op Auto-Detect… te klikken. In sommige gevallen zal Excel zelf de link vinden. (Als bijvoorbeeld beide tabellen een gelijknamige kolom hebben wat in ons voorbeeld niet het geval is; postcode in klanten en code in gemeentes) Je krijgt deze melding. Sluit ze af door op Close te klikken.
-
In plaats van op Auto-Detect… te klikken gaan we voor de andere optie: CREATE…
Dit scherm kan je vergelijken met wat je in de VLOOKUP-functie doet. Lees bovenstaande screenshot als volgt: “Zoek uit de tabel Klanten de waarde van de kolom Postcode op in het veld Code van de tabel Gemeentes“. Aangezien je zelf kan kiezen over welke kolommen het gaat speelt het dus ook geen rol of het veld Code de eerste of de tweede kolom van de tabel is. Bevestig met OK.De PivotTable wordt bijgewerkt en je krijgt het gewenste resultaat.
- Door het veld Geslacht uit de tabel Klanten naar de sectie Columns te slepen krijgen we de PivotTable die we initieel voor ogen hadden, zonder gebruik te maken van functies.
Het leuke is dat je dit niet hoeft te beperken tot slechts twee tabellen. Zolang de relaties correct zijn kan je meerdere tabellen in één PivotTable gebruiken.
Power Pivot: de power achter Excel
Ter info: Het feit dat je een PivotTable kan bouwen die gebaseerd is op meerdere tabellen is omdat Excel op de achtergrond een gegevensmodel bouwt. Als je dit gegevensmodel wil bekijken, dan zul je de Add-In Power Pivot moeten activeren. Deze laat u niet alleen toe om een PivotTable te creëren die gebaseerd is op meerdere tabellen, maar hiermee kan je ook custom sorteringen definiëren, totalen overrulen, berekeningen maken die in een gewone PivotTable niet mogelijk zijn, verder rekenen men de uitkomst van een PivotTable berekening, … Power Pivot is heel krachtig, maar niet eenvoudig.
Wil jij ook meer kunnen halen uit je PivotTables? Schrijf je dan in voor één van deze opleidingen:
