Už jste někdy použili funkci SVYHLEDAT k přenesení sloupce z jedné tabulky do jiné? Teď, když má Excel integrovaný datový model, je už tato funkce zastaralá. Můžete vytvořit relaci mezi dvěma tabulkami dat založenou na odpovídajících datech v každé tabulce. Potom můžete z polí v každé tabulce vytvořit listy nástroje Power View a sestavit kontingenční tabulky a jiné sestavy, i když tabulky pochází z různých zdrojů. Pokud třeba máte data o prodeji zákazníkům, bude se vám hodit možnost importovat a propojit data časového měřítka, abyste mohli analyzovat prodeje podle roku nebo měsíce.
Všechny tabulky v sešitu jsou uvedené v seznamech polí Kontingenční tabulka a Power View.
Pokud importujete související tabulky z relační databáze, Excel může často vytvořit ty relace v datovém modelu, které vytváří skrytě. Ve všech ostatních případech bude potřeba vytvořit relace ručně.
-
Zkontrolujte, jestli sešit obsahuje aspoň dvě tabulky a jestli každá tabulka obsahuje sloupec, který je možné namapovat na sloupec v jiné databázi.
-
Udělejte jednu z těchto věcí: Formátování dat jako tabulky nebo Import externích dat jako tabulky v novém listu.
-
Každou tabulku smysluplně pojmenujte: V nástrojích tabulky klikněte na Návrh > Název tabulky a zadejte název.
-
Zkontrolujte, že sloupec v jedné z tabulek obsahuje jedinečné datové hodnoty bez duplikátů. Excel umí vytvořit relaci jen v případě, že jeden sloupec obsahuje jedinečné hodnoty.
Pokud třeba chcete spojit prodeje zákazníkům s časovou řadou, obě tabulky musí mít data ve stejném formátu (třeba 1/1/2012) a aspoň jedna z tabulek (časová řada) musí každé datum uvést ve sloupci jenom jednou.
-
Klikněte na Data > Relace.
Pokud je položka Relace neaktivní, znamená to, že sešit obsahuje jenom jednu tabulku.
-
V dialogu Správa relací klikněte na Nový.
-
V poli Vytvořit relaci klikněte na šipku pro Tabulka a vyberte tabulku ze seznamu. V relaci 1:N by tato tabulka měla být na straně N. Při použití našeho příkladu zákazníka a časového měřítka byste jako první zvolili tabulku prodeje zákazníků, protože mnoho prodejů bude pravděpodobně probíhat v daný den.
-
V části Sloupec (cizí) vyberte sloupec obsahující data, která jsou v relaci s položkou Související sloupec (primární). Pokud by třeba obě tabulky obsahovaly sloupec s daty, vybrali byste teď tento sloupec.
-
V části Tabulka v relaci vyberte tabulku obsahující minimálně jeden sloupec dat, která jsou v relaci k tabulce vybrané v části Tabulka.
-
V části Sloupec v relaci (primární) vyberte sloupec, který obsahuje jedinečné hodnoty odpovídající hodnotám ve sloupci vybraném v části Sloupec.
-
Klikněte na OK.
Víc informací o relacích mezi tabulkami a Excelem
Poznámky k relacím
-
Jestli relace existuje poznáte po přetažení polí z jiných tabulek do seznamu Pole kontingenční tabulky. Pokud se neobjeví výzva k vytvoření relace, znamená to, že Excel už má informace o relacích, které potřebuje k propojení dat.
-
Vytvoření relace je podobné, jako když používáte funkci VLOOKUP: potřebujete sloupce, které obsahují odpovídající data, aby Excel mohl používat křížové odkazy řádků v jedné tabulce na řádky v jiné tabulce. V příkladu používajícím časovou řadu musí tabulka Customer (Zákazník) obsahovat hodnoty dat, které obsahuje taky tabulka s časovou řadou.
-
V datovém modelu můžou být relace mezi tabulkami 1:1 (každý cestující má jeden palubní pas) nebo 1:N (každý let má mnoho cestujících), ale ne M:N. Relace M:N mají za následek cyklické chyby závislostí, například "Byla zjištěna cyklický závislost". K této chybě dojde, pokud vytvoříte přímé spojení mezi dvěma tabulkami, které jsou M:N, nebo nepřímými připojeními (řetězem relací mezi tabulkami, které jsou 1:N v rámci každé relace, ale M:N při zobrazení od konce do konce. Přečtěte si další informace o relacích mezi tabulkami v datovém modelu.
-
Datové typy v obou sloupcích musí být kompatibilní. Další informace najdete v tématu Datové typy v datových modelech Excelu.
-
Další způsoby vytváření relací můžou být intuitivnější, zejména pokud si nejste jisti, které sloupce použít. Další informace najdete v tématu Vytvoření relací v zobrazení diagramu v doplňku Power Pivot.
Příklad: Spojení dat časové řady s údaji o letech letecké společnosti
Relace mezi tabulkami a časovou řadou si můžeme ukázat na datech, která zdarma získáte z webu Microsoft Azure Marketplace. Některé z těchto datových sad jsou dost velké, takže pokud si chcete data stáhnout za rozumnou dobu, potřebujete rychlé internetové připojení.
-
Spusťte doplněk Power Pivot v Microsoft Excelu a otevřete okno Power Pivot.
-
Klikněte na Načíst externí data > Z jiných zdrojů > Z webu Microsoft Azure Marketplace. V průvodci importem tabulky se otevře domovská stránka webu Microsoft Azure Marketplace.
-
V části Price (Cena) klikněte na Free (Zdarma).
-
V části Category (Kategorie) klikněte na Science & Statistics (Věda a statistika).
-
Vyhledání DateStream a klikněte na přihlásit k odběru.
-
Zadejte svůj účet Microsoft a klikněte na Sign in (Přihlásit se). V okně by se měl zobrazit náhled dat.
-
Přejděte dolů a klikněte na Select Query (Výběrový dotaz).
-
Klikněte na Další.
-
Vyberte BasicCalendarUS (Základní kalendář USA) a pak kliknutím na Finish (Dokončit) data importujte. Pokud máte rychlé internetové připojení, import by měl trvat asi minutu. Po dokončení uvidíte zprávu o stavu 73 414 přenesených řádků. Klikněte na Close (Zavřít).
-
Kliknutím na Načíst externí data > Z jiných zdrojů > Z webu Microsoft Azure Marketplace importujete druhou sadu dat.
-
V části Type (Typ) klikněte na Data.
-
V části Price (Cena) klikněte na Free (Zdarma).
-
Najděte US Air Carrier Flight Delays (Zpoždění letů US Air Carrier) a klikněte na Select (Vybrat).
-
Přejděte dolů a klikněte na Select Query (Výběrový dotaz).
-
Klikněte na Další.
-
Kliknutím na Finish (Dokončit) importujte data. Pokud máte rychlé internetové připojení, může import trvat 15 minut. Po dokončení uvidíte zprávu o stavu 2 427 284 přenesených řádků. Klikněte na Close (Zavřít). V modelu byste teď měli vidět dvě tabulky. Když je chcete propojit, potřebujete kompatibilní sloupce v každé z nich.
-
Všimněte si, že DateKey (Datový klíč) v BasicCalendarUS je ve formátu 1/1/2012 12:00:00 AM. Tabulka On_Time_Performance (Přílet na čas) má taky sloupec s datem a časem, FlightDate (Datum letu), a její hodnoty jsou uvedené ve stejném formátu: 1/1/2012 12:00:00 AM. Tyto dva sloupce obsahují odpovídající data stejného datového typu a aspoň jeden z těchto sloupců (DateKey) obsahuje jenom jedinečné hodnoty. V několika dalších krocích tyto sloupce použijeme ke propojení tabulek.
-
V okně Power Pivot kliknutím na Kontingenční tabulka vytvoříte kontingenční tabulku na novém nebo stávajícím listu.
-
V seznamu polí rozbalte On_Time_Performance a kliknutím na ArrDelayMinutes (Zpoždění při příletu v minutách) ji přidáte do oblasti Hodnoty. V kontingenční tabulce byste měli vidět, o kolik minut byly lety celkem zpožděné.
-
Rozbalte položku BasicCalendarUS a kliknutím na MonthInCalendar (Měsíc v kalendáři) ji přidejte do oblasti Řádky.
-
Všimněte si, že kontingenční tabulka teď uvádí měsíce, ale celkový součet minut je pro každý měsíc stejný. Znovu opakujeme, že identické hodnoty ukazují, že je potřeba použít relaci.
-
V seznamu polí v části „Můžou být potřeba relace mezi tabulkami” klikněte na Vytvořit.
-
V části Související tabulka vyberte On_Time_Performance a v části Související sloupec (primární) vyberte FlightDate.
-
V části Tabulka vyberte BasicCalendarUS a v části Sloupec (cizí) vyberte DateKey. Kliknutím na OK vytvoříte relaci.
-
Všimněte si, že součet minut zpoždění se teď pro každý měsíc liší.
-
V části BasicCalendarUS přetáhněte YearKey (Klíč – rok) do oblasti Řádky, nad MonthInCalendar.
Zpoždění při příletu teď můžete rozdělit podle roku a měsíce nebo jiných hodnot v kalendáři.
Tipy: Standardně se měsíce uvádějí v abecedním pořadí. Pomocí doplňku Power Pivot je můžete seřadit tak, aby byly v chronologickém pořadí.
-
Zkontrolujte, že je v okně Power Pivot otevřená tabulka BasicCalendarUS.
-
V domovské tabulce klikněte na Seřadit podle sloupce.
-
V části Seřadit vyberte MonthInCalendar (Měsíc v kalendáři).
-
V části Podle vyberte MonthOfYear (Měsíc v roce).
Kontingenční tabulka teď každou kombinaci měsíce a roku (říjen 2011, listopad 2011) řadí podle čísla měsíce v roce (10, 11). Změna pořadí řazení je snadná, protože kanál DateStream poskytuje všechny sloupce potřebné k tomu, aby to tak fungovalo. Pokud používáte jinou tabulku s časovou řadou, bude tento krok jiný.
„Můžou být potřeba relace mezi tabulkami”
V průběhu přidávání polí do kontingenční tabulky uvidíte informace o tom, jestli je potřeba vytvořit relace mezi tabulkami, aby pole, která jste v kontingenční tabulce vybrali, dávala smysl.
I když vám Excel může říct, kdy je potřeba relaci vytvořit, už vám neřekne, které tabulky a sloupce máte použít nebo jestli je vůbec relace mezi tabulkami možná. Požadovanou odpověď zkuste zjistit takhle:
Krok 1: Určení tabulek, které se mají zadat pro relaci
Pokud váš model obsahuje jenom několik tabulek, bude asi hned zřejmé, které máte použít. U rozsáhlejších modelů by vám ale mohla být užitečná nějaká pomoc. Jednou z možností je použít Zobrazení diagramu v doplňku Power Pivot. Zobrazení diagramu představuje grafické znázornění všech tabulek v datovém modelu. Pomocí tohoto zobrazení můžete rychle určit, které tabulky jsou oddělené od zbytku modelu.
Poznámka: Je možné vytvořit nejednoznačné relace, které jsou neplatné, pokud je použijete v kontingenční tabulce nebo sestavě Power View. Předpokládejme, že všechny vaše tabulky jsou nějak spojené s jinými tabulkami v modelu, ale když se pokusíte zkombinovat pole z různých tabulek, zobrazí se vám zpráva „Mohou být potřeba vztahy mezi tabulkami“. Nejpravděpodobnějším důvodem bude, že jste vytvořili relace N:N. Když budete sledovat řetěz relací spojených s tabulkami, které chcete použít, nejspíš zjistíte, že jedna nebo víc relací mezi tabulkami je 1:N. Žádný jednoduchý trik, který by fungoval vždycky, neexistuje, ale můžete zkusit vytvořit počítané sloupce a sloučit sloupce, které chcete použít, do jedné tabulky.
Krok 2: Vyhledání sloupců, které můžete použít k vytvoření cesty z jedné tabulky do jiné
Teď, když jste určili, která tabulka není spojená se zbytkem modelu, si prohlédněte její sloupce a zjistěte, jestli jiný sloupec – kdekoli v modelu – obsahuje odpovídající hodnoty.
Předpokládejme třeba, že máte model, který obsahuje prodeje produktu podle oblasti, a že následně importujete demografické údaje, abyste zjistili, jestli mezi prodeji a demografickými trendy v oblasti existuje korelace. Vzhledem k tomu, že demografické údaje pocházejí z jiného zdroje dat, jsou tabulky s těmito údaji na začátku izolované od zbytku modelu. Jestli chcete demografické údaje do zbytku modelu integrovat, budete muset v jedné z demografických tabulek najít sloupec, který odpovídá sloupci, který už používáte. Pokud jsou třeba demografické údaje organizované podle oblasti a vaše data o prodeji říkají, ve které oblasti k prodeji došlo, mohli byste tyto dvě sady dat spojit tím, že najdete společný sloupec, jako je stát, kraj nebo PSČ, a podle nich vyhledávat.
Kromě odpovídajících hodnot je k vytvoření relace potřeba několik dalších věcí:
-
Hodnoty dat ve vyhledávacím sloupci musí být jedinečné. Jinými slovy, sloupce nemůžou obsahovat duplikáty. V datovém modelu jsou hodnoty null a prázdné řetězce ekvivalentní prázdné hodnotě, která je samostatnou datovou hodnotou. To znamená, že ve vyhledávacím sloupci nemůžete mít víc hodnot Null.
-
Datové typy jak zdrojového sloupce, tak vyhledávacího sloupce musí být kompatibilní. Další informace o datových typech najdete v tématu Datové typy v datových modelech.
O relacích si můžete přečíst víc v tématu Relace mezi tabulkami v datovém modelu.
