Data­modellen maken in Excel: een complete gids

Datamodellen stellen ons in staat om data uit meerdere bronnen te laden, te integreren en te analyseren, wat zorgt voor een dynamischer en informatiever beeld van de gegevens. 

In deze tutorial verkennen we verschillende manieren om datamodellen in Excel te maken, zoals het gebruiken van bestaande tabellen, het importeren van nieuwe tabellen en het inzetten van tools als Power Query, Power Pivot en draaidiagrammen.   

Aan de slag!

Een datamodel maken in Excel

Dit zijn de algemene stappen om een datamodel in Excel te maken:

1. Formatteer je data als tabellen.
2. Maak relaties tussen de tabellen.
3. Gebruik Power Query om data te laden en te transformeren.
4. Gebruik Power Pivot voor geavanceerde datamodellering.
5. Gebruik draaitabellen en draaidiagrammen om je data te visualiseren.

Wanneer gebruik je een datamodel in Excel

Een datamodel in Excel is vooral nuttig in veelvoorkomende situaties zoals data-opslag en -opschoning, dataintegratie en -transformatie en complexe data-analyse. Dit zijn enkele van de belangrijkste voordelen.

• Maakt opschonen van data makkelijker: Datamodellen helpen bij het opschonen en standaardiseren van verschillende datasets om uniformiteit en consistentie te bewaren. Dit is cruciaal bij data uit verschillende bronnen, die vaak moeten worden samengevoegd en voorbewerkt. 
• Ondersteunt dataintegratie en -transformatie: Met een datamodel kunnen alle beschikbare gegevens uit andere bronnen eenvoudig worden geïntegreerd en getransformeerd, zodat gebruikers een eenduidig overzicht krijgen dat makkelijk te verwerken en te analyseren is.
• Verbetert datavisualisatie: Datamodellen ondersteunen het maken van geavanceerde visualisaties zoals draaidiagrammen en Power View-rapporten. 
• Verbetert gegevensbeveiliging en -beheer: Met een datamodel kun je de toegang tot verschillende delen van je data gecontroleerd regelen. Het wordt eenvoudiger om machtigingen en data binnen het model te beheren, waardoor het risico op onbevoegde toegang en datalekken afneemt.
• Stroomlijnt rapportage: Via datamodellen kun je rapportages volledig automatiseren, wat het rapportage- en besluitvormingsproces vergemakkelijkt. Rapporten en dashboards kunnen dynamisch worden aangepast wanneer er nieuwe data binnenkomt, zodat alle stakeholders over actuele informatie beschikken.
• Kan grote datasets aan: Datamodellen kunnen met grote datasets in Excel werken en omzeilen de beperkingen van traditionele werkbladen.
• Maakt complexe data-analyse mogelijk: Dankzij relaties tussen meerdere datatabellen kun je geavanceerde analyses uitvoeren die met reguliere Excel-functies lastig zijn.

Een datamodel maken door data te importeren

In deze sectie lopen we door het proces om in Excel een datamodel te maken door voorbeelddatasets te importeren. We gebruiken twee voorbeelddatasets: Order en Product. Ik heb een openbare GitHub-repository gemaakt, Excel-Data-Model, zodat je de datasets kunt downloaden en deze tutorial kunt volgen.

De eerste stap is data in Excel importeren. Open een nieuw Excel-werkboek en klik op het tabblad Gegevens. Kies onder Gegevens ophalen voor Gegevens ophalen > Uit bestand > Uit Excel-werkmap.

Data importeren in Excel. Afbeelding door de auteur

Selecteer het werkboek Order.xlsx en klik op Importeren. Hierdoor gaat het venster Navigator open: selecteer de tabel Sheet1.

Het Navigator-venster in Excel. Afbeelding door de auteur

Klik op de optie Laden in, waarmee het venster Gegevens importeren wordt geopend. Selecteer in het dialoogvenster Alleen verbinding maken en vink Deze gegevens toevoegen aan het Datamodel aan. Klik op OK.

Venster Gegevens importeren. Afbeelding door de auteur

Herhaal de bovenstaande stappen om het blad Product te laden. Als deze twee taken zijn voltooid, ziet je Excel-werkboek er zo uit:

Deelvenster Query's en verbindingen. Afbeelding door de auteur

Gefeliciteerd! Je hebt beide bladen geïmporteerd in het Excel-bestand. De volgende stap is het tot stand brengen van relaties tussen de geïmporteerde tabellen. Ga hiervoor naar het tabblad Hulpmiddelen voor gegevens en klik op Relaties.

Tabblad Hulpmiddelen voor gegevens. Afbeelding door de auteur 

Er verschijnt een nieuw venster.

Venster Relaties beheren. Afbeelding door de auteur

Klik op Nieuw om een relatie te maken. De pop-up Relatie maken wordt geopend. Stel de relatie in zoals hieronder weergegeven. 

Venster Relatie maken. Afbeelding door de auteur

Klik op OK. De relatie is gelegd en je kunt dit bevestigen op het tabblad Verbindingen.

Deelvenster Query's en verbindingen. Afbeelding door de auteur

De volgende stap is het maken van de draaitabel. Ga naar het tabblad Invoegen en klik op Draaitabel. Selecteer vervolgens in het dialoogvenster Draaitabel de optie Het datamodel van deze werkmap gebruiken.

Dialoogvenster Draaitabel. Afbeelding door de auteur

Kies de locatie voor de draaitabel. Klik op OK. De draaitabel wordt geopend en je ziet de velden onder Draaitabelvelden.

Draaitabelvelden. Afbeelding door de auteur

Voer de volgende stap uit om de draaitabel op te bouwen.

Sleep in het deelvenster Draaitabelvelden velden naar de gebieden Rijen, Kolommen, Waarden en Filters:

• Rijen: ProductName
• Waarden: Quantity

De resulterende draaitabel ziet er zo uit:

Deelvenster Draaitabelvelden. Afbeelding door de auteur

Je kunt de draaitabel naar wens aanpassen en opmaken.

Een datamodel maken met Power Query 

Voor deze sectie gebruiken we de data in het Excel-bestand Sample_Data. Dit bestand is beschikbaar via dezelfde link als in de vorige sectie. Er staan twee tabellen in het blad Data met de volgende records.

Excel-tabellen om te joinen. Afbeelding door de auteur

We bouwen datamodellen in dit bestaande Excel-blad. Selecteer om te beginnen een willekeurige cel in de tabel Customer ID and Name en druk op Ctrl + T.

Een cel selecteren. Afbeelding door de auteur

Er verschijnt een pop-up Tabel maken zoals hieronder.

Pop-up Tabel maken. Afbeelding door de auteur

 Klik op OK en de tabel is klaar. 

Draaitabel in Excel. Afbeelding door de auteur

Geef nu een naam aan deze tabel. Ga naar het contextuele venster Tabelontwerp en wijzig onder Tabelnaam de naam in CustomerPQ. Je kunt ook een andere naam kiezen. 

Contextvenster Tabelontwerp. Afbeelding door de auteur

Deze tabel heet nu CustomerPQ.

Contextvenster Tabelontwerp. Afbeelding door de auteur

Herhaal het proces voor de tweede tabel. Zodra de tabel is gemaakt, geef je deze de naam PurchasePQ.

Twee tabellen in Excel klaar voor datamodel. Afbeelding door de auteur

De actieve tabel is nu PurchasePQ. Ga naar Gegevens en kies Van tabel/bereik. Het volgende blad verschijnt.

De Power Query-editor openen. Afbeelding door de auteur

Klik om een verbinding te maken en de tabel Purchase PQ toe te voegen aan het datamodel op het uitklapmenu van Sluiten & Laden en kies Sluiten & Laden naar…  

De Power Query-editor sluiten. Afbeelding door de auteur

Er wordt een venster Gegevens importeren geopend dat je als volgt invult:

Venster Gegevens importeren. Afbeelding door de auteur 

Na het voltooien van de bovenstaande stappen zie je het vak Query's en verbindingen.

Vak Query's en verbindingen. Afbeelding door de auteur

Voeg nu de tabel CustomerPQ toe aan de verbinding. Klik daarvoor op een cel in de tabel, ga naar Gegevens en kies Van tabel/bereik. Het volgende blad verschijnt.

Tabellen klaar voor Excel-datamodel. Afbeelding door de auteur

Herhaal het proces dat je voor de tabel PurchasePQ hebt uitgevoerd en vul het venster Gegevens importeren in zoals hieronder:

Venster Gegevens importeren. Afbeelding door de auteur 

 Klik op OK. Het tabblad Query's en verbindingen toont nu beide tabellen.

Query's en verbindingen. Afbeelding door de auteur

We kunnen nu relaties leggen. Ga binnen het tabblad Gegevens naar de sectie Hulpmiddelen voor gegevens en klik op Datamodel beheren.

Hulpmiddelen voor gegevens. Afbeelding door de auteur

Hiermee wordt Power Pivot voor Excel geopend.

Power Pivot voor Excel. Afbeelding door de auteur

Klik nu op Diagramweergave om een relatie tussen de twee tabellen te maken.

Weergave-venster. Afbeelding door de auteur

 Er verschijnt een scherm zoals hieronder weergegeven. 

Diagramweergave. Afbeelding door de auteur

Je ziet twee tabellen die nog niet verbonden zijn. Om de relatie te maken, ga je naar het tabblad Ontwerpen en klik je op Relatie maken.

Tabblad Ontwerpen. Afbeelding door de auteur

Er wordt een nieuw venster geopend. Vul de gegevens in zoals hieronder:

Venster Een relatie maken. Afbeelding door de auteur

Klik op OK. Je ziet dat de relatie nu is gelegd.

Venster dat toont dat de relatie is gelegd. Afbeelding door de auteur 

Sla Power Pivot op via het opslagpictogram of met Ctrl + S en sluit het tabblad. De volgende melding verschijnt.

Pop-up Inhoud inschakelen. Afbeelding door de auteur

Klik op Inhoud inschakelen om de verbinding te maken. De verbinding is nu gelegd. We kunnen nu de draaitabel gebruiken. Ga naar Invoegen, klik op Draaitabel en kies Van datamodel.

Deelvenster Draaitabel. Afbeelding door de auteur

Je krijgt een pop-up zoals deze:

Draaitabel vanuit Datamodel-tab. Afbeelding door de auteur

Klik op Nieuw werkblad en vervolgens op OK. Er wordt een nieuw blad met de naam Sheet1 gemaakt.

Draaitabelblad. Afbeelding door de auteur 

In de Draaitabelvelden vind je de tabellen.

Labels onder Draaitabelvelden. Afbeelding door de auteur

Selecteer Customer Name in de tabel CustomerPQ. Selecteer vervolgens onder PurchasePQ Purchase. Draaitabelvelden ziet er dan zo uit:

Items selecteren in de Draaitabelvelden. Afbeelding door de auteur 

Hiermee wordt de draaitabel in het blad gegenereerd, die er als volgt uitziet:

Uitvoer draaitabel. Afbeelding door de auteur

Top! Zo kun je Power Query gebruiken om het datamodel te maken en Power Pivot om samenvattende rapporten te maken.

Een datamodel rechtstreeks in Power Pivot maken

In deze sectie gebruik je Power Pivot om een datamodel te maken en een samenvattend rapport voor te bereiden. 

Ga om te beginnen naar het blad Power Pivot in de data die we hierboven hebben besproken en maak de tabellen voor onze twee records.

Records die worden gebruikt. Afbeelding door de auteur 

We hebben in de vorige secties al gezien hoe je tabellen maakt, dus volg die stappen om de twee tabellen te maken.

Het enige verschil is dat we nieuwe namen voor deze tabellen instellen, zodat we ze kunnen onderscheiden van de vorige sectie: 

• Stel de naam van de eerste tabel in op CustomerPP.
• Stel de naam van de tweede tabel in op PurchasePP.

Data Tabellen uit de records. Afbeelding door de auteur

De twee tabellen zijn gemaakt en de volgende stap is ze toevoegen aan het datamodel. Ga hiervoor naar Power Pivot, en kies Toevoegen aan datamodel.

Toevoegen aan datamodel onder PowerPivot. Afbeelding door de auteur

Er verschijnt een pop-up Power Pivot voor Excel die er zo uitziet.

Power Pivot voor Excel-pop-up. Afbeelding door de auteur 

Sla dit venster op en sluit het. Herhaal hetzelfde proces voor de tabel CustomerPP om deze toe te voegen aan het datamodel.

Power Pivot voor Excel-pop-up. Afbeelding door de auteur

Zodra deze tabellen aan het datamodel zijn toegevoegd, kun je de relatie ertussen leggen met Power Pivot. Klik hiervoor op de Diagramweergave.

Diagramweergave. Afbeelding door de auteur

Ga vervolgens naar het tabblad Ontwerpen en klik op Relatie maken.

Tabblad Een relatie maken. Afbeelding door de auteur

Er wordt een nieuw venster geopend. Vul de gegevens in zoals hieronder:

Een relatie maken tussen twee tabellen. Afbeelding door de auteur

 Klik op OK. Je ziet dat de relatie is gelegd.

Relaties zijn gelegd. Afbeelding door de auteur

Vanaf hier is het eenvoudig om een draaitabel te maken, zoals we in de vorige sectie hebben gedaan. De uiteindelijke output zou er zo uit moeten zien.

Samenvatting draaitabel. Afbeelding door de auteur

Met de bovenstaande stappen kun je Power Pivot gebruiken om het datamodel te maken en een samenvattende analyse te maken.

Een datamodel maken met draaitabel of draaidiagram

Zodra je de draaitabel hebt gemaakt, is het eenvoudig om een draaidiagram in te voegen. Met de Draaitabel (Sheet 1) geselecteerd, ga je naar het tabblad Invoegen.

Samenvatting draaitabel. Afbeelding door de auteur

Klik op Draaidiagram, kies het diagramtype en klik op OK.

Opties Diagram invoegen. Afbeelding door de auteur

Dit maakt het volgende draaidiagram.

Draaidiagramvisualisatie. Afbeelding door de auteur

Je kunt het draaidiagram aanpassen door:

• Elementen van het diagram aan te passen, zoals titels, legenda's en aslabels, en
• De opmaak van het diagram te wijzigen om de uitstraling en leesbaarheid te verbeteren.

Mooi, je hebt de draaitabel gebruikt om je eerste visualisatie te bouwen.

Veelvoorkomende vragen en problemen met Excel-datamodellen

Dit zijn enkele veelvoorkomende vragen en problemen rondom Excel-datamodellen:

Wat moet ik doen als een probleem met het datamodel voorkomt dat Microsoft Excel een werkmap opent?

Dit kan gebeuren door beschadiging in het datamodel of compatibiliteitsproblemen. Zo los je het op:

1. Openen in de veilige modus: Probeer Excel in de veilige modus te starten door de Ctrl-toets ingedrukt te houden bij het openen. Dit kan het probleem soms omzeilen.
2. Controleren op updates: Zorg dat Excel up-to-date is via Bestand > Account > Updateopties.
3. Office repareren: Gebruik de Office-hersteltool. Ga naar Configuratiescherm > Programma's en onderdelen, selecteer je Office-installatie en kies Wijzigen > Repareren.
4. Invoegtoepassingen uitschakelen: Ga naar Bestand > Opties > Invoegtoepassingen en schakel alle invoegtoepassingen uit. Start Excel opnieuw en probeer de werkmap opnieuw te openen.
5. Data extraheren: Als het niet lukt om de werkmap te openen, probeer dan een datahersteltool of neem contact op met Microsoft Support voor geavanceerde hulp.

Hoe kan ik fouten in het datamodel oplossen?

Los fouten in het datamodel zo op:

1. Gegevenstypen controleren: Zorg dat de gegevenstypen in kolommen die voor relaties worden gebruikt consistent zijn.
2. Relaties valideren: Controleer of alle relaties correct zijn gedefinieerd.
3. Berekeningen nalopen: Controleer berekende kolommen en measures op fouten.
4. Gegevens vernieuwen: Zorg dat alle gegevensverbindingen zijn bijgewerkt.
5. Foutmeldingen: Bekijk foutmeldingen in het Power Pivot-venster voor specifieke problemen.

Door deze stappen te volgen, kun je je datamodel in Excel effectief beheren en problemen oplossen, zodat je data-analyse soepel en efficiënt verloopt. Microsoft Support is een andere handige bron voor probleemoplossing. 

Tot slot

Bedankt voor het volgen van de tutorial! Je hebt een belangrijk concept geleerd op weg naar het beheersen van data-analyse met Excel. Het maken van datamodellen zal jouw en je organisatie’s analysecapaciteiten aanzienlijk verbeteren. Het verhoogt ook je efficiëntie bij het beheren van verschillende datatabellen en records. 

Voor verdere verdieping kun je de volgende bronnen verkennen:   

• Introductie tot Excel: Een complete gids om met Excel te starten, met de basisfunctionaliteiten en -functies.
• Data-analyse in Excel: Leer verschillende analysetechnieken en tools in Excel om je analytische vaardigheden te versterken.
• Excel Fundamentals: Een cursus met de fundamentele concepten en handelingen in Excel voor een stevige basis.
• Datavisualisatie in Excel: Ontdek hoe je impactvolle en interactieve visualisaties maakt met de ingebouwde tools van Excel.
• Introductie tot Power Query in Excel: Begrijp hoe je Power Query gebruikt om data efficiënt te importeren, op te schonen en te transformeren in Excel.