Excel Data Model (kazalo)

  • Uvod v podatkovni model v Excelu
  • Kako ustvariti podatkovni model v Excelu?

Uvod v podatkovni model v Excelu

Funkcija podatkovnega modela programa Excel omogoča enostavno gradnjo odnosov med enostavnim poročanjem in njihovimi nabori podatkov v ozadju. Veliko olajša analizo podatkov. Omogoča integracijo podatkov iz množice tabel, razporejenih po več delovnih listih, s preprosto izgradnjo odnosov med ujemajočimi stolpci. Deluje popolnoma za prizoriščem in močno poenostavlja funkcije poročanja, kot je PivotTable itd.

V našem članku bomo poskušali pokazati, kako ustvariti vrtilno tabelo iz dveh tabel s pomočjo funkcije Data Model, s čimer bomo vzpostavili odnos med dvema objektoma tabele in s tem ustvarili vrtilno tabelo.

Kako ustvariti podatkovni model v Excelu?

Poglejmo, kako ustvariti podatkovni model v Excelu z nekaj primeri.

Predlogo Data Excel Predloga lahko prenesete tukaj - Podatkovni model Excel Predloga

Primer # 1

  • Imamo seznam izdelkov in imamo kodo za police za vsak izdelek. Potrebujemo tabelo, kjer imamo opis polic in šifre polic. Kako torej vključimo opise polic za vsako kodo polic? Morda bi se mnogi od nas zatekali k uporabi tukaj VLOOKUP, vendar bomo v celoti odpravili potrebo po uporabi VLOOKUP s pomočjo Excelovega podatkovnega modela.

  • Tabela na levi je tabela podatkov, tabela na desni pa tabela za iskanje. Kot lahko vidimo iz podatkov, je mogoče ustvariti odnos, ki temelji na skupnih stolpcih.

  • Zdaj je podatkovni model združljiv samo s predmeti tabel. Včasih bo morda treba pretvoriti nabore podatkov v predmete tabel. To naredite tako, da sledite spodnjim korakom.
  1. Z levim gumbom kliknite kjer koli v naboru podatkov.
  2. Kliknite zavihek Vstavljanje in se pomaknite do Tabela v skupini Tabele ali preprosto pritisnite Ctrl + T.
  3. Počistite ali označite možnost Moja miza možnost Header. V našem primeru res ima glavo. Kliknite V redu.
  4. Čeprav smo še vedno osredotočeni na novo tabelo, moramo v polju Ime (na levi strani formule) navesti ime, ki je smiselno.

V našem primeru smo tabelo poimenovali Osebje.

  • Zdaj moramo narediti enak postopek tudi za iskalno tabelo in jo poimenovati Shelf Code.

Ustvarjanje odnosa

Najprej gremo na zavihek Podatki in nato v podskupini Podatkovna orodja izberemo Razmerja. Ko kliknemo na Odnos, v začetku, ker ni zveze, torej ne bomo imeli ničesar.

Najprej bomo kliknili Novo in ustvarili odnos. Zdaj bomo morali na spustnem seznamu navesti imena primarne in iskalne tabele ter nato omeniti stolpec, ki je običajen med dvema tabelama, da bomo lahko s spustnega seznama vzpostavili odnos med obema tabelama. stolpcev.

  • Zdaj je primarna tabela tabela s podatki. To je primarna tabela podatkov - Tabela5. Po drugi strani je sorodna tabela tabela s podatki o iskanju - to je naša iskalna tabela ShelfCodesTable. Primarna tabela je tista, ki se analizira na podlagi iskalne tabele, ki vsebuje podatke o iskanju, zaradi česar bodo na koncu sporočeni podatki bolj smiselni.

  • Torej, skupni stolpec med dvema tabelama je stolpec Koda police. To smo uporabili pri vzpostavljanju razmerja med obema tabelama. V stolpcih je stolpec (tuj) tisti, ki se nanaša na podatkovno tabelo, kjer so lahko podvojene vrednosti. Po drugi strani se sorodni stolpec (primarni) nanaša na stolpec v tabeli iskanja, kjer imamo edinstvene vrednosti. Polje preprosto nastavimo za iskanje vrednosti iz tabele za iskanje v podatkovni tabeli.
  • Ko smo to postavili, bi Excel ustvaril odnos med obema v zakulisju. Vključuje podatke in ustvari podatkovni model, ki temelji na skupnem stolpcu. To ni dovolj le za potrebe pomnjenja, ampak tudi za veliko hitrejšo uporabo kot uporaba VLOOKUP v velikih delovnih zvezkih. Po definiranju podatkovnega modela bo Excel te predmete obravnaval kot tabele podatkovnega modela namesto tabele delovnega lista.
  • Zdaj, da vidimo, kaj se je Excel lotil, lahko kliknemo Upravljanje podatkovnih modelov v Podatki -> Podatkovna orodja.

  • S spremembo pogleda lahko dobimo tudi shematski prikaz podatkovnega modela. Klikali bomo na možnost Pogled. To bo odprlo možnosti pogleda. Nato bomo izbrali Diagram View. Nato bomo videli shematski prikaz, ki prikazuje dve tabeli in odnos med njima, tj. Skupni stolpec - Šifra police.

  • Zgornji diagram prikazuje razmerje med številnimi edinstvenimi vrednostmi tabele za iskanje in podatkovno tabelo z podvojenimi vrednostmi.
  • Zdaj bomo morali izdelati vrtilno tabelo. To storimo tako, da gremo na zavihek Vstavljanje in nato kliknemo na možnost vrtilne tabele.

V pogovornem oknu Ustvari vrtilno tabelo v tabeli bomo izbrali vir kot "Uporabi podatkovni model tega delovnega zvezka".

  • Tako bomo ustvarili vrtilno tabelo in videli smo, da sta v izvornem razdelku na voljo obe izvorni tabeli.

  • Zdaj bomo ustvarili vrtilno tabelo, ki prikazuje število vsake osebe, ki ima na policah predmete.

  • V razdelku vrstic iz tabele 5 (podatkovna tabela) bomo izbrali osebje, ki mu sledi Opis (tabela iskanja).

  • Zdaj povlečemo kodo police iz preglednice 5 v razdelek Vrednosti.

  • Zdaj bomo v razdelek vrstic dodali mesece iz tabele 5.

  • Lahko pa mesece dodamo kot filter in jih dodamo v razdelek Filtri.

Primer # 2

  • Zdaj imamo gospod Basu voditelj tovarne Basu Corporation. G. Basu poskuša oceniti prihodke za leto 2019 na podlagi podatkov iz leta 2018.
  • Imamo tabelo, v kateri imamo prihodke za leto 2018 in prihodnje prihodke na različnih stopnjah.

  • Torej imamo prihodke za leto 2018 - 1, 5 milijona dolarjev, najmanjša rast, ki se pričakuje naslednje leto, pa znaša 12%. G. Basu želi tabelo, ki bo prikazovala prihodke na različnih stopnjah.
  • Naslednjo tabelo bomo ustvarili za projekcije na različnih stopnjah za leto 2019.

  • Zdaj bomo v prvi vrstici o prihodkih navedli predviden minimalni prihodek za leto 2019, tj. 1, 68 m USD.

  • Po uporabi formule je odgovor prikazan spodaj.

  • Zdaj bomo izbrali celotno tabelo, tj. D2: E12, nato pa pojdite na Podatki -> Napoved -> Kaj-če analiza -> Podatkovna tabela.

  • To se bo odprlo pogovorno okno Podatkovna tabela. Tu vnesemo najmanjši prirastek od celice B4 v celici vnosa stolpca. Razlog za to je, da so naši predvideni ocenjeni odstotki rasti v tabeli razporejeni na stolpen način.

  • Ko kliknemo V redu, analiza What-If bo samodejno zapolnila tabelo s predvidenimi prihodki v različnih stopnjah.

Primer # 3

  • Predpostavimo, da imamo enak scenarij kot zgoraj, le da moramo zdaj upoštevati še eno os. Predpostavimo, da poleg prikazanih predvidenih prihodkov v letu 2019 na podlagi podatkov leta 2018 in najnižje pričakovane stopnje rasti imamo zdaj tudi ocenjeno diskontno stopnjo.

  • Najprej bomo imeli spodaj prikazano tabelo.

  • Zdaj se bomo sklicevali na najmanjši predvideni prihodek za leto 2019, tj. Celico B5 do celice D8.

  • Zdaj bomo izbrali celotno tabelo, tj. D8: J18, nato pa pojdite na Podatki -> Napoved -> Kaj-če analiza -> Podatkovna tabela.

  • To bo odprlo pogovorno okno Podatkovna tabela. Tu vpišemo najmanjši odstotek prirastka iz celice B3 v celico Vnos stolpca. Razlog za to je, da so naši predvideni ocenjeni odstotki rasti v tabeli razporejeni na stolpen način. Zdaj bomo dodatno vnesli najnižji odstotek popusta od celice B4 v celici Vnos vrstice. Razlog za to je, da so naši predvideni odstotni odstotki v tabeli urejeni po vrstnem redu.

  • Kliknite V redu. S tem bo analiza What-If avtomatično zapolnila tabelo s predvidenimi prihodki v različnih prirastenih odstotkih glede na diskontne odstotke.

Ne smemo pozabiti na model podatkov v Excelu

  • Ko uspešno izračunate vrednosti iz podatkovne tabele, preprosto razveljavi, tj. Ctrl + Z, ne bo delovalo. Vendar pa je mogoče vrednosti ročno izbrisati iz tabele.
  • Iz tabele ni mogoče izbrisati ene same celice. V Excelu je opisan kot matrika, zato bomo morali izbrisati vse vrednosti.
  • Pravilno moramo izbrati vrstico in vhodno celico stolpca.
  • Tabele podatkov, za razliko od vrtilne tabele, ni treba vsakič osveževati.
  • Z uporabo podatkovnega modela v Excelu ne moremo le izboljšati zmogljivosti, temveč tudi olajšati potrebe po pomnilniku na velikih delovnih listih.
  • Podatkovni modeli prav tako naredijo našo analizo veliko enostavnejšo v primerjavi z uporabo številnih zapletenih formul v delovnem zvezku.

Priporočeni članki

To je vodnik za podatkovni model v Excelu. Tukaj razpravljamo o tem, kako ustvariti podatkovni model v Excelu skupaj s praktičnimi primeri in naloženo predlogo Excela. Ogledate si lahko tudi druge naše predlagane članke -

  1. Formula bar v Excelu
  2. Natisni mreže v Excelu
  3. Oglejte si Window v Excelu
  4. Excel SUMIFS z datumi

Kategorija:

Excel Nasveti