Model podataka u programu Excel | Kako stvoriti model podataka? (s primjerima)

Što je podatkovni model u Excelu?

Podatkovni model u Excelu vrsta je tablice podataka u kojoj smo dvije ili više od dvije tablice međusobno u vezi kroz zajedničku ili više serija podataka, u tablicama podatkovnih modela i podaci iz raznih drugih listova ili izvora okupljaju se da bi stvorili jedinstvenu tablica koja može imati pristup podacima iz svih tablica.

Obrazloženje

  • Omogućuje integriranje podataka iz više tablica stvaranjem odnosa na temelju zajedničkog stupca.
  • Modeli podataka koriste se transparentno, pružajući tablične podatke koji se mogu koristiti u zaokretnoj tablici u Excelu i zaokretnim grafikonima u Excelu. Integrira tablice omogućujući opsežnu analizu pomoću zaokretnih tablica, Power Pivota i Power Viewa u Excelu.
  • Model podataka omogućuje učitavanje podataka u Excelovu memoriju.
  • Sprema se u memoriju tamo gdje je ne možemo izravno vidjeti. Tada se Excelu može naložiti da međusobno povezuje podatke pomoću zajedničkog stupca. Dio „Model“ modela podataka odnosi se na međusobni odnos svih tablica.
  • Podatkovni model može pristupiti svim potrebnim informacijama čak i kad su informacije u više tablica. Nakon izrade podatkovnog modela, Excel ima podatke na raspolaganju u svojoj memoriji. S podacima u svojoj memoriji, podacima se može pristupiti na više načina.

Primjeri

Ovaj predložak modela podataka modela Excel možete preuzeti ovdje - predložak modela podataka modela Excel

Primjer # 1

Ako imamo tri skupa podataka koji se odnose na prodavača: prvi koji sadrži podatke o prihodima, drugi koji sadrži prihod prodavača i treći koji sadrži troškove prodavača.

Da bismo povezali ova tri skupa podataka i uspostavili vezu s njima, izrađujemo podatkovni model sa sljedećim koracima:

  • Pretvori skupove podataka u objekte tablice:

Ne možemo stvoriti odnos s uobičajenim skupovima podataka. Podatkovni model radi samo s objektima Excel tablica. Uraditi ovo:

  • Korak 1 - Kliknite bilo gdje unutar skupa podataka, zatim kliknite karticu "Umetni", a zatim kliknite "Tablica" u grupi "Tablice".

  • Korak 2 - Označite ili poništite opciju: 'Moja tablica ima zaglavlja' i kliknite U redu.

  • Korak 3 - Odabirom nove tablice unesite naziv tablice u "Naziv tablice" u grupi "Alati".

  • Korak 4 - Sad možemo vidjeti da se prvi skup podataka pretvara u objekt 'Tablica'. Ponavljajući ove korake za druga dva skupa podataka, vidimo da se i oni pretvaraju u objekte 'Tablice' kao što je prikazano u nastavku:

Dodavanje objekata 'Tablice' u model podataka: putem veza ili veza.

Putem veza

  • Odaberite jednu tablicu i kliknite karticu 'Podaci', a zatim kliknite 'Veze'.

  • U rezultirajućem dijaloškom okviru nalazi se ikona "Dodaj". Proširite padajući meni "Dodaj" i kliknite "Dodaj u model podataka".

  • Kliknite "Tablice" u rezultirajućem dijaloškom okviru, a zatim odaberite jednu od tablica i kliknite "Otvori".

Nakon toga stvorit će se model podataka radne knjige s jednom tablicom i pojavit će se dijaloški okvir kako slijedi:

Dakle, ako ponovimo ove korake i za druge dvije tablice, podatkovni model sad će sadržavati sve tri tablice.

Sada možemo vidjeti da se sve tri tablice pojavljuju u Workbook Connections.

Preko odnosa

Stvorite odnos: Kad su oba skupa podataka objekti tablice, možemo stvoriti odnos između njih. Uraditi ovo:

  • Kliknite karticu "Podaci", a zatim "Veze".

  • Vidjet ćemo prazan dijaloški okvir jer nema trenutačnih veza.

  • Kliknite "Novo" i pojavit će se drugi dijaloški okvir.

  • Proširite padajuće padajuće "Tablice" i "Povezane tablice": Pojavit će se dijaloški okvir "Stvori odnos" za odabir tablica i stupaca koji će se koristiti za vezu. U proširenju "Tablice" odaberite skup podataka koji želimo na neki način analizirati, a u "Povezana tablica" odaberite skup podataka koji ima vrijednosti pretraživanja.
  • Pregledna tablica u Excelu manja je tablica u slučaju odnosa jedan do više i ne sadrži ponovljene vrijednosti u zajedničkom stupcu. U proširenju "Stupac (inozemni)" odaberite zajednički stupac u glavnoj tablici, u "Povezani stupac (primarni)" odaberite zajednički stupac u povezanoj tablici.

  • Kada su odabrane sve ove četiri postavke, kliknite "U redu". Dijaloški okvir pojavit će se na sljedeći način klikom na "U redu".

Ako ponovimo ove korake kako bismo povezali druge dvije tablice: Tablica prihoda s tablicom Rashodi, tada se oni također povezuju u podatkovnom modelu na sljedeći način:

Excel sada stvara odnos iza scene kombinirajući podatke u podatkovnom modelu na temelju zajedničkog stupca: ID prodavača (u ovom slučaju).

Primjer # 2

Recimo sada u gornjem primjeru da želimo stvoriti zaokretnu tablicu koja procjenjuje ili analizira objekte tablice:

  • Kliknite "Umetni" -> "Zaokretna tablica".

  • U rezultirajućem dijaloškom okviru kliknite opciju navodeći: 'Upotrijebi vanjski izvor podataka', a zatim kliknite 'Odaberi vezu'.

  • Kliknite "Tablice" u rezultirajućem dijaloškom okviru i odaberite model podataka radne knjige koji sadrži tri tablice i kliknite "Otvori".

  • Odaberite opciju "Novi radni list" na mjestu i kliknite "U redu".

  • U oknu Polja zaokretne tablice prikazat će se objekti tablice.

  • Sada se izmjene u zaokretnoj tablici mogu izvršiti u skladu s tim kako bi se analizirali objekti tablice prema potrebi.

Na primjer, u ovom slučaju, ako želimo pronaći ukupan prihod ili prihod određenog prodavača, tada se kreira zaokretna tablica kako slijedi:

Ovo je od velike pomoći u slučaju modela / tablice koji sadrži velik broj opažanja.

Dakle, možemo vidjeti da izvedena tablica trenutno koristi model podataka (odabirom odabirom veze) u Excel memoriji za prikaz odnosa između tablica.

Stvari koje treba zapamtiti

  • Koristeći podatkovni model, možemo analizirati podatke iz nekoliko tablica odjednom.
  • Stvaranjem odnosa s podatkovnim modelom nadmašujemo potrebu za korištenjem formula VLOOKUP, SUMIF, INDEX i MATCH jer ne moramo dobiti sve stupce unutar jedne tablice.
  • Kada se skupovi podataka u Excel uvoze iz vanjskih izvora, tada se modeli izrađuju implicitno.
  • Odnosi tablica mogu se automatski stvoriti ako uvozimo povezane tablice koje imaju veze primarnog i stranog ključa.
  • Tijekom stvaranja odnosa, stupci koje povezujemo u tablicama trebali bi imati isti tip podataka.
  • Pomoću zaokretnih tablica kreiranih s podatkovnim modelom možemo dodati i rezače i razrezati zaokretne tablice na bilo kojem polju koje želimo.
  • Prednost podatkovnog modela u odnosu na funkcije LOOKUP () je u tome što zahtijeva znatno manje memorije.
  • Excel 2013 podržava samo jedan prema jedan ili jedan prema više odnosa, tj. Jedna od tablica ne smije imati dvostruke vrijednosti u stupcu na koji povezujemo.