Datamodell i Excel | Hvordan lage datamodell? (med eksempler)

Hva er datamodellen i Excel?

Datamodell i Excel er en type datatabell der vi to eller flere enn to tabeller er i forhold til hverandre gjennom en felles eller flere dataserier, i datamodelltabeller og data fra forskjellige andre ark eller kilder kommer sammen for å danne et unikt tabell som kan ha tilgang til dataene fra alle tabellene.

Forklaring

  • Det gjør det mulig å integrere data fra flere tabeller ved å opprette relasjoner basert på en felles kolonne.
  • Datamodeller brukes transparent, og gir tabelldata som kan brukes i pivottabellen i Excel og pivotdiagrammer i excel. Den integrerer tabellene, og muliggjør omfattende analyse ved hjelp av pivottabeller, Power Pivot og Power View i Excel.
  • Datamodellen tillater innlasting av data i Excels minne.
  • Det er lagret i minnet der vi ikke kan se det direkte. Deretter kan Excel instrueres om å relatere data til hverandre ved hjelp av en vanlig kolonne. 'Model'-delen av datamodellen refererer til hvordan alle tabeller forholder seg til hverandre.
  • Data Model har tilgang til all informasjonen den trenger, selv når informasjonen er i flere tabeller. Etter at datamodellen er opprettet, har Excel dataene tilgjengelig i minnet. Med dataene i minnet kan du få tilgang til dataene på mange måter.

Eksempler

Du kan laste ned denne Excel-malen for datamodell her - Data-modellen Excel-mal

Eksempel 1

Hvis vi har tre datasett relatert til selgeren: Først inneholder inntektsinformasjon, et andre inneholder inntekt fra selger, og tredje inneholder utgifter til selger.

For å koble disse tre datasettene og lage et forhold til disse lager vi en datamodell med følgende trinn:

  • Konverter datasett til tabellobjekter:

Vi kan ikke opprette et forhold til vanlige datasett. Datamodellen fungerer bare med Excel Tables-objekter. Å gjøre dette:

  • Trinn 1 - Klikk hvor som helst i datasettet, klikk deretter på 'Sett inn' -fanen og deretter på 'Tabell' i gruppen 'Tabeller'.

  • Trinn 2 - Merk av eller fjern merket for alternativet: 'Min tabell har overskrifter' og klikk OK.

  • Trinn 3 - Med den nye tabellen valgt, skriv inn navnet på tabellen i 'Tabellnavn' i 'Verktøy'-gruppen.

  • Trinn 4 - Nå kan vi se at det første datasettet konverteres til 'Table' -objekt. Når vi gjentar disse trinnene for de to andre datasettene, ser vi at de også blir konvertert til 'Table' -objekter som nedenfor:

Legge til tabellobjektene i datamodellen: via tilkoblinger eller forhold.

Via tilkoblinger

  • Velg en tabell og klikk på 'Data' -fanen, og klikk deretter på 'Connections'.

  • I den resulterende dialogboksen er det et ikon for 'Legg til'. Utvid rullegardinmenyen "Legg til" og klikk på "Legg til datamodellen".

  • Klikk på 'Tabeller' i den resulterende dialogboksen, og velg deretter en av tabellene og klikk 'Åpne'.

Når du gjør dette, vil en arbeidsbok datamodell bli opprettet med en tabell, og en dialogboks vises som følger:

Så hvis vi gjentar disse trinnene for de to andre tabellene også, vil datamodellen nå inneholde alle tre tabellene.

Vi kan nå se at alle tre tabellene vises i arbeidsbokstilkoblingene.

Via forhold

Opprett forholdet: Når begge datasettene er tabellobjekter, kan vi opprette et forhold mellom dem. Å gjøre dette:

  • Klikk på 'Data' -fanen og deretter på 'Relationships'.

  • Vi ser en tom dialogboks da det ikke er noen nåværende tilkoblinger.

  • Klikk på 'Ny' og en annen dialogboks vises.

  • Utvid rullegardinlistene 'Tabell' og 'Relatert tabell: Dialogboksen' Opprett forhold 'vises for å velge tabellene og kolonnene som skal brukes til et forhold. I utvidelsen av 'Tabeller' velger du datasettet vi ønsker å analysere på en eller annen måte, og i 'Relatert tabell' velger du datasettet som har oppslagsverdier.
  • Oppslagstabellen i Excel er den mindre tabellen i tilfelle en til mange relasjoner, og den inneholder ingen gjentatte verdier i den vanlige kolonnen. I utvidelsen av 'Kolonne (utenlandsk)', velg den vanlige kolonnen i hovedtabellen, i 'Relatert kolonne (primær)', velg den vanlige kolonnen i den relaterte tabellen.

  • Når alle disse fire innstillingene er valgt, klikker du på 'OK'. En dialogboks vises som følger når du klikker 'OK'.

Hvis vi gjentar disse trinnene for å relatere to andre tabeller: Inntektstabell med utgifts-tabellen, blir de også relatert i datamodellen som følger:

Excel oppretter nå forholdet bak kulissene ved å kombinere data i datamodellen basert på en vanlig kolonne: selger-ID (i dette tilfellet).

Eksempel 2

La oss si i eksemplet ovenfor at vi ønsker å lage en pivottabell som evaluerer eller analyserer tabellobjektene:

  • Klikk på 'Sett inn' -> 'Pivottabell'.

  • I den resulterende dialogboksen klikker du på alternativet som sier: 'Bruk en ekstern datakilde' og deretter klikker du på 'Velg tilkobling'.

  • Klikk på 'Tabeller' i den resulterende dialogboksen, og velg arbeidsbokdatamodellen som inneholder tre tabeller, og klikk 'Åpne'.

  • Velg alternativet 'Nytt regneark' på stedet og klikk på 'OK'.

  • Pivottabellfeltruten viser tabellobjekter.

  • Nå kan endringer i pivottabellen gjøres tilsvarende for å analysere tabellobjektene etter behov.

I dette tilfellet, hvis vi ønsker å finne den totale inntekten eller inntekten for en bestemt selger, opprettes en pivottabell som følger:

Dette er til stor hjelp når det gjelder en modell / tabell som inneholder et stort antall observasjoner.

Så vi kan se at pivottabellen umiddelbart bruker datamodellen (plukker den ved å velge tilkobling) i Excel-minne for å vise forholdet mellom tabellene.

Ting å huske

  • Ved hjelp av datamodellen kan vi analysere data fra flere tabeller samtidig.
  • Ved å skape relasjoner med datamodellen overgår vi behovet for å bruke formlene VLOOKUP, SUMIF, INDEX og MATCH, ettersom vi ikke trenger å få alle kolonnene i en enkelt tabell.
  • Når datasett importeres i Excel fra eksterne kilder, opprettes modeller implisitt.
  • Tabellrelasjoner kan opprettes automatisk hvis vi importerer relaterte tabeller som har primære og utenlandske nøkkelrelasjoner.
  • Mens du oppretter relasjoner, skal kolonnene vi kobler til i tabeller ha samme datatype.
  • Med pivottabellene opprettet med datamodellen kan vi også legge til snitt og skjære pivottabellene på hvilket felt vi vil.
  • Fordelen med datamodellen over LOOKUP () -funksjonene er at den krever betydelig mindre minne.
  • Excel 2013 støtter bare en til en eller en til mange relasjoner, dvs. at en av tabellene ikke må ha duplikatverdier i kolonnen vi kobler til.