Datamodel i Excel | Hvordan oprettes datamodel? (med eksempler)

Hvad er datamodellen i Excel?

Datamodel i Excel er en type datatabel, hvor vi to eller flere end to tabeller er i forhold til hinanden gennem en fælles eller flere dataserier, i datamodeltabeller og data fra forskellige andre ark eller kilder samles for at danne en unik tabel, der kan have adgang til dataene fra alle tabellerne.

Forklaring

  • Det giver mulighed for at integrere data fra flere tabeller ved at oprette relationer baseret på en fælles kolonne.
  • Datamodeller bruges gennemsigtigt og leverer tabeldata, der kan bruges i pivottabel i Excel og pivotdiagrammer i excel. Det integrerer tabellerne, hvilket muliggør omfattende analyse ved hjælp af pivottabeller, Power Pivot og Power View i Excel.
  • Datamodellen tillader indlæsning af data i Excels hukommelse.
  • Det gemmes i hukommelsen, hvor vi ikke direkte kan se det. Derefter kan Excel instrueres i at relatere data til hinanden ved hjælp af en fælles kolonne. 'Model'-delen af ​​datamodellen henviser til, hvordan alle tabeller relaterer til hinanden.
  • Data Model kan få adgang til alle de oplysninger, den har brug for, selv når oplysningerne findes i flere tabeller. Når datamodellen er oprettet, har Excel dataene tilgængelige i hukommelsen. Med dataene i hukommelsen kan der tilgås data på mange måder.

Eksempler

Du kan downloade denne Excel-skabelon til datamodel her - Excel-skabelon til datamodel

Eksempel nr. 1

Hvis vi har tre datasæt relateret til sælgeren: Først indeholdende indtægtsoplysninger, et andet indeholdende salgsperson og tredje indeholdende salgspersonens udgifter.

For at forbinde disse tre datasæt og skabe et forhold til disse opretter vi en datamodel med følgende trin:

  • Konverter datasættene til tabelobjekter:

Vi kan ikke skabe et forhold til almindelige datasæt. Datamodellen fungerer kun med Excel Tables-objekter. At gøre dette:

  • Trin 1 - Klik et vilkårligt sted i datasættet, klik derefter på fanen 'Indsæt' og klik derefter på 'Tabel' i gruppen 'Tabeller'.

  • Trin 2 - Marker eller fjern markeringen i indstillingen: 'Min tabel har overskrifter', og klik på OK.

  • Trin 3 - Med den nye tabel valgt, skal du indtaste navnet på tabellen i 'Tabelnavn' i gruppen 'Værktøjer'.

  • Trin 4 - Nu kan vi se, at det første datasæt konverteres til 'Table' -objekt. Når vi gentager disse trin for de to andre datasæt, ser vi, at de også bliver konverteret til 'Table' -objekter som nedenfor:

Tilføjelse af 'Tabel' -objekterne til datamodellen: Via forbindelser eller forhold.

Via forbindelser

  • Vælg en tabel, og klik på fanen 'Data', og klik derefter på 'Forbindelser'.

  • I den resulterende dialogboks er der et ikon for 'Tilføj'. Udvid rullemenuen 'Tilføj' og klik på 'Føj til datamodellen'.

  • Klik på 'Tabeller' i den resulterende dialogboks, og vælg derefter en af ​​tabellerne, og klik på 'Åbn'.

Når du gør dette, oprettes der en projektmappemodel med en tabel, og en dialogboks vises som følger:

Så hvis vi også gentager disse trin for de to andre tabeller, indeholder datamodellen nu alle tre tabeller.

Vi kan nu se, at alle tre tabeller vises i Workbook Connections.

Via forhold

Opret forholdet: Når begge datasættene er tabelobjekter, kan vi oprette et forhold mellem dem. At gøre dette:

  • Klik på fanen 'Data', og klik derefter på 'Relationer'.

  • Vi ser en tom dialogboks, da der ikke er nogen aktuelle forbindelser.

  • Klik på 'Ny' og en anden dialogboks vises.

  • Udvid rullelisterne 'Tabel' og 'Relateret tabel': En dialogboks 'Opret forhold' vises for at vælge de tabeller og kolonner, der skal bruges til et forhold. I udvidelsen af ​​'Tabeller' skal du vælge det datasæt, vi ønsker at analysere på en eller anden måde, og i 'Relateret tabel' skal du vælge det datasæt, der har opslagsværdier.
  • Opslagstabellen i Excel er den mindre tabel i tilfælde af et til mange forhold, og den indeholder ingen gentagne værdier i den fælles kolonne. I udvidelsen af ​​'Kolonne (fremmed)' skal du vælge den fælles kolonne i hovedtabellen, i 'Relateret kolonne (primær)', vælge den fælles kolonne i den relaterede tabel.

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

Hvis vi gentager disse trin for at relatere andre to tabeller: Indtjeningstabel med udgifter, så bliver de også relaterede i datamodellen som følger:

Excel opretter nu forholdet bag kulisserne ved at kombinere data i datamodellen baseret på en fælles kolonne: sælger-id (i dette tilfælde).

Eksempel 2

Lad os nu sige i ovenstående eksempel, at vi ønsker at oprette en pivottabel, der evaluerer eller analyserer tabelobjekterne:

  • Klik på 'Indsæt' -> 'Pivottabel'.

  • I den resulterende dialogboks skal du klikke på indstillingen med angivelse: 'Brug en ekstern datakilde' og derefter klikke på 'Vælg forbindelse'.

  • Klik på 'Tabeller' i den resulterende dialogboks, og vælg projektmappemodellen, der indeholder tre tabeller, og klik på 'Åbn'.

  • Vælg indstillingen 'Nyt regneark' og klik på 'OK'.

  • Ruden Pivot Table Fields viser tabelobjekter.

  • Nu kan ændringer i pivottabellen gøres i overensstemmelse hermed for at analysere tabelobjekterne efter behov.

For eksempel, i dette tilfælde, hvis vi ønsker at finde den samlede indtægt eller indtjening for en bestemt sælger, oprettes der en pivottabel som følger:

Dette er til stor hjælp i tilfælde af en model / tabel, der indeholder et stort antal observationer.

Så vi kan se, at pivottabellen straks bruger datamodellen (vælger den ved at vælge forbindelse) i Excel-hukommelse til at vise forholdet mellem tabeller.

Ting at huske

  • Ved hjælp af datamodellen kan vi analysere data fra flere tabeller på én gang.
  • Ved at skabe relationer med datamodellen overgår vi behovet for at bruge formlerne VLOOKUP, SUMIF, INDEX og MATCH, da vi ikke behøver at få alle kolonner inden for en enkelt tabel.
  • Når datasæt importeres i Excel fra eksterne kilder, oprettes modeller implicit.
  • Tabelrelationer kan oprettes automatisk, hvis vi importerer relaterede tabeller, der har primære og udenlandske nøglerelationer.
  • Mens du opretter relationer, skal de kolonner, som vi forbinder i tabeller, have den samme datatype.
  • Med pivottabellerne oprettet med datamodellen kan vi også tilføje udskærere og udskifte pivottabellerne på ethvert felt, vi ønsker.
  • Fordelen ved datamodellen i forhold til LOOKUP () - funktionerne er, at den kræver væsentligt mindre hukommelse.
  • Excel 2013 understøtter kun en til en eller en til mange relationer, dvs. en af ​​tabellerne må ikke have duplikatværdier i den kolonne, vi linker til.