Applies ToExcel pro Microsoft 365 Excel 2024 Excel 2021 Excel 2019 Excel 2016 Power BI

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.

Poznámka: Než budete postupovat podle kroků v tomto článku, ujistěte se, že je tato funkce povolená. Další informace najdete v článku Spuštění doplňku Power Pivot pro Excel.

Váš prohlížeč nepodporuje video. Nainstalujte si Microsoft Silverlight, Adobe Flash Player nebo Internet Explorer 9.

Když importujete související tabulky z relační databáze, Excel často může tyto relace vytvořit v datovém modelu, který vytváří na pozadí. Ve všech ostatních případech budete muset relace vytvořit 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. 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.

  3. Každou tabulku smysluplně pojmenujte: V nástrojích tabulky klikněte na Návrh > Název tabulky a zadejte název.

  4. 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.

  5. 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 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.

  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 při přetažení polí z různých tabulek do seznamu Pole kontingenční tabulky. Pokud se výzva k vytvoření relace nezobrazí, Excel už má informace o relaci, 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í.

  1. Spusťte doplněk Power Pivot v Microsoft Excelu 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. Vyhledání DateStream a klikněte na přihlásit k odběru.

  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. Abychom je mohli propojit, budeme potřebovat kompatibilní sloupce v každé tabulce.

  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 použijete tyto sloupce ke spojení 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 Relace mezi tabulkami mohou být potřeba 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.

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

  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 časového měřítka, váš krok se bude lišit.

"Můžou být potřeba relace mezi tabulkami"

Při přidávání polí do kontingenční tabulky budete informováni o tom, jestli je relace mezi tabulkami nutná k tomu, aby pole vybraná v kontingenční tabulce dávala smysl.

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

I když vám Excel dokáže říct, kdy je potřeba relace, nedokáže vám říct, které tabulky a sloupce použít nebo jestli je vůbec možné vytvořit relaci mezi tabulkami. 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: Při použití v kontingenční tabulce nebo sestavě Power View je možné vytvořit nejednoznačné relace, které jsou neplatné. Předpokládejme, že všechny vaše tabulky nějakým způsobem souvisejí s jinými tabulkami v modelu, ale když se pokusíte zkombinovat pole z různých tabulek, zobrazí se zpráva "Relace mezi tabulkami mohou být potřeba". Nejpravděpodobnější příčinou je, že jste narazili na relaci M: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é

Jakmile zjistíte, která tabulka je odpojená od zbytku modelu, zkontrolujte její sloupce a zjistěte, jestli jiný sloupec jinde 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. Pokud chcete integrovat demografické údaje se zbytkem modelu, budete muset najít sloupec v jedné z demografických tabulek, který odpovídá té, kterou 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, sloupec nemůže obsahovat duplicity. 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íce 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

Potřebujete další pomoc?

Chcete další možnosti?

Prozkoumejte výhody předplatného, projděte si školicí kurzy, zjistěte, jak zabezpečit své zařízení a mnohem více.

Komunity vám pomohou klást otázky a odpovídat na ně, poskytovat zpětnou vazbu a vyslechnout odborníky s bohatými znalostmi.