Použili ste niekedy funkciu VLOOKUP na prenesenie stĺpca z jednej tabuľky do druhej? Teraz, keď má Excel vstavaný dátový model, je už funkcia VLOOKUP zastaraná. Vzťah medzi dvoma tabuľkami údajov môžete vytvoriť na základe zhodných údajov v jednotlivých tabuľkách. Potom môžete vytvoriť hárky Power View, kontingenčné tabuľky a iné zostavy obsahujúce polia jednotlivých tabuliek, aj keď tieto tabuľky pochádzajú z rôznych zdrojov. Ak máte napríklad údaje o predaji zákazníkom, môžete importovať údaje časovej inteligencie a vytvoriť k nim vzťah, aby ste mohli analyzovať vzory predaja v jednotlivých rokoch a mesiacoch.
Všetky tabuľky zošita nájdete uvedené v kontingenčnej tabuľke a zoznamoch Polia funkcie Power View.
Keď importujete súvisiace tabuľky z relačnej databázy, Excel tieto vzťahy často dokáže vytvoriť v dátovom modeli, ktorý zostavuje na pozadí. Vo všetkých ostatných prípadoch je potrebné vytvoriť vzťahy manuálne.
-
Presvedčte sa, či zošit obsahuje aspoň dve tabuľky a či každá tabuľka obsahuje stĺpec, ktorý možno priradiť k stĺpcu v inej tabuľke.
-
Vykonajte niektorý z týchto krokov: Naformátujte údaje ako tabuľku alebo importujte externé údaje ako tabuľku v novom hárku.
-
Dajte každej tabuľke zmysluplný názov: Na karte Nástroje tabuľky kliknite na položky Návrh > Názov tabuľky a zadajte názov.
-
Presvedčte sa, či má stĺpec jednej z tabuliek jedinečné, neduplicitné hodnoty údajov. Excel dokáže vytvoriť vzťah len v prípade, že jeden stĺpec obsahuje jedinečné hodnoty.
Ak chcete napríklad priradiť predaj zákazníkom k časovej inteligencii, je potrebné, aby obe tabuľky obsahovali dátumy v rovnakom formáte (napríklad 1.1.2012) a aby minimálne jedna tabuľka (časová inteligencia) uvádzala každý dátum v rámci jedného stĺpca len raz.
-
Kliknite na položky Údaje > Vzťahy.
Ak položka Vzťahy nie je aktívna, zošit obsahuje len jednu tabuľku.
-
V dialógovom okne Správa vzťahov kliknite na položku Nové.
-
V dialógovom okne Vytvorenie vzťahu kliknite na šípku položky Tabuľka a v zozname vyberte tabuľku. V prípade vzťahu „one-to-many“ je potrebné, aby táto tabuľka bola na strane vzťahu „many“. V našom prípade so zákazníkmi a časovou inteligenciou vyberiete najskôr tabuľku predaja zákazníkom, pretože každý deň sa pravdepodobne vyskytne viacero zákazníkov.
-
V časti Stĺpec (cudzí) vyberte stĺpec obsahujúci údaje, ktoré súvisia s položkou Súvisiaci stĺpec (primárny). Ak napríklad obidve tabuľky obsahujú stĺpec s dátumom, vyberte teraz tento stĺpec.
-
V časti Súvisiaca tabuľka vyberte tabuľku obsahujúcu najmenej jeden stĺpec údajov, ktoré súvisia s tabuľkou vybranou v časti Tabuľka.
-
V časti Súvisiaci stĺpec (primárny) vyberte stĺpec, ktorý obsahuje jedinečné hodnoty zodpovedajúce hodnotám v stĺpci vybratom v časti Stĺpec.
-
Kliknite na tlačidlo OK.
Ďalšie informácie o vzťahoch medzi tabuľkami v Exceli
Poznámky k vzťahom
-
Existenciu vzťahov zistíte, keď presuniete polia z rôznych tabuliek do zoznamu polí kontingenčnej tabuľky. Ak sa nezobrazí výzva na vytvorenie vzťahu, Excel už disponuje informáciami o vzťahu, ktoré potrebuje na pripojenie údajov.
-
Vytvorenie vzťahov je podobné používaniu funkcie VLOOKUP: potrebujete stĺpce, ktoré obsahujú zodpovedajúce údaje, aby Excel mohol používať krížové odkazy riadkov v jednej tabuľke na riadky v inej tabuľke. V príklade s časovou inteligenciou musí tabuľka Customer (Zákazník) obsahovať hodnoty dátumu, ktoré existujú aj v tabuľke časovej inteligencie.
-
V dátovom modeli môžu byť vzťahy tabuliek „one-to-one“ (každý pasažier má jeden palubný lístok) alebo „one-to-many“ (každý let má viacero pasažierov), nie však vzťahy „many-to-many“. Vzťahy „many-to-many“ vedú k chybám cyklickej závislosti, napríklad k chybe Zistila sa cyklická závislosť. Táto chyba sa vyskytne vtedy, keď vytvoríte priame prepojenie medzi dvoma tabuľkami typu „many-to-many“ alebo keď vytvoríte nepriame prepojenia (reťazec vzťahov tabuliek, ktoré sú vo vzájomných vzťahoch typu „one-to-many“, ale pri koncovom zobrazení sa zobrazujú ako „many-to-many“. Ďalšie informácie si môžete prečítať v téme Vzťahy medzi tabuľkami v modeli údajov.
-
Typy údajov v dvoch stĺpcoch musia byť kompatibilné. Ďalšie informácie nájdete v téme Typy údajov v dátových modeloch Excelu.
-
Ďalšie spôsoby vytvárania vzťahov môžu byť intuitívnejšie, najmä ak si nie ste istí, ktoré stĺpce sa majú použiť. Ďalšie informácie nájdete v téme Vytváranie vzťahov v zobrazení diagramu doplnkuPower Pivot.
Príklad: Priradenie údajov časovej inteligencie k údajom o letoch leteckých spoločností
Informácie o vzťahoch tabuliek a časovej inteligencii môžete získať pomocou bezplatných údajov z lokality Microsoft Azure Marketplace. Niektoré z týchto množín údajov sú veľmi veľké a vyžadujú rýchle internetové pripojenie, aby sa sťahovanie údajov dokončilo za primeraný čas.
-
Spustite doplnok Power Pivot v Microsoft Exceli a otvorte okno doplnku Power Pivot.
-
Kliknite na položky Získať externé údaje > Zo služby údajov > Zo služby Microsoft Azure Marketplace. V Sprievodcovi importom tabuľky sa otvorí domovská stránka lokality Microsoft Azure Marketplace.
-
V časti Price (Cena) kliknite na možnosť Free (Zdarma).
-
V sekciiCategory (Kategória) kliknite na možnosť Science & Statistics (Veda a štatistika).
-
Vyhľadanie DateStream a kliknite na položku Prihlásiť sa na odber.
-
Zadajte svoje konto Microsoft a kliknite na položku Sign in (Prihlásiť sa). V okne by sa mala zobraziť ukážka údajov.
-
Presuňte sa nadol a kliknite na položku Select Query (Výberový dotaz).
-
Kliknite na tlačidlo Next (Ďalej).
-
Vyberte možnosť BasicCalendarUS (Základný kalendár pre USA), kliknite na položku Finish (Dokončiť) a importujte údaje. Pri rýchlom internetovom pripojení by mal import trvať asi minútu. Po dokončení by sa mala zobraziť správa o stave oznamujúca prenos 73 414 riadkov. Kliknite na tlačidlo Close (Zavrieť).
-
Kliknite na položky Získať externé údaje > Zo služby údajov > Zo služby Microsoft Azure Marketplace a importujte druhú množinu údajov.
-
V časti Type (Typ) kliknite na možnosť Data (Údaje).
-
V časti Price (Cena) kliknite na možnosť Free (Zdarma).
-
Vyhľadajte možnosť US Air Carrier Flight Delays (Meškania amerických leteckých spoločností) a kliknite na položkuSelect (Vybrať).
-
Presuňte sa nadol a kliknite na položku Select Query (Výberový dotaz).
-
Kliknite na tlačidlo Next (Ďalej).
-
Kliknutím na tlačidlo Finish (Dokončiť) vykonáte importovanie údajov. V prípade rýchleho internetového pripojenia to môže trvať okolo 15 minút. Po dokončení by sa mala zobraziť správa o stave oznamujúca prenos 2 427 284 riadkov. Kliknite na položku Close (Zavrieť). V dátovom modeli by ste teraz mali mať dve tabuľky. Ak medzi nimi chcete vytvoriť vzťah, je potrebné, aby sa v nich nachádzali kompatibilné stĺpce.
-
Všimnite si, že položka DateKey (Formát dátumu) možnosti BasicCalendarUS (Základný kalendár pre USA) je vo formáte 1/1/2012 12:00:00 AM. Tabuľka On_Time_Performance (O časovom výkone) tiež obsahuje stĺpec s údajmi o dátume a čase nazvaný FlightDate (Dátum letu), ktorého hodnoty sú zadané v rovnakom formáte: 1/1/2012 12:00:00 AM. Tieto dva stĺpce obsahujú zhodné údaje toho istého údajového typu a minimálne jeden stĺpec (DateKey) obsahuje len jedinečné hodnoty. V nasledujúcich niekoľkých krokoch tieto stĺpce použijete na vytvorenie vzťahu medzi tabuľkami.
-
V okne doplnku Power Pivot kliknite na položku Kontingenčná tabuľka a v novom alebo existujúcom hárku vytvorte kontingenčnú tabuľku.
-
V zozname polí rozbaľte tabuľku On_Time_Performance (Výkon_načas) a kliknutím na položku ArrDelayMinutes (Minúty meškania) ju pridajte do oblasti Values (Hodnoty). V kontingenčnej tabuľke by sa mal zobraziť celkový čas oneskorení letov uvedený v minútach.
-
Rozbaľte BasicCalendarUS (Základný kalendár pre USA) a kliknite na položku MonthInCalendar (Mesiace kalendára), aby ste ju pridali do oblasti Rows (Riadky).
-
Všimnite si, že kontingenčná tabuľka teraz uvádza mesiace, ale celkový súčet minút je pre každý mesiac rovnaký. Opakujúce sa identické hodnoty znamenajú, že je potrebné vytvoriť vzťah.
-
V zozname polí v časti Môžu byť potrebné vzťahy medzi tabuľkami kliknite na položku Vytvoriť.
-
V časti Súvisiaca tabuľka vyberte položku On_Time_Performance a v časti Súvisiaci stĺpec (primárny) vyberte položku FlightDate.
-
V časti Tabuľka vyberte položky BasicCalendarUS a v sekcii Stĺpec (cudzí) vyberte položku DateKey. Kliknutím na tlačidlo OK vytvorte vzťah.
-
Všimnite si, že súčet omeškaných minút v jednotlivých mesiacoch je teraz odlišný.
-
V sekcii BasicCalendarUS (Základný kalendár pre USA)presuňte položku YearKey (Formát roka) do oblasti Rows (Riadky) nad položku MonthInCalendar (Mesiace kalendára).
Oneskorené prílety teraz môžete zobraziť podľa roka a mesiaca alebo inej hodnoty kalendára.
Tipy: Mesiace sa predvolene zobrazujú v abecednom poradí. Pomocou doplnku Power Pivot môžete zmeniť poradie tak, aby sa mesiace zobrazovali v chronologickom poradí.
-
Presvedčte sa, že tabuľka BasicCalendarUS je otvorená v okne doplnku Power Pivot.
-
Na karte Domov kliknite na možnosť Zoradiť podľa stĺpca.
-
V časti Zoradiť vyberte položku MonthInCalendar (Mesiace kalendára).
-
V časti Podľa vyberte možnosť MonthOfYear (Mesiac v roku).
V kontingenčnej tabuľke sú teraz zoradené všetky kombinácie mesiacov a rokov (október 2011, november 2011) podľa čísla mesiaca v roku (10, 11). Zmena spôsobu zoradenia je jednoduchá, pretože informačný kanál DateStream poskytuje všetky stĺpce potrebné na fungovanie tohto scenára. Ak používate inú tabuľku časovej inteligencie, bude váš postup odlišný.
„Môžu byť potrebné vzťahy medzi tabuľkami“
Pri pridávaní polí do kontingenčnej tabuľky budete informovaní o tom, či je potrebné vytvoriť vzťahy tabuliek, aby vybraté polia kontingenčnej tabuľky dávali zmysel.
Hoci vás Excel vie upozorniť na to, kedy je potrebné vytvoriť vzťah, nedokáže vám povedať, ktoré tabuľky a stĺpce na to použiť, ani to, či je vzťah tabuliek možný. Na získanie potrebných odpovedí vyskúšajte nasledovné kroky.
Krok 1: Určite, ktoré tabuľky sa majú v rámci vzťahov špecifikovať
Ak váš model obsahuje len niekoľko tabuliek, môže byť hneď zrejmé, ktoré bude potrebné použiť. Pri väčších modeloch však možno budete potrebovať pomoc. Jedným z prístupov je použiť zobrazenie diagramu v doplnku Power Pivot. Zobrazenie diagramu poskytuje vizuálnu znázornenie všetkých tabuliek v dátovom modeli. Pomocou zobrazenia diagramu môžete rýchlo určiť, ktoré tabuľky sú od zvyšku modelu oddelené.
Poznámka: Je tiež možné vytvoriť nejednoznačné vzťahy, ktoré nie sú pri používaní kontingenčných tabuliek alebo zostáv Power View platné. Predpokladajme, že všetky tabuľky sú určitým spôsobom prepojené s inými tabuľkami modelu, avšak pri pokuse o skombinovanie polí z rôznych tabuliek sa zobrazí správa Môžu byť potrebné vzťahy medzi tabuľkami. Najpravdepodobnejšou príčinou je, že ide o vzťah „many-to-many“. Ak budete sledovať reťazec vzťahov tabuliek, ktoré sa pripájajú k tabuľkám, ktoré chcete použiť, pravdepodobne zistíte, že máte dva alebo viac vzťahov „one-to-many“. K dispozícii nie je žiadne jednoduché alternatívne riešenie, ktoré by fungovalo vo všetkých prípadoch, ale môžete vyskúšať vytvoriť vypočítané stĺpce a konsolidovať tak stĺpce, ktoré chcete použiť, do jednej tabuľky.
Krok 2: Vyhľadajte stĺpce, ktoré možno použiť na vytvorenie cesty z jednej tabuľky do druhej
Po tom, ako zistíte, ktorá tabuľka nie je pripojená k zvyšku modelu, môžete prezrieť jej stĺpce a zistiť, či iný stĺpec, v inej časti modelu, neobsahuje zhodné hodnoty.
Predpokladajme, že máte model obsahujúci predaj produktov podľa regiónu a že následne importuje demografické údaje, aby ste zistili, či existuje korelácia medzi predajom a demografickými trendmi jednotlivých území. Keďže demografické údaje pochádzajú z rôznych zdrojov údajov, tabuľky sú spočiatku izolované od zvyšku modelu. Ak chcete demografické údaje integrovať so zvyškom modelu, bude potrebné vyhľadať stĺpec v jednej z demografických tabuliek, ktorý by korešpondoval so stĺpcom, ktorí už používate. Ak sú napríklad demografické údaje zorganizované podľa oblasti a údaje o predaji uvádzajú, v ktorej oblasti k predaju došlo, môžete vytvoriť vzťah medzi týmito dvoma množinami údajov, nájsť spoločný stĺpec, napríklad Štát, PSČ alebo Oblasť a poskytnúť ich na vyhľadávanie.
Okrem zhodných hodnôt je na vytvorenie vzťahu potrebné splniť aj ďalšie požiadavky:
-
Hodnoty dátumov v stĺpci vyhľadávania musia byť jedinečné. Inými slovami, stĺpec nemôže obsahovať duplicitné údaje. Hodnota null a prázdne reťazce sú v dátovom modeli ekvivalentné prázdnej hodnote, čo predstavuje jednoznačnú hodnotu údajov. Znamená to, že vo vyhľadávacom stĺpci nemôžete mať viacero hodnôt null.
-
Je tiež potrebné, aby typy údajov zdrojového stĺpca a vyhľadávacieho stĺpca boli kompatibilné. Ďalšie informácie o typoch údajov nájdete v téme Typy údajov v údajových modeloch.
Ďalšie informácie o vzťahoch tabuliek nájdete v téme Vzťahy medzi tabuľkami v údajovom modeli.
