Propojování tabulek v Excelu, Accessu, Power Pivotu a SQL

S tímto tématem se setkáte na našich kurzech ExcelTown.
Aktuálně: kurzy můžete absolvovat jak online, tak prezenčně.

Propojování tabulek je téma, které na různých kurzech často řešíme. Proto bych chtěl tento článek věnovat tomu, jak se tato otázka řeší v různých typech aplikací. Vybral jsem si ty, které běžný uživatel asi použije nejčastěji – tedy samozřejmě Excel, dále pak Access a Power Pivot, a navíc ještě jazyk SQL – platný plus minus univerzálně v databázových nástrojích.

Příklad

Ve všech čtyřech ukázkách budu pracovat s jedním příkladem. Řekněme, že máme několik zaměstnanců, kteří pracují na pobočkách. Tyto pobočky jsou ve městech.

V jedné tabulce jsou zaměstnanci a čísla poboček, kde pracují. Ve druhé tabulce jsou pobočky a města. Každý zaměstnanec tedy pracuje na určité pobočce, a protože každá pobočka je v určitém městě, tak každý zaměstnanec pracuje i v určitém městě.

zadani

Mým cílem je přidat do posledního sloupce v první tabulce města, kde zaměstnanci pracují. Přičemž tato města jsou ve druhé tabulce. K tomuto se tedy chci dopracovat:

vysledek spojeni

Jak na to v Excelu

V Excelu použiji, nám už docela dobře známou, funkci SVYHLEDAT / VLOOKUP.

Zapíšu ji do první buňky oblasti s výsledky a roztáhnu dolů.

řešení v excelu

 Funkce má v první buňce takovouto syntaxi:

=SVYHLEDAT(C4;F:G;2;0)

  • C4 – co se má ve druhé tabulce hledat
  • F:G – kde je tabulka, ze které se čerpá výsledek
  • 2 – z kolikátého sloupce se čerpá výsledek
  • 0 – protože chci přesnou shodu (ne přibližnou)
Více o funkci SVYHLEDAT tady.

Jak na to v datovém modelu (v Power Pivotu)

V datovém modelu, který v Excelu často spojujeme s Power Pivotem, se tabulky propojí takto. Pak použijeme zápis jazyka DAX a do tabulky s lidmi přidáme vzorec, který odkáže na tabulku s pobočkami.

Syntaxe funkce je:

=RELATED(POBOCKY[Město])

vzorec v power pivotu

Kulaté závorky označují tabulku, do které se odkazuji, a hranatá závorka konkrétní sloupec. Vidíme, že syntaxe je výrazně jednodušší než v běžném Excelu. Je to proto, že ve vzorci nemusím určovat, přes co se tabulky propojují. To už je dáno nadefinováním relace (vztahu) tabulek. Funkci RELATED tedy stačí jen jeden parametr oproti čtyřem parametrům, které vyžaduje SVYHLEDAT.

Výsledek je ale v zásadě podobný jako v předchozím případě.

Jak na to v Accessu

V Accessu je to podobné jako v Power Pivotu.

Nejprve musíme propojit tabulky, podobně jako v Power Pivotu:

navrh dotazu access

Pak vytvořím dotaz. Tady je tedy principiální rozdíl mezi předchozími dvěma případy a Accessem i SQL. Zatímco v Excelu a v Power Pivotu jsme do jedné tabulky přidávali hodnoty z druhé tabulky, tak v Accessu propojíme tabulky tak, že vytvoříme nový dotaz obsahující sloupce z obou tabulek.

navrh dotazu access (1)

Výsledkem je pak takováto tabulka (která je ovšem ve skutečnosti dotazem):

vysledek access

Jak na to v jazyce SQL

Jazyk SQL se používá univerzálně, nezávisle na technologii.
Dotaz, který jsme si ukázali v předchozím případě, by vypadal takto:
  • SELECT zamestnanci.[Jméno zaměstnance], zamestnanci.[Číslo pobočky], pobočky.Město FROM pobočky INNER JOIN zamestnanci ON pobočky.[Číslo pobočky] = zamestnanci.[Číslo pobočky];
Vysvětlení logiky:
  • SELECT zamestnanci.[Jméno zaměstnance], zamestnanci.[Číslo pobočky], pobočky.Město (vyber Jméno zaměstnance a Číslo pobočky z tabulky “zamestnanci” a dále Město z tabulky “pobočky”) FROM pobočky INNER JOIN zamestnanci ON pobočky.[Číslo pobočky] = zamestnanci.[Číslo pobočky] (tabulka “pobočky” je provázaná s tabulkou “zamestnanci” na základě toho, že Číslo pobočky je v obou tabulkách stejné).
Výsledkem je stejný dotaz jako v předchozím případě.

S tímto tématem se setkáte na našich kurzech ExcelTown.
Aktuálně: kurzy můžete absolvovat jak online, tak prezenčně.