Dnes se podíváme na velmi užitečnou techniku v Excelu, a to jak sloučit více tabulek do jedné tabulky v Excelu, a to dynamicky. Určitě jste se někdy setkali s Excelem, ve kterém jste měli data v několika různých tabulkách nebo na několika listech a potřebovali jste data seskupit do jedné tabulky. Data na více listech se obtížně analyzují a musíte vymýšlet zbytečně složité kombinace excelových funkcí, abyste se dopočítali výsledku. Dnes si ukážeme, jak sloučit více tabulek do jedné tabulky pomocí Power Query.
Excelový soubor ke stažení
Jak sloučit více tabulek do jedné tabulky | Power Query
V dnešním příkladu máme například tržby pro produkty pro každý měsíc na vlastním listu. Lednové tržby máme na listu leden, únorové na listu únor atd. Když máte data rozdělená do několika tabulek nebo na několika listech, tak jako v dnešním příkladu, tak musíte vymýšlet zbytečně složité kombinace excelových funkcí, abyste z dat dostali to, co potřebujete. Už jen základní výpočet jako je spočítat celkové tržby pro vybraný produkt může být obtížné, když máte tabulky na několika listech nebo v několika separátních tabulkách. Nehledě na to, že pokud byste chtěli tato data analyzovat v kontingenční tabulce, tak se vám to nepovede, jelikož klasická kontingenční tabulka akceptuje jako zdroj pouze jednu zdrojovou tabulku.
Dnes si ukážeme techniku, která vám dovolí spojit tabulky do jedné sloučené tabulky a nebudete k tomu muset přitom použít ani jednu excelovou funkci. A asi nemusím říkat, že technika, kterou vám budu ukazovat nespočívá v prostém nakopírování tabulek pod sebe. Tabulky sloučíme dynamicky, takže se jakákoliv změna ve zdrojových datech, včetně přidání nových dat, projeví i ve sloučené tabulce.
Pro dnešní ukázku poslouží excelový sešit, ve kterém máme tři listy, leden, únor a březen. Jedná se o klasický případ z praxe, kdy v jednom Excelu například shromažďujeme prodejní data a každý měsíc zaznamenáme na samostatný list. Na každém listu máme přitom identickou tabulku. V tabulce je uvedené datum prodeje, druh prodaného produktu, počet prodaných kusů a celková výše tržeb. Tím, že máme každý měsíc zvlášť na speciálním listu, tak by pro nás bylo velmi obtížné analyzovat celkové tržby nebo spočítat tržby pro jednotlivé produkty na základě nějakých kritérií.
Pojďme si tedy ukázat, jak tyto tři tabulky na třech listech dostat do jedné jediné sloučené tabulky.
Ke sloučení tabulek použijeme nástroj, kterému se říká Power Query neboli Načíst a transformovat data. Tento excelový nástroj najdeme na kartě Data pod záložkou Načíst a Transformovat data. Máme zde několik možností, jak načíst data do nástroje Power Query. Už jsme si ukázali, jak můžeme do Excelu přes Power Query importovat data z dokumentu PDF nebo webu. Kromě načtení dat z PDF, webu z jiných excelových souborů, zde máme i možnost načíst data z excelové tabulky. Tím je myšlena oficiální excelová tabulka.
Převod tabulek na oficiální excelové tabulky
Takže první krok, který musíme udělat, je změnit tyto tři tabulky na třech listech na oficiální excelové tabulky. Nejrychleji to uděláme tak, že postupně do každé tabulky klikneme a zmáčkneme klávesovou zkratku CTRL+T. Potvrdíme, že naše tabulka má záhlaví a z obyčejné tabulky se stane oficiální excelová tabulka. Pro pořádek nezapomeneme tabulku pojmenovat na kartě Návrh tabulky. První tabulku pojmenujeme stejně jako list, takže leden. To samé zopakujeme i pro ostatní dvě tabulky. Klikneme do tabulky s únorem, zmáčkneme CTRL+T, potvrdíme výběr a přejmenujeme tabulku na únor. Zbývá březen, takže klikneme do tabulky, klávesová zkratka CTRL+T, potvrdíme a přejmenujeme tabulku na březen.
Když máme tabulky pojmenované, vrátíme se k lednové tabulce a klikneme do jakékoliv buňky v tabulce. Teď se můžeme vrátit k záložce Data > Načíst a transformovat a zde vybrat možnost Z tabulky nebo oblasti. Rozbalí se nástroj Power Query, kde vidíme načtenou tabulku za leden. Data máme upravená a vyčištěná, takže s nimi nechceme provádět žádné velké úpravy. Jediné, co můžeme udělat je u datumu upravit datový typ, jelikož vidíme, že se nám zde datum zobrazuje i s časem. To je pro naše účely zbytečné, jelikož v datech nemáme žádný konkrétní čas, takže klikneme na ikonu datového typu v záhlaví a změníme typ na Datum. Power Query se zeptá, zda chceme tento krok provést. Potvrdíme a datový typ se změnil na prosté datum.
Abychom mohli spojit tabulky dohromady, tak je postupně musíme všechny nahrát tímto způsobem do Power Query.
Leden máme nahraný, takže v liště vybereme Zavřít a Načíst, ale nevybereme přímo tuto ikonu, ale rozklikneme šipku a vybereme Zavřít a Načíst do. Zde se rozbalí několik možností, nás bude v tuto chvíli zajímat Pouze vytvořit připojení. Pokud bychom zvolili pouze Zavřít a Načíst, tak by se lednová tabulka znovu načetla do Excelu a měli bychom ji tam tedy dvakrát. Když vytvoříme připojení, tak se tabulka do excelového sešitu znovu nenačte. Výběr potvrdíme a připojení na lednovou tabulku se zobrazí v pravém bočním panelu.
Ten samý proces zopakujeme i pro únor. Překlikneme se na list únor, klikneme do tabulky a vybereme Načíst z tabulky. Opět můžeme jako jedinou úpravu udělat změnu datového typu u Datumu. Když jsme hotovi, znovu vybereme Zavřít a Načíst do > Pouze připojení a potvrdíme. V pravém panelu teď přibyla i únorová tabulka. To samé zopakujeme i pro březen. Klikneme do březnové tabulky, vybereme načíst z tabulky a změníme datový typ pro datum na prosté datum. Když jsme hotovi, tak tabulku nahrajeme zpět a vytvoříme pouze připojení.
Připojit tabulky
Teď máme všechny tři tabulky napojené přes Power Query. V pravém panelu klikneme na leden, jelikož je to první měsíc a klikneme pravým tlačítkem myši. Z nabídky vybereme připojit. V anglické verzi Excelu se připojit nachází pod názvem Append. Zobrazí se tabulka, kde můžeme vybrat, které tabulky se mají spojit. Důležité je, aby tabulky měly stejně pojmenované záhlaví. Tabulky se totiž budou spojovat přes názvy sloupců v záhlaví. Potřebujeme spojit více než dvě tabulky dohromady, takže vybereme možnost Tři a více tabulek. Zobrazení se trochu změní a teď si můžeme vybrat, které tabulky chceme připojit k lednové tabulce. Klikneme na únor a vybereme přidat. Klikneme na březen a opět vybereme přidat. Potvrdíme.
A Power Query pro nás vytvořilo sloučenou tabulku ze tří listů. Máme zde k dispozici náhled na data, takže pokud byste chtěli provádět jakékoliv změny, tak zde máte možnost. My jsme s daty spokojeni, takže jediné, co uděláme je, že pojmenujeme tabulku na Sloučená data.
Tentokrát chceme tabulku zobrazit ve formátu tabulky na samostatném listu, a nikoliv jako připojení, takže vybereme prosté Zavřít a Načíst. Po chvilce se sloučená tabulka načte na samostatný list v Excelu, který je pojmenovaný stejně jako tabulka. V pravém panelu vidíme rozdíl mezi připojením a načtením dat do Excelu. Sloučená tabulka je načtená přímo do Excelu, takže ukazuje celkový počet řádků. Sloučená tabulka je ve formátu oficiální excelové tabulky, takže s ní můžete normálně pracovat, používat ji pro výpočet funkcí a nebo ji použít jako zdroj pro kontingenční tabulku.
Nakonec ještě zkusíme aktualizaci dat. Na list leden zkusíme dodat jeden nový řádek dat. Když máme data dodána, tak se překlikneme na list Sloučená data a na liště Data vybereme Aktualizovat vše. Sledujte sloučenou tabulku a celkový počet řádků tabulky. Power Query začne aktualizovat data a když narazí na nová data v lednové tabulce, tak je do tabulky automaticky načte. Nová data se objevila ve sloučené tabulce a stejně tak se změnil celkový počet řádků v tabulce.
2 komentáře
Teď jste mne dostala. Vy snad dokážete číst myšlenky. 👍👍👍
Toto video bylo, pro mě, zatím nejužitečnější. Power query jsem si musel doinstalovat jako doplněk, protože mám starší excel. Jinak super. Ušetřilo mi to spoustu práce. Moc děkuju.