Hur man skapar relationer mellan flera tabeller med hjälp av datamodell i Excel

Excel är ett kraftfullt verktyg för dataanalys och efterföljande automatisering vid hantering av stora datamängder. Du kan spendera mycket tid på att analysera massor av data med VLOOKUP, INDEX-MATCH, SUMIF, etc.

Tack vare Excel -datamodellen kan du spara värdefull tid genom automatiska datarapporter. Ta reda på hur enkelt du kan tilldela en relation mellan två tabeller med hjälp av datamodellen och en illustration av en sådan relation i en pivottabell i följande avsnitt.

De grundläggande kraven

Du behöver Power Pivot och Power Query (Get & Transform) för att utföra flera uppgifter medan du skapar Excel Datamodell. Så här kan du få dessa funktioner i din Excel -arbetsbok:

Hur man får Power Pivot

1. Excel 2010: Du måste ladda ner Power Pivot-tillägget från Microsoft och sedan installera det för ditt Excel -program på din dator.

2. Excel 2013: Office Professional Plus -utgåvan av Excel 2013 innehåller Power Pivot. Men du måste aktivera den före första användning. Så här gör du:

Klicka på Fil på Band i en Excel -arbetsbok.
Klicka sedan på alternativ att öppna Excel -alternativ .
Klicka nu på Tillägg .
Välj COM-tillägg genom att klicka på rullgardinsmenyn i Hantera låda.
Klicka på Gå och markera sedan kryssrutan för Microsoft Power Pivot för Excel .

3. Excel 2016 och senare: Du hittar Power Pivot -menyn på Band .

Relaterad: Så här lägger du till fliken Utvecklare i menyfliksområdet i Microsoft Word och Excel

Så här får du Power Query (Get & Transform)

1. Excel 2010: Du kan ladda ner tillägget Power Query från Microsoft . Efter installationen, Strömfråga kommer att dyka upp på Band .

2. Excel 2013: Du måste aktivera Power Query genom att följa samma steg som du just gjorde för att göra Power Pivot funktionell i Excel 2013.

3. Excel 2016 och senare: Du hittar Power Query (Get & Transform) genom att gå till Data flik i Excel Band .

Skapa datamodell genom att importera data till Excel -arbetsboken

För den här självstudien kan du få förformaterade exempeldata från Microsoft:

Ladda ner : Provstudent data (endast data) | Provstudent data (komplett modell)

Du kan importera en databas med flera relaterade tabeller från många källor som Excel -arbetsböcker, Microsoft Access, webbplatser, SQL Server, etc. Sedan måste du formatera datauppsättningen så att Excel kan använda den. Här är stegen du kan prova:

1. I Excel 2016 och senare versioner klickar du på Data fliken och välj Ny fråga .

2. Du hittar flera sätt att importera data från externa eller interna källor. Välja den som passar dig.

3. Om du använder Excel 2013 -utgåvan, klicka på Strömfråga på Band och välj sedan Skaffa externa data att välja data för import.

4. Du kommer att se Navigatör rutan där du måste välja vilka tabeller du behöver importera. Klicka på kryssrutan för Välj flera objekt att välja flera tabeller för import.

5. Klicka på Ladda för att slutföra importprocessen.

6. Excel skapar en datamodell för dig med hjälp av dessa tabeller. Du kan se tabellkolumnrubrikerna i Pivottabellfält listor.

Du kan också använda Power Pivot -funktioner som beräknade kolumner, KPI: er, hierarkier, beräknade fält och filtrerade datamängder från Excel -datamodell. För detta ändamål måste du generera datamodell från en enda tabell. Du kan prova dessa steg:

1. Formatera dina data i en tabellmodell genom att markera alla celler som innehåller data och klicka sedan på Ctrl+T .

2. Välj nu hela tabellen och klicka sedan på Power Pivot fliken på Band .

3. Från Tabeller klicka på Lägg till i datamodell .

Excel skapar tabellrelationer mellan relaterade data från datamodellen. För detta bör det finnas primära och främmande nyckelrelationer inom de importerade tabellerna.

Excel använder relationsinformationen från den importerade tabellen som grund för att skapa anslutningar mellan tabellerna i en datamodell.

Relaterad: Hur man skapar en vad-om-analys i Microsoft Excel

Skapa relationer mellan tabellerna i datamodellen

Nu när du har en datamodell i din Excel -arbetsbok måste du definiera relationer mellan tabellerna för att skapa meningsfulla rapporter. Du måste tilldela en unik fältidentifierare eller primärnyckel till varje tabell, till exempel termins -ID, klassnummer, student -ID, etc.

Funktionen Diagram View i Power Pivot låter dig dra och släppa dessa fält för att skapa en relation. Följ dessa steg för att skapa tabellänkar i Excel -datamodell:

1. På Band i Excel -arbetsboken, klicka på Power Pivot meny.

2. Klicka nu på Hantera i Datamodell sektion. Du kommer att se Power Pivot redigerare enligt nedan:

3. Klicka på Diagramvy knappen i Se av Power Pivot Hem flik. Du ser tabellkolumnrubriker grupperade enligt tabellnamnet.

4. Du kan nu dra och släppa den unika fältidentifieraren från en tabell till en annan. Följande är sambandsschemat mellan de fyra tabellerna i Excel -datamodellen:

Följande beskriver kopplingen mellan tabeller:

Bordstudenter | Student -ID till tabell Betyg | Student-ID
Tabellterminer | Semester -ID till tabell Betyg | Termin
Bordsklasser | Klassnummer till tabell Betyg | Klass -ID

5. Du kan skapa relationer genom att välja ett par unika värdekolumner. Om det finns några dubbletter ser du följande fel:

6. Du märker det Stjärna (*) på ena sidan och En (1) å andra sidan i diagramvyn över relationer. Det definierar att det finns en en-till-många-relation mellan tabellerna.

7. I Power Pivot -redigeraren klickar du på Design fliken och välj sedan Hantera relationer för att veta vilka fält som gör anslutningarna.

Skapa en pivottabell med hjälp av Excel -datamodellen

Du kan nu skapa en pivottabell eller pivottabell för att visualisera dina data från Excel -datamodell. En Excel -arbetsbok kan innehålla endast en datamodell, men du kan fortsätta att uppdatera tabellerna.

Relaterat: Vad är datamining och är det olagligt?

Eftersom data ändras över tiden kan du fortsätta använda samma modell och spara tid när du arbetar med samma datamängd. Du kommer att märka mer tidsbesparing när du arbetar med data i tusentals rader och kolumner. Följ dessa steg för att skapa en pivottabellbaserad rapport:

1. I Power Pivot -redigeraren klickar du på Hem flik.

2. På Band , Klicka på Pivottabell .

3. Välja någon mellan nytt kalkylblad eller befintligt kalkylblad.

kan du spela ps4 -spel på ps3

4. Välj OK . Excel lägger till en Pivottabell som kommer att visa Fältlista rutan till höger.

Följande är en helhetsvy av en pivottabell som skapats genom att använda Excel -datamodellen för exempelstudiedata som används i den här självstudien. Du kan också skapa professionella pivottabeller eller diagram från stora data med hjälp av Excel Data Model -verktyget.

Transformera komplexa datauppsättningar till enkla rapporter med hjälp av Excels datamodell

Excel datamodell utnyttjar fördelarna med att skapa relationer mellan tabeller för att skapa meningsfulla pivottabeller eller diagram för datarapporteringsändamål.

Du kan kontinuerligt uppdatera den befintliga arbetsboken och publicera rapporter om uppdaterad data. Du behöver inte redigera formler eller lägga tid på att bläddra igenom tusentals kolumner och rader varje gång källdata uppdateras.

Dela med sig Dela med sig Tweet E-post Hur man skapar ett pivottabell i Excel

Lär dig hur du skapar pivottabeller i Excel och hur du använder dem för att ta fram den information du vill se.

Läs Nästa Relaterade ämnen

Produktivitet
Tips för kalkylblad
Microsoft excel
Microsoft Office -tips
Dataanalys

Om författaren Tamal Das(100 artiklar publicerade)

Tamal är frilansskribent på MakeUseOf. Efter att ha fått stor erfarenhet av teknik, ekonomi och affärsprocesser i sitt tidigare jobb i ett IT-konsultföretag, antog han skrivandet som ett heltidsyrke för 3 år sedan. Även om han inte skriver om produktivitet och de senaste tekniska nyheterna, älskar han att spela Splinter Cell och titta på Netflix/ Prime Video.

Mer från Tamal Das

Prenumerera på vårt nyhetsbrev

Gå med i vårt nyhetsbrev för tekniska tips, recensioner, gratis e -böcker och exklusiva erbjudanden!

Klicka här för att prenumerera