Už jste někdy použili funkci SVYHLEDAT k přenesení sloupce z jedné tabulky do jiné? Excel také obsahuje integrovaný datový model, který umožňuje vytvářet relace mezi tabulkami, což může být alternativa k použití vyhledávacích funkcí, jako je funkce SVYHLEDAT. Můžete vytvořit relaci mezi dvěma tabulkami dat založenou na odpovídajících datech v každé tabulce. Pak můžete vytvořit kontingenční tabulky a další sestavy s poli z každé tabulky, i když jsou tabulky 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 seznamu Pole kontingenční tabulky.
Relace se nejčastěji používají při vytváření kontingenčních tabulek z více tabulek v datovém modelu. To vám umožní analyzovat související data, aniž byste je museli kombinovat do jedné tabulky.
Poznámka: Pokud sešit obsahuje datový model, můžete spravovat relace mezi tabulkami na kartě Data.
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ě.
-
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 například chcete spojit prodej zákazníků s časovým měřítkem, musí obě tabulky obsahovat kalendářní data ve stejném formátu (například 1. 1. 2026) a alespoň jedna tabulka (časové měřítko) uvádí každé datum ve sloupci jen jednou.
-
Vyberte Relace > dat.
Pokud je položka Relace neaktivní, znamená to, že sešit obsahuje jenom jednu tabulku.
-
V poli Správa relací vyberte 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.
-
Vyberte 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 Excelu jsou relace obvykle 1:1 nebo 1:N. Relace M:N vyžadují další modelování (například pomocí vyhledávací tabulky). 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ěz 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.
-
-
Na rozdíl od vyhledávacích vzorců relace nezdvojují data. Místo toho propojují tabulky, aby bylo možné pole z každé tabulky používat společně v kontingenční tabulce.
-
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.
"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.
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.
Poznámka: Při použití v kontingenční tabulce 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.
