Automatická konsolidace dat ze souborů Excelu

Mistrovství v PowerQuery – automatické vytváření reportů v Excelu – kurz pro pokročilé uživatele Excelu Jiří Číhař – www.dataspectrum.cz

Automatická konsolidace dat ze souborů Excelu Tento postup je prezentován v průběhu kurzu Mistrovství v Power Query – automatické vytváření reportů v Excelu http://www.dataspectrum.cz/pages/kurzy/kurzexcel.html Zdrojová data pro tento příklad si můžete stáhnout zde – konsolidace dat Excelu Ve složce Data na disku D: máme uloženy 3 sešity Excelu, obsahující záznamy o uskutečněných prodejích obchodníků v dané pobočce:   

Pardubice.xlsx Olomouc.xlsx Liberec.xlsx

V sešitech se nacházejí listy s detailními záznamy pro každého obchodníka – počet listů je pro každý sešit jiný. Záložky listu obsahují jméno obchodníka:

Struktura listů s daty je jednotná:   

sloupec Datum – obsahuje kalendářní datum realizovaného prodeje sloupec Název zboží – obsahuje název prodaného zboží sloupec Prodej – obsahuje částku, za kterou bylo zboží prodáno

V reálné praxi musíme počítat i s chybami uživatelů – v některých sešitech našeho příkladu jsou obsaženy listy, které manažer pobočky při vyplňování výkazu zapomněl odstranit – List1, List2. V sešitu Olomouc.xlsx jsou tyto listy prázdné, v sešitu Pardubice.xlsx je v listu List1 dokonce i „zapomenutá“ pomocná tabulka. Sešit Olomouc.xlsx obsahuje i pojmenovanou oblast buněk Tržby, kterou manažer pobočky také zapomněl odstranit.

1


Naším cílem je vytvořit systém, který: 1. umožní vytvořit souhrnnou tabulku všech prodejů všech obchodníků ze všech poboček společnosti 2. doplní v této tabulce automaticky jméno obchodníka k jeho záznamům 3. konsoliduje data ze všech sešitů uložených v zadané složce, aniž je předem znám jejich počet a struktura názvů 4. při konsolidaci ignoruje listy, které neobsahují data prodejů 5. při doplnění nových údajů do složky (nové sešity pro nové pobočky nebo nové listy pro nové prodejce) automaticky načte data do tabulky konsolidace 6. při odstranění údajů ve složce (odstranění sešitů nebo listů) automaticky odstraní data v tabulce konsolidace 7. lze snadno editovat nebo přenést na jiný počítač 8. není založený na makrech nebo programování v prostředí VBA – lze jej použít i v prostředí, kde jsou makra zakázána

2


PowerQuery Řešení vytvoříme pomocí nástroje PowerQuery, který umožňuje: 

   



napojovat Excel na externí zdroje dat – jiné sešity Excelu, databáze Access, data na SQL serveru, databáze SAP, databáze Oracle, textové soubory, OLAP kostky, data na webových úložištích… načtená data konsolidovat načtená data editovat a transformovat před jejich uložením do sešitu Excelu načtená data doplnit o další informace a nové sloupce s doplňkovými výpočty načtená data zobrazit v listu Excelu NEBO v případě rozsáhlých dat (více než 1 milion záznamů) data zpracovat kontingenčními tabulkami nebo nástrojem PowerPivot před jejich zobrazením v listu automatizovat řešení pomocí objektů, metod a funkcí nového jazyka M, který je nezávislý na jazyku VBA

Nástroj PowerQuery můžete využívat ve verzích Excel 2010, Excel 2013 a Excel 2016.

Excel 2016 – PowerQuery je standardní součástí všech licenčních verzí Excel 2010 a Excel 2013 – PowerQuery je standardní součástí pouze ve verzi Excel pro profesionály. Můžete si jej však zcela zdarma doinstalovat po stažení z webu Microsoftu z adresy https://www.microsoft.com/cs-cz/download/details.aspx?id=39379

3


Excel 2016 – editor PowerQuery spustíte pomocí příkazu Spustit Editor dotazů nebo automaticky vytvořením nového dotazu s napojením na externí data.

Excel 2010 a Excel 2013 – PowerQuery má svoji vlastní kartu a editor aktivujete klepnutím na příkaz Launch Editor.

Editor PowerQuery má své vlastní aplikační okno s řadou nástrojů - PowerQuery pro Excel 2016

PowerQuery pro Excel 2010 a Excel 2013

PowerQuery pro Excel 2010 a Excel 2013 je k dispozici pouze v angličtině, ve verzi Excel 2016 je PowerQuery již lokalizován do češtiny nebo slovenštiny.

4


Postup Řešení lze rozdělit do několika navazujících kroků: 1. 2. 3. 4. 5. 6. 7. 8. 9.

Napojení na zdroj dat. Spojení listů s daty do jedné tabulky. Vyloučení prázdných listů a listů bez dat prodeje z výsledné konsolidace. Načtení dat s vynecháním opakujících se záhlaví sloupců. Formátovaní sloupců pro správné zobrazení dat (text, datum, číslo). Načtení dat do listu. Nastavení automatické aktualizace při změně dat. Změna složky se soubory obsahující data pro konsolidaci. Zálohování postupu konsolidace pro opakované využití nebo přenesení na jiný počítač.

Postup pro Power Query ve verzi Excel 2010 a Excel 2013 naleznete na straně 31 - 56 Postup pro Power Query ve verzi Excel 2016 1. Napojení na zdroj dat Otevřeme nový sešit (CTRL + N) do kterého budeme chtít načíst data a ve kterém bude uloženo propojení s daty spolu s postupem konsolidace.

Na kartě Data klepneme ve skupině Načíst a transponovat na příkaz Ze složky.

5


V dialogovém okně zvolíme pomocí tlačítka Procházet požadovanou složku – v našem případě D:/Data.

Excel aktivuje editor PowerQuery se zobrazením informací o všech sešitech uložených ve zvolené složce.

Okno editoru zobrazí i pracovní podokno v pravé části, které ukládá informaci o jednotlivých krocích. V poli Název změníme název dotazu pro snazší orientaci v případě, že vytváříme mnoho dotazů pro práci s našimi daty – použijeme název NactiDataProdeje.

Z načtených informací ponecháme pouze sloupec Content – klepneme PRAVÝM tlačítkem myši na záhlaví sloupce a v místní nabídce klepneme na příkaz Odebrat ostatní sloupce.

6


V editoru PowerQuery nyní zůstane jediný sloupec s popisnými informacemi propojení na data.

V podokně Nastavení dotazů v poli Použitý postup vidíme heslovitý popis jednotlivých kroků dotazu. Klepnutím na položku v seznamu můžeme zobrazit nebo editovat krok, který zastupuje.

2. Spojení listů s daty do jedné tabulky Na kartě Přidání sloupce klepneme ve skupině Obecné na příkaz Přidat vlastní sloupec.

Zapíšeme název sloupce GetExcelData01 a funkci, pomocí které určíme obsah buněk nově vloženého sloupce:

=Excel.Workbook([Content]) Tato funkce říká, že chceme načíst veškerý obsah propojených sešitů do našeho dotazu. Funkci nemusíme zapisovat celou ručně - můžeme zapsat pouze začátek

=Excel.Workbook( a následně klepnout v pravé části dialogového pole na nabídnutou položku Content. Excel doplní i hranaté závorky, které syntaxe zápisu vyžaduje.

7


Pro dokončení zápisu vložíme ukončovací pravou kulatou závorku a potvrdíme tlačítkem OK.

Nyní můžeme odebrat sloupec Content, který již nebude více potřeba – klepneme pravým tlačítkem myši na záhlaví sloupce Content a v místní nabídce klepneme na příkaz Odebrat.

8


V dalším kroku načteme obsah propojených sešitů do dotazu – klepneme ve vloženém sloupci GetExcelData01 na aktivační tlačítko a v rozbalovací nabídce vlastností na tlačítko OK.

PowerQuery do sloupce GetExcelData01.Item načte názvy všech listů všech sešitů v odkazované složce a pokud tento sloupec ponecháme, vloží tyto názvy (jména obchodníků) k jednotlivým záznamům v těchto listech.

9


3. Vyloučení prázdných listů a listů bez dat prodeje z výsledné konsolidace V dalším kroku sdělíme PowerQuery, že chceme načítat pouze data z listů, které jsou pojmenovány - eliminujeme listy List1, List2. Stejně tak eliminujeme i pro konsolidaci nepotřebný definovaný název Tržby a objekt Tabulka_Jména. Otevřeme nabídku filtru ve sloupci GetExcelData01.Kind a necháme aktivní výběr pouze pro objekt Sheet, který odpovídá pracovnímu listu v sešitu. Objekty Table (tabulka) a DefinedName (definovaný název) deaktivujeme.

V dalším kroku vyloučíme z konsolidace dat listy, které nemají přiřazené jméno obchodníka.

10


Použijeme opět filtr – klepneme na rozbalovací tlačítko filtru v záhlaví sloupce GetExcelData01.Item, otevřeme nabídku Textové filtry a v ní klepneme na příkaz Neobsahuje.

V dialogovém okně Neobsahuje zapíšeme text List (dodržíme i velikost písmen) a potvrdíme tlačítkem OK.

11


V závěru této části postupu odstraníme z návrháře sloupce, které již nebudeme potřebovat. Klepneme na jejich záhlaví se současným stiskem klávesy CTRL. Pak klepneme pravým tlačítkem myši na záhlaví některé vybraného sloupce a v místní nabídce klepneme na příkaz Odebrat.

Ponecháme tedy pouze sloupce GetExcelData01.Data a GetExcelData01.Item. Pro kontrolu naší práce můžeme klepnout na některou z položek ve sloupci GetExcelData01.Data. PowerQuery zobrazí v okně náhledu první řádky tabulky prodejů v propojeném sešitu z vybraného listu – zde list Libor v sešitu Liberec.xlsx.

12


Poznámka: Pokud se nám některý z kroků postupu nepovede, můžeme jej vždy buď odstranit nebo opravit. Slouží k tomu podokno úloh v pravé části editoru PowerQuery.

Poznámka: PowerQuery je vybaven velmi silným jazykem, který se nazývá M. Každý krok, který provádíte v editoru, je v tomto jazyku zaznamenáván a vy pak můžete tento záznam editovat nebo kopírovat do jiných dotazů. Pokročilí uživatelé Excelu a PowerQuery dokáží i velmi komplikované dotazy s množstvím konsolidací a transformací dat zapsat velmi rychle pomocí několika málo řádků kódu v tomto jazyku. Pro zobrazení editoru kódu M klepněte na kartu Zobrazení a pak klepněte na tlačítko Rozšířený editor. Jednotlivé řádky kódu odpovídají krokům v podokně úloh – tlačítkem Nastavení dotazů zobrazení tohoto podokna zapnete nebo vypnete.

Zároveň pro práci s editorem PowerQuery je určena řada (několika desítek) funkcí, kterými řídíte způsob manipulace s daty a realizujete potřebné výpočty. Stručný přehled těchto funkcí naleznete na webu Microsoftu na adrese https://msdn.microsoft.com/library/Mt253322?ui=en-US&rs=en-US&ad=US

13


V kurzu Mistrovství v Power Query – automatické vytváření reportů v Excelu se dozvíte i málo dokumentované postupy a triky. Chcete například vidět seznam všech podporovaných funkcí ve vaší verzi PowerQuery? Zapište do řádku vzorců =#shared

4. Načtení dat s vynecháním opakujících se záhlaví sloupců Při načítání dat z různých listů by došlo i k načtení záhlaví sloupců, které bychom pak museli ručně odstraňovat. PowerQuery je připraven nám pomoci i v tomto případě a zajistit, že nadpisy sloupců budou načteny pouze jednou. Na kartě Přidání sloupce klepněte na tlačítko Přidat vlastní sloupec.

14


Sloupec nazveme v dialogovém okně DataBezZahlavi a do sloupce zapíšeme vzorec Table.PromoteHeaders([GetExcelData01.Data]) Vzorec načte všechna data ze sloupce GetExcelData01.Data, potlačí zobrazení opakujících se záhlaví sloupců v datech a data očištěná o nadpisy (kromě prvního výskytu) zapíše do položek sloupce DataBezZahlavi.

Rozdíl mezi zobrazením zdrojových dat v různých sloupcích můžeme vidět v otevřeném okně náhledu, které zobrazíme klepnutím na některou položku sloupce.

15


Nyní již sloupec GetExcelData01.Data nepotřebujeme a můžeme jej odebrat.

5. Formátovaní sloupců pro správné zobrazení dat (text, datum, číslo) Klepneme na rozbalovací tlačítko v záhlaví sloupce DataBezZahlavi. Vypneme políčko Používat původní název sloupce jako předponu a klepneme na tlačítko OK.

16


Sloupec GetExcelData01.Item přejmenujeme na Obchodník. Buď můžeme poklepat na název sloupce a název sloupce přepsat nebo klepneme na název sloupce pravým tlačítkem myši a v místní nabídce klepneme na příkaz Přejmenovat.

Nyní rozhodneme o formátování (datovém typu) jednotlivých sloupců. Aktivujeme kartu Domů, klepneme na název sloupce Obchodník a v rozbalovacím seznamu Datový typ klepneme na příkaz Text.

Postup opakujeme pro každý další sloupec a postupně nastavíme   

datový typ Datum pro sloupec Datum datový typ Text pro sloupec Název zboží datový typ Desetinné číslo pro sloupec Prodej

Toto byl poslední krok ve vytváření našeho propojení na data do složky D:/Data a můžeme si výsledný postup zobrazit v zápise jazyka M (případně kód zkopírovat do textového editoru a uložit).

17


6. Načtení dat do listu Data načtená z listů jednotlivých obchodníků chceme zobrazit v samostatném listu. Na kartě Domů klepneme na tlačítko Zavřít a načíst a zvolíme příkaz Zavřít a načíst do.

V dialogovém okně zvolíme možnost Tabulka a načtení do nově založeného listu Nový list – tím se vyhneme časté chybě z nepozornosti - přepsání starých dat v listu novými.

18


Klepnutím na tlačítko automatického filtru ve sloupci tabulky Obchodník a kontrolou položek v seznamu se můžeme přesvědčit, že došlo k načtení záznamů všech obchodníků, kteří jsou ve výkazech poboček Liberec, Olomouc i Pardubice.

Cíl jsme splnili a vytvořili systém, který bude automaticky načítat data ze všech listů s daty a ze všech sešitů, které nahrajeme do složky D:/Data.

Jak zajistíme úplnou aktualizaci dat, změnu adresy složky s daty a zálohování celého řešení si ukážeme v následujících posledních 3 krocích postupu.

19


7. Nastavení automatické aktualizace při změně dat V reálné praxi bude složka D:/Data obsahovat proměnlivou sadu dat:    

Budou vznikat nové pobočky – ve složce se objeví nové sešity, například PobočkaXYZ.xlsx. Budou nastupovat noví prodejci do stávajících poboček – v existujících sešitech přibydou nové listy, například v sešitu Pardubice.xlsx se nově objeví list Pavlína. V existujících listech budou přibývat nové řádky s prodeji v aktuálních měsících. A samozřejmě mohou i někteří prodejci odcházet a pobočky zanikat.

Při změně dat v propojené složce D:/Data nereflektuje výsledná tabulka okamžitě tyto změny – Excel vyžaduje, abychom provedli aktualizaci dat. Můžeme však nastavit různé varianty této aktualizace:   

Aktualizaci spouští uživatel klepnutím na tlačítko nebo stiskem kombinace kláves. Aktualizace se provádí automaticky po určené době (například každých 10 minut Excel načte data ze složky a výslednou tabulku aktualizuje) Aktualizace se provádí automaticky vždy, když uživatel otevírá sešit s tabulkou konsolidace.

Všechny tyto postupy lze kombinovat a přidat ještě možnost aktualizaci provést pomocí události zachytávanou VBA makrem – například aktualizace se spustí vždy, když uživatel aktivuje list s tabulkou konsolidace.

Aktualizace vyvolaná uživatelem Aktualizaci můžeme spustit libovolným z následujících postupů:    

Klepnutím na tlačítko Aktualizovat vše na kartě Data nebo kartě Tabulka. Klepnutím pravým tlačítkem myši na buňku v tabulce konsolidovaných hodnot a příkazem Obnovit z místní nabídky. Klepnutím na tlačítko Aktualizovat v podokně úloh se seznamem uložených dotazů. Klepnutím na tlačítko Připojení na kartě Data.

tlačítko Aktualizovat vše na kartě Data

20


tlačítko Aktualizovat vše na kartě Tabulka

tlačítko Aktualizovat v podokně úloh se seznamem uložených dotazů.

  

21

Klepnutí na tlačítko Připojení na kartě Data. Následně klepnutí na rozbalovací tlačítko Aktualizovat v dialogovém okně Připojení sešitu. Klepnutí na příkaz Aktualizovat vše.


V dialogovém okně Připojení sešitu máme také možnost vybrat ze seznamu dotazů pouze ten, pro který chceme aktualizaci dat spustit.

Automatická aktualizace po určitém čase V dialogovém okně Vlastnosti připojení můžeme nastavit požadavek, aby Excel načítal data periodicky po určité době. Máme tedy jistotu, že například každých 10 minut se Excel napojí na propojenou složku a načte aktuální stav dat. Nevýhodou bývá zpomalování odezvy Excelu nebo počítače v době, kdy jsou data načítána.

22


Dialogové okno Vlastnosti připojení aktivujeme klepnutím na tlačítko Aktualizovat vše na kartě Data a následně na příkaz Vlastnosti připojení.

V dialogovém okně Vlastnosti připojení aktivujeme možnost Obnovovat každých a nastavíme počet minut periody obnovování dat.

23


Automatická aktualizace dat při otevírání sešitu Jestliže chceme zabezpečit, že při každém otevření sešitu s dotazem a tabulkou konsolidovaných dat jsou naše údaje aktuální, zapneme v dialogovém okně Vlastnosti připojení možnost Aktualizovat data při otevírání souboru. Dialogové okno Vlastnosti připojení aktivujeme klepnutím na tlačítko Aktualizovat vše na kartě Data a následně na příkaz Vlastnosti připojení.

V dialogovém okně Vlastnosti připojení aktivujeme možnost Aktualizovat data při otevírání souboru.

24


8. Změna složky se soubory obsahující data ke konsolidaci V běžné praxi se můžeme setkat po otevření sešitu s tabulkou konsolidovaných dat s nepříjemným překvapením:

Prázdná tabulka signalizuje, že Excel nenalezl buď žádná data v propojené složce D:/Data nebo tato složka neexistuje. Pro řešení problému je potřeba nejdříve zjistit, kterou složku uložený dotaz prochází. Ne vždy si budeme adresní údaje pamatovat, je třeba umět připojení analyzovat a opravit. Na kartě Data klepneme na tlačítko Zobrazit dotazy.

Excel aktivuje podokno úloh se seznamem dotazů v tomto sešitu a pokusí se analyzovat příčinu chyby. Potenciální zdroj chyby vypíše do informačního okna, které zobrazíme klepnutím na hypertextový odkaz Počet chyb

Přesvědčili jsme se, že dotaz nenalezl odkazovanou složku s daty.

25

Mistrovství v PowerQuery – automatické vytváření reportů v Excelu – kurz pro pokročilé uživatele Excelu Jiří Číhař – www.dataspectrum.cz Můžeme tuto složku vytvořit a nahrát do ní požadovaná data a pokusit se pomocí aktualizace dat (příkazem Aktualizovat vše z předchozí části postupu) data načíst do tabulky. Pokud tento postup možný není a data jsou trvale umístěna jinde, budeme muset změnit adresu v našem dotazu. Pokusíme se pomocí průzkumníku Windows nalézt cestu ke složce s našimi daty:

Vidíme, že data se přestěhovala z adresy D:/Data na jiné místo.

Bohužel v řadě společností není cesta, kterou zobrazuje průzkumník použitelná pro zápis do parametru dotazu Excelu. Důvodem bývá, že zejména u síťových úložišť a externích disků jsou složky pojmenovány aliasy, kterým Excel nemusí rozumět.

Ve většině případů nám ale postačí následující jednoduchý postup: Klepneme pravým tlačítkem myši do adresního řádku průzkumníku Windows a zvolíme příkaz Kopírovat adresu jako text nebo příkaz Upravit adresu. Zobrazený zápis adresy již můžeme většinou použít jako parametr dotazu Excelu.

Zkopírujeme adresu jako text a otevřeme dotaz v editačním režimu: 1) Na kartě Data klepneme na tlačítko Zobrazit dotazy. 2) Zobrazíme informační okno dotazu najetím myši na ikonu Aktualizovat zcela vpravo v podokně úloh. 3) V náhledu dotazu klepneme na příkaz Upravit.

26


Někdy se tento postup zobrazení informačního okna nedaří – jde o vnitřní chybu Excelu, která snad bude dalším vydáním ServicePack opravena. Pomoci si ale můžeme tak, že na ikonu Aktualizovat klepneme PRAVÝM tlačítkem myši a v místní nabídce klepneme na příkaz Zobrazit náhled.

V náhledu dotazu klepneme na příkaz Upravit.

27

Mistrovství v PowerQuery – automatické vytváření reportů v Excelu – kurz pro pokročilé uživatele Excelu Jiří Číhař – www.dataspectrum.cz Excel zobrazí okno editoru PowerQuery, v jehož pravé části klepneme na ikonu hvězdičky (editace) u prvního kroku Zdroj. V dialogovém okně vložíme do pole Cesta ke složce zkopírovanou cestu ze schránky.

Nově zadanou cestu k datům potvrdíme klepnutím na tlačítko OK.

Pronalezení cesty můžeme také otevřít dialogové okno pomocí tlačítka Browse (Procházet). Posledním krokem je instrukce Excelu, že má opravený zápis dotazu uložit a načíst data do původní tabulky. Klepneme na tlačítko Zavřít a načíst na kartě Domů a pak klepneme na příkaz Zavřít a načíst.

28


Opravený dotaz s novou adresou připojení nyní úspěšně načítá data z nové složky:

9. Zálohování dotazu pro možnost obnovy nebo přenesení na jiný počítač Otevřeme nový sešit (CTRL + N) do kterého budeme chtít načíst data a ve kterém bude uloženo propojení Sešit s tabulkou obsahující konsolidovaná data obsahuje i dotaz, pomocí kterého jsou tato data načítána. Sešit tedy můžeme uložit, přenést pomocí flashdisku, zálohovat vypálením na CD nebo umístit na onine datové úložiště jako je Dropbox, Google Disk nebo OneDrive. Pokud jej spustíme na jiném počítači s kompatibilní verzí Excelu, neměl by nastat žádný problém při zpracování dat. Posloupnost kroků dotazu je zapsána pomocí příkazů a objektů jazyka M v rozšířeném módu editoru. Jedná se o standardní textový formát – můžeme jej tedy pomocí schránky překopírovat do jiného sešitu nebo si vytvořit například v Notepadu vlastní knihovnu dotazů a tu pak podle potřeby využívat. Klepneme PRAVÝM tlačítkem myši na ikonu Aktualizovat u dotazu, který chceme exportovat a v místní nabídce klepneme na příkaz Upravit.

29

Mistrovství v PowerQuery – automatické vytváření reportů v Excelu – kurz pro pokročilé uživatele Excelu Jiří Číhař – www.dataspectrum.cz V otevřeném okně editoru PowerQuery aktivujeme kartu Zobrazení a klepneme na tlačítko Rozšířený editor. V okně editoru označíme veškerý obsah a zkopírujeme jej do schránky. Poznámka: pro rychlé označení celého kódu klepneme na libovolnou jeho část a stiskneme kombinaci kláves CTRL + A .

Editor vypneme a otevřeme nový sešit – buď prázdný (CTRL+N) nebo sešit, do kterého ukládáme dotazy. Aktivujeme editor PowerQuery pro zadání prázdného dotazu – na kartě Data klepneme ve skupině Načíst a transformovat na tlačítko Nový dotaz. Zvolíme položku Z jiných zdrojů a nakonec klepneme na příkaz Prázdný dotaz.

V PowerQuery otevřeme okno rozšířeného editoru, kód do okna vložíme (CTRL+V) a dotaz v tomto novém sešitu uložíme.

30


Postup pro Power Query ve verzi Excel 2010 a Excel 2013 1. Napojení na zdroj dat Otevřeme nový sešit (CTRL + N) do kterého budeme chtít načíst data a ve kterém bude uloženo propojení s daty spolu s postupem konsolidace.

Na kartě PowerQuery klepneme ve skupině Get External Data (Načíst externí data) na příkaz From File (Ze souboru) a v nabídce na příkaz From Folder (Ze složky).

V dialogovém okně zvolíme pomocí tlačítka Browse (Procházet) požadovanou složku – v našem případě D:/Data.

31


Excel aktivuje editor PowerQuery se zobrazením informací o všech sešitech uložených ve zvolené složce.

Okno editoru zobrazí i pracovní podokno v pravé části, které ukládá informaci o jednotlivých krocích. V poli Name (Název) změníme název dotazu pro snazší orientaci v případě, že vytváříme mnoho dotazů pro práci s našimi daty – použijeme název NactiDataProdeje.

Z načtených informací ponecháme pouze sloupec Content – klepneme PRAVÝM tlačítkem myši na záhlaví sloupce a v místní nabídce klepneme na příkaz Remove Other Columns (Odebrat ostatní sloupce).

V editoru PowerQuery nyní zůstane jediný sloupec s popisnými informacemi propojení na data.

32


V podokně Query Settings (Nastavení dotazů) v poli Applied steps (Použitý postup) vidíme heslovitý popis jednotlivých kroků dotazu. Klepnutím na položku v seznamu můžeme zobrazit nebo editovat krok, který zastupuje.

2. Spojení listů s daty do jedné tabulky Na kartě Add Column (Přidání sloupce) klepneme ve skupině General (Obecné) na příkaz Add Custom Column (Přidat vlastní sloupec).

Zapíšeme název sloupce GetExcelData01 a funkci, pomocí které určíme obsah buněk nově vloženého sloupce:

=Excel.Workbook([Content]) Tato funkce říká, že chceme načíst veškerý obsah propojených sešitů do našeho dotazu. Funkci nemusíme zapisovat celou ručně - můžeme zapsat pouze začátek

=Excel.Workbook( a následně klepnout v pravé části dialogového pole na nabídnutou položku Content. Excel doplní i hranaté závorky, které syntaxe zápisu vyžaduje. Pro dokončení zápisu vložíme ukončovací pravou kulatou závorku a potvrdíme tlačítkem OK.

33


Nyní můžeme odebrat sloupec Content, který již nebude více potřeba – klepneme pravým tlačítkem myši na záhlaví sloupce Content a v místní nabídce klepneme na příkaz Remove (Odebrat).

V dalším kroku načteme obsah propojených sešitů do dotazu – klepneme ve vloženém sloupci GetExcelData01 na aktivační tlačítko a v rozbalovací nabídce vlastností na tlačítko OK.

34


PowerQuery do sloupce GetExcelData01.Item načte názvy všech listů všech sešitů v odkazované složce a pokud tento sloupec ponecháme, vloží tyto názvy (jména obchodníků) k jednotlivým záznamům v těchto listech.

3. Vyloučení prázdných listů a listů bez dat prodeje z výsledné konsolidace V dalším kroku sdělíme PowerQuery, že chceme načítat pouze data z listů, které jsou pojmenovány - eliminujeme listy List1, List2. Stejně tak eliminujeme i pro konsolidaci nepotřebný definovaný název Tržby a objekt Tabulka_Jména. Otevřeme nabídku filtru ve sloupci GetExcelData01.Kind a necháme aktivní výběr pouze pro objekt Sheet (list), který odpovídá pracovnímu listu v sešitu. Objekty Table (tabulka) a DefinedName (definovaný název) deaktivujeme.

35


V dalším kroku vyloučíme z konsolidace dat listy, které nemají přiřazené jméno obchodníka.

36


Použijeme opět filtr – klepneme na rozbalovací tlačítko filtru v záhlaví sloupce GetExcelData01.Item, otevřeme nabídku Text filters (Textové filtry) a v ní klepneme na příkaz Does Not Contain (Neobsahuje).

V dialogovém okně Does Not Contain (Neobsahuje) zapíšeme text List (dodržíme i velikost písmen) a potvrdíme tlačítkem OK.

37


V závěru této části postupu odstraníme z návrháře sloupce, které již nebudeme potřebovat. Klepneme na jejich záhlaví se současným stiskem klávesy CTRL. Pak klepneme pravým tlačítkem myši na záhlaví některé vybraného sloupce a v místní nabídce klepneme na příkaz Remove Columns (Odebrat sloupce).

Ponecháme tedy pouze sloupce GetExcelData01.Data a GetExcelData01.Item. Pro kontrolu naší práce můžeme klepnout na některou z položek ve sloupci GetExcelData01.Data. PowerQuery zobrazí v okně náhledu první řádky tabulky prodejů v propojeném sešitu z vybraného listu – zde list Libor v sešitu Liberec.xlsx.

38


Poznámka: Pokud se nám některý z kroků postupu nepovede, můžeme jej vždy buď odstranit nebo opravit. Slouží k tomu podokno úloh v pravé části editoru PowerQuery.

Poznámka: PowerQuery je vybaven velmi silným jazykem, který se nazývá M. Každý krok, který provádíte v editoru, je v tomto jazyku zaznamenáván a vy pak můžete tento záznam editovat nebo kopírovat do jiných dotazů. Pokročilí uživatelé Excelu a PowerQuery dokáží i velmi komplikované dotazy s množstvím konsolidací a transformací dat zapsat velmi rychle pomocí několika málo řádků kódu v tomto jazyku. Pro zobrazení editoru kódu M klepněte na kartu View (Zobrazení) a pak klepněte na tlačítko Advanced Editor (Rozšířený editor). Jednotlivé řádky kódu odpovídají krokům v podokně úloh – tlačítkem Query Settings (Nastavení dotazů) zobrazení tohoto podokna zapnete nebo vypnete.

39


Zároveň pro práci s editorem PowerQuery je určena řada (několika desítek) funkcí, kterými řídíte způsob manipulace s daty a realizujete potřebné výpočty. Stručný přehled těchto funkcí naleznete na webu Microsoftu na adrese https://msdn.microsoft.com/library/Mt253322?ui=en-US&rs=en-US&ad=US

V kurzu Mistrovství v Power Query – automatické vytváření reportů v Excelu se dozvíte i málo dokumentované postupy a triky. Chcete například vidět seznam všech podporovaných funkcí ve vaší verzi PowerQuery? Zapište do řádku vzorců =#shared

4. Načtení dat s vynecháním opakujících se záhlaví sloupců Při načítání dat z různých listů by došlo i k načtení záhlaví sloupců, které bychom pak museli ručně odstraňovat. PowerQuery je připraven nám pomoci i v tomto případě a zajistit, že nadpisy sloupců budou načteny pouze jednou. Na kartě Add Column (Přidání sloupce) klepněte na tlačítko Add Custom Column (Přidat vlastní sloupec).

40


Sloupec nazveme v dialogovém okně DataBezZahlavi a do sloupce zapíšeme vzorec Table.PromoteHeaders([GetExcelData01.Data]) Vzorec načte všechna data ze sloupce GetExcelData01.Data, potlačí zobrazení opakujících se záhlaví sloupců v datech a data očištěná o nadpisy (kromě prvního výskytu) zapíše do položek sloupce DataBezZahlavi.

41


Rozdíl mezi zobrazením zdrojových dat v různých sloupcích můžeme vidět v otevřeném okně náhledu, které zobrazíme klepnutím na některou položku sloupce.

Nyní již sloupec GetExcelData01.Data nepotřebujeme a můžeme jej odebrat.

5. Formátovaní sloupců pro správné zobrazení dat (text, datum, číslo) Klepneme na rozbalovací tlačítko v záhlaví sloupce DataBezZahlavi. Vypneme políčko Používat původní název sloupce jako předponu a klepneme na tlačítko OK.

42


Sloupec GetExcelData01.Item přejmenujeme na Obchodník. Buď můžeme poklepat na název sloupce a název sloupce přepsat nebo klepneme na název sloupce pravým tlačítkem myši a v místní nabídce klepneme na příkaz Rename (Přejmenovat).

Nyní rozhodneme o formátování (datovém typu) jednotlivých sloupců. Aktivujeme kartu Home (Domů), klepneme na název sloupce Obchodník a v rozbalovacím seznamu Date Typ (Datový typ) klepneme na příkaz Text (Text).

43


Postup opakujeme pro každý další sloupec a postupně nastavíme   

datový typ Date (Datum) pro sloupec Datum datový typ Text (Text)pro sloupec Název zboží datový typ Desetinné číslo Decimal Number (Desetinné číslo) pro sloupec Prodej

Toto byl poslední krok ve vytváření našeho propojení na data do složky D:/Data a můžeme si výsledný postup zobrazit v zápise jazyka M (případně kód zkopírovat do textového editoru a uložit).

6. Načtení dat do listu Data načtená z listů jednotlivých obchodníků chceme zobrazit v samostatném listu. Na kartě Home (Domů) klepneme na tlačítko Close & Load (Zavřít a načíst) a zvolíme příkaz Close & Load To (Zavřít a načíst do).

44


V dialogovém okně zvolíme možnost Table (Tabulka) a načtení do nově založeného listu New worksheet (Nový list) – tím se vyhneme časté chybě z nepozornosti - přepsání starých dat v listu novými.

Klepnutím na tlačítko automatického filtru ve sloupci tabulky Obchodník a kontrolou položek v seznamu se můžeme přesvědčit, že došlo k načtení záznamů všech obchodníků, kteří jsou ve výkazech poboček Liberec, Olomouc i Pardubice.

45


Cíl jsme splnili a vytvořili systém, který bude automaticky načítat data ze všech listů s daty a ze všech sešitů, které nahrajeme do složky D:/Data.

Jak zajistíme úplnou aktualizaci dat, změnu adresy složky s daty a zálohování celého řešení si ukážeme v následujících posledních 3 krocích postupu.

7. Nastavení automatické aktualizace při změně dat V reálné praxi bude složka D:/Data obsahovat proměnlivou sadu dat:    

Budou vznikat nové pobočky – ve složce se objeví nové sešity, například PobočkaXYZ.xlsx. Budou nastupovat noví prodejci do stávajících poboček – v existujících sešitech přibydou nové listy, například v sešitu Pardubice.xlsx se nově objeví list Pavlína. V existujících listech budou přibývat nové řádky s prodeji v aktuálních měsících. A samozřejmě mohou i někteří prodejci odcházet a pobočky zanikat.

Při změně dat v propojené složce D:/Data nereflektuje výsledná tabulka okamžitě tyto změny – Excel vyžaduje, abychom provedli aktualizaci dat. Můžeme však nastavit různé varianty této aktualizace:   

Aktualizaci spouští uživatel klepnutím na tlačítko nebo stiskem kombinace kláves. Aktualizace se provádí automaticky po určené době (například každých 10 minut Excel načte data ze složky a výslednou tabulku aktualizuje) Aktualizace se provádí automaticky vždy, když uživatel otevírá sešit s tabulkou konsolidace.

Všechny tyto postupy lze kombinovat a přidat ještě možnost aktualizaci provést pomocí události zachytávanou VBA makrem – například aktualizace se spustí vždy, když uživatel aktivuje list s tabulkou konsolidace.

46


Aktualizace vyvolaná uživatelem Aktualizaci můžeme spustit libovolným z následujících postupů:    

Klepnutím na tlačítko Aktualizovat vše na kartě Data nebo kartě Nástroje tabulky. Klepnutím pravým tlačítkem myši na buňku v tabulce konsolidovaných hodnot a příkazem Obnovit z místní nabídky. Klepnutím na tlačítko Aktualizovat v podokně úloh se seznamem uložených dotazů. Klepnutím na tlačítko Připojení na kartě Data.

tlačítko Aktualizovat vše na kartě Data

tlačítko Aktualizovat vše na kartě Tabulka

47


tlačítko Refresh (Aktualizovat) v podokně úloh se seznamem uložených dotazů.

  

Klepnutí na tlačítko Připojení na kartě Data. Následně klepnutí na rozbalovací tlačítko Aktualizovat v dialogovém okně Připojení sešitu. Klepnutí na příkaz Aktualizovat vše.

V dialogovém okně Připojení sešitu máme také možnost vybrat ze seznamu dotazů pouze ten, pro který chceme aktualizaci dat spustit.

48


Automatická aktualizace po určitém čase V dialogovém okně Vlastnosti připojení můžeme nastavit požadavek, aby Excel načítal data periodicky po určité době. Máme tedy jistotu, že například každých 10 minut se Excel napojí na propojenou složku a načte aktuální stav dat. Nevýhodou bývá zpomalování odezvy Excelu nebo počítače v době, kdy jsou data načítána. Dialogové okno Vlastnosti připojení aktivujeme klepnutím na tlačítko Aktualizovat vše na kartě Data a následně na příkaz Vlastnosti připojení.

V dialogovém okně Vlastnosti připojení aktivujeme možnost Obnovovat každých a nastavíme počet minut periody obnovování dat.

49


Automatická aktualizace dat při otevírání sešitu Jestliže chceme zabezpečit, že při každém otevření sešitu s dotazem a tabulkou konsolidovaných dat jsou naše údaje aktuální, zapneme v dialogovém okně Vlastnosti připojení možnost Aktualizovat data při otevírání souboru. Dialogové okno Vlastnosti připojení aktivujeme klepnutím na tlačítko Aktualizovat vše na kartě Data a následně na příkaz Vlastnosti připojení.

V dialogovém okně Vlastnosti připojení aktivujeme možnost Aktualizovat data při otevírání souboru.

50


8. Změna složky se soubory obsahující data ke konsolidaci V běžné praxi se můžeme setkat po otevření sešitu s tabulkou konsolidovaných dat s nepříjemným překvapením:

Prázdná tabulka signalizuje, že Excel nenalezl buď žádná data v propojené složce D:/Data nebo tato složka neexistuje. Pro řešení problému je potřeba nejdříve zjistit, kterou složku uložený dotaz prochází. Ne vždy si budeme adresní údaje pamatovat, je třeba umět připojení analyzovat a opravit. Na kartě PowerQuery klepneme na tlačítko Show Pane (Zobrazit podokno s dotazy).

Excel aktivuje podokno úloh se seznamem dotazů v tomto sešitu a pokusí se analyzovat příčinu chyby. Potenciální zdroj chyby vypíše do informačního okna, které zobrazíme klepnutím na hypertextový odkaz Error (Chyba).

Přesvědčili jsme se, že dotaz nenalezl odkazovanou složku s daty.

51

Mistrovství v PowerQuery – automatické vytváření reportů v Excelu – kurz pro pokročilé uživatele Excelu Jiří Číhař – www.dataspectrum.cz Můžeme tuto složku vytvořit a nahrát do ní požadovaná data a pokusit se pomocí aktualizace dat (příkazem Aktualizovat vše z předchozí části postupu) data načíst do tabulky. Pokud tento postup možný není a data jsou trvale umístěna jinde, budeme muset změnit adresu v našem dotazu. Pokusíme se pomocí průzkumníku Windows nalézt cestu ke složce s našimi daty:

Vidíme, že data se přestěhovala z adresy D:/Data na jiné místo. Bohužel v řadě společností není cesta, kterou zobrazuje průzkumník použitelná pro zápis do parametru dotazu Excelu. Důvodem bývá, že zejména u síťových úložišť a externích disků jsou složky pojmenovány aliasy, kterým Excel nemusí rozumět. Ve většině případů nám ale postačí následující jednoduchý postup: Klepneme pravým tlačítkem myši do adresního řádku průzkumníku Windows a zvolíme příkaz Kopírovat adresu jako text nebo příkaz Upravit adresu. Zobrazený zápis adresy již můžeme většinou použít jako parametr dotazu Excelu.

Zkopírujeme adresu jako text a otevřeme dotaz v editačním režimu: 1) Na kartě PowerQuery klepneme na tlačítko Show Pane (Zobrazit podokno s dotazy). 2) Zobrazíme informační okno dotazu najetím myši na ikonu Refresh (Aktualizovat) zcela vpravo v podokně dotazů. 3) V náhledu dotazu klepneme na příkaz Edit (Upravit).

52


Někdy se tento postup zobrazení informačního okna nedaří – jde o vnitřní chybu Excelu, která snad bude dalším vydáním ServicePack opravena. Pomoci si ale můžeme tak, že na ikonu Refresh (Aktualizovat) klepneme PRAVÝM tlačítkem myši a v místní nabídce klepneme na příkaz Show the peek (Zobrazit náhled).

V náhledu dotazu klepneme na příkaz Edit (Upravit).

53

Mistrovství v PowerQuery – automatické vytváření reportů v Excelu – kurz pro pokročilé uživatele Excelu Jiří Číhař – www.dataspectrum.cz Excel zobrazí okno editoru PowerQuery, v jehož pravé části klepneme na ikonu hvězdičky (editace) u prvního kroku Source (Zdroj). V dialogovém okně vložíme do pole Folder Path (Cesta ke složce) zkopírovanou cestu ze schránky.

Nově zadanou cestu k datům potvrdíme klepnutím na tlačítko OK. Pronalezení cesty můžeme také otevřít dialogové okno pomocí tlačítka Browse (Procházet).

Posledním krokem je instrukce Excelu, že má opravený zápis dotazu uložit a načíst data do původní tabulky. Na kartě Home (Domů) klepneme na tlačítko Close & Load (Zavřít a načíst) a zvolíme příkaz Close & Load (Zavřít a načíst).

54


Opravený dotaz s novou adresou připojení nyní úspěšně načítá data z nové složky:

9. Zálohování dotazu pro možnost obnovy nebo přenesení na jiný počítač Otevřeme nový sešit (CTRL + N) do kterého budeme chtít načíst data a ve kterém bude uloženo propojení Sešit s tabulkou obsahující konsolidovaná data obsahuje i dotaz, pomocí kterého jsou tato data načítána. Sešit tedy můžeme uložit, přenést pomocí flashdisku, zálohovat vypálením na CD nebo umístit na onine datové úložiště jako je Dropbox, Google Disk nebo OneDrive. Pokud jej spustíme na jiném počítači s kompatibilní verzí Excelu, neměl by nastat žádný problém při zpracování dat. Posloupnost kroků dotazu je zapsána pomocí příkazů a objektů jazyka M v rozšířeném módu editoru. Jedná se o standardní textový formát – můžeme jej tedy pomocí schránky překopírovat do jiného sešitu nebo si vytvořit například v Notepadu vlastní knihovnu dotazů a tu pak podle potřeby využívat. Klepneme PRAVÝM tlačítkem myši na ikonu Refresh (Aktualizovat) u dotazu, který chceme exportovat a v místní nabídce klepneme na příkaz Edit (Upravit).

V otevřeném okně editoru PowerQuery aktivujeme kartu View (Zobrazení)a klepneme na tlačítko Advanced Editor (Rozšířený editor). V okně editoru označíme veškerý obsah a zkopírujeme jej do schránky.

55

Mistrovství v PowerQuery – automatické vytváření reportů v Excelu – kurz pro pokročilé uživatele Excelu Jiří Číhař – www.dataspectrum.cz Poznámka: pro rychlé označení celého kódu klepneme na libovolnou jeho část a stiskneme kombinaci kláves CTRL + A .

Editor vypneme a otevřeme nový sešit – buď prázdný (CTRL+N) nebo sešit, do kterého ukládáme dotazy. Aktivujeme editor PowerQuery pro zadání prázdného dotazu – na kartě PowerQuery klepneme ve skupině Get External Data (Načíst externí data) na tlačítko From Other Sources (Z jiných zdrojů). V nabídce odrolujeme až zcela na konec a klepneme na příkaz Blank Query (Prázdný dotaz).

V editoru PowerQuery otevřeme okno rozšířeného editoru, kód do okna vložíme (CTRL+V) a dotaz v tomto novém sešitu uložíme.

56

Automatická konsolidace dat ze souborů Excelu

Recommend Documents