Dátový model Excel (obsah)

  • Úvod do dátového modelu v Exceli
  • Ako vytvoriť dátový model v Exceli?

Úvod do dátového modelu v Exceli

Funkcia dátového modelu programu Excel umožňuje ľahké vytváranie vzťahov medzi ľahkým vykazovaním a ich súbormi údajov na pozadí. Uľahčuje to analýzu údajov. Umožňuje integráciu údajov z veľkého množstva tabuliek rozložených na viacerých pracovných hárkoch jednoduchým vytváraním vzťahov medzi zodpovedajúcimi stĺpcami. Funguje to úplne v pozadí a výrazne zjednodušuje funkcie vykazovania, ako je napríklad kontingenčná tabuľka atď.

V našom článku sa pokúsime ukázať, ako vytvoriť kontingenčnú tabuľku z dvoch tabuliek pomocou funkcie Data Model, čím sa vytvorí vzťah medzi dvoma objektmi tabuľky, a tým sa vytvorí kontingenčná tabuľka.

Ako vytvoriť dátový model v Exceli?

Poďme pochopiť, ako vytvoriť dátový model v Exceli s niekoľkými príkladmi.

Túto šablónu dátového modelu Excel si môžete stiahnuť tu - šablónu dátového modelu Excel

Príklad č. 1

  • Máme zoznam produktov a pre každý produkt máme regálový kód. Potrebujeme tabuľku, v ktorej budeme mať popis regálov spolu s kódmi regálov. Ako teda zahrnieme popisy policových prvkov do každého kódu policových jednotiek? Možno by sa mnohí z nás uchýlili k používaniu VLOOKUP tu, ale úplne odstránime potrebu používať VLOOKUP tu pomocou Excel Data Model.

  • Tabuľka vľavo je tabuľka údajov a tabuľka vpravo je vyhľadávacia tabuľka. Ako vidíme z údajov, je možné vytvoriť vzťah založený na spoločných stĺpcoch.

  • Teraz je dátový model kompatibilný iba s tabuľkovými objektmi. Preto môže byť niekedy potrebné prevádzať množiny údajov na tabuľkové objekty. Postupujte podľa nasledujúcich krokov.
  1. Kliknite ľavým tlačidlom myši kdekoľvek v množine údajov.
  2. Kliknite na kartu Vložiť a prejdite do tabuľky v skupine Tabuľky alebo jednoducho stlačte klávesy Ctrl + T.
  3. Zrušte začiarknutie alebo začiarknite políčko Tabuľka má záhlavie. V našom príklade má skutočne hlavičku. Kliknite na tlačidlo OK.
  4. Aj keď sa stále zameriavame na novú tabuľku, musíme do poľa Názov (naľavo od lišty vzorcov) uviesť názov, ktorý má zmysel.

V našom príklade sme nazvali tabuľku Personál.

  • Teraz musíme urobiť rovnaký postup aj pre vyhľadávaciu tabuľku a pomenovať ju Shelf Code.

Vytváranie vzťahov

Najprv prejdeme na kartu Údaje a potom v podskupine Nástroje údajov vyberieme Vzťahy. Keď klikneme na možnosť Vzťahy, na začiatku, pretože neexistuje žiadny vzťah, nebudeme mať nič.

Najprv klikneme na Nový, aby sme vytvorili vzťah. Teraz budeme musieť z rozbaľovacieho zoznamu uviesť názvy primárnych a vyhľadávacích tabuliek a potom tiež spomenúť stĺpec, ktorý je medzi týmito dvoma tabuľkami spoločný, aby sme mohli zistiť vzťah medzi týmito dvoma tabuľkami z rozbaľovacieho zoznamu. stĺpcov.

  • Teraz je primárnou tabuľkou tabuľka, ktorá obsahuje údaje. Je to tabuľka primárnych údajov - tabuľka5. Na druhej strane, príbuzná tabuľka je tabuľka, ktorá obsahuje vyhľadávacie údaje - je to naša vyhľadávacia tabuľka ShelfCodesTable. Primárna tabuľka je tá, ktorá je analyzovaná na základe vyhľadávacej tabuľky, ktorá obsahuje vyhľadávacie údaje, vďaka ktorým budú vykazované údaje na konci zmysluplnejšie.

  • Spoločným stĺpcom medzi týmito dvoma tabuľkami je teda stĺpec Kód police. Toto sme použili na vytvorenie vzťahu medzi týmito dvoma tabuľkami. Pokiaľ ide o stĺpce, stĺpec (cudzí) je stĺpec, ktorý odkazuje na tabuľku údajov, kde môžu byť duplicitné hodnoty. Na druhej strane, súvisiaci stĺpec (primárny) odkazuje na stĺpec vo vyhľadávacej tabuľke, kde máme jedinečné hodnoty. Jednoducho nastavujeme pole na vyhľadávanie hodnôt z vyhľadávacej tabuľky v tabuľke údajov.
  • Keď to nastavíme, Excel vytvorí vzťah medzi dvoma za scénou. Integruje údaje a vytvára dátový model založený na spoločnom stĺpci. Nejde len o ľahšie pochopenie požiadaviek na pamäť, ale aj o oveľa rýchlejší postup ako pri použití VLOOKUP vo veľkých zošitoch. Po definovaní dátového modelu by Excel spracovával tieto objekty ako tabuľky dátového modelu namiesto tabuľky pracovného hárka.
  • Teraz, aby sme videli, čo Excel robil, môžeme kliknúť na Spravovať dátové modely v údajoch -> dátové nástroje.

  • Môžeme tiež získať schematické znázornenie údajového modelu zmenou pohľadu. Klikneme na možnosť Zobraziť. Tým sa otvoria možnosti zobrazenia. Potom vyberieme zobrazenie diagramu. Potom uvidíme schematické znázornenie, znázorňujúce dve tabuľky a vzťah medzi nimi, tj spoločný stĺpec - Policový kód.

  • Diagram vyššie ukazuje vzájomný vzťah medzi jedinečnými hodnotami vyhľadávacej tabuľky a dátovou tabuľkou s duplikovanými hodnotami.
  • Teraz budeme musieť vytvoriť kontingenčnú tabuľku. Ak to chceme urobiť, prejdeme na kartu Vložiť a potom klikneme na možnosť Kontingenčná tabuľka.

V dialógovom okne Vytvorenie kontingenčnej tabuľky kontingenčnej tabuľky vyberieme zdroj ako „Použiť dátový model tohto zošita“.

  • Tým sa vytvorí kontingenčná tabuľka a môžeme vidieť, že obe zdrojové tabuľky sú k dispozícii v zdrojovej časti.

  • Teraz vytvoríme pivotnú tabuľku ukazujúcu počet každej osoby, ktorá má uložené veci.

  • V sekcii Riadky vyberieme Personál z tabuľky 5 (tabuľka s údajmi) a následne popisu (vyhľadávacia tabuľka).

  • Teraz pretiahneme kód police z tabuľky 5 do sekcie Hodnoty.

  • Teraz pridáme mesiace z tabuľky 5 do sekcie Riadky.

  • Alebo by sme mohli pridať mesiace ako filter a pridať ich do sekcie Filtre.

Príklad č. 2

  • Teraz máme pána Basu v prevádzke s názvom Basu Corporation. Basu sa pokúša odhadnúť príjmy na rok 2019 na základe údajov z roku 2018.
  • Máme tabuľku, kde máme príjmy za rok 2018 a následné príjmy na rôznych prírastkových úrovniach.

  • Máme teda príjem za rok 2018 - 1, 5 milióna dolárov a minimálny rast, ktorý sa očakáva v nasledujúcom roku, je 12%. Basu chce tabuľku, ktorá bude zobrazovať príjmy na rôznych prírastkových úrovniach.
  • V roku 2019 vytvoríme nasledujúcu tabuľku pre projekcie na rôznych prírastkových úrovniach.

  • Teraz poskytneme prvý riadok Príjmy odkaz na odhadovaný minimálny príjem za rok 2019, tj 1, 68 USD.

  • Po použití vzorca je odpoveď uvedená nižšie.

  • Teraz vyberieme celú tabuľku tj D2: E12 a potom prejdeme na Data -> Prognóza -> What-If Analysis -> Data Table.

  • Otvorí sa dialógové okno Tabuľka údajov. Tu zadáme minimálne percento prírastku z bunky B4 do bunky Vstup stĺpca. Dôvodom je to, že naše predpokladané odhadované percentá rastu v tabuľke sú usporiadané stĺpcovým spôsobom.

  • Keď klikneme na tlačidlo OK, analýza What-If Analysis automaticky naplní tabuľku projektovanými výnosmi pri rôznych prírastkových percentách.

Príklad č. 3

  • Teraz predpokladajme, že máme rovnaký scenár ako vyššie, okrem toho, že teraz musíme zvážiť aj ďalšiu os. Predpokladajme, že okrem vykázania plánovaných výnosov v roku 2019 na základe údajov z roku 2018 a minimálnej očakávanej miery rastu máme teraz aj odhadovanú diskontnú sadzbu.

  • Najprv budeme mať tabuľku uvedenú nižšie.

  • Teraz uvádzame odkaz na minimálny plánovaný príjem na rok 2019, tj bunka B5 do bunky D8.

  • Teraz vyberieme celú tabuľku tj D8: J18 a potom prejdeme na Data -> Prognóza -> What-If Analysis -> Data Table.

  • Otvorí sa dialógové okno Tabuľka údajov. Tu zadáme minimálne percento prírastku z bunky B3 do bunky Vstup stĺpca. Dôvodom je to, že naše predpokladané odhadované percentá rastu v tabuľke sú usporiadané stĺpcovým spôsobom. Teraz tiež dodatočne zadáme minimálne percento zľavy z bunky B4 v bunke Riadkový vstup. Dôvodom je to, že naše predpokladané percentuálne zľavy v tabuľke sú usporiadané riadkovým spôsobom.

  • Kliknite na tlačidlo OK. Vďaka tomu bude analýza What-If automaticky vyplniť tabuľku s predpokladaným príjmom pri rôznych prírastkových percentách podľa percentuálnej hodnoty zľavy.

Pamätajte na dátový model v Exceli

  • Po úspešnom výpočte hodnôt z tabuľky s údajmi nebude jednoduché Undo tj Ctrl + Z fungovať. Je však možné manuálne vymazať hodnoty z tabuľky.
  • Nie je možné odstrániť jednu bunku z tabuľky. V Exceli je interne opísaný ako pole, preto budeme musieť všetky hodnoty vymazať.
  • Musíme správne vybrať riadkovú vstupnú bunku a stĺpcovú vstupnú bunku.
  • Na rozdiel od kontingenčnej tabuľky sa tabuľka údajov nemusí zakaždým obnovovať.
  • Použitím dátového modelu v Exceli môžeme nielen zvýšiť výkon, ale tiež si môžeme ľahko zapamätať požiadavky na pamäť vo veľkých pracovných hárkoch.
  • Dátové modely tiež uľahčujú našu analýzu v porovnaní s použitím množstva komplikovaných vzorcov v celom zošite.

Odporúčané články

Toto je sprievodca dátovým modelom v Exceli. Tu diskutujeme o tom, ako vytvoriť dátový model v Exceli spolu s praktickými príkladmi a stiahnuteľnou šablónou Excel. Môžete si tiež prečítať naše ďalšie navrhované články -

  1. Vzorcový pruh v Exceli
  2. Tlač Gridlines v Exceli
  3. Okno sledovania v Exceli
  4. Excel SUMIFS s dátumami

Kategórie:

Tipy programu Excel