Excel-datamodell (innholdsfortegnelse)

  • Introduksjon til datamodell i Excel
  • Hvordan lage en datamodell i Excel?

Introduksjon til datamodell i Excel

Datamodelfunksjonen i Excel gjør det enkelt å bygge sammenhenger mellom enkel rapportering og bakgrunnsdatasettet deres. Det gjør dataanalyse mye enklere. Det gjør det mulig å integrere data fra en mengde tabeller spredt over flere regneark ved ganske enkelt å bygge relasjoner mellom samsvarende kolonner. Det fungerer helt bak scenen og forenkler rapporteringsfunksjonene som PivotTable osv.

I vår artikkel skal vi forsøke å vise hvordan du oppretter en pivottabell fra to tabeller ved å benytte funksjonen Datamodell, og dermed etablere et forhold mellom to bordobjekter og derved opprette en pivottabel.

Hvordan lage en datamodell i Excel?

La oss forstå hvordan du lager datamodellen i Excel med noen få eksempler.

Du kan laste ned denne datamodellen Excel-malen her - datamodellen Excel-malen

Eksempel 1

  • Vi har en liste over produkter, og vi har en hyllkode for hvert produkt. Vi trenger et bord der vi har hyllebeskrivelsen sammen med hyllkodene. Så hvordan tar vi inn hyllebeskrivelser i forhold til hver hyllkode? Kanskje vil mange av oss ta til bruk av VLOOKUP her, men vi skal helt fjerne behovet for å bruke VLOOKUP her ved hjelp av Excel Data Model.

  • Tabellen til venstre er datatabellen og tabellen til høyre er oppslagstabellen. Som vi ser av dataene, er det mulig å opprette et forhold basert på vanlige kolonner.

  • Nå er datamodellen bare kompatibel med tabellobjekter. Så kan det være nødvendig noen ganger å konvertere datasett til tabellobjekter. Følg trinnene nedenfor for å gjøre det.
  1. Venstreklikk hvor som helst i datasettet.
  2. Klikk på kategorien Sett inn og naviger til Tabell i tabellen-gruppen, eller trykk Ctrl + T.
  3. Fjern merket for eller merk av for at Mitt bord har alternativet Overskrift. I vårt eksempel har den faktisk en overskrift. Klikk OK.
  4. Selv om vi fremdeles er fokusert på det nye bordet, må vi oppgi et navn som er meningsfylt i Navn-boksen (mot venstre for formellinjen).

I vårt eksempel har vi navngitt tabellen Personell.

  • Nå må vi gjøre den samme prosessen også for oppslagstabellen og navngi den Hyllekode.

Opprette et forhold

Så for det første skal vi gå til Data-fanen og deretter velge Forhold i underverktøyet Dataverktøy. Etter at vi har klikket på alternativet Forhold, i begynnelsen, siden det ikke er noe forhold, vil vi ikke ha noe.

Vi vil først klikke på Ny for å opprette et forhold. Vi må nå oppgi navnene til primær- og oppslagstabellen fra rullegardinlisten, og så også nevne kolonnen som er vanlig mellom de to tabellene, slik at vi kan etablere forholdet mellom de to tabellene, fra rullegardinlisten. av kolonner.

  • Nå er den primære tabellen tabellen som har dataene. Det er den primære datatabellen - Tabell5. På den annen side er den relaterte tabellen tabellen som har oppslagsdataene - det er vår oppslagstabell ShelfCodesTable. Den primære tabellen er den som blir analysert basert på oppslagstabellen som inneholder oppslagsdata som vil gjøre de rapporterte dataene til slutt mer meningsfulle.

  • Den vanlige kolonnen mellom de to tabellene er kolonnen Hyllekode. Dette er hva vi har brukt for å etablere forholdet mellom de to tabellene. Når du kommer til kolonnene, er kolonnen (fremmed) den som refererer til datatabellen der det kan være dupliserte verdier. På den annen side refererer den relaterte kolonnen (primær) til kolonnen i oppslagstabellen der vi har unike verdier. Vi setter ganske enkelt opp feltet for oppslag av verdier fra oppslagstabellen i datatabellen.
  • Når vi konfigurerte dette, ville Excel skape et forhold mellom de to bak scenen. Den integrerer dataene og lager en datamodell basert på den vanlige kolonnen. Dette er ikke bare lett på minnekravene, men også mye raskere enn å bruke VLOOKUP i store arbeidsbøker. Etter å ha definert datamodellen, vil Excel behandle disse objektene som datamodell-tabeller i stedet for en regnearkstabell.
  • Nå for å se hva Excel har gjort, kan vi klikke på Administrer datamodeller i data -> Dataverktøy.

  • Vi kan også få den skjematiske representasjonen av datamodellen ved å endre visningen. Vi klikker på Vis-alternativet. Dette vil åpne visningsalternativene. Vi velger deretter Diagramvisning. Så får vi se den skjematiske representasjonen, som viser de to tabellene og forholdet mellom dem, dvs. den vanlige kolonnen - Hyllekode.

  • Diagrammet over viser en en til mange forhold mellom de unike oppslagstabellverdiene og datatabellen med dupliserte verdier.
  • Nå må vi lage et pivottabell. For å gjøre det, går vi til kategorien Sett inn og deretter klikker du på Pivot Table-alternativet.

I dialogboksen Create Pivot Table i Pivot-tabellen vil vi velge kilden som “Bruk denne arbeidsbokens datamodell”.

  • Dette vil lage Pivot-tabellen, og vi kan se at begge kildetabellene er tilgjengelige i kildedelen.

  • Nå skal vi lage et pivottabell som viser tellingen til hver person som har hyller.

  • Vi vil velge Personell i rader-delen fra tabell 5 (datatabell), etterfulgt av beskrivelse (oppslagstabell).

  • Nå skal vi dra hyllekoden fra tabell 5 inn i Verdiseksjonen.

  • Nå skal vi legge måneder fra tabell 5 til rader-delen.

  • Eller vi kan legge til månedene som et filter og legge dem til i filteret-delen.

Eksempel 2

  • Vi har nå Mr. Basu som driver en fabrikk som heter Basu Corporation. Basu prøver å estimere inntektene for 2019 basert på dataene fra 2018.
  • Vi har en tabell der vi har inntektene for 2018 og de påfølgende inntektene på forskjellige trinnvise nivåer.

  • Så vi har inntektene for 2018 - $ 1, 5 millioner, og minimumsveksten som forventes året etter er 12%. Basu vil ha en tabell som viser inntektene på forskjellige trinnvise nivåer.
  • Vi vil lage følgende tabell for anslagene på forskjellige trinnvise nivåer for 2019.

  • Nå skal vi gi den første inntektsraden en referanse til estimert minimumsinntekt for 2019, dvs. 1, 68 M $.

  • Etter bruk av formelen vises svaret nedenfor.

  • Nå skal vi velge hele tabellen dvs. D2: E12 og deretter gå til Data -> Prognose -> Hva-hvis analyse -> Datatabell.

  • Dette åpner dialogboksen Datatabell. Her skal vi legge inn minimumsvekstprosenten fra celle B4 i kolonneinputcellen. Årsaken til det er at våre anslåtte vekstprosenter i tabellen er ordnet på en søyleform.

  • Når vi har klikket OK, vil What-If-analysen automatisk fylle ut tabellen med anslått inntekt med de forskjellige trinnvise prosentene.

Eksempel 3

  • Anta at vi har det samme scenariet som over, bortsett fra at vi nå også har en annen akse å vurdere. Anta at i tillegg til å vise de anslåtte inntektene i 2019 basert på dataene fra 2018 og den forventede minimumsveksten, har vi nå også den estimerte diskonteringsrenten.

  • Først skal vi ha en tabell vist nedenfor.

  • Nå skal vi vise til minimum forventet inntekt for 2019, dvs. celle B5 til celle D8.

  • Nå skal vi velge hele tabellen dvs. D8: J18 og deretter gå til Data -> Prognose -> Hva-hvis analyse -> Datatabell.

  • Dette åpner dialogboksen Datatabell. Her skal vi legge inn minimumsvekstprosenten fra celle B3 i kolonneinputcellen. Årsaken til det er at våre anslåtte vekstprosenter i tabellen er ordnet på en søyleform. Vi skal nå også legge inn minimumsrabattprosenten fra celle B4 i radinndata-cellen. Årsaken til det er at våre prosjekterte rabattprosenter i tabellen er ordnet på rekke og rad.

  • Klikk OK. Dette vil gjøre What-If-analysen til automatisk å fylle ut tabellen med anslått inntekt med de forskjellige trinnvise prosentene i henhold til rabattprosentene.

Ting å huske på datamodell i Excel

  • Når du har beregnet verdiene fra datatabellen, vil en enkel Angre dvs. Ctrl + Z ikke fungere. Det er imidlertid mulig å slette verdiene manuelt fra tabellen.
  • Det er ikke mulig å slette en enkelt celle fra tabellen. Det beskrives som en matrise internt i Excel, og derfor må vi slette alle verdiene.
  • Vi må velge riktig inputcelle og kolonneinputcellen.
  • Datatabell, i motsetning til pivottabellen, trenger ikke å oppdateres hver gang.
  • Ved å bruke datamodellen i Excel kan vi ikke bare forbedre ytelsen, men også gå lett på minnekravene i store regneark.
  • Datamodeller gjør også analysen vår mye enklere sammenlignet med å bruke en rekke kompliserte formler over hele arbeidsboka.

Anbefalte artikler

Dette er en guide til datamodell i Excel. Her diskuterer vi hvordan du lager Data Model i Excel sammen med praktiske eksempler og nedlastbar Excel-mal. Du kan også gå gjennom andre foreslåtte artikler -

  1. Formelbar i Excel
  2. Skriv ut rutenett i Excel
  3. Se vindu i Excel
  4. Excel SUMIFS med datoer

Kategori:

Tips for Excel