1. Jak efektivně sloučit listy/sešity Excelu dohromady?

To, že každá analýza začíná u dat, je vcelku jasné. Ať máme v plánu počítat základní popisné statistiky nebo vytvářet složitý predikční model, vždy je potřeba mít data na vstupu vhodně připravená. Dnešní článek je věnován úpravě vstupních dat, a to konkrétně spojování proměnných z různých listů/sešitů Excelu. V praxi totiž není výjimkou, že datový podklad nemáme jeden, ale údaje jsou dostupné v několika samostatných souborech, například za každou pobočku, pohlaví nebo třeba region. V takových případech je nutné jednotlivé soubory sloučit do jedné datové matice, nad kterou následně bude samotná analýza probíhat.

Spojit dohromady data ze dvou nebo tří souborů zpravidla není problém a zvládne jej každý analytik pomocí prostého kopírování CTRL+C a CTRL+V. Nicméně občas je vstupních souborů k dispozici opravdu mnoho a v takovém případě by již ruční práce byla velmi neefektivní, a to jak z ohledu časové náročnosti, tak pro riziko různých chyb. Z toho důvodu je často lepší vytvořit automatizované řešení, které sloučení souborů udělá za nás.

Jedním z vhodných nástrojů pro tuto činnost je Excelový doplněk Power Query. V tomto a navazujícím článku si postupně ukážeme, jak s pomocí Power Query poměrně jednoduše spojit dohromady:

• Několik sešitů
• Několik listů z několika sešitů.

Pozn.: Sloučení dat z několika listů jednoho sešitu dohromady lze pohodlně provést např. prostřednictvím funkce SVYHLEDAT().

Příklady budou názorně prezentovány na datech zachycujících měsíční míru nezaměstnanosti v České republice v období 01/2022-03/2023, jenž byly získány ze stránek MPSV (https://www.mpsv.cz/web/cz/mesicni) a následně vhodně upraveny pro potřeby ukázek (rozbalení formátu .zip, přejmenování či přemístění souborů do jedné složky).

Jak efektivně sloučit několik sešitů dohromady?

Cílem této části článku je získat jednu souhrnnou tabulku s měsíčními podíly nezaměstnaných osob ve všech okresech ČR od roku 2022. Konkrétně půjde o spojení tabulek jednoho konkrétního listu nacházejícího se v 15 samostatných excelovských souborech. Ke správnému načtení dat je zapotřebí, aby všechny soubory byly uloženy v jedné složce, měly stejnou příponu (tedy nikoliv např. kombinace .xls a .xlsx), údaje se nacházely ve všech Excelech ve stejných sloupcích a listy, které chceme spojovat, měly stejný název ve všech sešitech.

Pro načtení údajů z několika sešitů zvolíme (například v prázdném sešitu Excelu) na kartě Data možnost Načíst data – Ze souboru – Ze složky a vybereme složku, ve které jsou soubory uloženy.

Dále zvolíme možnost Kombinovat – Sloučit a transformovat data a v dalším okně vybereme název listu, jehož data chceme spojit dohromady.

Následně dojde k otevření editoru Power Query, ve kterém jsou již data sloučeny do jednoho dokumentu. Nyní je však při další práci potřeba dbát značné ostražitosti, jelikož se do programu načetly vždy celé tabulky z původních listů – tedy včetně opakujících se nadpisů nebo např. prázdných řádků mezi nadpisy a samotnými hodnotami. Identifikátor původního sešitu nalezneme v prvním sloupci (Zdroj.Název), který obsahuje názvy zdrojových Excelů.

Aby byla data lépe použitelná pro další analýzu, je nutné provést několik úprav, jako například odstranit prázdné nebo nezajímavé řádky/sloupce, vytvořit jednodušší sloupec označující příslušné období, vhodně pojmenovat sloupce nebo část tabulky transformovat do vhodnější podoby. Celý postup slučování a úprav, spolu s návrhem na transformaci vhodnou pro analýzu, je zachycen v následujícím videu.

Rádi byste se o statistice a analýze dat dozvěděli více? Chcete se stát mistrem ve svém oboru nebo si jen potřebujete doplnit znalosti? V ACREA nabízíme širokou nabídku kurzů pro váš profesní růst. Máte-li jiný dotaz. Nebojte se využít naši nezávaznou konzultaci, při které vám rádi zodpovíme všechny vaše dotazy a najdeme vhodné řešení.