Už jste někdy použili funkci SVYHLEDAT k přenesení sloupce z jedné tabulky do jiné? Excel taky 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 třeba SVYHLEDAT. 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 vytvářet kontingenční tabulky a jiné sestavy s poli z každé tabulky, i když tabulky pocházejí 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 polí 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 slučovat do jedné tabulky.
Poznámka
Pokud sešit obsahuje datový model, můžete relace mezi tabulkami spravovat z karty Data.
Při importu souvisejících tabulek z relační databáze může Excel č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í: Naformátujte data jako tabulku nebo importujte externí data jako tabulku do nového listu.
- Dejte každé tabulce smysluplný název: V Nástrojích tabulky klikněte na Navrhnout>název> tabulky. 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 chcete například spojit prodeje zákazníkům s časovou řadou, obě tabulky musí obsahovat data ve stejném formátu (třeba 1/1/2026) a alespoň jedna z tabulek (časová řada) musí každé datum uvést ve sloupci jenom 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 okně Vytvořit relaci klikněte na šipku u položky Tabulka a vyberte tabulku ze seznamu. 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ů.
- 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 po přetažení polí z jiných tabulek do seznamu Pole kontingenční tabulky. Pokud se nezobrazí 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 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 chyby cyklické závislosti, 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). 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 neduplikují data. Místo toho propojují tabulky, aby se pole z jednotlivých tabulek dala společně použít 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 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. 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 relace 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é
Až zjistíte, která tabulka není spojená se zbytkem modelu, prohlédněte si 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. Pokud chcete demografické údaje integrovat se zbytkem modelu, 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é. Sloupec tedy nesmí obsahovat duplicitní položky. 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ůže bý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.