Een gegevensmodel maken in Excel

Met een gegevensmodel kunt u gegevens uit meerdere tabellen integreren, waardoor er effectief een relationele gegevensbron in een Excel-werkmap wordt gebouwd. In Excel worden gegevensmodellen transparant gebruikt, waarbij gegevens in tabelvorm worden weergegeven die worden gebruikt in draaitabellen en draaigrafieken. Een gegevensmodel wordt gevisualiseerd als een verzameling tabellen in een lijst met velden en meestal weet u niet eens dat het er is.

Voordat u aan de slag kunt met het gegevensmodel, moet u enkele gegevens ophalen. Hiervoor gebruiken we de ervaring Get & Transform (Power Query), dus misschien wilt u een stapje teruggaan en een video bekijken of onze leergids over Get & Transform en Power Pivot volgen.

Waar is PowerPivot?

Excel 2016 & Excel voor Microsoft 365 : Power Pivot is opgenomen op het lint.

Waar is Get & Transform (Power Query)?

Excel 2016 & Excel voor Microsoft 365 - Get & Transform (Power Query) is geïntegreerd met Excel op het tabblad Gegevens .

Aan de slag

Eerst moet u enkele gegevens ophalen.

In Excel 2016 en Excel voor Microsoft 365 gebruikt u Gegevens > Gegevens ophalen & Gegevens transformeren > Gegevens ophalen om gegevens te importeren uit een willekeurig aantal externe gegevensbronnen, zoals een tekstbestand, Excel-werkmap, website, Microsoft Access, SQL Server of een andere relationele database met meerdere gerelateerde tabellen.
In Excel wordt u gevraagd om een tabel te selecteren. Als u meerdere tabellen uit dezelfde gegevensbron wilt ophalen, schakelt u de optie Selectie van meerdere tabellen inschakelen in. Wanneer u meerdere tabellen selecteert, wordt er automatisch een gegevensmodel voor u gemaakt.

Opmerking: Voor deze voorbeelden gebruiken we een Excel-werkmap met fictieve gegevens van leerlingen/studenten over klassen en cijfers. U kunt de voorbeeldwerkmap Student Data Model downloaden en verder gaan. U kunt ook een versie downloaden met een voltooid gegevensmodel..
Selecteer een of meer tabellen en klik vervolgens op Laden.

Als u de brongegevens wilt bewerken, kunt u de optie Bewerken kiezen. Zie Voor meer informatie: Inleiding tot de queryeditor (Power Query).

U hebt nu een gegevensmodel dat alle tabellen bevat die u hebt geïmporteerd en deze worden weergegeven in de lijst met draaitabelvelden.

Notities:

Er wordt impliciet een model gemaakt wanneer u twee of meer tabellen tegelijk importeert in Excel.
Er wordt expliciet een model gemaakt wanneer u via de Power Pivot-invoegtoepassing gegevens importeert. In de invoegtoepassing wordt het model weergegeven in een indeling met tabbladen die vergelijkbaar is met Excel, waarbij elk tabblad gegevens in tabelvorm bevat. Zie Gegevens ophalen met de Power Pivot-invoegtoepassingvoor meer informatie over de basisbeginselen van het importeren van gegevens met behulp van een SQL Server-database.
Een model kan één tabel bevatten. Als u een model wilt maken op basis van slechts één tabel, selecteert u de tabel en klikt u op Toevoegen aan gegevensmodel in Power Pivot. U wilt dit mogelijk doen als u Power Pivot-functies zoals gefilterde gegevenssets, berekende kolommen, berekende velden, KPI's en hiërarchieën wilt gebruiken.
Tabelrelaties kunnen automatisch worden gemaakt als u verwante tabellen importeert die primaire en refererende-sleutelrelaties hebben. Excel kan meestal de geïmporteerde relatiegegevens gebruiken als basis voor tabelrelaties in het gegevensmodel.
Zie Een geheugenefficiënt gegevensmodel maken met Excel en Power Pivot voor tips over het verkleinen van de grootte van een gegevensmodel.
Zie Zelfstudie: Gegevens importeren in Excel en Een gegevensmodel maken voor meer informatie.

Tip: Hoe weet u of uw werkmap een gegevensmodel heeft? Ga naar Power Pivot > Beheren. Als u werkbladachtige gegevens ziet, bestaat er een model. Zie: Ontdek welke gegevensbronnen worden gebruikt in een werkmapgegevensmodel voor meer informatie.

Relaties tussen uw tabellen maken

De volgende stap is het maken van relaties tussen uw tabellen, zodat u er gegevens uit kunt ophalen. Elke tabel moet een primaire sleutel of unieke veld-id hebben, zoals Student-id of Klasnummer. De eenvoudigste manier is om deze velden te slepen en neer te zetten om ze te verbinden in de diagramweergave van Power Pivot.

Ga naar Power Pivot > Beheren.
Selecteer op het tabblad Startde optie Diagramweergave.
Al uw geïmporteerde tabellen worden weergegeven en u kunt even de tijd nemen om de grootte ervan te wijzigen, afhankelijk van het aantal velden dat elke tabel heeft.
Sleep vervolgens het primaire-sleutelveld van de ene tabel naar de volgende. Het volgende voorbeeld is de diagramweergave van onze studenttabellen:

We hebben de volgende koppelingen gemaakt:
- tbl_Students | Student-id > tbl_Grades | Student-id
  
  Met andere woorden: sleep het veld Student-id van de tabel Studenten naar het veld Student-id in de tabel Cijfers.
- tbl_Semesters | Semester-id > tbl_Grades | Semester
- tbl_Classes | Klassenummer > tbl_Grades | Klassenummer
Notities:
- Veldnamen hoeven niet hetzelfde te zijn om een relatie te maken, maar ze moeten wel hetzelfde gegevenstype hebben.
- De verbindingslijnen in de diagramweergave hebben een '1' aan de ene kant en een '*' aan de andere kant. Dit betekent dat er een een-op-veel-relatie is tussen de tabellen en dat bepaalt hoe de gegevens in uw draaitabellen worden gebruikt. Zie Relaties tussen tabellen in een gegevensmodel voor meer informatie.
- De connectors geven alleen aan dat er een relatie tussen tabellen is. Ze laten niet zien welke velden aan elkaar zijn gekoppeld. Als u de koppelingen wilt zien, gaat u naar Power Pivot > Manage > Design > Relationships > Manage Relationships. In Excel kunt u naar Gegevens > Relaties gaan.

Een gegevensmodel gebruiken om een draaitabel of draaigrafiek te maken

Een Excel-werkmap kan slechts één gegevensmodel bevatten, maar dat model kan meerdere tabellen bevatten die herhaaldelijk in de werkmap kunnen worden gebruikt. U kunt op elk gewenst moment meer tabellen toevoegen aan een bestaand gegevensmodel.

Ga in Power Pivotnaar Beheren.
Selecteer draaitabel op het tabblad Start.
Selecteer waar u de draaitabel wilt plaatsen: een nieuw werkblad of de huidige locatie.
Klik op OK om een lege draaitabel toe te voegen met het deelvenster Lijst met velden aan de rechterkant.

Maak vervolgens een draaitabel of maak een draaigrafiek. Als u al relaties tussen de tabellen hebt gemaakt, kunt u een van de bijbehorende velden in de draaitabel gebruiken. We hebben al relaties gemaakt in de voorbeeldwerkmap StudentGegevensmodel.

Bestaande, niet-gerelateerde gegevens toevoegen aan een gegevensmodel

Stel dat u veel gegevens hebt geïmporteerd of gekopieerd die u in een model wilt gebruiken, maar deze niet hebt toegevoegd aan het gegevensmodel. Het overbrengen van nieuwe gegevens naar een model is eenvoudiger dan u denkt.

Selecteer eerst een cel in de gegevens die u aan het model wilt toevoegen. Dit kan elk gegevensbereik zijn, maar gegevens die zijn opgemaakt als een Excel-tabel zijn het beste.
Voeg uw gegevens op een van de volgende manieren toe:
Klik op Power Pivot>Toevoegen aan gegevensmodel.
Klik op Invoegen > Draaitabel en schakel vervolgens in het dialoogvenster Draaitabel maken het selectievakje Deze gegevens toevoegen aan het gegevensmodel in.

Het bereik of de tabel wordt nu toegevoegd aan het model als gekoppelde tabel. Zie Gegevens toevoegen met gekoppelde Excel-tabellen in Power Pivot voor meer informatie over het werken met gekoppelde tabellen in een model.

Gegevens toevoegen aan een Power Pivot tabel

In Power Pivot is het niet mogelijk om een rij toe te voegen aan een tabel door rechtstreeks in een nieuwe rij te typen, zoals dat kan in een werkblad van Excel. U kunt echter rijen toevoegen door te kopiëren en plakken, of de brongegevens bij te werken en het Power Pivot-model te vernieuwen.