Een gegevensmodel maken in Excel

Van toepassing op

Excel voor Microsoft 365 Excel 2024 Excel 2021

Met een gegevensmodel kunt u gegevens uit meerdere tabellen integreren en zo een efficiënte, relationele gegevensbron in een Excel-werkmap maken. In Excel worden gegevensmodellen op transparante wijze gebruikt met gegevens die afkomstig zijn uit draaitabellen en draaigrafieken. Een gegevensmodel ziet eruit als een verzameling tabellen in een lijst met velden en meestal werkt u ermee via de lijst met draaitabelvelden en merkt u mogelijk niet dat het model daar wordt weergegeven.

Voordat u met het gegevensmodel kunt gaan werken, moet u enkele gegevens ophalen. Daarvoor gebruiken we de Power Query Get & Transform-ervaring, zodat je misschien een video terug kunt bekijken of onze handleiding over Get & Transform en Power Pivot kunt volgen. Uw gegevens moeten in tabellen staan (niet alleen in celbereiken), zodat deze correct kunnen worden geladen en gerelateerd.

Vereisten

Waar is PowerPivot?

Excel voor Microsoft 365 - Power Pivot is opgenomen in het lint.

Waar is Get & Transform (Power Query)?

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.

Maak een nieuwe werkmap of open een werkmap die de gegevens niet bevat.
Selecteer op het lint in Excel voor Microsoft 365 het tabblad Gegevens. Selecteer in de sectie Gegevens ophalen & transformeren de optie Gegevens ophalen om gegevens te importeren uit een onbeperkt aantal externe gegevensbronnen, zoals een tekstbestand, Excel-werkmap, website, Microsoft Access SQL Server of een andere relationele database die meerdere gerelateerde tabellen bevat.
In Excel wordt u gevraagd een of meer tabellen te selecteren. Als u meerdere tabellen uit dezelfde gegevensbron wilt ophalen, schakelt u het vakje Meerdere items selecteren in.
1. Selecteer Transformeren. Wanneer u meerdere tabellen selecteert, wordt er automatisch een gegevensmodel voor u gemaakt. Zie voor meer informatie: Een query maken, laden of bewerken in Excel (Power Query).
  
  Opmerking
  
  Voor deze voorbeelden gebruiken we een Excel-werkmap met fictieve studentgegevens over klassen en cijfers. U kunt ons voorbeeldwerkboek Student Data Model downloaden en meedoen. U kunt ook een versie met een voltooid gegevensmodel downloaden.
U hebt nu een gegevensmodel dat alle geïmporteerde tabellen bevat, en deze worden weergegeven in de lijst met draaitabelvelden.

Opmerking

Er wordt impliciet een model gemaakt wanneer u twee of meer tabellen tegelijk importeert in Excel.
Er worden expliciet modellen gemaakt wanneer u de Power Pivot-invoegtoepassing gebruikt om gegevens te importeren. In de invoegtoepassing wordt het model voorgesteld in een indeling met tabbladen die vergelijkbaar is met Excel, waarbij elk tabblad gegevens in tabelvorm bevat. Zie Gegevens ophalen met de Power Pivot-invoegtoepassing voor de basisbeginselen van het importeren van gegevens uit 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 wilt gebruiken, zoals gefilterde gegevenssets, berekende kolommen, berekende velden, KPI's en hiërarchieën.
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 voor een nadere uitleg de zelfstudie: Gegevens importeren in Excel en een gegevensmodel maken.

Tip

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

Relaties tussen tabellen maken

De volgende stap is het maken van relaties tussen de tabellen, zodat u gegevens uit elke tabel kunt halen. Elke tabel moet een primaire sleutel of unieke veld-id hebben, zoals studentnummer 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 Beheren van Power Pivot>.
Selecteer op het tabblad Startde diagramweergave.
Al uw geïmporteerde tabellen worden weergegeven en u wilt misschien even de tijd nemen om het formaat 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 studententabellen:

We hebben de volgende koppelingen gemaakt:
- tbl_Students | Studentnummer tbl_Grades > | Student-id
  Sleep met andere woorden het veld Studentnummer uit de tabel Studenten naar het veld Studentnummer in de tabel Cijfers.
- tbl_Semesters | Semester-id > tbl_Grades | Semester
- tbl_Classes | Klasnummer > tbl_Grades | Klassenummer
Opmerking
- 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 tussen de tabellen is en dat bepaalt hoe de gegevens in de draaitabellen worden gebruikt. Zie: Relaties tussen tabellen in een gegevensmodel voor meer informatie.
- De verbindingslijnen geven alleen aan dat er een relatie tussen tabellen is. U ziet niet werkelijk welke velden aan elkaar zijn gekoppeld. Als u de koppelingen wilt zien, gaat u naar Power Pivot>Ontwerprelaties>>beherenRelaties>beheren. In Excel gaat u naar Gegevensrelaties>.

Een draaitabel of draaigrafiek maken met behulp van een gegevensmodel

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 Pivot naar Beheren.
Selecteer Draaitabel op het tabblad Start.
Selecteer waar u de draaitabel wilt plaatsen: een nieuw werkblad of de huidige locatie.
Klik op OK en Excel voegt een lege draaitabel toe met het deelvenster Lijst met velden aan de rechterkant.

Maak vervolgens een draaitabel of draaigrafiek. Als u al relaties tussen de tabellen hebt gemaakt, kunt u elk veld hiervan gebruiken in de draaitabel. We hebben al relaties gemaakt in de voorbeeldwerkmap Student Data Model.

Bestaande, niet-gerelateerde gegevens toevoegen aan een gegevensmodel

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

Begin met het selecteren van 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 werken het beste.
Voeg uw gegevens op een van de volgende manieren toe:
Klik op Power Pivot>:Toevoegen aan gegevensmodel.
Klik op Draaitabel invoegen> en schakel Deze gegevens toevoegen aan het gegevensmodel in het dialoogvenster Draaitabel maken 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 Excel-werkblad. Maar u kunt rijen toevoegen door de brongegevens te kopiëren en te plakken, of door de brongegevens bij te werken en het Power Pivot-model te vernieuwen.