Excel-gegevensmodel (inhoudsopgave)
- Inleiding tot gegevensmodel in Excel
- Hoe maak je een datamodel in Excel?
Inleiding tot gegevensmodel in Excel
Met de gegevensmodelfunctie van Excel kunt u eenvoudig relaties opbouwen tussen eenvoudige rapportage en hun achtergrondgegevenssets. Het maakt gegevensanalyse veel eenvoudiger. Het maakt de integratie van gegevens uit een overvloed aan tabellen verspreid over meerdere werkbladen mogelijk door eenvoudig relaties tussen overeenkomende kolommen op te bouwen. Het werkt volledig achter de schermen en vereenvoudigt de rapportagefuncties zoals de draaitabel etc. aanzienlijk.
In ons artikel zullen we proberen te laten zien hoe u een draaitabel kunt maken van twee tabellen met behulp van de functie Datamodel, waardoor een relatie tussen twee tabelobjecten tot stand wordt gebracht en daardoor een draaitabel wordt gemaakt.
Hoe maak je een datamodel in Excel?
Laten we begrijpen hoe we het gegevensmodel in Excel kunnen maken met een paar voorbeelden.
U kunt dit datamodel Excel-sjabloon hier downloaden - Datamodel Excel-sjabloonVoorbeeld 1
- We hebben een lijst met producten en we hebben een plankcode voor elk product. We hebben een tabel nodig waarin we de beschrijving van de stelling samen met de stellingcodes hebben. Dus hoe integreren we de plankbeschrijvingen tegen elke plankcode? Misschien zouden velen van ons hier hun toevlucht nemen tot het gebruik van VLOOKUP, maar we zullen helemaal de noodzaak verwijderen om VLOOKUP hier te gebruiken met behulp van het Excel-gegevensmodel.
- De tabel links is de gegevenstabel en de tabel rechts is de opzoektabel. Zoals we aan de gegevens kunnen zien, is het mogelijk om een relatie te maken op basis van gemeenschappelijke kolommen.
- Nu is het gegevensmodel alleen compatibel met tabelobjecten. Het kan dus soms nodig zijn om gegevenssets naar tabelobjecten te converteren. Volg hiervoor de onderstaande stappen.
- Klik met de linkermuisknop ergens in de gegevensset.
- Klik op het tabblad Invoegen en navigeer naar Tabel in de groep Tabellen of druk op Ctrl + T.
- Verwijder het vinkje of schakel de optie Mijn tabel bevat de koptekst in. In ons voorbeeld heeft het inderdaad een header. Klik OK.
- Hoewel we ons nog steeds op de nieuwe tabel concentreren, moeten we een naam opgeven die zinvol is in het vak Naam (links van de formulebalk).
In ons voorbeeld hebben we de tabel Personeel genoemd.
- Nu moeten we hetzelfde proces ook voor de opzoektabel doen en deze Shelf Code noemen.
Relatie creëren
Dus eerst gaan we naar het tabblad Gegevens en selecteren vervolgens Relaties in de subgroep Gegevenshulpmiddelen. Nadat we in het begin op de optie Relaties hebben geklikt, omdat er geen relatie is, hebben we dus niets.
We klikken eerst op Nieuw om een relatie te maken. We moeten nu de primaire en de opzoektabelnamen uit de vervolgkeuzelijst opgeven en vervolgens ook de kolom vermelden die gebruikelijk is tussen de twee tabellen, zodat we de relatie tussen de twee tabellen kunnen vaststellen, uit de vervolgkeuzelijst van kolommen.
- Nu is de primaire tabel de tabel met de gegevens. Het is de primaire gegevenstabel - Table5. Aan de andere kant is de gerelateerde tabel de tabel met de opzoekgegevens - het is onze opzoektabel ShelfCodesTable. De primaire tabel is degene die wordt geanalyseerd op basis van de opzoektabel die opzoekgegevens bevat die de gerapporteerde gegevens uiteindelijk zinvoller zullen maken.
- De gemeenschappelijke kolom tussen de twee tabellen is dus de kolom Plankcode. Dit is wat we hebben gebruikt om de relatie tussen de twee tabellen vast te stellen. Komend naar de kolommen, is de kolom (vreemd) degene die verwijst naar de gegevenstabel waar dubbele waarden kunnen voorkomen. Aan de andere kant verwijst de gerelateerde kolom (primair) naar de kolom in de opzoektabel waar we unieke waarden hebben. We stellen eenvoudigweg het veld in om waarden op te zoeken uit de opzoektabel in de gegevenstabel.
- Zodra we dit hadden ingesteld, zou Excel een relatie creëren tussen de twee achter de scène. Het integreert de gegevens en maakt een gegevensmodel op basis van de gemeenschappelijke kolom. Dit is niet alleen licht voor de geheugenvereisten, maar ook veel sneller dan het gebruik van VLOOKUP in grote werkmappen. Na het definiëren van het gegevensmodel zou Excel deze objecten behandelen als gegevensmodeltabellen in plaats van een werkbladtabel.
- Om te zien wat Excel heeft gedaan, kunnen we klikken op Gegevensmodellen beheren in Gegevens -> Gegevenshulpmiddelen.
- We kunnen ook de schematische weergave van het gegevensmodel krijgen door de weergave te wijzigen. We klikken op de weergaveoptie. Dit opent de weergave-opties. We zullen dan de diagramweergave selecteren. Dan zullen we de schematische weergave zien, die de twee tabellen toont en de relatie daartussen, dwz de gemeenschappelijke kolom - Plankcode.
- Het bovenstaande diagram toont een één-op-veel-relatie tussen de unieke opzoektabelwaarden en de gegevenstabel met dubbele waarden.
- Nu moeten we een draaitabel maken. Om dat te doen, gaan we naar het tabblad Invoegen en klikken vervolgens op de optie Draaitabel.
In het dialoogvenster Draaitabel maken van de draaitabel selecteren we de bron als "Gebruik het gegevensmodel van deze werkmap".
- Hiermee wordt de draaitabel gemaakt en we kunnen zien dat beide brontabellen beschikbaar zijn in de bronsectie.
- Nu zullen we een draaitabel maken met de telling van elke persoon die items heeft opgeborgen.
- We selecteren Personeel in de sectie Rijen van Tabel 5 (gegevenstabel), gevolgd door Beschrijving (opzoektabel).
- Nu slepen we de plankcode uit tabel 5 naar het gedeelte Waarden.
- Nu voegen we maanden uit tabel 5 toe aan de sectie Rijen.
- Of we kunnen de maanden als een filter toevoegen en toevoegen aan de sectie Filters.
Voorbeeld 2
- We hebben nu de heer Basu die de fabriek Basu Corporation runt. De heer Basu probeert de omzet voor 2019 te schatten op basis van de gegevens van 2018.
- We hebben een tabel waarin we de omzet voor 2018 en de daaropvolgende omzet op verschillende incrementele niveaus hebben.
- We hebben dus de omzet voor 2018 - $ 1, 5 miljoen en de verwachte minimale groei voor het volgende jaar is 12%. De heer Basu wil een tabel waarin de omzet op verschillende incrementele niveaus wordt weergegeven.
- We maken de volgende tabel voor de projecties op verschillende incrementele niveaus voor 2019.
- Nu geven we de eerste omzetrij een verwijzing naar de geschatte minimale omzet voor 2019, dat wil zeggen $ 1, 68 M.
- Na gebruik van de formule wordt het antwoord hieronder weergegeven.
- Nu zullen we de hele tabel selecteren, dwz D2: E12 en dan naar Gegevens -> Voorspelling -> What-If-analyse -> Gegevenstabel gaan.
- Dit opent het dialoogvenster Gegevenstabel. Hier zullen we het minimale incrementpercentage van cel B4 in de kolom Invoercel invoeren. De reden hiervoor is dat onze geraamde geschatte groeipercentages in de tabel op een zuilvormige manier zijn gerangschikt.
- Nadat we op OK hebben geklikt, vult de What-If-analyse de tabel automatisch in met geprojecteerde omzet in de verschillende incrementele percentages.
Voorbeeld 3
- Stel nu dat we hetzelfde scenario als hierboven hebben, behalve dat we nu ook een andere as hebben om te overwegen. Stel dat we naast het weergeven van de verwachte omzet in 2019 op basis van de gegevens van 2018 en de verwachte minimale groeivoet, nu ook de geschatte discontovoet hebben.
- Eerst zullen we een onderstaande tabel hebben.
- Nu zullen we verwijzen naar de verwachte minimuminkomsten voor 2019, dwz cel B5 tot cel D8.
- Nu zullen we de hele tabel selecteren, dwz D8: J18 en dan naar Gegevens -> Voorspelling -> What-If-analyse -> Gegevenstabel gaan.
- Dit opent het dialoogvenster Gegevenstabel. Hier zullen we het minimale incrementpercentage van cel B3 in de kolom Invoercel invoeren. De reden hiervoor is dat onze geraamde geschatte groeipercentages in de tabel op een zuilvormige manier zijn gerangschikt. We zullen nu ook aanvullend het minimum kortingspercentage van cel B4 in de rijinvoercel invoeren. De reden hiervoor is dat onze geprojecteerde kortingspercentages in de tabel rij-gerangschikt zijn.
- Klik OK. Dit zal de What-If-analyse maken om de tabel automatisch te vullen met geprojecteerde inkomsten met de verschillende incrementele percentages volgens de kortingspercentages.
Dingen om te onthouden over het gegevensmodel in Excel
- Nadat de waarden uit de gegevenstabel met succes zijn berekend, werkt een eenvoudige Undo, dwz Ctrl + Z, niet. Het is echter mogelijk om de waarden handmatig uit de tabel te verwijderen.
- Het is niet mogelijk om een enkele cel uit de tabel te verwijderen. Het wordt intern beschreven als een array, dus moeten we alle waarden verwijderen.
- We moeten de rijinvoercel en de kolominvoercel correct selecteren.
- Datatabel, in tegenstelling tot de draaitabel, hoeft niet elke keer te worden vernieuwd.
- Met behulp van het gegevensmodel in Excel kunnen we niet alleen de prestaties verbeteren, maar ook de geheugenvereisten in grote werkbladen vereenvoudigen.
- Gegevensmodellen maken onze analyse ook veel eenvoudiger in vergelijking met het gebruik van een aantal gecompliceerde formules in de hele werkmap.
Aanbevolen artikelen
Dit is een handleiding voor het gegevensmodel in Excel. Hier bespreken we hoe u een gegevensmodel in Excel kunt maken, samen met praktische voorbeelden en een downloadbare Excel-sjabloon. U kunt ook onze andere voorgestelde artikelen doornemen -
- Formulebalk in Excel
- Rasterlijnen afdrukken in Excel
- Kijkvenster in Excel
- Excel-SOMMEN met datums