Hvordan lage relasjoner mellom flere tabeller ved hjelp av datamodell i Excel

Excel er et kraftig verktøy for dataanalyse og påfølgende automatisering ved håndtering av store datasett. Du kan bruke betydelig tid på å analysere tonnevis med data ved å bruke VLOOKUP, INDEX-MATCH, SUMIF, etc.

Takket være Excel -datamodellen kan du spare dyrebar tid gjennom automatiske datarapporter. Finn ut hvor enkelt du kan tilordne et forhold mellom to tabeller ved å bruke datamodellen og en illustrasjon av et slikt forhold i en pivottabell i den følgende delen.

De grunnleggende kravene

Du trenger Power Pivot og Power Query (Get & Transform) for å utføre flere oppgaver mens du oppretter utmerke Datamodell. Slik kan du få disse funksjonene i Excel -arbeidsboken:

Slik får du Power Pivot

1. Excel 2010: Du må laste ned Power Pivot-tillegget fra Microsoft og installer det deretter for Excel -programmet på datamaskinen.

2. Excel 2013: Office Professional Plus -utgaven av Excel 2013 inkluderer Power Pivot. Men du må aktivere den før første gangs bruk. Slik gjør du:

Klikk på Fil på Bånd av en Excel -arbeidsbok.
Klikk deretter på Alternativer å åpne Excel -alternativer .
Klikk nå på Tillegg .
Plukke ut COM-tillegg ved å klikke på rullegardinmenyen i Få til eske.
Klikk på Gå og merk deretter av for Microsoft Power Pivot for Excel .

3. Excel 2016 og senere: Du finner Power Pivot -menyen på Bånd .

I slekt: Slik legger du til utviklerfanen på båndet i Microsoft Word og Excel

Slik får du Power Query (Get & Transform)

1. Excel 2010: Du kan laste ned Power Query-tillegget fra Microsoft . Etter installasjon, Strømforespørsel vil dukke opp på Bånd .

2. Excel 2013: Du må aktivere Power Query ved å følge de samme trinnene som du nettopp gjorde for å gjøre Power Pivot funksjonell i Excel 2013.

3. Excel 2016 og senere: Du finner Power Query (Get & Transform) ved å gå til Data kategorien i Excel Bånd .

Lag datamodell ved å importere data til Excel -arbeidsboken

For denne opplæringen kan du få forhåndsformaterte eksempeldata fra Microsoft:

nedlasting : Eksempel på student data (kun data) | Eksempel på student data (komplett modell)

Du kan importere en database med flere relaterte tabeller fra mange kilder som Excel -arbeidsbøker, Microsoft Access, nettsteder, SQL Server, etc. Deretter må du formatere datasettet slik at Excel kan bruke det. Her er trinnene du kan prøve:

1. I Excel 2016 og senere utgaver klikker du på Data kategorien og velg Ny forespørsel .

2. Du finner flere måter å importere data fra eksterne eller interne kilder. Velge den som passer deg.

3. Hvis du bruker Excel 2013 -utgaven, klikker du på Strømforespørsel på Bånd og velg deretter Få eksterne data å velge data for import.

4. Du vil se Navigator boksen der du må velge hvilke tabeller du trenger å importere. Klikk på avmerkingsboksen for Velg flere elementer å velge flere tabeller for import.

5. Klikk på Laste for å fullføre importprosessen.

6. Excel vil opprette en datamodell for deg ved hjelp av disse tabellene. Du kan se tabellkolonneoverskriftene i Pivottabellfelt oppføringer.

Du kan også bruke Power Pivot -funksjoner som beregnede kolonner, KPI -er, hierarkier, beregnede felt og filtrerte datasett fra Excel -datamodell. For dette formålet må du generere datamodell fra en enkelt tabell. Du kan prøve disse trinnene:

1. Formater dataene i en tabellmodell ved å velge alle cellene som inneholder data, og klikk deretter Ctrl+T .

2. Velg nå hele tabellen, og klikk deretter på Power Pivot fanen på Bånd .

3. Fra Tabeller delen, klikk på Legg til i datamodell .

Excel vil opprette tabellforhold mellom relaterte data fra datamodellen. For dette bør det være primære og utenlandske nøkkelforhold i de importerte tabellene.

Excel bruker relasjonsinformasjonen fra den importerte tabellen som grunnlag for å generere forbindelser mellom tabellene i en datamodell.

I slekt: Hvordan lage en What-If-analyse i Microsoft Excel

Bygg relasjoner mellom tabellene i datamodellen

Nå som du har en datamodell i Excel -arbeidsboken, må du definere relasjoner mellom tabellene for å lage meningsfulle rapporter. Du må tilordne en unik feltidentifikator eller primærnøkkel til hver tabell, for eksempel semester -ID, klassenummer, student -ID, etc.

Diagramvisning -funksjonen i Power Pivot lar deg dra og slippe disse feltene for å bygge et forhold. Følg disse trinnene for å opprette tabelllenker i Excel -datamodell:

1. På Bånd i Excel -arbeidsboken, klikker du på Power Pivot Meny.

2. Klikk nå på Få til i Datamodell seksjon. Du vil se Power Pivot redaktør som vist nedenfor:

3. Klikk på Diagramvisning -knappen i Utsikt delen av Power Pivot Hjem kategorien. Du ser tabellkolonneoverskrifter gruppert i henhold til tabellnavnet.

4. Du kan nå dra og slippe den unike feltidentifikatoren fra en tabell til en annen. Følgende er forholdet skjematisk mellom de fire tabellene i Excel -datamodellen:

Følgende beskriver koblingen mellom tabeller:

Bordstudenter | Student -ID til bord Karakterer | Student ID
Bordsemester | Semester -ID til tabell Karakterer Semester
Bordklasser | Klassetall til tabell Karakterer Klasse -ID

5. Du kan opprette relasjoner ved å velge et par unike verdikolonner. Hvis det er noen duplikater, ser du følgende feil:

6. Du vil legge merke til det Stjerne (*) på den ene siden og En (1) på den andre i diagrammet Oversikt over relasjoner. Den definerer at det eksisterer et en-til-mange-forhold mellom tabellene.

7. I Power Pivot -editoren klikker du på Design kategorien og velg deretter Administrer relasjoner å vite hvilke felt som gjør forbindelsene.

Lag en pivottabell ved hjelp av Excel -datamodellen

Du kan nå opprette en pivottabell eller pivottabell for å visualisere dataene dine fra Excel -datamodell. En Excel -arbeidsbok kan inneholde bare én datamodell, men du kan fortsette å oppdatere tabellene.

Relatert: Hva er datautvinning og er det ulovlig?

Fordi data endres over tid, kan du fortsette å bruke samme modell og spare tid når du arbeider med det samme datasettet. Du vil merke mer tidsbesparelse når du jobber med data i tusenvis av rader og kolonner. Følg disse trinnene for å opprette en pivottabellbasert rapport:

1. I Power Pivot -editoren klikker du på Hjem kategorien.

2. På Bånd , klikk på Pivottabell .

3. Velge noen mellom nytt regneark eller eksisterende regneark.

hvordan gjøre bakgrunnen gjennomsiktig

4. Velg OK . Excel vil legge til en Pivottabell som vil vise Feltliste ruten til høyre.

Følgende er en helhetlig oversikt over et pivottabell som er opprettet ved å bruke Excel -datamodellen for eksempelstudiedataene som ble brukt i denne opplæringen. Du kan også lage profesjonelle pivottabeller eller diagrammer fra store data ved hjelp av Excel Data Model -verktøyet.

Transform komplekse datasett til enkle rapporter ved hjelp av Excels datamodell

Excel datamodell utnytter fordelene ved å opprette relasjoner mellom tabeller for å lage meningsfulle pivottabeller eller diagrammer for datarapporteringsformål.

Du kan kontinuerlig oppdatere den eksisterende arbeidsboken og publisere rapporter om oppdaterte data. Du trenger ikke å redigere formler eller investere tid i å bla gjennom tusenvis av kolonner og rader hver gang kildedata oppdateres.

Dele Dele kvitring E -post Hvordan lage et pivottabell i Excel

Lær hvordan du lager pivottabeller i Excel og hvordan du bruker dem til å trekke ut informasjonen du vil se.

Les neste Relaterte temaer

Produktivitet
Tips til regneark
Microsoft Excel
Microsoft Office -tips
Dataanalyse

Om forfatteren Tamal Das(100 artikler publisert)

Tamal er frilansskribent på MakeUseOf. Etter å ha opparbeidet seg betydelig erfaring innen teknologi, økonomi og forretningsprosesser i sin forrige jobb i et IT-konsulentselskap, adopterte han skriving som et heltidsyrke for 3 år siden. Selv om han ikke skriver om produktivitet og de siste tekniske nyhetene, elsker han å spille Splinter Cell og se på Netflix/ Prime Video.

Mer fra Tamal Das

Abonner på vårt nyhetsbrev

Bli med i vårt nyhetsbrev for tekniske tips, anmeldelser, gratis ebøker og eksklusive tilbud!

Klikk her for å abonnere