Dátový model v programe Excel Ako vytvoriť dátový model? (s príkladmi)

Čo je údajový model v programe Excel?

Dátový model v programe Excel je typ údajovej tabuľky, v ktorej máme dve alebo viac ako dve tabuľky vzájomné vzťahy prostredníctvom spoločného alebo viacerých údajových radov. V tabuľkách údajových modelov sa údaje z rôznych iných hárkov alebo zdrojov spájajú a vytvárajú jedinečný tabuľka, ktorá má prístup k údajom zo všetkých tabuliek.

Vysvetlenie

• Umožňuje integráciu údajov z viacerých tabuliek vytváraním vzťahov na základe spoločného stĺpca.
• Dátové modely sa používajú transparentne a poskytujú tabuľkové údaje, ktoré je možné použiť v kontingenčnej tabuľke v programe Excel a kontingenčných grafoch v programe Excel. Integruje tabuľky a umožňuje rozsiahlu analýzu pomocou kontingenčných tabuliek, Power Pivot a Power View v Exceli.
• Dátový model umožňuje načítanie údajov do pamäte programu Excel.
• Je uložený v pamäti, kde ho priamo nevidíme. Potom môže byť programu Excel nariadené, aby navzájom spájali údaje pomocou spoločného stĺpca. Časť „Dátový model“ sa týka vzťahu všetkých tabuliek.
• Dátový model má prístup ku všetkým potrebným informáciám, aj keď sú tieto informácie vo viacerých tabuľkách. Po vytvorení dátového modelu má program Excel k dispozícii údaje vo svojej pamäti. Ak sú údaje v pamäti, k údajom je možné pristupovať mnohými spôsobmi.

Príklady

Tu si môžete stiahnuť túto šablónu dátového modelu Excel - Šablóna dátového modelu Excel

Príklad č

Ak máme k dispozícii tri súbory údajov súvisiace s predajcom: prvý obsahuje informácie o výnosoch, druhý obsahuje príjmy obchodníka a tretí obsahuje výdaje obchodníka.

Ak chcete prepojiť tieto tri množiny údajov a vytvoriť s nimi vzťah, vytvoríme dátový model s nasledujúcimi krokmi:

• Preveďte súbory údajov na objekty tabuľky:

Nemôžeme vytvoriť vzťah s bežnými súbormi údajov. Dátový model funguje iba s objektmi tabuliek Excel. Robiť to:

• Krok 1 - Kliknite kamkoľvek do súboru údajov, potom kliknite na kartu „Vložiť“ a potom kliknite na položku „Tabuľka“ v skupine „Tabuľky“.

• Krok 2 - Začiarknite alebo zrušte začiarknutie možnosti: „Moja tabuľka má hlavičky“ a kliknite na OK.

• Krok 3 - Po vybratí novej tabuľky zadajte názov tabuľky do poľa „Názov tabuľky“ v skupine „Nástroje“.

• Krok 4 - Teraz vidíme, že prvá množina údajov sa prevedie na objekt „Tabuľka“. Pri opakovaní týchto krokov pre ďalšie dva súbory údajov vidíme, že sa tiež prevedú na objekty „tabuľky“, ako je uvedené nižšie:

Pridanie objektov „Tabuľka“ do dátového modelu: Prostredníctvom pripojení alebo vzťahov.

Cez Connections

• Vyberte jednu tabuľku, kliknite na kartu „Údaje“ a potom na položku „Pripojenia“.

• Vo výslednom dialógovom okne je ikona „Pridať“. Rozbaľte rozbaľovaciu ponuku „Pridať“ a kliknite na „Pridať do dátového modelu“.

• Vo výslednom dialógovom okne kliknite na „Tabuľky“, potom vyberte jednu z tabuliek a kliknite na „Otvoriť“.

Ak to urobíte, vytvoril by sa dátový model zošita s jednou tabuľkou a nasledovné dialógové okno:

Takže ak zopakujeme tieto kroky aj pre ďalšie dve tabuľky, dátový model bude teraz obsahovať všetky tri tabuľky.

Teraz môžeme vidieť, že všetky tri tabuľky sa zobrazujú v pripojeniach k zošitu.

Prostredníctvom vzťahov

Vytvorenie vzťahu: Keď sú obidva súbory údajov objektmi tabuľky, môžeme medzi nimi vytvoriť vzťah. Robiť to:

• Kliknite na kartu „Údaje“ a potom na položku „Vzťahy“.

• Uvidíme prázdne dialógové okno, pretože tu nie sú žiadne aktuálne spojenia.

• Kliknite na „Nové“ a zobrazí sa ďalšie dialógové okno.

• Rozbaľte rozbaľovacie ponuky „Tabuľka“ a „Súvisiaca tabuľka“: Zobrazí sa dialógové okno „Vytvoriť vzťah“ na výber tabuliek a stĺpcov, ktoré sa majú pre vzťah použiť. V rozšírení „Tabuľky“ vyberte množinu údajov, ktorú chceme nejakým spôsobom analyzovať, a v časti „Súvisiaca tabuľka“ vyberte množinu údajov, ktorá má vyhľadávacie hodnoty.
• Vyhľadávacia tabuľka v programe Excel je menšia tabuľka v prípade jedného až mnohých vzťahov a neobsahuje žiadne opakované hodnoty v spoločnom stĺpci. V rozbaľovacej ponuke „Stĺpec (Zahraničné)“ vyberte spoločný stĺpec v hlavnej tabuľke, v časti „Súvisiaci stĺpec (primárny)“ vyberte spoločný stĺpec v súvisiacej tabuľke.

• Po výbere všetkých týchto štyroch nastavení kliknite na „OK“. Po kliknutí na tlačidlo „OK“ sa zobrazí nasledujúce dialógové okno.

Ak tieto kroky zopakujeme, aby sme súviseli s ďalšími dvoma tabuľkami: Tabuľka výnosov s tabuľkou výdavkov, budú v dátovom modeli tiež súvisiace:

Excel teraz vytvára vzťah v zákulisí kombináciou údajov v dátovom modeli na základe spoločného stĺpca: ID predajcu (v tomto prípade).

Príklad č

Teraz si povedzme vo vyššie uvedenom príklade želáme vytvoriť kontingenčnú tabuľku, ktorá vyhodnotí alebo analyzuje objekty tabuľky:

• Kliknite na „Vložiť“ -> „Kontingenčná tabuľka“.

• Vo výslednom dialógovom okne kliknite na možnosť s uvedením: „Použiť externý zdroj údajov“ a potom kliknite na „Vybrať pripojenie“.

• Vo výslednom dialógovom okne kliknite na „Tabuľky“, vyberte dátový model zošita obsahujúci tri tabuľky a kliknite na „Otvoriť“.

• Vyberte v umiestnení možnosť „Nový pracovný hárok“ a kliknite na „OK“.

• Na table Polia kontingenčnej tabuľky sa zobrazia objekty tabuľky.

• Teraz je možné zodpovedajúcim spôsobom vykonať zmeny v kontingenčnej tabuľke, aby sa mohli podľa potreby analyzovať objekty tabuľky.

Napríklad v tomto prípade, ak chceme zistiť celkový príjem alebo príjem konkrétneho predajcu, vytvorí sa kontingenčná tabuľka takto:

To nesmierne pomáha v prípade modelu / tabuľky obsahujúcej veľké množstvo pozorovaní.

Vidíme teda, že kontingenčná tabuľka okamžite používa dátový model (vyberá ho výberom pripojenia) v pamäti programu Excel na zobrazenie vzťahov medzi tabuľkami.

Na čo treba pamätať

• Pomocou dátového modelu môžeme analyzovať údaje z niekoľkých tabuliek naraz.
• Vytvorením vzťahov s dátovým modelom prekonávame potrebu používania funkcií VLOOKUP, SUMIF, INDEX a MATCH vzorcov, pretože nepotrebujeme dostať všetky stĺpce do jednej tabuľky.
• Keď sa súbory údajov importujú do programu Excel z vonkajších zdrojov, potom sa modely vytvárajú implicitne.
• Vzťahy s tabuľkami je možné vytvoriť automaticky, ak importujeme súvisiace tabuľky, ktoré majú vzťahy primárneho a cudzieho kľúča.
• Pri vytváraní vzťahov by stĺpce, ktoré spájame v tabuľkách, mali mať rovnaký dátový typ.
• Pomocou kontingenčných tabuliek vytvorených pomocou dátového modelu môžeme pridať aj priečniky a kontingenčné tabuľky rozdeliť na ľubovoľné pole, ktoré chceme.
• Výhodou dátového modelu oproti funkciám LOOKUP () je, že vyžaduje podstatne menej pamäte.
• Excel 2013 podporuje iba jeden na jeden alebo jeden na veľa vzťahov, tj. Jedna z tabuliek nesmie mať duplicitné hodnoty v stĺpci, na ktorý odkazujeme.