Vytvoření relace mezi tabulkami v Excelu

Už jste někdy použili funkci SVYHLEDAT k přenesení sloupce z jedné tabulky do jiné? Teď, když má Excel 2013 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ě.

  1. 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.
  2. Naformátujte data jako tabulku nebo

importujte externí data jako tabulku na novém listu.

  1. Každou tabulku smysluplně pojmenujte: V nástrojích tabulky klikněte na Návrh > Název tabulky a zadejte název.
  2. 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.

  1. Klikněte na Data > Relace.

Pokud je položka Relace neaktivní, znamená to, že sešit obsahuje jenom jednu tabulku.

  1. V dialogu Správa relací klikněte na Nový.
  2. V dialogu Vytvořit relaci klikněte u položky Tabulka na šipku a ze seznamu vyberte tabulku. V případě relace 1:N by tato tabulka měla být na straně N. V našem příkladu se zákazníky a časovou řadou byste vybrali nejdřív tabulku prodeje zákazníkům, protože každý den může dojít k velkému množství prodejů.
  3. 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.
  4. V části Tabulka v relaci vyberte tabulku obsahující minimálně jeden sloupec dat, která jsou v relaci k tabulce vybrané v části Tabulka.
  5. 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.
  6. 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 typu 1:1 (každý cestující má jednu palubní vstupenku) nebo typu 1:N (každý let má hodně cestujících), ale ne typu N:N. Relace typu N:N mají za následek chyby způsobené cyklickou závislostí, třeba „Byla zjištěna cyklická závislost.“ K této chybě dojde, pokud vytvoříte přímá spojení mezi dvěma tabulkami, které jsou typu N:N, nebo nepřímá spojení (řetěz relací mezi tabulkami, které jsou v každé relaci typu 1:N, ale z širšího hlediska jsou typu N:N). Další informace o relacích najdete v tématu Relace 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í.

  1. Spusťte doplněk Power Pivot v Microsoft Excelu 2013 a otevřete okno Power Pivot.
  2. 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.
  3. V části Price (Cena) klikněte na Free (Zdarma).
  4. V části Category (Kategorie) klikněte na Science & Statistics (Věda a statistika).
  5. Najděte DateStream a klikněte na Subscribe (Přihlásit k odběru). Tady najdete další informace o datovém kanálu časové řady.
  6. 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.
  7. Přejděte dolů a klikněte na Select Query (Výběrový dotaz).
  8. Klikněte na Další.
  9. 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).
  10. Kliknutím na Načíst externí data > Z jiných zdrojů > Z webu Microsoft Azure Marketplace importujete druhou sadu dat.
  11. V části Type (Typ) klikněte na Data.
  12. V části Price (Cena) klikněte na Free (Zdarma).
  13. Najděte US Air Carrier Flight Delays (Zpoždění letů US Air Carrier) a klikněte na Select (Vybrat).
  14. Přejděte dolů a klikněte na Select Query (Výběrový dotaz).
  15. Klikněte na Další.
  16. 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.
  17. 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.
  18. V okně Power Pivot kliknutím na Kontingenční tabulka vytvoříte kontingenční tabulku na novém nebo stávajícím listu.
  19. 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é.
  20. Rozbalte položku BasicCalendarUS a kliknutím na MonthInCalendar (Měsíc v kalendáři) ji přidejte do oblasti Řádky.
  21. 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.
  22. V seznamu polí v části „Můžou být potřeba relace mezi tabulkami” klikněte na Vytvořit.
  23. V části Související tabulka vyberte On_Time_Performance a v části Související sloupec (primární) vyberte FlightDate.
  24. V části Tabulka vyberte BasicCalendarUS a v části Sloupec (cizí) vyberte DateKey. Kliknutím na OK vytvoříte relaci.
  25. Všimněte si, že součet minut zpoždění se teď pro každý měsíc liší.
  26. 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.

 Tip:    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í.

  1. Zkontrolujte, že je v okně Power Pivot otevřená tabulka BasicCalendarUS .
  2. V domovské tabulce klikněte na Seřadit podle sloupce .
  3. V části Seřadit vyberte MonthInCalendar (Měsíc v kalendáři) .
  4. 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.

Tlačítko Vytvořit se objeví, když je potřeba vytvořit relaci

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.

Zobrazení diagramu znázorňující nespojené tabulky

 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í s prázdnou hodnotou, což je odlišná datová hodnota. 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.

Začátek stránky Začátek stránky

 
 
Platí pro:
Excel 2013, PowerPivot v Excelu 2013