Podatkovni model v Excelu | Kako ustvariti podatkovni model? (s primeri)

Kaj je podatkovni model v Excelu?

Podatkovni model v Excelu je vrsta podatkovne tabele, pri kateri imamo dve ali več tabel medsebojne povezave prek skupne ali več podatkovnih serij, v tabelah podatkovnih modelov in podatki iz različnih drugih listov ali virov sestavijo v edinstveno tabela, ki ima dostop do podatkov iz vseh tabel.

Pojasnilo

  • Omogoča integracijo podatkov iz več tabel z ustvarjanjem odnosov na podlagi skupnega stolpca.
  • Podatkovni modeli se uporabljajo pregledno in zagotavljajo tabelarne podatke, ki jih je mogoče uporabiti v vrtilni tabeli v Excelu in vrtilnih grafikonih v Excelu. Vključuje tabele in omogoča obsežno analizo s pomočjo vrtilnih tabel, Power Pivot in Power View v Excelu.
  • Podatkovni model omogoča nalaganje podatkov v Excelov pomnilnik.
  • Shrani se v spomin tam, kjer ga ne moremo neposredno videti. Nato lahko Excelu naročite, naj podatke poveže med seboj s skupnim stolpcem. Del podatkovnega modela „Model“ se nanaša na medsebojno povezanost vseh tabel.
  • Podatkovni model lahko dostopa do vseh informacij, ki jih potrebuje, tudi če so informacije v več tabelah. Po izdelavi podatkovnega modela ima Excel na voljo podatke v svojem pomnilniku. S podatki v pomnilniku je do njih mogoče dostopati na več načinov.

Primeri

To predlogo podatkovnega modela Excel lahko prenesete tukaj - Predloga podatkovnega modela Excel

Primer # 1

Če imamo tri nabore podatkov, povezane s prodajalcem: prvi vsebuje podatke o prihodkih, drugi vsebuje dohodek prodajalca in tretji vsebuje stroške prodajalca.

Za povezavo teh treh podatkovnih nizov in vzpostavitev povezave z njimi izdelamo podatkovni model z naslednjimi koraki:

  • Pretvorite nabore podatkov v predmete tabele:

Z običajnimi nabori podatkov ne moremo vzpostaviti odnosa. Podatkovni model deluje samo z objekti Excelove tabele. Storiti to:

  • 1. korak - Kliknite kjer koli znotraj nabora podatkov, nato zavihek »Vstavi« in nato v razpredelnici »Tabele« kliknite »Tabela«.

  • 2. korak - potrdite ali počistite možnost: »Moja tabela ima glave« in kliknite V redu.

  • 3. korak - Z novo izbrano tabelo vnesite ime tabele v "Ime tabele" v skupini "Orodja".

  • 4. korak - Zdaj lahko vidimo, da se prvi nabor podatkov pretvori v objekt 'Tabela'. Pri ponavljanju teh korakov za druga dva nabora podatkov vidimo, da se tudi pretvorijo v predmete 'Tabela', kot je prikazano spodaj:

Dodajanje predmetov 'Tabela' v podatkovni model: prek povezav ali odnosov.

Preko povezav

  • Izberite eno tabelo in kliknite zavihek "Podatki" in nato "Povezave".

  • V pogovornem oknu, ki se odpre, je ikona »Dodaj«. Razširite spustni meni »Dodaj« in kliknite »Dodaj v podatkovni model«.

  • V nastalem pogovornem oknu kliknite "Tabele", nato izberite eno od tabel in kliknite "Odpri".

Po tem bi se ustvaril podatkovni model delovnega zvezka z eno tabelo in pogovorno okno se prikaže na naslednji način:

Če ponovimo te korake tudi za drugi dve tabeli, bo podatkovni model zdaj vseboval vse tri tabele.

Zdaj lahko vidimo, da so vse tri tabele prikazane v povezavah z delovnim zvezkom.

Preko odnosov

Ustvari razmerje: Ko sta oba nabora podatkov predmeta Tabela, lahko med njima ustvarimo razmerje. Storiti to:

  • Kliknite zavihek "Podatki" in nato "Povezave".

  • Videli bomo prazno pogovorno okno, ker trenutno ni povezav.

  • Kliknite »Novo« in prikaže se drugo pogovorno okno.

  • Razširite spustna menija »Tabela« in »Povezana tabela«: prikaže se pogovorno okno »Ustvari odnos«, da izberete tabele in stolpce, ki jih želite uporabiti za odnos. V razširitvi 'Tabele' izberite nabor podatkov, ki ga želimo na nek način analizirati, v 'Povezani tabeli' pa nabor podatkov, ki ima iskalne vrednosti.
  • Iskalna tabela v Excelu je manjša tabela v primeru enega do več razmerij in ne vsebuje ponavljajočih se vrednosti v skupnem stolpcu. V razširitvi 'Stolpec (tuj)' izberite skupni stolpec v glavni tabeli, v 'Povezani stolpec (primarni)' izberite skupni stolpec v povezani tabeli.

  • Ko so izbrane vse te štiri nastavitve, kliknite "V redu". Ob kliku na 'V redu' se odpre pogovorno okno, kot sledi.

Če ponovimo te korake za povezovanje drugih dveh tabel: Tabela prihodkov s tabelo odhodkov, potem se v podatkovnem modelu tudi povežejo na naslednji način:

Excel zdaj ustvari odnos v ozadju s kombiniranjem podatkov v podatkovnem modelu na podlagi skupnega stolpca: ID prodajalca (v tem primeru).

2. primer

Zdaj, recimo, v zgornjem primeru želimo ustvariti vrtilno tabelo, ki ovrednoti ali analizira predmete tabele:

  • Kliknite "Vstavi" -> "Vrtilna tabela".

  • V pogovornem oknu, ki se prikaže, kliknite možnost z navedbo: 'Uporabi zunanji vir podatkov' in nato 'Izberi povezavo'.

  • V nastalem pogovornem oknu kliknite 'Tabele' in izberite podatkovni model delovnega zvezka, ki vsebuje tri tabele, in kliknite 'Odpri'.

  • Na mestu izberite možnost »Nov delovni list« in kliknite »V redu«.

  • Podokno Polja vrtilne tabele bo prikazalo predmete tabele.

  • Zdaj je mogoče v vrtilni tabeli spremeniti spremembe, da se po potrebi analizirajo predmeti tabele.

Na primer, v tem primeru, če želimo najti skupni prihodek ali prihodek za določenega prodajalca, se ustvari vrtilna tabela, kot sledi:

To je v neizmerno pomoč v primeru modela / tabele, ki vsebuje veliko število opažanj.

Tako lahko vidimo, da vrtilna tabela takoj uporabi podatkovni model (izbere ga tako, da izbere povezavo) v Excelovem pomnilniku za prikaz razmerij med tabelami.

Stvari, ki si jih je treba zapomniti

  • Z uporabo podatkovnega modela lahko analiziramo podatke iz več tabel hkrati.
  • Z ustvarjanjem odnosov s podatkovnim modelom presežemo potrebo po uporabi formul VLOOKUP, SUMIF, INDEX in MATCH, saj nam ni treba dobiti vseh stolpcev v eni tabeli.
  • Ko se nabori podatkov v Excel uvozijo iz zunanjih virov, se modeli ustvarijo implicitno.
  • Razmerja tabel je mogoče ustvariti samodejno, če uvozimo sorodne tabele s povezavami primarnega in tujega ključa.
  • Med ustvarjanjem razmerij morajo imeti stolpci, ki jih povezujemo v tabelah, isti podatkovni tip.
  • Z vrtilnimi tabelami, ustvarjenimi z podatkovnim modelom, lahko dodamo tudi rezalnike in vrtilne tabele razrežemo na poljubno polje.
  • Prednost podatkovnega modela pred funkcijami LOOKUP () je, da zahteva bistveno manj pomnilnika.
  • Excel 2013 podpira samo ena do ena ali ena do več razmerij, tj. Ena od tabel ne sme imeti podvojenih vrednosti v stolpcu, na katerega se povezujemo.