Obsah
Formulácia problému
Pozrime sa na krásne riešenie jednej z veľmi štandardných situácií, s ktorými sa väčšina používateľov Excelu skôr či neskôr stretne: potrebujete rýchlo a automaticky zhromaždiť údaje z veľkého množstva súborov do jednej výslednej tabuľky.
Predpokladajme, že máme nasledujúci priečinok, ktorý obsahuje niekoľko súborov s údajmi z miest pobočiek:
Na počte súborov nezáleží a v budúcnosti sa môže zmeniť. Každý súbor má hárok s názvom Predajkde sa nachádza dátová tabuľka:
Počet riadkov (poradí) v tabuľkách je samozrejme rôzny, ale množina stĺpcov je všade štandardná.
Úloha: zhromaždiť údaje zo všetkých súborov do jednej knihy s následnou automatickou aktualizáciou pri pridávaní alebo odstraňovaní mestských súborov alebo riadkov v tabuľkách. Podľa konečnej konsolidovanej tabuľky potom bude možné zostaviť ľubovoľné reporty, kontingenčné tabuľky, filtrovať-triediť dáta atď. Hlavná vec je vedieť zbierať.
Vyberáme zbrane
Pre riešenie potrebujeme najnovšiu verziu Excelu 2016 (potrebná funkcionalita je v nej už štandardne zabudovaná) alebo predchádzajúce verzie Excelu 2010-2013 s nainštalovaným bezplatným doplnkom mocenský dotaz od spoločnosti Microsoft (stiahnite si ho tu). Power Query je super flexibilný a super výkonný nástroj na načítanie údajov do Excelu z vonkajšieho sveta, ich odstránenie a spracovanie. Power Query podporuje takmer všetky existujúce zdroje údajov – od textových súborov po SQL a dokonca aj Facebook 🙂
Ak nemáte Excel 2013 alebo 2016, nemôžete čítať ďalej (len si robíte srandu). V starších verziách Excelu sa takáto úloha dá realizovať len naprogramovaním makra vo Visual Basicu (čo je pre začiatočníkov veľmi náročné) alebo monotónnym ručným kopírovaním (ktoré trvá dlho a generuje chyby).
Krok 1. Importujte jeden súbor ako vzor
Najprv importujme údaje z jedného zošita ako príklad, aby Excel „prebral nápad“. Ak to chcete urobiť, vytvorte nový prázdny zošit a…
- ak máte Excel 2016, otvorte kartu dátum a potom Vytvoriť dopyt – Zo súboru – Z knihy (Údaje – Nový dotaz – Zo súboru – Z Excelu)
- ak máte Excel 2010-2013 s nainštalovaným doplnkom Power Query, otvorte kartu mocenský dotaz a vyberte na ňom Zo súboru – Z knihy (Zo súboru – Z Excelu)
Potom v okne, ktoré sa otvorí, prejdite do nášho priečinka so správami a vyberte ktorýkoľvek z mestských súborov (nezáleží na tom, ktorý z nich, pretože všetky sú typické). Po niekoľkých sekundách by sa malo zobraziť okno Navigátora, kde musíte na ľavej strane vybrať požadovaný hárok (Predaj) a jeho obsah sa zobrazí na pravej strane:
Ak kliknete na tlačidlo v pravom dolnom rohu tohto okna Stiahnuť ▼ (Naložiť), potom sa tabuľka okamžite importuje do hárku v pôvodnej podobe. Pre jeden súbor je to dobré, ale potrebujeme načítať veľa takýchto súborov, takže pôjdeme trochu inak a klikneme na tlačidlo Oprava (Edit). Potom by sa mal editor dotazov Power Query zobraziť v samostatnom okne s našimi údajmi z knihy:
Ide o veľmi výkonný nástroj, ktorý vám umožní „dokončiť“ tabuľku do zobrazenia, ktoré potrebujeme. Aj povrchný popis všetkých jeho funkcií by zabral asi sto strán, ale ak je to veľmi stručné, pomocou tohto okna môžete:
- odfiltrovať nepotrebné údaje, prázdne riadky, riadky s chybami
- triediť údaje podľa jedného alebo viacerých stĺpcov
- zbaviť sa opakovania
- rozdeľte nalepovací text podľa stĺpcov (oddeľovačmi, počtom znakov atď.)
- usporiadať text (odstrániť nadbytočné medzery, opraviť malé a veľké písmená atď.)
- konvertujte dátové typy všetkými možnými spôsobmi (premeňte čísla ako text na normálne čísla a naopak)
- transponovať (otáčať) tabuľky a rozširovať dvojrozmerné krížové tabuľky na ploché
- pridajte do tabuľky ďalšie stĺpce a používajte v nich vzorce a funkcie pomocou jazyka M zabudovaného do Power Query.
- ...
Pridajme napríklad stĺpec s textovým názvom mesiaca do našej tabuľky, aby bolo neskôr jednoduchšie vytvárať zostavy kontingenčných tabuliek. Ak to chcete urobiť, kliknite pravým tlačidlom myši na hlavičku stĺpca dátaa vyberte príkaz Duplicitný stĺpec (Duplicitný stĺpec)a potom kliknite pravým tlačidlom myši na hlavičku duplicitného stĺpca, ktorý sa zobrazí, a vyberte príkaz Príkazy Transformácia – Mesiac – Názov mesiaca:
Mal by sa vytvoriť nový stĺpec s textovými názvami mesiaca pre každý riadok. Dvojitým kliknutím na nadpis stĺpca ho môžete premenovať z Dátum kopírovania k pohodlnejšiemu mesiac, napr.
Ak v niektorých stĺpcoch program celkom správne nerozpoznal typ údajov, môžete mu pomôcť kliknutím na ikonu formátu na ľavej strane každého stĺpca:
Pomocou jednoduchého filtra môžete vylúčiť riadky s chybami alebo prázdnymi riadkami, ako aj nepotrebných manažérov alebo zákazníkov:
Všetky vykonané transformácie sú navyše zafixované v pravom paneli, kde sa dajú vždy vrátiť späť (kríž) alebo zmeniť ich parametre (ozubené koleso):
Ľahké a elegantné, však?
Krok 2. Transformujme našu požiadavku na funkciu
Aby sme následne mohli zopakovať všetky transformácie údajov vykonané pre každú importovanú knihu, musíme našu vytvorenú požiadavku previesť na funkciu, ktorá sa následne použije na všetky naše súbory. Urobiť to je v skutočnosti veľmi jednoduché.
V editore dotazov prejdite na kartu Zobraziť a kliknite na tlačidlo Pokročilý editor (Zobraziť — Pokročilý editor). Malo by sa otvoriť okno, kde budú všetky naše predchádzajúce akcie napísané vo forme kódu v jazyku M. Upozorňujeme, že cesta k súboru, ktorý sme pre príklad importovali, je pevne zakódovaná v kóde:
Teraz urobme pár úprav:
Ich význam je jednoduchý: prvý riadok (cesta k súboru)=> zmení našu procedúru na funkciu s argumentom cesta k súboru, a nižšie zmeníme pevnú cestu na hodnotu tejto premennej.
Všetky. Kliknite na úprava a mal by vidieť toto:
Nebojte sa, že dáta zmizli – v skutočnosti je všetko v poriadku, všetko by malo vyzerať takto 🙂 Úspešne sme vytvorili našu vlastnú funkciu, kde je zapamätaný celý algoritmus na import a spracovanie dát bez toho, aby bol viazaný na konkrétny súbor . Zostáva mu dať zrozumiteľnejší názov (napr getData) na paneli vpravo v poli Krstné meno a môžete žať Domov — Zatvorte a stiahnite (Domov – zavrieť a načítať). Upozorňujeme, že cesta k súboru, ktorý sme pre príklad importovali, je pevne zakódovaná v kóde. Vrátite sa do hlavného okna programu Microsoft Excel, ale vpravo by sa mal objaviť panel s vytvoreným pripojením k našej funkcii:
Krok 3. Zhromažďovanie všetkých súborov
Všetko najťažšie je za nami, ostáva príjemné a ľahké. Prejdite na kartu Údaje – Vytvoriť dopyt – Zo súboru – Z priečinka (Údaje — Nový dotaz — Zo súboru — Z priečinka) alebo, ak máte Excel 2010-2013, podobne ako na karte mocenský dotaz. V zobrazenom okne zadajte priečinok, v ktorom sa nachádzajú všetky naše súbory zdrojových miest, a kliknite OK. V ďalšom kroku by sa malo otvoriť okno, v ktorom budú uvedené všetky súbory programu Excel nájdené v tomto priečinku (a jeho podpriečinkoch) a podrobnosti o každom z nich:
kliknite Zmena (Edit) a opäť sa dostaneme do známeho okna editora dotazov.
Teraz musíme do našej tabuľky s našou vytvorenou funkciou pridať ďalší stĺpec, ktorý „vytiahne“ údaje z každého súboru. Ak to chcete urobiť, prejdite na kartu Pridať stĺpec – vlastný stĺpec (Pridať stĺpec — Pridať vlastný stĺpec) a v zobrazenom okne zadajte našu funkciu getData, pričom ako argument uveďte úplnú cestu ku každému súboru:
Po kliknutí na OK vytvorený stĺpec by mal byť pridaný do našej tabuľky vpravo.
Teraz vymažte všetky nepotrebné stĺpce (ako v Exceli pomocou pravého tlačidla myši – odstrániť), ponechajúc len pridaný stĺpec a stĺpec s názvom súboru, pretože tento názov (presnejšie mesto) bude užitočné mať v celkových údajoch pre každý riadok.
A teraz ten „wow moment“ – kliknite na ikonu s vlastnými šípkami v pravom hornom rohu pridaného stĺpca s našou funkciou:
… zrušte začiarknutie Ako predponu použite pôvodný názov stĺpca (ako predponu použite pôvodný názov stĺpca)a kliknite na tlačidlo OK. Naša funkcia načíta a spracuje údaje z každého súboru podľa zaznamenaného algoritmu a zhromaždí všetko do spoločnej tabuľky:
Pre úplnú krásu môžete z prvého stĺpca s názvami súborov odstrániť aj prípony .xlsx – štandardným nahradením „nič“ (kliknite pravým tlačidlom myši na hlavičku stĺpca – náhradka) a premenujte tento stĺpec na City. A tiež opravte formát údajov v stĺpci s dátumom.
Všetky! Kliknite na Domov – zavrieť a načítať (Domov – zavrieť a načítať). Všetky údaje zhromaždené dopytom pre všetky mestá sa nahrajú do aktuálneho hárku Excelu vo formáte „inteligentnej tabuľky“:
Vytvorený spoj a našu montážnu funkciu nie je potrebné nijak zvlášť ukladať – ukladajú sa spolu s aktuálnym súborom bežným spôsobom.
V budúcnosti pri akýchkoľvek zmenách v priečinku (pridávanie alebo odstraňovanie miest) alebo v súboroch (zmena počtu riadkov) bude stačiť kliknúť pravým tlačidlom myši priamo na tabuľku alebo na dotaz v pravom paneli a vybrať príkaz Aktualizovať a uložiť (Obnoviť) – Power Query znova „obnoví“ všetky údaje v priebehu niekoľkých sekúnd.
PS
novela. Po aktualizáciách z januára 2017 sa Power Query naučilo, ako samostatne zbierať zošity Excelu, teda už nie je potrebné vytvárať samostatnú funkciu – deje sa to automaticky. Druhý krok z tohto článku už teda nie je potrebný a celý proces sa výrazne zjednoduší:
- Vybrať Vytvoriť požiadavku – Zo súboru – Z priečinka – Vyberte priečinok – OK
- Po zobrazení zoznamu súborov stlačte Zmena
- V okne Editor dotazov rozbaľte stĺpec Binárny dvojitou šípkou a vyberte názov hárka, ktorý sa má prevziať z každého súboru
A to je všetko! Pieseň!
- Prerobenie krížovej lišty na plochú vhodnú na stavbu kontingenčných stolov
- Vytváranie animovaného bublinového grafu v Power View
- Makro na zostavenie listov z rôznych súborov programu Excel do jedného