Obsah
Formulace problému
Podívejme se na krásné řešení jedné z velmi standardních situací, se kterými se dříve či později většina uživatelů Excelu potýká: potřebujete rychle a automaticky shromáždit data z velkého množství souborů do jedné výsledné tabulky.
Předpokládejme, že máme následující složku, která obsahuje několik souborů s daty z pobočkových měst:
Na počtu souborů nezáleží a může se v budoucnu změnit. Každý soubor má list s názvem Prodejkde se nachází datová tabulka:
Počet řádků (objednávek) v tabulkách je samozřejmě různý, ale sada sloupců je všude standardní.
Úkol: shromáždit data ze všech souborů do jedné knihy s následnou automatickou aktualizací při přidávání nebo mazání souborů měst nebo řádků v tabulkách. Podle výsledné konsolidované tabulky pak bude možné sestavovat libovolné reporty, kontingenční tabulky, filtrovat-sortovat data atd. Hlavní je umět sbírat.
Vybíráme zbraně
K řešení potřebujeme nejnovější verzi Excelu 2016 (potřebná funkcionalita je v ní již ve výchozím nastavení zabudována) nebo předchozí verze Excelu 2010-2013 s nainstalovaným bezplatným doplňkem Dotaz na napájení od společnosti Microsoft (stáhněte si jej zde). Power Query je super flexibilní a super výkonný nástroj pro načítání dat do Excelu z vnějšího světa, jejich odstranění a zpracování. Power Query podporuje téměř všechny existující zdroje dat – od textových souborů po SQL a dokonce i Facebook 🙂
Pokud nemáte Excel 2013 nebo 2016, tak nemůžete číst dále (dělám si srandu). Ve starších verzích Excelu lze takový úkol splnit pouze naprogramováním makra ve Visual Basicu (což je pro začátečníky velmi obtížné) nebo monotónním ručním kopírováním (které trvá dlouho a generuje chyby).
Krok 1. Importujte jeden soubor jako vzorek
Nejprve importujme data z jednoho sešitu jako příklad, aby Excel „vychytal myšlenku“. Chcete-li to provést, vytvořte nový prázdný sešit a…
- pokud máte Excel 2016, otevřete kartu Data a pak Vytvořit dotaz – Ze souboru – Z knihy (Data — Nový dotaz- Ze souboru — Z Excelu)
- pokud máte Excel 2010-2013 s nainstalovaným doplňkem Power Query, otevřete kartu Dotaz na napájení a vyberte na něm Ze souboru – Z knihy (Ze souboru — Z Excelu)
Poté v okně, které se otevře, přejděte do naší složky se zprávami a vyberte libovolný ze souborů města (nezáleží na tom, který, protože všechny jsou typické). Po několika sekundách by se mělo objevit okno Navigátor, kde musíte na levé straně vybrat list, který potřebujeme (Prodej), a jeho obsah se zobrazí na pravé straně:
Pokud kliknete na tlačítko v pravém dolním rohu tohoto okna Ke stažení (Zatížení), pak bude tabulka okamžitě importována do listu v původní podobě. Pro jeden soubor je to dobré, ale potřebujeme načíst mnoho takových souborů, takže půjdeme trochu jinak a klikneme na tlačítko Oprava (Edit). Poté by se měl editor dotazů Power Query zobrazit v samostatném okně s našimi daty z knihy:
Jedná se o velmi výkonný nástroj, který vám umožní „dokončit“ tabulku do požadovaného zobrazení. I povrchní popis všech jeho funkcí by zabral asi sto stran, ale pokud je to velmi stručné, pomocí tohoto okna můžete:
- odfiltrujte nepotřebná data, prázdné řádky, řádky s chybami
- třídit data podle jednoho nebo více sloupců
- zbavit se opakování
- rozdělit nalepovací text podle sloupců (oddělovači, počtem znaků atd.)
- seřaďte text (odstraňte přebytečné mezery, opravte velká a malá písmena atd.)
- převádějte datové typy všemi možnými způsoby (přeměňte čísla jako text na normální čísla a naopak)
- transponovat (otáčet) tabulky a rozšiřovat dvourozměrné křížové tabulky na ploché
- přidejte do tabulky další sloupce a používejte v nich vzorce a funkce pomocí jazyka M zabudovaného do Power Query.
- ...
Přidejme do naší tabulky například sloupec s textovým názvem měsíce, aby bylo později snazší vytvářet sestavy kontingenční tabulky. Chcete-li to provést, klepněte pravým tlačítkem myši na záhlaví sloupce datuma vyberte příkaz Duplicitní sloupec (Duplicitní sloupec)a poté klikněte pravým tlačítkem na záhlaví duplicitního sloupce, který se zobrazí, a vyberte Příkazy Transform – Měsíc – Název měsíce:
Měl by být vytvořen nový sloupec s textovými názvy měsíce pro každý řádek. Dvojitým kliknutím na záhlaví sloupce jej můžete přejmenovat Datum kopírování k pohodlnějšímu Měsíc, např.
Pokud v některých sloupcích program zcela správně nerozpoznal datový typ, můžete mu pomoci kliknutím na ikonu formátu na levé straně každého sloupce:
Pomocí jednoduchého filtru můžete vyloučit řádky s chybami nebo prázdnými řádky, stejně jako zbytečné manažery nebo zákazníky:
Všechny provedené transformace jsou navíc pevně dané v pravém panelu, kde je lze vždy vrátit zpět (kříž) nebo změnit jejich parametry (ozubené kolo):
Lehké a elegantní, že?
Krok 2. Transformujme náš požadavek na funkci
Abychom následně mohli opakovat všechny transformace dat provedené pro každou importovanou knihu, musíme náš vytvořený požadavek převést na funkci, která bude následně aplikována na všechny naše soubory. Udělat to je vlastně velmi jednoduché.
V Editoru dotazů přejděte na kartu Zobrazit a klikněte na tlačítko Pokročilý editor (Zobrazit — Pokročilý editor). Mělo by se otevřít okno, kde budou všechny naše předchozí akce zapsány ve formě kódu v jazyce M. Upozorňujeme, že cesta k souboru, který jsme pro příklad importovali, je pevně zakódována v kódu:
Nyní provedeme několik úprav:
Jejich význam je jednoduchý: první řádek (cesta k souboru)=> změní naši proceduru na funkci s argumentem cesta k souboru, a níže změníme pevnou cestu na hodnotu této proměnné.
Všechno. Klikněte na úprava a měl by vidět toto:
Nebojte se, že data zmizela – ve skutečnosti je vše v pořádku, vše by mělo vypadat takto 🙂 Úspěšně jsme vytvořili naši vlastní funkci, kde je zapamatován celý algoritmus pro import a zpracování dat, aniž by byl vázán na konkrétní soubor . Zbývá dát mu srozumitelnější název (např getData) v panelu vpravo v poli Jméno a můžete sklízet Domů — Zavřete a stáhněte (Domů – Zavřít a načíst). Vezměte prosím na vědomí, že cesta k souboru, který jsme pro příklad importovali, je pevně zakódována v kódu. Vrátíte se do hlavního okna Microsoft Excel, ale vpravo by se měl objevit panel s vytvořeným připojením k naší funkci:
Krok 3. Shromáždění všech souborů
Vše nejtěžší je za námi, zbývá příjemné a snadné. Přejděte na kartu Data – Vytvořit dotaz – Ze souboru – Ze složky (Data — Nový dotaz — Ze souboru — Ze složky) nebo, pokud máte Excel 2010-2013, podobně jako na kartě Dotaz na napájení. V okně, které se objeví, určete složku, kde jsou umístěny všechny naše soubory zdrojového města, a klikněte OK. V dalším kroku by se mělo otevřít okno, kde budou uvedeny všechny soubory Excel nalezené v této složce (a jejích podsložkách) a podrobnosti pro každý z nich:
klikněte Přeměna (Edit) a opět se dostaneme do známého okna editoru dotazů.
Nyní musíme do naší tabulky přidat další sloupec s naší vytvořenou funkcí, která bude „tahat“ data z každého souboru. Chcete-li to provést, přejděte na kartu Přidat sloupec – Vlastní sloupec (Přidat sloupec — Přidat vlastní sloupec) a v okně, které se objeví, zadejte naši funkci getData, přičemž pro něj jako argument uveďte úplnou cestu ke každému souboru:
Po kliknutí na tlačítko OK vytvořený sloupec by měl být přidán do naší tabulky vpravo.
Nyní smažeme všechny nepotřebné sloupce (jako v Excelu pomocí pravého tlačítka myši – Odstranit), ponechte pouze přidaný sloupec a sloupec s názvem souboru, protože tento název (přesněji město) se bude hodit mít v celkových datech pro každý řádek.
A teď ten „wow moment“ – klikněte na ikonku s vlastními šipkami v pravém horním rohu přidaného sloupce s naší funkcí:
… zrušte zaškrtnutí Jako předponu použijte původní název sloupce (Jako předponu použijte původní název sloupce)a klepněte na tlačítko OK. A naše funkce načte a zpracuje data z každého souboru podle zaznamenaného algoritmu a shromáždí vše do společné tabulky:
Pro úplnou krásu můžete z prvního sloupce s názvy souborů odstranit i přípony .xlsx – standardním nahrazením „nic“ (klikněte pravým tlačítkem na záhlaví sloupce – Náhradní) a přejmenujte tento sloupec na Město. A také opravit formát dat ve sloupci s datem.
Všechno! Klikněte na Domů – Zavřít a načíst (Domů – Zavřít a načíst). Všechna data shromážděná dotazem pro všechna města budou nahrána do aktuálního listu Excelu ve formátu „chytré tabulky“:
Vytvořený spoj a naši montážní funkci není třeba nijak zvlášť ukládat – ukládají se běžným způsobem společně s aktuálním souborem.
V budoucnu při jakýchkoli změnách ve složce (přidání nebo odebrání měst) nebo v souborech (změna počtu řádků) bude stačit kliknout pravým tlačítkem přímo na tabulku nebo na dotaz v pravém panelu a vybrat příkaz Aktualizovat a uložit (Obnovit) – Power Query během několika sekund znovu „obnoví“ všechna data.
PS
Pozměňovací návrh. Po aktualizacích z ledna 2017 se Power Query naučil shromažďovat sešity Excel sám, tj. již není potřeba vytvářet samostatnou funkci – děje se to automaticky. Druhý krok z tohoto článku tedy již není potřeba a celý proces se znatelně zjednoduší:
- Vybrat Vytvořit požadavek – Ze souboru – Ze složky – Vyberte složku – OK
- Po zobrazení seznamu souborů stiskněte Přeměna
- V okně Editor dotazů rozbalte sloupec Binární dvojitou šipkou a vyberte název listu, který se má převzít z každého souboru
A to je vše! Píseň!
- Přepracování křížové tabulky na plochou vhodnou pro stavbu otočných stolů
- Vytvoření animovaného bublinového grafu v Power View
- Makro pro sestavení listů z různých souborů aplikace Excel do jednoho