Vytvoření datového modelu v Excelu

Datový model umožňuje integrovat data z více tabulek a efektivně vytvořit relační zdroj dat v excelovém sešitu. V Excelu se datové modely používají transparentně a poskytují tabulková data používaná v kontingenčních tabulkách a kontingenčních grafech. Datový model se vizualizuje jako kolekce tabulek v seznamu polí a většinou ani nevíte, že tam je.

Než začnete pracovat s datovým modelem, potřebujete získat nějaká data. K tomu použijeme prostředí Získat & transformaci (Power Query), takže můžete chtít udělat krok zpět a watch video nebo postupovat podle našeho výukového průvodce získáním transformace & a Power Pivotem.

Kde je Power Pivot?

Excel 2016 & Excel pro Microsoft 365 – Power Pivot je součástí pásu karet.
Excel 2013 – Power Pivot je součástí Office Professional Plus edice Excelu 2013, ale ve výchozím nastavení není povolený. Přečtěte si další informace o spuštění doplňku Power Pivot pro Excel 2013.
Excel 2010 – Stáhněte si doplněk Power Pivot a pak nainstalujte doplněk Power Pivot.

Kde je získat & transformaci (Power Query)?

Excel 2016 & Excel pro Microsoft 365 – Funkce Get & Transform (Power Query) je integrovaná s Excelem na kartě Data.
Excel 2013 – Power Query je doplněk, který je součástí Excelu, ale je potřeba ho aktivovat. Přejděte na Možnosti > souborů > Doplňky a pak v rozevíracím seznamu Spravovat v dolní části podokna vyberte Doplňky modelu COM > Přejít. Zkontrolujte Microsoft Power Query pro Excel a pak ok aktivujte. Na pás karet se přidá karta Power Query.
Excel 2010 – Stáhněte a nainstalujte doplněk Power Query.. Po aktivaci se na pás karet přidá karta Power Query.

Začínáme

Nejprve potřebujete získat nějaká data.

V Excel 2016 a Excel pro Microsoft 365 použijte data > Získat & transformovat data > Získat data k importu dat z libovolného počtu externích zdrojů dat, například z textového souboru, excelového sešitu, webu, aplikace Microsoft Access, SQL Server nebo jiné relační databáze, která obsahuje více souvisejících tabulek.

V Excelu 2013 a 2010 přejděte na Power Query > Získat externí data a vyberte zdroj dat.
Excel vás vyzve k výběru tabulky. Pokud chcete získat více tabulek ze stejného zdroje dat, zaškrtněte políčko Povolit výběr více tabulek . Když vyberete více tabulek, Excel automaticky vytvoří datový model.

Poznámka: Pro tyto příklady používáme excelový sešit s fiktivními podrobnostmi studentů o předmětech a známkách. Můžete si stáhnout náš ukázkový sešit studentských datových modelů a postupovat podle pokynů. Můžete si také stáhnout verzi s dokončeným datovým modelem..
Vyberte jednu nebo více tabulek a pak klikněte na Načíst.

Pokud potřebujete zdrojová data upravit, můžete zvolit možnost Upravit . Další podrobnosti najdete v tématu Úvod do Editor Power Query (Power Query).

Teď máte datový model, který obsahuje všechny tabulky, které jste naimportovali, a ty se zobrazí v seznamu polí kontingenční tabulky.

Poznámky:

Modely se vytvářejí implicitně při importu dvou nebo více tabulek současně v Excelu.
Modely se vytvářejí explicitně, když k importu dat použijete doplněk Power Pivot. V doplňku je model reprezentován v rozložení s kartami podobně jako v Excelu, kde každá karta obsahuje tabulková data. Základní informace o importu dat pomocí SQL Server databáze najdete v tématu Získání dat pomocí doplňku Power Pivot.
Model může obsahovat jednu tabulku. Pokud chcete vytvořit model založený jenom na jedné tabulce, vyberte ji a klikněte na Přidat do datového modelu v Power Pivot. Můžete to udělat, pokud chcete použít Power Pivot funkce, jako jsou filtrované datové sady, počítané sloupce, počítaná pole, klíčové ukazatele výkonu a hierarchie.
Relace mezi tabulkami se dají vytvořit automaticky, pokud importujete související tabulky, které mají relace primárního a cizího klíče. Excel obvykle používá importované informace o relacích jako základ pro relace mezi tabulkami v datovém modelu.
Tipy, jak zmenšit velikost datového modelu, najdete v tématu Vytvoření datového modelu efektivního pro paměť pomocí Excelu a Power Pivotu.
Další informace najdete v tématech Kurz: Import dat do Excelu a Vytvoření datového modelu.

Tip: Jak poznáte, jestli váš sešit obsahuje datový model? Přejděte na Power Pivot > Spravovat. Pokud se zobrazí data podobná listu, model existuje. Další informace najdete v tématu Zjištění, které zdroje dat se používají v datovém modelu sešitu .

Vytvoření relací mezi tabulkami

Dalším krokem je vytvoření relací mezi tabulkami, abyste z nich mohli načíst data. Každá tabulka musí mít primární klíč nebo jedinečný identifikátor pole, jako je ID studenta nebo číslo třídy. Nejjednodušším způsobem je tato pole přetáhnout a propojit je v zobrazení diagramu Power Pivotu.

Přejděte na Power Pivot > Spravovat.
Na kartě Domů vyberte Zobrazení diagramu.
Zobrazí se všechny importované tabulky a v závislosti na tom, kolik polí každá z nich má, můžete chvíli trvat, než změníte jejich velikost.
Potom přetáhněte pole primárního klíče z jedné tabulky do další. Následující příklad je zobrazení diagramu našich tabulek studentů:

Vytvořili jsme následující odkazy:
- tbl_Students | ID studenta > tbl_Grades | ID studenta
  
  Jinými slovy, přetáhněte pole ID studenta z tabulky Studenti do pole ID studenta v tabulce Známky.
- tbl_Semesters | ID semestru > tbl_Grades | Semestr
- tbl_Classes | Číslo třídy > tbl_Grades | Číslo třídy
Poznámky:
- Názvy polí nemusí být stejné, aby bylo možné vytvořit relaci, ale musí mít stejný datový typ.
- Spojnice v zobrazení diagramu mají na jedné straně "1" a na druhé "*". To znamená, že mezi tabulkami existuje relace 1:N, která určuje, jak se data v kontingenčních tabulkách používají. Další informace najdete v tématu Relace mezi tabulkami v datovém modelu .
- Konektory pouze označují, že mezi tabulkami existuje relace. Ve skutečnosti nezobrazí, která pole jsou vzájemně propojená. Odkazy najdete v článku Power Pivot > Správa > >relací > Správa relací Power Pivotu. V Excelu můžete přejít na Data > Relace.

Vytvoření kontingenční tabulky nebo kontingenčního grafu pomocí datového modelu

Excelový sešit může obsahovat jenom jeden datový model, ale tento model může obsahovat více tabulek, které se dají opakovaně používat v celém sešitu. Do existujícího datového modelu můžete kdykoli přidat další tabulky.

V Power Pivotpřejděte na Spravovat.
Na kartě Domů vyberte Kontingenční tabulka.
Vyberte, kam chcete kontingenční tabulku umístit: nový list nebo aktuální umístění.
Klikněte na OK a Excel přidá prázdnou kontingenční tabulku s podoknem Seznam polí vpravo.

Pak vytvořte kontingenční tabulku nebo vytvořte kontingenční graf. Pokud jste už vytvořili relace mezi tabulkami, můžete v kontingenční tabulce použít libovolné z jejich polí. V ukázkovém sešitu Modelu dat studentů jsme už vytvořili relace.

Přidání existujících nesouvisejících dat do datového modelu

Předpokládejme, že jste importovali nebo zkopírovali velké množství dat, která chcete v modelu použít, ale nepřidali jste je do datového modelu. Vložení nových dat do modelu je jednodušší, než si myslíte.

Začněte tím, že vyberete libovolnou buňku v datech, kterou chcete přidat do modelu. Může to být libovolná oblast dat, ale nejvhodnější jsou data formátovaná jako excelová tabulka .
K přidání dat použijte jeden z těchto přístupů:
Klikněte naPower Pivot > Přidat do datového modelu.
Klikněte na Vložit > kontingenční tabulky a v dialogovém okně Vytvořit kontingenční tabulku zaškrtněte políčko Přidat tato data do datového modelu .

Oblast nebo tabulka se teď do modelu přidá jako propojená tabulka. Další informace o práci s propojenými tabulkami v modelu najdete v článku Přidání dat pomocí propojených tabulek Excelu v Power Pivotu.

Přidání dat do tabulky Power Pivot

V Power Pivot nemůžete přidat řádek do tabulky tak, že zadáte nový řádek přímo jako v excelovém listu. Řádky ale můžete přidat zkopírováním a vložením nebo aktualizací zdrojových dat a aktualizací modelu Power Pivotu.