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

V předchozím článku jsme si ukázali, jak efektivně spojit data z několika sešitů Excelu dohromady, avšak pouze pro tabulky z jednoho konkrétního listu. Toto pokračování ukáže, jak postupovat, pokud bychom potřebovali spojit několik sešitů a v nich několik listů najednou.

Ukázka bude opět prováděna na datech měsíční míry nezaměstnanosti v České republice za období 01/2022-03/2023 získaných ze stránek MPSV (https://www.mpsv.cz/web/cz/mesicni) s několika vstupními úpravami pro potřebu ukázky (rozbalení formátu .zip, přejmenování či přemístění souborů do jedné složky).

Jak sloučit několik listů z různých sešitů dohromady?

Stejně jako při spojování tabulek z jednoho listu v několika sešitech, i v případě slučování dat z více listů použijeme Excelový doplněk Power Query. Například v prázdném sešitu vybereme na kartě Data možnost Načíst data – Ze souboru – Ze složky a zvolíme příslušnou složku, ve které jsou soubory uloženy. V dalším kroku stiskneme možnost Transformovat.

Platí, že pro co nejjednodušší slučování musí být všechny soubory uložené v jedné složce, mít stejnou příponu (nikoliv kombinace .xls a .xlsx), údaje se musí nacházet ve všech Excelech ve stejných sloupcích a listy, které chceme spojovat, by měly mít stejný název ve všech sešitech.

Jakmile po výběru složky zvolíme možnost Transformovat data, otevře se editor Power Query. Vybereme typicky v pořadí první dva sloupce Content a Name (pomocí klávesy Shift) a pravým tlačítkem vybereme položku Odebrat ostatní sloupce.

Dále přidáme Vlastní sloupec a do zadávacího okna napíšeme příkaz Excel.Workbook([Content]).

Dále klikneme na symbol šipek u nově vytvořeného sloupce s názvem Vlastní a obsah sloupce rozbalíme prostřednictvím tlačítka OK.

Zkontrolujeme, že ve sloupci Vlastní.Kind se nachází pouze položky listů Sheet, nikoliv nic dalšího. Pokud tomu tak není, ostatní položky odebereme – v našem případě odškrtneme DefinedName.

V dalším kroku je potřeba odebrat všechny ostatní sloupce kromě názvu souboru Name, názvu listu Vlastní.Name a sloupce ukrývající samotná data Vlatní.Data.

Následně již rozbalíme jednotlivé tabulky ve sloupci Vlastní.Data prostřednictvím kliknutí na šipky ve hlavičce sloupce. Zejména pokud by se v načítaných sešitech Excelu nacházely nadpisy sloupců v prvním řádku, je vhodné rovněž odškrtnout volbu Používat původní název sloupce jako předponu.

Tímto postupem došlo k načtení dat z tabulek z jednotlivých listů ze všech sešitů uložených ve složce.

Jelikož se však načetla data z celých listů a v našem případě se nejednalo o základní tabulky, ve kterých by data začínala na prvním řádku a navíc obsahují několik sloupců, kdy se na každém listu nachází jiná proměnná, bylo potřeba takto načtená data dále upravit. Celý postup spojování včetně dalších úprav vhodných pro analýzu zachycuje následující video.