Excel-datamodel (indholdsfortegnelse)

  • Introduktion til datamodel i Excel
  • Sådan oprettes en datamodel i Excel?

Introduktion til datamodel i Excel

Datamodelfunktionen i Excel muliggør let opbygning af forhold mellem let rapportering og deres baggrundsdatasæt. Det gør dataanalyse meget lettere. Det tillader integration af data fra et væld af tabeller spredt over flere regneark ved blot at opbygge relationer mellem matchende kolonner. Det fungerer fuldstændigt bag scenen og forenkler rapporteringsfunktioner såsom PivotTable osv.

I vores artikel skal vi forsøge at vise, hvordan man opretter en pivottabel fra to tabeller ved at anvende funktionen Datamodel, hvorved der oprettes et forhold mellem to tabelobjekter og derved oprette en PivotTable.

Sådan oprettes en datamodel i Excel?

Lad os forstå, hvordan man opretter datamodellen i Excel med et par eksempler.

Du kan downloade denne Datamodel Excel-skabelon her - Datamodel Excel Skabelon

Eksempel 1

  • Vi har en liste over produkter, og vi har en hyldekode for hvert produkt. Vi har brug for en tabel, hvor vi har hyldebeskrivelsen sammen med hyldekoder. Så hvordan inkorporerer vi hyldebeskrivelserne på hver hyldekode? Måske vil mange af os ty til at bruge VLOOKUP her, men vi skal helt fjerne behovet for at bruge VLOOKUP her ved hjælp af Excel Data Model.

  • Tabellen til venstre er datatabellen, og tabellen til højre er opslagstabellen. Som vi kan se fra dataene, er det muligt at oprette et forhold baseret på fælles kolonner.

  • Nu er datamodellen kun kompatibel med tabelobjekter. Så det kan være nødvendigt undertiden at konvertere datasæt til tabelobjekter. For at gøre dette skal du følge nedenstående trin.
  1. Venstreklik hvor som helst i datasættet.
  2. Klik på fanen Indsæt og naviger til tabel i gruppen Tabeller eller tryk blot på Ctrl + T.
  3. Fjern markeringen eller markér, at Min tabel har indstillingen Header. I vores eksempel har den faktisk en overskrift. Klik på OK.
  4. Selvom vi stadig fokuserer på den nye tabel, er vi nødt til at angive et navn, der er meningsfuldt i feltet Navn (til venstre for formelbjælken).

I vores eksempel har vi navngivet tabellen Personale.

  • Nu skal vi også udføre den samme proces for opslagstabellen og navngive den hyldekode.

Oprettelse af et forhold

Så først skal vi gå til fanen Data og derefter vælge Forhold i undergruppen Dataværktøjer. Når vi har klikket på indstillingen Forhold, i starten, da der ikke er noget forhold, har vi derfor intet.

Vi vil først klikke på Ny for at oprette et forhold. Vi bliver nu nødt til at angive primærnavne og opslagstabelnavne fra rullelisten og derefter også nævne den kolonne, der er fælles mellem de to tabeller, så vi kan etablere forholdet mellem de to tabeller fra rullelisten af kolonner.

  • Nu er den primære tabel den tabel, der har dataene. Det er den primære datatabel - Tabel5. På den anden side er den relaterede tabel den tabel, der har opslagsdata - det er vores opslagstabel ShelfCodesTable. Den primære tabel er den, der analyseres på baggrund af opslagstabellen, der indeholder opslagsdata, der vil gøre de rapporterede data til sidst mere meningsfulde.

  • Så den fælles kolonne mellem de to tabeller er kolonnen Hyldekode. Dette er, hvad vi har brugt til at fastlægge forholdet mellem de to tabeller. Når man kommer til kolonnerne, er kolonnen (fremmed) den, der henviser til datatabellen, hvor der kan være duplikatværdier. På den anden side henviser den relaterede kolonne (primær) til kolonnen i opslagstabellen, hvor vi har unikke værdier. Vi sætter simpelt hen feltet til opsamlingsværdier fra opslagstabellen i datatabellen.
  • Når vi opsatte dette, ville Excel skabe et forhold mellem de to bag scenen. Det integrerer dataene og opretter en datamodel baseret på den fælles kolonne. Dette er ikke kun lys på hukommelseskravene, men også meget hurtigere end at bruge VLOOKUP i store arbejdsbøger. Efter at have defineret datamodellen, vil Excel behandle disse objekter som datamodellstabeller i stedet for en regnearkstabel.
  • For nu at se, hvad Excel har været med, kan vi klikke på Administrer datamodeller i Data -> Data Tools.

  • Vi kan også få den skematiske gengivelse af datamodellen ved at ændre visningen. Vi klikker på Vis-indstillingen. Dette åbner visningsmulighederne. Vi vælger derefter diagrammavisningen. Så vil vi se den skematiske repræsentation, der viser de to tabeller og forholdet mellem dem, dvs. den fælles kolonne - Hyldekode.

  • Diagrammet ovenfor viser en en-til-mange-forbindelse mellem de unikke opslagstabelværdier og datatabellen med duplikerede værdier.
  • Nu bliver vi nødt til at oprette en pivottabel. For at gøre det, går vi til fanen Indsæt og klikker derefter på indstillingen Pivot Table.

I dialogboksen Opret pivottabel i Pivot-tabel vælger vi kilden som “Brug denne arbejdsbogs datamodel”.

  • Dette skaber Pivot-tabellen, og vi kan se, at begge kildetabellerne er tilgængelige i kildedelen.

  • Nu skal vi oprette en pivottabel, der viser antallet af hver person, der har hylder.

  • Vi vælger personale i sektionen Rækker fra tabel 5 (datatabel) efterfulgt af beskrivelse (opslagstabel).

  • Nu trækker vi hyldekoden fra tabel 5 til afsnittet Værdier.

  • Nu tilføjer vi måneder fra tabel 5 til sektionen Rækker.

  • Eller vi kan tilføje månederne som et filter og føje dem til sektionen Filtre.

Eksempel 2

  • Vi har nu Mr. Basu, der driver en fabrik kaldet Basu Corporation. Mr. Basu forsøger at estimere indtægterne for 2019 baseret på dataene fra 2018.
  • Vi har en tabel, hvor vi har indtægterne for 2018 og de efterfølgende indtægter på forskellige inkrementelle niveauer.

  • Så vi har indtægterne for 2018 - $ 1, 5 Mio. og den forventede minimumsvækst det følgende år er 12%. Mr. Basu vil have en tabel, der viser indtægterne på forskellige trinvise niveauer.
  • Vi opretter følgende tabel for fremskrivningerne på forskellige trinvise niveauer for 2019.

  • Nu skal vi give den første indtægtsrekke en henvisning til den anslåede minimumsindtægt for 2019, dvs. $ 1, 68 M.

  • Efter anvendelse af formlen vises svaret nedenfor.

  • Nu skal vi vælge hele tabellen, dvs. D2: E12 og derefter gå til Data -> Prognose -> Hvad-hvis analyse -> Datatabel.

  • Dette åbner dialogboksen Datatabel. Her skal vi indtaste det mindste forøgelsesprocent fra celle B4 i kolonnen inputcelle. Årsagen hertil er, at vores forventede vækstprocent i tabellen er arrangeret på en søjleformet måde.

  • Når vi har klikket på OK, udfylder What-If-analysen automatisk tabellen med forventede indtægter i de forskellige trinvise procenter.

Eksempel 3

  • Antag nu, at vi har det samme scenarie som ovenfor, bortset fra at vi nu også har en anden akse at overveje. Antag, at ud over at vise de forventede indtægter i 2019 baseret på dataene fra 2018 og den forventede minimumsvækst, har vi nu også den estimerede diskonteringsrente.

  • Først skal vi have en tabel vist nedenfor.

  • Nu skal vi henvise til den forventede minimumsindtægt for 2019, dvs. celle B5 til celle D8.

  • Nu skal vi vælge hele tabellen dvs. D8: J18 og derefter gå til Data -> Prognose -> Hvad-hvis analyse -> Datatabel.

  • Dette åbner dialogboksen Datatabel. Her skal vi indtaste det mindste forøgelsesprocent fra celle B3 i celleindgangscellen. Årsagen hertil er, at vores forventede vækstprocent i tabellen er arrangeret på en søjleformet måde. Vi indtaster nu også yderligere den mindste rabatprocent fra celle B4 i cellen Row Input. Årsagen hertil er, at vores forventede rabatprocenter i tabellen er arrangeret på rækkevis måde.

  • Klik på OK. Dette gør, hvad-hvis-analysen til automatisk at udfylde tabellen med forventede indtægter med de forskellige inkrementelle procenter pr. Rabatprocentdel.

Ting at huske på datamodel i Excel

  • Ved vellykket beregning af værdierne fra datatabellen fungerer en simpel Fortryd dvs. Ctrl + Z ikke. Det er dog muligt manuelt at slette værdierne fra tabellen.
  • Det er ikke muligt at slette en enkelt celle fra tabellen. Det beskrives som en matrix internt i Excel, og derfor bliver vi nødt til at slette alle værdier.
  • Vi er nødt til at vælge ræden inputcelle og kolonnen inputcelle korrekt.
  • Datatabel, i modsætning til pivottabellen, behøver ikke at opdateres hver gang.
  • Ved hjælp af datamodellen i Excel kan vi ikke kun forbedre ydelsen, men også gå let med hukommelseskravene i store regneark.
  • Datamodeller gør vores analyse også meget enklere sammenlignet med at bruge et antal komplicerede formler overalt i projektmappen.

Anbefalede artikler

Dette er en guide til datamodel i Excel. Her diskuterer vi, hvordan man opretter datamodel i Excel sammen med praktiske eksempler og downloadbar excel-skabelon. Du kan også gennemgå vores andre foreslåede artikler -

  1. Formelbjælke i Excel
  2. Udskriv rutelinjer i Excel
  3. Se vindue i Excel
  4. Excel SUMIFS med datoer

Kategori:

Excel-tip