Datamodell i Excel med Pivottabell

I följande exempel skall vi bygga en enkel datamodell. En datamodell kan vi här beskriva som en pivottabell som är skapade av flera relaterade tabeller. Genom att bygga en datamodell så får vi tillgång till all data via en pivottabell.

Vår datamodell består av 3 dynamiska och namngivna tabeller som i bilden nedan. Du kan även ladda ner exempelfil under inläggets rubrik..

    1 Tbl_Försäljning - Vår Referenstabell, här finns information om varje order. Den består av antalet sålda enheter, Antal. Ett Produkt_ID  och ett Kund_ID.

    2 Tbl_Produkter – En uppslagstabell där det finns information om varje produkt. Raden Produkt_ID har unika värden och är vår nyckelkolumn som vi kan skapa en relation till fältet Produkt_ID i tabellen Tbl_Försäljning.

    3 Tbl_Kunder– En uppslagstabell där det finns information om varje kund. Raden Kund_ID har unika värden och är vår nyckelkolumn som vi kan skapa en relation till fältet Kund_ID i tabellen Tbl_Försäljning.

Vi skall nu skapa en pivottabell som visar försäljningen av Antal (värden) av vilket Produkt (rader) och till vilken Kund som det sålts till (kolumner).

Börja med att infoga en pivottabell ifrån Tbl_Försäljning. Ställ dig i Tbl_Försäljning och under fliken Infoga välj Pivottabell.

Obs! Se till att bocka för Lägg till dessa data i datamodellen som i bilden ovan. Klicka sedan OK. Detta är det viktiga steget.

Du får nu upp din pivottabell och med pivottabellfältlista längst ut till höger som i bilden nedan. I bilden nedan kan du se våra tre tabeller, Tbl_Försäljning  Tbl_Produkter och Tbl_Kunder. När man först öppnar pivottabellen så ser man endast tabellen Tbl_Försäljinng eftersom det var den vi infogade vår pivottabell ifrån. Högst upp i rutan finns knapparna Aktiv och Alla. I bilden nedan är Alla fetmarkerad eftersom vi klickat på den. När vi står under alla så ser vi samtliga dynamiska tabeller i vårat dokument och kan lägga till data ifrån dessa till vår pivottabell.

För att kunna bygga en datamodell så måste samtliga tabeller som skall ingå vara formaterade som dynamiska tabeller. När vi infogar en pivottabell så kommer vi ihåg att bocka för Lägg till dessa data i datamodellen. Sedan har vi tillgång till all data och sedan behöver vi enbart skap relation mellan tabellerna.

Skapa relationer

Börja med att dra in fältet Antal ifrån Tbl_Försäljning och lägg det under värden.

Dra sedan ner fältet Produkt Namn ifrån tabellen Tbl_Produkter och lägg det under Rader.

Dra sedan ner fältet Namn ifrån tabellen Tbl_Kund och lägg det under Kolumner.

Din pivottabellfältlista skall nu se ut som i bilden nedan. Dina siffror i pivottabellen visar samma sak för alla värden. Notera det gula fältet där det står ”Relation mellan tabeller kan behövas” som kommit upp då vi la in fälten Namn och Produkt Namn. Anledningen till att det blir samma överallt är pga. att vi inte har några relationer mellan tabellerna.

Vi skall nu gå igenom hur man kan skapa relationer mellan tabellerna på två olika sätt. Man kan här klicka på SKAPA… för att göra dom manuellt men man kan även göra dom på ett lite mer användarvänligt sätt genom att göra det via PowerPivot fönstret.

1 Skapa relationer via dialogrutan Skapa relationer.

Klicka på den gula knappen SKAPA… i pivottabellfältlistan.  Du får nu upp dialogrutan Skapa relation som i bilden nedan.

Här skall du matcha de kolumner som binder ihop varandra. Man väljer först en tabell och sedan en kolumn med värden som skall matcha värdena i en kolumn i nästa tabell. I den andra raden väljer vi den tabell som datan skall kopplas med. Här är det viktigt att tänka på att den kolumn som vi slutligen väljer endast får ha unika värden av t.ex. Kund_ID. Jämfört med när vi valde Kund_ID ifrån Tbl_Försäljning där varje Kund_ID kan förekomma flera gånger.

Fyll i som i ovan och klicka på OK. Repetera sedan steget och koppla ihop Produkt_ID.

2 Klicka och dra realtioner i vår datamodell

Under fliken Data klicka på ikonen Hantera datamodell som i bilden nedan.

Beroende på vilken version och vilka tillägg du har aktiverade på den version av Excel så kan du eventuellt få upp meddelandet enligt nedan. Klicka Slå på för att fortsätta.

Du får nu upp ett nytt fönster som heter PowerPivot fönstret som är väldigt bra för att kunna hantera datamodeller i Excel. När du kommer in i det nya fönstret så befinner du dig i något som kallas för Datavyn. Här kan du se dina olika tabeller som finns tillgängliga i datamodellen. Klicka på knappen Diagramvy i fliken Start som i bilden nedan.

Du kan nu se dina tabeller som rutor i diamgramvyn och du kan flytta runt dom och ändra på storlek. Du kan härifrån även enkelt skapa relationer.  Du klickar på ett fält och drar det till motsvarande fält i en annan tabell som du vill länka till. Nedan så skapar vi en raltion mellan Kund_ID.

När du gjort dina relationer bör det se ut som i bilden nedan. Notera att det är en etta ”1” och en stjärna ”*” vid Tbl_Försäljing. Detta är för att förtydliga att det är en (1) till många (*) realtion. Där vi har våra många i referenstabellen TBl_Försäljing.

Du kan nu stänga ner PowerPivot fönstret eller bara gå tillbaka till arbetsboken där pivottabellen finns så kommer siffrorna stämma som i bilden nedan.

Sammanfattning

När man har flera tabeller att jobba med och man vill hämta in data från en till en annan så har man traditionellt sätt använt sig av formeln LETARAD(). Med den formel hämtar man över ett fält i taget och samlar allt i en tabell. När man bygger en datamodell så gör man det i tre steg.

1 Man laddar man först in sina tabeller genom att göra dom till dynamiska tabeller.

2 Sedan skapar man en modell när man infogar en pivottabeller genom att bocka för Lägg till dessa data i datamodellen.

3 Sedan skapar man sina realtioner.