Excel-datamodell (innehållsförteckning)

  • Introduktion till datamodell i Excel
  • Hur skapar jag en datamodell i Excel?

Introduktion till datamodell i Excel

Datamodelfunktionen i Excel möjliggör enkel byggnad av förhållanden mellan enkel rapportering och deras bakgrundsdatasatser. Det gör dataanalysen mycket enklare. Det tillåter integration av data från en mängd tabeller spridda över flera kalkylblad genom att helt enkelt bygga relationer mellan matchande kolumner. Det fungerar helt bakom scenen och förenklar rapporteringsfunktioner som PivotTable etc. mycket.

I vår artikel ska vi försöka visa hur man skapar en pivottabell från två tabeller genom att använda funktionen Datamodell och därigenom upprätta en relation mellan två tabellobjekt och därigenom skapa en pivottabell.

Hur skapar jag en datamodell i Excel?

Låt oss förstå hur du skapar datamodellen i Excel med några exempel.

Du kan ladda ner denna Datamodell Excel-mall här - Datamodell Excel-mall

Exempel 1

  • Vi har en lista över produkter och vi har en hyllkod för varje produkt. Vi behöver en tabell där vi har hyllbeskrivningen tillsammans med hyllkoderna. Så hur tar vi med hyllbeskrivningarna mot varje hyllkod? Kanske skulle många av oss använda oss av att använda VLOOKUP här, men vi ska helt ta bort behovet av att använda VLOOKUP här med hjälp av Excel Data Model.

  • Tabellen till vänster är datatabellen och tabellen till höger är uppslagstabellen. Som vi kan se från data är det möjligt att skapa en relation baserad på vanliga kolumner.

  • Nu är datamodellen bara kompatibel med tabellobjekt. Så det kan ibland vara nödvändigt att konvertera datauppsättningar till tabellobjekt. För att göra det följer du stegen nedan.
  1. Vänsterklicka var som helst i datauppsättningen.
  2. Klicka på Infoga-fliken och navigera till Tabell i gruppen Tabeller eller tryck helt enkelt på Ctrl + T.
  3. Avmarkera eller markera att Mitt bord har alternativet Header. I vårt exempel har den verkligen en rubrik. Klicka på OK.
  4. Medan vi fortfarande fokuserar på det nya bordet, måste vi ange ett namn som är meningsfullt i rutan Namn (till vänster om formelfältet).

I vårt exempel har vi namngivit tabellen Personal.

  • Nu måste vi också göra samma process för uppslagstabellen och namnge den Hylla kod.

Skapa ett förhållande

Så för det första ska vi gå till fliken Data och sedan välja Relationer i undergruppen Data Tools. När vi har klickat på alternativet Relationer, i början eftersom det inte finns någon relation så kommer vi inte ha något.

Vi klickar först på Nytt för att skapa en relation. Vi kommer nu att behöva ange de primära och uppslagstabellnamnen från listrutan och sedan också nämna kolumnen som är gemensam mellan de två tabellerna så att vi kan fastställa förhållandet mellan de två tabellerna, från listrutan. av kolumner.

  • Nu är den primära tabellen den tabell som innehåller data. Det är den primära datatabellen - Tabell5. Å andra sidan är den relaterade tabellen den tabell som har uppslagningsdata - det är vår uppslagstabell ShelfCodesTable. Den primära tabellen är den som analyseras baserat på uppslagstabellen som innehåller uppslagningsdata som gör den rapporterade informationen i slutändan mer meningsfull.

  • Så den gemensamma kolumnen mellan de två tabellerna är kolumnen Hyllkod. Detta är vad vi har använt för att fastställa förhållandet mellan de två tabellerna. När du kommer till kolumnerna är kolumnen (främmande) den som hänvisar till datatabellen där det kan finnas duplikatvärden. Å andra sidan hänvisar den relaterade kolumnen (primär) till kolumnen i uppslagstabellen där vi har unika värden. Vi ställer helt enkelt in fältet för att söka upp värden från uppslagstabellen i datatabellen.
  • När vi skapade detta skulle Excel skapa en relation mellan de två bakom scenen. Den integrerar data och skapar en datamodell baserad på den gemensamma kolumnen. Detta är inte bara lätt på minneskraven utan också mycket snabbare än att använda VLOOKUP i stora arbetsböcker. Efter att ha definierat datamodellen, skulle Excel behandla dessa objekt som datamodellstabeller istället för en kalkyltabell.
  • För att se vad Excel har gjort kan vi klicka på Hantera datamodeller i data -> Dataverktyg.

  • Vi kan också få den schematiska representationen av datamodellen genom att ändra vyn. Vi klickar på alternativet Visa. Detta öppnar visningsalternativen. Vi väljer sedan Diagramvy. Då ser vi den schematiska representationen, som visar de två tabellerna och förhållandet mellan dem, dvs den gemensamma kolumnen - Hyllkod.

  • Diagrammet ovan visar ett en-till-många-förhållande mellan de unika uppslagstabellvärdena och datatabellen med duplicerade värden.
  • Nu måste vi skapa en pivottabell. För att göra det går vi till fliken Infoga och klickar sedan på alternativet Pivot Table.

I dialogrutan Skapa pivottabell i pivottabellen kommer vi att välja källan som ”Använd den här arbetsboks datamodellen”.

  • Detta skapar pivottabellen och vi kan se att båda källtabellerna är tillgängliga i källsektionen.

  • Nu ska vi skapa en pivottabell som visar antalet för varje person som har hyllade föremål.

  • Vi väljer personal i raderna från tabell 5 (datatabell), följt av beskrivning (uppslagstabell).

  • Nu ska vi dra hyllkoden från tabell 5 till avsnittet Värden.

  • Nu ska vi lägga till månader från tabell 5 till raderna.

  • Eller så kan vi lägga till månaderna som ett filter och lägga till dem i avsnittet Filter.

Exempel 2

  • Vi har nu Mr. Basu som driver en fabrik som heter Basu Corporation. Basu försöker uppskatta intäkterna för 2019 baserat på uppgifterna från 2018.
  • Vi har en tabell där vi har intäkterna för 2018 och de efterföljande intäkterna på olika inkrementella nivåer.

  • Så vi har intäkterna för 2018 - 1, 5 MUSD och den lägsta tillväxt som förväntas året efter är 12%. Basu vill ha en tabell som visar intäkterna på olika inkrementella nivåer.
  • Vi kommer att skapa följande tabell för prognoserna på olika inkrementella nivåer för 2019.

  • Nu ska vi ge den första intäktsraden en hänvisning till den beräknade minimiinkomsten för 2019, dvs. 1, 68 MUSD.

  • Efter användning av formeln visas svaret nedan.

  • Nu ska vi välja hela tabellen dvs. D2: E12 och sedan gå till Data -> Prognos -> Vad-om-analys -> Datatabell.

  • Detta öppnar dialogrutan Datatabell. Här ska vi ange minsta inkrementsprocent från cell B4 i kolumninmatningscellen. Anledningen till detta är att våra beräknade tillväxtprocent i tabellen är ordnade på ett kolumnigt sätt.

  • När vi klickar på OK kommer What-If-analysen automatiskt att fylla i tabellen med beräknade intäkter med olika stegvisa procentsatser.

Exempel 3

  • Anta nu att vi har samma scenario som ovan, förutom att vi nu också har en annan axel att tänka på. Anta att förutom att de beräknade intäkterna ska visas under 2019 baserat på uppgifterna från 2018 och den förväntade lägsta tillväxttakten har vi nu också den uppskattade diskonteringsräntan.

  • Först ska vi ha en tabell som visas nedan.

  • Nu kommer vi att hänvisa till den lägsta beräknade intäkten för 2019, dvs. cell B5 till cell D8.

  • Nu ska vi välja hela tabellen dvs. D8: J18 och sedan gå till Data -> Prognos -> Vad-om-analys -> Datatabell.

  • Detta öppnar dialogrutan Datatabell. Här ska vi ange minsta inkrementsprocent från cell B3 i kolumninmatningscellen. Anledningen till detta är att våra beräknade tillväxtprocent i tabellen är ordnade på ett kolumnigt sätt. Vi ska nu dessutom ange lägsta rabattprocent från cell B4 i radinmatningscellen. Anledningen till detta är att våra beräknade rabattprocent i tabellen är ordnade på rad.

  • Klicka på OK. Detta kommer att göra vad-om-analysen för att automatiskt fylla tabellen med beräknade intäkter med olika stegvisa procentsatser enligt rabattprocenten.

Saker att komma ihåg om datamodellen i Excel

  • Vid framgångsrik beräkning av värdena från datatabellen fungerar inte en enkel Ångra, dvs Ctrl + Z. Det är dock möjligt att manuellt radera värdena från tabellen.
  • Det är inte möjligt att ta bort en enda cell från tabellen. Det beskrivs som en matris internt i Excel, därför måste vi ta bort alla värden.
  • Vi måste ordentligt välja radinmatningscell och kolumninmatningscell.
  • Datatabellen, till skillnad från pivottabellen, behöver inte uppdateras varje gång.
  • Med hjälp av datamodellen i Excel kan vi inte bara förbättra prestandan utan också göra det enkelt med minneskraven i stora kalkylblad.
  • Datamodeller gör vår analys också mycket enklare jämfört med att använda ett antal komplicerade formler över hela arbetsboken.

Rekommenderade artiklar

Detta är en guide till datamodell i Excel. Här diskuterar vi hur du skapar datamodell i Excel tillsammans med praktiska exempel och nedladdningsbar Excel-mall. Du kan också gå igenom våra andra föreslagna artiklar -

  1. Formelrad i Excel
  2. Skriv ut rutnät i Excel
  3. Titta på Fönster i Excel
  4. Excel SUMIFS med datum

Kategori:

Excel-tips