Hoe relaties tussen meerdere tabellen te creëren met behulp van het gegevensmodel in Excel

Hoe relaties tussen meerdere tabellen te creëren met behulp van het gegevensmodel in Excel

Excel is een krachtig hulpmiddel voor gegevensanalyse en daaropvolgende automatisering bij het verwerken van grote gegevenssets. U zou veel tijd kunnen besteden aan het analyseren van tonnen gegevens met behulp van VERT.ZOEKEN, INDEX-MATCH, SUMIF, enz.





Dankzij het Excel Data Model kunt u kostbare tijd besparen door automatische datarapportages. Ontdek in het volgende gedeelte hoe u gemakkelijk een relatie tussen twee tabellen kunt toewijzen met behulp van het gegevensmodel en een illustratie van een dergelijke relatie in een draaitabel.





De basisvereisten

U hebt Power Pivot en Power Query (Get & Transform) nodig om verschillende taken uit te voeren tijdens het maken Excel Gegevensmodel. Hier leest u hoe u deze functies in uw Excel-werkmap kunt krijgen:





Hoe u Power Pivot kunt krijgen

1. Excel 2010: U moet de Power Pivot-invoegtoepassing downloaden van Microsoft en installeer het vervolgens voor uw Excel-programma op uw computer.

2. Excel 2013: Office Professional Plus-editie van Excel 2013 bevat Power Pivot. Maar u moet het voor het eerste gebruik activeren. Hier is hoe:



  1. Klik op Bestand op de Lintje van een Excel-werkmap.
  2. Klik dan op Opties openen Excel-opties .
  3. Klik nu op Invoegtoepassingen .
  4. Selecteer COM-invoegtoepassingen door te klikken op het vervolgkeuzemenu van de Beheren doos.
  5. Klik op Gaan en selecteer vervolgens het selectievakje voor Microsoft Power Pivot voor Excel .

3. Excel 2016 en later: U vindt het Power Pivot-menu op de Lintje .

Verwant: Het tabblad Ontwikkelaars toevoegen aan het lint in Microsoft Word en Excel





Power Query ophalen (ophalen en transformeren)

1. Excel 2010: U kunt de Power Query-invoegtoepassing downloaden van Microsoft . Na installatie, Power-query zal verschijnen op de Lintje .

2. Excel 2013: U moet Power Query activeren door dezelfde stappen te volgen die u zojuist hebt uitgevoerd om Power Pivot functioneel te maken in Excel 2013.





3. Excel 2016 en later: U kunt Power Query (Get & Transform) vinden door naar de Gegevens tabblad in Excel Lintje .

Gegevensmodel maken door gegevens in de Excel-werkmap te importeren

Voor deze zelfstudie kunt u vooraf opgemaakte voorbeeldgegevens van Microsoft krijgen:

Downloaden : Voorbeeld student gegevens (alleen gegevens) | Voorbeeld student gegevens (compleet model)

U kunt een database met meerdere gerelateerde tabellen importeren uit vele bronnen, zoals Excel-werkmappen, Microsoft Access, websites, SQL Server, enz. Vervolgens moet u de gegevensset opmaken zodat Excel deze kan gebruiken. Dit zijn de stappen die u kunt proberen:

1. Klik in Excel 2016 en latere edities op de Gegevens tabblad en selecteer Nieuwe vraag .

2. U vindt verschillende manieren om gegevens uit externe of interne bronnen te importeren. Kiezen degene die bij je past.

3. Als u de Excel 2013-editie gebruikt, klikt u op Power-query op de Lintje en selecteer vervolgens Externe gegevens ophalen om gegevens voor import te kiezen.

4. Je ziet de Navigator box waar u moet kiezen welke tabellen u moet importeren. Klik op het selectievakje voor: Selecteer meerdere items om meerdere tabellen te kiezen om te importeren.

5. Klik op Laden om het importproces te voltooien.

6. Excel maakt met behulp van deze tabellen een gegevensmodel voor u. U kunt de kolomkoppen van de tabel zien in de Draaitabelvelden lijsten.

U kunt ook Power Pivot-functies gebruiken, zoals berekende kolommen, KPI's, hiërarchieën, berekende velden en gefilterde gegevenssets uit Excel Data Model. Voor dit doel moet u een gegevensmodel genereren op basis van een enkele tabel. U kunt deze stappen proberen:

1. Formatteer uw gegevens in een tabelmodel door alle cellen met gegevens te selecteren en klik vervolgens op Ctrl+T .

2. Selecteer nu de hele tabel en klik vervolgens op de Power Pivot tabblad op de Lintje .

3. Van de Tafels sectie, klik op Toevoegen aan gegevensmodel .

Excel maakt tabelrelaties tussen gerelateerde gegevens uit het gegevensmodel. Hiervoor moeten er primaire en refererende sleutelrelaties zijn binnen de geïmporteerde tabellen.

Excel gebruikt de relatiegegevens uit de geïmporteerde tabel als basis om verbindingen tussen de tabellen in een gegevensmodel te genereren.

Verwant: Een wat-als-analyse maken in Microsoft Excel

Bouw relaties op tussen de tabellen in het gegevensmodel

Nu u een gegevensmodel in uw Excel-werkmap hebt, moet u relaties tussen de tabellen definiëren om zinvolle rapporten te maken. U moet aan elke tabel een unieke veld-ID of primaire sleutel toewijzen, zoals semester-ID, klasnummer, student-ID, enz.

Met de functie Diagramweergave van Power Pivot kunt u die velden slepen en neerzetten om een ​​relatie op te bouwen. Volg deze stappen om tabelkoppelingen te maken in Excel Data Model:

1. Op de Lintje van de Excel-werkmap, klik op de Power Pivot menu.

2. Klik nu op Beheren in de Gegevensmodel sectie. Je ziet de Power Pivot editor zoals hieronder weergegeven:

3. Klik op de Diagramweergave knop in de Weergave gedeelte van de Power Pivot Huis tabblad. U ziet tabelkolomkoppen gegroepeerd op basis van de tabelnaam.

4. U kunt nu de unieke veld-ID van de ene tabel naar de andere slepen en neerzetten. Hieronder volgt het relatieschema tussen de vier tabellen van het Excel-gegevensmodel:

Hieronder wordt de koppeling tussen tabellen beschreven:

  • Tafel Studenten | Student-ID naar cijferlijst | Student-ID
  • Tabel semesters | Semester-ID naar tabelcijfers | Semester
  • Tafelklassen | Klasse Nummer naar tabel Cijfers | Klasse-ID

5. U kunt relaties creëren door een paar unieke waardekolommen te kiezen. Als er duplicaten zijn, ziet u de volgende foutmelding:

6. Je zult het merken Ster (*) aan de ene kant en Een 1) aan de andere kant in de diagramweergave van relaties. Het definieert dat er een een-op-veel-relatie bestaat tussen de tabellen.

7. Klik in de Power Pivot-editor op de Ontwerp tabblad en selecteer vervolgens Relaties beheren om te weten welke velden de verbindingen maken.

Een draaitabel genereren met het Excel-gegevensmodel

U kunt nu een draaitabel of draaigrafiek maken om uw gegevens uit het Excel-gegevensmodel te visualiseren. Een Excel-werkmap kan slechts één gegevensmodel bevatten, maar u kunt de tabellen blijven bijwerken.

Gerelateerd: Wat is datamining en is het illegaal?

Omdat gegevens in de loop van de tijd veranderen, kunt u hetzelfde model blijven gebruiken en tijd besparen wanneer u met dezelfde gegevensset werkt. U zult meer tijd besparen wanneer u aan gegevens in duizenden rijen en kolommen werkt. Volg deze stappen om een ​​op draaitabel gebaseerd rapport te maken:

1. Klik in de Power Pivot-editor op de Huis tabblad.

2. Op de Lintje , Klik op Draaitabel .

3. Kiezen een tussen Nieuw werkblad of Bestaand werkblad.

windows xp gratis volledige versie downloaden

4. Selecteer Oke . Excel voegt een toe Draaitabel dat zal de . tonen Lijst met velden paneel aan de rechterkant.

Hieronder volgt een holistische weergave van een draaitabel die is gemaakt met behulp van het Excel-gegevensmodel voor de voorbeeldgegevens van studenten die in deze zelfstudie worden gebruikt. U kunt ook professionele draaitabellen of grafieken maken van big data met behulp van de Excel Data Model-tool.

Transformeer complexe gegevenssets in eenvoudige rapporten met behulp van het gegevensmodel van Excel

Het Excel-gegevensmodel maakt gebruik van de voordelen van het creëren van relaties tussen tabellen om zinvolle draaitabellen of grafieken te maken voor gegevensrapportagedoeleinden.

U kunt de bestaande werkmap continu bijwerken en rapporten publiceren over bijgewerkte gegevens. U hoeft geen formules te bewerken of tijd te investeren in het scrollen door duizenden kolommen en rijen telkens wanneer de brongegevens worden bijgewerkt.

Deel Deel Tweeten E-mail Een draaitabel maken in Excel

Leer hoe u draaitabellen in Excel kunt maken en hoe u deze kunt gebruiken om de informatie te tekenen die u wilt zien.

Lees volgende
Gerelateerde onderwerpen
  • productiviteit
  • Spreadsheettips
  • Microsoft Excel
  • Microsoft Office-tips
  • Gegevensanalyse
Over de auteur Tamal Das(100 artikelen gepubliceerd)

Tamal is een freelance schrijver bij MakeUseOf. Nadat hij in zijn vorige baan bij een IT-adviesbureau aanzienlijke ervaring had opgedaan in technologie, financiën en bedrijfsprocessen, nam hij 3 jaar geleden het schrijven over als een voltijds beroep. Hoewel hij niet schrijft over productiviteit en het laatste technische nieuws, speelt hij graag Splinter Cell en bingewatcht hij Netflix/ Prime Video.

Meer van Tamal Das

Abonneer op onze nieuwsbrief

Word lid van onze nieuwsbrief voor technische tips, recensies, gratis e-boeken en exclusieve deals!

Klik hier om je te abonneren