Wat is het gegevensmodel in Excel?
Datamodel in Excel is een type datatabel waarbij we twee of meer dan twee tabellen met elkaar in relatie hebben via een gemeenschappelijke of meer datareeks, in datamodeltabellen en data uit verschillende andere bladen of bronnen komen samen om een uniek tabel die toegang kan hebben tot de gegevens van alle tabellen.
Uitleg
- Hiermee kunnen gegevens uit meerdere tabellen worden geïntegreerd door relaties te creëren op basis van een gemeenschappelijke kolom.
- Gegevensmodellen worden transparant gebruikt en bieden tabelgegevens die kunnen worden gebruikt in draaitabel in Excel en draaigrafieken in Excel. Het integreert de tabellen, waardoor uitgebreide analyses mogelijk zijn met draaitabellen, Power Pivot en Power View in Excel.
- Met het datamodel kunnen gegevens in het geheugen van Excel worden geladen.
- Het wordt in het geheugen opgeslagen, waar we het niet direct kunnen zien. Vervolgens kan Excel worden geïnstrueerd om gegevens aan elkaar te relateren met behulp van een gemeenschappelijke kolom. Het deel 'Model' van het datamodel verwijst naar hoe alle tabellen zich tot elkaar verhouden.
- Data Model heeft toegang tot alle informatie die het nodig heeft, zelfs als de informatie in meerdere tabellen staat. Nadat het gegevensmodel is gemaakt, heeft Excel de gegevens beschikbaar in het geheugen. Met de gegevens in het geheugen kunnen de gegevens op vele manieren worden geopend.
Voorbeelden
Voorbeeld 1
Als we drie datasets hebben die betrekking hebben op de verkoper: de eerste bevat inkomsteninformatie, de tweede bevat het inkomen van de verkoper en de derde bevat de uitgaven van de verkoper.
Om deze drie datasets met elkaar te verbinden en hier een relatie mee te maken, maken we een Datamodel met de volgende stappen:
- Converteer de datasets naar Table-objecten:
We kunnen geen relatie leggen met gewone datasets. Het gegevensmodel werkt alleen met Excel Tables-objecten. Om dit te doen:
- Stap 1 - Klik ergens in de dataset, klik vervolgens op het tabblad 'Invoegen' en klik vervolgens op 'Tabel' in de groep 'Tabellen'.
- Stap 2 - Schakel de optie 'My Table has headers' in of uit en klik op OK.
- Stap 3 - Terwijl de nieuwe tabel is geselecteerd, voert u de naam van de tabel in bij 'Tabelnaam' in de groep 'Tools'.
- Stap 4 - Nu kunnen we zien dat de eerste dataset is geconverteerd naar 'Table'-object. Bij het herhalen van deze stappen voor de andere twee datasets, zien we dat ze ook worden geconverteerd naar 'Table'-objecten zoals hieronder:
Toevoegen van de 'Tabel'-objecten aan het datamodel: via verbindingen of relaties.
Via verbindingen
- Selecteer een tabel en klik op het tabblad 'Gegevens' en klik vervolgens op 'Verbindingen'.
- In het resulterende dialoogvenster is er een pictogram 'Toevoegen'. Vouw de vervolgkeuzelijst 'Toevoegen' uit en klik op 'Toevoegen aan het gegevensmodel'.
- Klik op 'Tabellen' in het resulterende dialoogvenster en selecteer vervolgens een van de tabellen en klik op 'Openen'.
Als u dit doet, wordt een gegevensmodel van een werkmap gemaakt met één tabel en verschijnt er een dialoogvenster als volgt:
Dus als we deze stappen ook voor de andere twee tabellen herhalen, zal het datamodel nu alle drie de tabellen bevatten.
We kunnen nu zien dat alle drie de tabellen verschijnen in de Workbook Connections.
Via relaties
Creëer de relatie: zodra beide datasets tabelobjecten zijn, kunnen we een relatie tussen beide creëren. Om dit te doen:
- Klik op het tabblad 'Gegevens' en klik vervolgens op 'Relaties'.
- We zullen een leeg dialoogvenster zien omdat er geen huidige verbindingen zijn.
- Klik op 'Nieuw' en een ander dialoogvenster verschijnt.
- Vouw de vervolgkeuzelijsten 'Tabel' en 'Gerelateerde tabel' uit: het dialoogvenster 'Een relatie maken' verschijnt om de tabellen en kolommen te kiezen die voor een relatie moeten worden gebruikt. Selecteer in de uitbreiding van 'Tabellen' de dataset die we op de een of andere manier willen analyseren, en selecteer in 'Related Table' de dataset met opzoekwaarden.
- De opzoektabel in Excel is de kleinere tabel in het geval van een tot veel relaties en bevat geen herhaalde waarden in de gemeenschappelijke kolom. In de uitbreiding van 'Kolom (vreemd)' selecteert u de gemeenschappelijke kolom in de hoofdtabel, in 'Gerelateerde kolom (primair)' selecteert u de gemeenschappelijke kolom in de gerelateerde tabel.
- Met al deze vier instellingen geselecteerd, klikt u op 'OK'. Een dialoogvenster verschijnt als volgt als u op 'OK' klikt.
Als we deze stappen herhalen om andere twee tabellen met elkaar te verbinden: Omzetentabel met Uitgaven-tabel, dan worden ze ook als volgt gerelateerd in het Gegevensmodel:
Excel creëert nu de relatie achter de schermen door gegevens in het gegevensmodel te combineren op basis van een gemeenschappelijke kolom: verkoper-ID (in dit geval).
Voorbeeld 2
Laten we nu zeggen dat we in het bovenstaande voorbeeld een draaitabel willen maken die de tabelobjecten evalueert of analyseert:
- Klik op 'Invoegen' -> 'Draaitabel.'
- Klik in het resulterende dialoogvenster op de optie met de vermelding: 'Gebruik een externe gegevensbron' en klik vervolgens op 'Verbinding kiezen'.
- Klik op 'Tabellen' in het resulterende dialoogvenster en selecteer het werkmapgegevensmodel met drie tabellen en klik op 'Openen'.
- Selecteer de optie 'Nieuw werkblad' op de locatie en klik op 'OK'.
- In het deelvenster Draaitabelvelden worden tabelobjecten weergegeven.
- Nu kunnen wijzigingen in de draaitabel dienovereenkomstig worden aangebracht om de tabelobjecten zoals vereist te analyseren.
Als we in dit geval bijvoorbeeld de totale omzet of omzet voor een bepaalde verkoper willen vinden, wordt er als volgt een draaitabel gemaakt:
Dit helpt enorm bij een model / tabel met een groot aantal observaties.
We kunnen dus zien dat de draaitabel onmiddellijk het gegevensmodel gebruikt (het kiezen door een verbinding te kiezen) in het Excel-geheugen om relaties tussen tabellen weer te geven.
Dingen om te onthouden
- Met behulp van het datamodel kunnen we gegevens uit meerdere tabellen tegelijk analyseren.
- Door relaties te creëren met Data Model, overtreffen we de behoefte aan het gebruik van VLOOKUP-, SUMIF-, INDEX-functie en MATCH-formules, omdat we niet alle kolommen in één tabel hoeven te krijgen.
- Wanneer datasets vanuit externe bronnen in Excel worden geïmporteerd, worden impliciet modellen gemaakt.
- Tabelrelaties kunnen automatisch worden gemaakt als we gerelateerde tabellen importeren die relaties met primaire en externe sleutels hebben.
- Bij het maken van relaties moeten de kolommen die we in tabellen met elkaar verbinden, hetzelfde gegevenstype hebben.
- Met de draaitabellen die met het gegevensmodel zijn gemaakt, kunnen we ook slicers toevoegen en de draaitabellen op elk gewenst veld verdelen.
- Het voordeel van het datamodel ten opzichte van LOOKUP () - functies is dat het aanzienlijk minder geheugen vereist.
- Excel 2013 ondersteunt slechts één op één of één op veel relaties, dwz een van de tabellen mag geen dubbele waarden hebben in de kolom waarnaar we linken.
