Datový model v Excelu Vytváření tabulek pomocí funkce datového modelu

• Úvod do datového modelu v Excelu

Datový model Excel (obsah)

• Úvod do datového modelu v Excelu
• Jak vytvořit datový model v Excelu?

Úvod do datového modelu v Excelu

Funkce datového modelu Excelu umožňuje snadné vytváření vztahů mezi snadným reportováním a jejich datovými soubory na pozadí. To výrazně usnadňuje analýzu dat. Umožňuje integraci dat z celé řady tabulek rozložených na více listech tím, že se jednoduše vytvoří vztahy mezi odpovídajícími sloupci. Funguje zcela za scénou a výrazně zjednodušuje funkce hlášení, jako je kontingenční tabulka atd.

V našem článku se pokusíme ukázat, jak vytvořit kontingenční tabulku ze dvou tabulek pomocí funkce Datový model, čímž se vytvoří vztah mezi dvěma objekty tabulky a vytvoří se kontingenční tabulka.

Jak vytvořit datový model v Excelu?

Podívejme se, jak vytvořit datový model v Excelu, s několika příklady.

Tuto šablonu datového modelu Excel si můžete stáhnout zde - šablonu datového modelu Excel

Příklad č. 1

• Máme seznam produktů a pro každý produkt máme regálový kód. Potřebujeme tabulku, kde budeme mít popis regálu spolu s policovými kódy. Jak tedy začleníme popisy regálů proti každému kódu regálu? Možná by se mnozí z nás uchýlili k použití VLOOKUPu zde, ale zcela odstraníme potřebu používat VLOOKUP zde pomocí Excel Data Model.

• Tabulka vlevo je tabulka dat a tabulka vpravo je vyhledávací tabulka. Jak je vidět z dat, je možné vytvořit vztah založený na společných sloupcích.

• Nyní je datový model kompatibilní pouze s objekty tabulky. Proto může být někdy nutné převést datové sady na objekty tabulky. Postupujte podle následujících kroků.

1. Klikněte levým tlačítkem myši kdekoli v datové sadě.
2. Klikněte na kartu Vložit a přejděte do tabulky ve skupině Tabulky nebo jednoduše stiskněte Ctrl + T.
3. Zrušte zaškrtnutí nebo zaškrtněte políčko Moje tabulka má záhlaví. V našem příkladu má skutečně záhlaví. Klikněte na OK.
4. I když se stále zaměřujeme na novou tabulku, musíme do pole Název (vlevo od lišty vzorců) uvést název, který má smysl.

V našem příkladu jsme pojmenovali tabulku Personál.

• Nyní musíme udělat stejný postup pro vyhledávací tabulku a pojmenovat ji Shelf Code.

Vytvoření vztahu

Nejprve přejdeme na kartu Data a poté v podskupině Nástroje dat vybereme relace. Jakmile klikneme na možnost Vztahy, na začátku, protože neexistuje žádný vztah, nebudeme mít nic.

Nejprve klikneme na Nový pro vytvoření vztahu. Nyní budeme muset z rozevíracího seznamu uvést názvy primárních a vyhledávacích tabulek a poté také zmínit sloupec, který je mezi oběma tabulkami společný, abychom mohli stanovit vztah mezi oběma tabulkami, z rozevíracího seznamu sloupců.

• Nyní je primární tabulka tabulka, která obsahuje data. Je to primární tabulka dat - tabulka5. Na druhé straně, Související tabulka je tabulka, která obsahuje vyhledávací data - je to naše vyhledávací tabulka ShelfCodesTable. Primární tabulka je ta, která je analyzována na základě vyhledávací tabulky, která obsahuje vyhledávací data, díky nimž budou vykazovaná data nakonec smysluplnější.

• Společným sloupcem mezi dvěma tabulkami je tedy sloupec Kód police. To je to, co jsme použili k vytvoření vztahu mezi oběma tabulkami. Pokud jde o sloupce, sloupec (cizí) je ten, který odkazuje na tabulku dat, kde mohou být duplicitní hodnoty. Na druhou stranu, související sloupec (primární) odkazuje na sloupec ve vyhledávací tabulce, kde máme jedinečné hodnoty. Jednoduše nastavujeme pole pro vyhledávání hodnot z vyhledávací tabulky v datové tabulce.
• Jakmile to nastavíme, Excel vytvoří vztah mezi dvěma za scénou. Integruje data a vytváří datový model založený na společném sloupci. Nejedná se pouze o požadavky na paměť, ale také o mnoho rychlejší než použití VLOOKUP ve velkých sešitech. Po definování datového modelu by Excel považoval tyto objekty za tabulky datového modelu místo tabulky pracovního listu.
• Nyní, abychom viděli, co Excel dělal, můžeme kliknout na Spravovat datové modely v datech -> Nástroje dat.

• Také můžeme získat schematické znázornění datového modelu změnou pohledu. Klikneme na možnost Zobrazit. Tím se otevře možnosti zobrazení. Poté vybereme zobrazení diagramu. Pak uvidíme schematické znázornění, znázorňující dvě tabulky a vztah mezi nimi, tj. Společný sloupec - Shelf Code.

• Výše uvedený diagram ukazuje vzájemný vztah mezi jedinečnými hodnotami tabulky vyhledávací tabulky a tabulkou dat se zdvojenými hodnotami.
• Nyní budeme muset vytvořit kontingenční tabulku. K tomu přejdeme na kartu Vložit a poté klikneme na možnost Kontingenční tabulka.

V dialogovém okně Vytvoření kontingenční tabulky v kontingenční tabulce vybereme zdroj jako „Použít datový model tohoto sešitu“.

• Tím se vytvoří kontingenční tabulka a můžeme vidět, že obě zdrojové tabulky jsou k dispozici ve zdrojové sekci.

• Nyní vytvoříme kontingenční tabulku s počtem osob, které mají odložené předměty.

• V sekci Řádky vybereme Personál z tabulky 5 (tabulka dat), následuje popis (vyhledávací tabulka).

• Nyní přetáhneme kód police z tabulky 5 do sekce Hodnoty.

• Nyní přidáme měsíce z tabulky 5 do sekce Řádky.

• Nebo bychom mohli přidat měsíce jako filtr a přidat je do sekce Filtry.

Příklad č. 2

• Nyní máme pana Basu provozující továrnu s názvem Basu Corporation. Pan Basu se snaží odhadnout tržby za rok 2019 na základě údajů z roku 2018.
• Máme tabulku, kde máme příjmy za rok 2018 a následné příjmy na různých přírůstkových úrovních.

• Máme tedy příjmy za rok 2018 - 1, 5 milionu USD a očekávaný minimální růst v následujícím roce je 12%. Pan Basu chce tabulku, která zobrazí příjmy na různých přírůstkových úrovních.
• Pro rok 2019 vytvoříme následující tabulku pro projekce na různých přírůstkových úrovních.

• Nyní poskytneme první řádek Příjmy odkaz na odhadovaný minimální příjem za rok 2019, tj. 1, 68 $ M.

• Po použití vzorce je odpověď uvedena níže.

• Nyní vybereme celou tabulku, tj. D2: E12 a poté přejdeme na Data -> Prognóza -> What-If Analysis -> Data Table.

• Otevře se dialogové okno Tabulka dat. Zde zadáme minimální procentuální přírůstek z buňky B4 do buňky Vstup sloupce. Důvodem je to, že naše předpokládané odhadované procenta růstu v tabulce jsou uspořádány sloupcovým způsobem.

• Jakmile klikneme na tlačítko OK, analýza What-If automaticky naplní tabulku plánovanými výnosy v různých dílčích procentech.

Příklad č. 3

• Předpokládejme, že máme stejný scénář jako výše, kromě toho, že nyní máme také v úvahu jinou osu. Předpokládejme, že kromě zobrazení plánovaných výnosů v roce 2019 na základě údajů z roku 2018 a minimální očekávané míry růstu máme nyní také odhadovanou diskontní sazbu.

• Nejprve budeme mít níže uvedenou tabulku.

• Nyní uvedeme odkaz na minimální plánovaný příjem pro rok 2019, tj. Buňka B5 až buňka D8.

• Nyní vybereme celou tabulku, tj. D8: J18, a poté přejděte na Data -> Prognóza -> What-If Analysis -> Data Table.

• Otevře se dialogové okno Tabulka dat. Zde zadáme minimální procentuální přírůstek z buňky B3 do buňky Vstup sloupce. Důvodem je to, že naše předpokládané odhadované procenta růstu v tabulce jsou uspořádány sloupcovým způsobem. Nyní také dodatečně zadáme minimální slevové procento z buňky B4 do buňky Řádkový vstup. Důvodem je to, že naše předpokládaná procenta slev v tabulce jsou uspořádána po řádcích.

• Klikněte na OK. To umožní, aby analýza What-If automaticky naplnila tabulku předpokládanými příjmy při různých přírůstkových procentech podle procent slev.

Důležité informace o datovém modelu v Excelu

• Po úspěšném výpočtu hodnot z datové tabulky nebude fungovat jednoduché Zpět, tj. Ctrl + Z. Je však možné ručně vymazat hodnoty z tabulky.
• Není možné odstranit jednu buňku z tabulky. V Excelu je popisován jako pole interně, proto budeme muset vymazat všechny hodnoty.
• Musíme správně vybrat řádkovou vstupní buňku a sloupcovou vstupní buňku.
• Datová tabulka, na rozdíl od kontingenční tabulky, nemusí být pokaždé obnovována.
• Pomocí datového modelu v Excelu můžeme nejen zlepšit výkon, ale také snadno zvládnout požadavky na paměť ve velkých listech.
• Datové modely také usnadňují naši analýzu ve srovnání s použitím řady komplikovaných vzorců v celém sešitu.

Doporučené články

Toto je průvodce datovým modelem v Excelu. Zde diskutujeme o tom, jak vytvořit datový model v Excelu spolu s praktickými příklady a šablonou Excel ke stažení. Můžete si také prohlédnout naše další doporučené články -

1. Vzorec Bar v Excelu
2. Tisk rastrových čar v Excelu
3. Okno sledování v Excelu
4. Excel SUMIFS s daty