Načíst a transformovat – Power Query – základní návod

Načíst a transformovat je dalším z „Power“ nástrojů pro Excel, jako je například Power Pivot, Power BI nebo Power View.

Terminologicky je to trochu zmatené - ve verzích 2010 a 2013 se tento nástroj jmenuje Power Query, od verze 2016 už se nijak nejmenuje je v Excelu k nalezení na kartě Data / Načíst a transformovat (Get and Transform).

K čemu a kdy Power Query využijete?

Nástroj Power Query se vám bude hodit v situaci, kdy potřebujete do Excelu načíst data z různých datových zdrojů. Například kvůli vytvoření nějakého reportu či přehledu.

Nemusí jít ale o pouhé načtení dat. Hlavním přínosem Power Query v takové situaci totiž je, že data můžete před samotným vložením do excelu všemožně upravovat a v Excelu pak pracovat pouze s těmi daty, která skutečně potřebujete.

Zdrojem dat mohou být jak soubory (např. Excel, csv), tak i různé typy relačních databází (např. Access, MySQL, Azure SQL, Oracle,…). Z každou novou verzí Power Query se seznam možných zdrojů rozšiřuje.

Data z těchto zdrojů pak „proženete“ nástrojem Power Query. Ten umí tato data filtrovat, řadit, slučovat, vybírat konkrétní sloupce, provádět výpočty a další operace. Výsledkem pak bude přehledná tabulka, vhodná k okamžitému použití. Budete z ní moci bez dalších úprav například vytvořit kontingenční tabulku nebo ji použít jako podklad pro vizualizaci v Power BI.

Jednoduchý příklad

Představte si, že pracujete jako manažer obchodního týmu, a vaším úkolem je vytvořit report výkonnosti prodejců ve vaší firmě. K dispozici máte soubor (ale stejně tak to může být databáze na serveru apod.), který obsahuje seznam všech objednávek za nějaké období, včetně čísla objednávky, data objednání a druhu zboží.  Zdrojový soubor vypadá třeba takto:

obr0

Vás ale v tuto chvíli zajímá pouze celkový prodej u každého prodejce a žádné další údaje a podrobnosti nepotřebujete. Výsledek by mohl vypadat nějak takto:

obr00

Kde najdu  Power Query?

  • V Excelu 2016 je nástroj Power Query součástí instalace a ikony tohoto nástroje najdete na kartě Data ve skupině Načíst a transformovat.

obr1

  • V Excelu 2013 / 2010 si stáhněte doplněk Power Query a nainstalujte jej. Power Query bude mít samostatnou kartu se svým názvem.

Volba zdroje dat

Nejprve musíme nástroji Power Query říct, která data budeme zpracovávat.

  • Klikneme na tlačítko Nový dotaz.
  • Vybereme zdroj dat, v našem případě zvolíme Ze souboru -> Ze sešitu.
  • Načítání souboru může chvilinku trvat.

obr3

Výběr dat a sestavení dotazu

Po načtení nástroj Power Query soubor analyzuje a zobrazí nalezené rozsahy dat, tabulky nebo listy. V našem případě se v souboru nachází jediný list Prodeje.

obr4

Pokud bychom vstupní data nechtěli nijak měnit, stačí kliknout na tlačítko Načíst. Power Query ale umí mnohem více. Klikneme proto na tlačítko Upravit, čímž vyvoláme Editor dotazů, tedy samotnou podstatu nástroje Power Query.

obr5

V editoru dotazů se nacházejí tři hlavní části:

  • Hlavní plochu zaujímá náhled na výslednou podobu dat
  • Nahoře se nachází Karty a na nich nástroje pro úpravu dat
  • Vpravo pak vidíme seznam kroků, které jsme na úpravu dat použili

Protože nás v našem příkladu zajímá pouze to, za kolik každý prodejce celkem prodal, zbavíme se nejprve přebytečných sloupců, abychom získali pouze seznam prodejců a jednotlivých tržeb za objednávky. Nejrychleji to provedeme tak, že

  • Označíme sloupce Prodejce a Tržba (kliknutím na jejich záhlaví)
  • Po kliknutí pravým tlačítkem myši vybereme Odstranit ostatní sloupce
  • Výsledkem bude tabulka o pouze dvou sloupcích, které nás zajímají

obr6

V tuto chvíli je vhodné zmínit, že tato úprava se zdrojových dat nijak nedotkla. Jedná se tedy pouze o krok, jak se mají zdrojová data změnit na výsledná. Provedený krok též můžeme vidět v seznamu kroků v pravé části editoru dotazů.

obr7

V tuto chvíli provedeme ještě druhý krok dotazu, protože nás zajímají celkové tržby - nikoliv tržby za jednotlivé objednávky.

  • Použijeme nástroj Seskupení dat na kartě Transformace.
  • Ve formuláři vyplníme, že chceme seskupovat podle Prodejce.
  • Dále zvolíme, že seskupený sloupec se bude jmenovat tržba Celkem, a že bude Součet dat sloupce Tržba

obr8

Máme hotovo

Po seskupení dat se tabulka výrazně zmenší a vypadá přesně tak, jak má.

obr9

Na závěr je nutné editor dotazů ukončit a provést samotné načtení dat do listu Excelu. Stačí kliknout na tlačítko Zavřít a načíst na kartě Domů. Výsledek bude tabulka vložená v listu Excelu, která tváří jako běžná tabulka.

obr10

V pravé části excelovského listu vidíme seznam dotazů nástore Power Query, které jsou v daném souboru použity. Díky tomu je možné kliknutím pravého tlačítka myši na dotaz data v tabulce například aktualizovat, nebo dotaz podle potřeby upravit.

Nástrojem Power Query jsme dokázali z poměrně velké tabulky do Excelu vložit pouze její potřebnou část a dokonce provést jednoduchý výpočet. Odpadá tak zdlouhavé načítání všech dat přímo do sešitu, což může ušetřit čas. A Power Query umí jít ještě mnohem dále, což si ukážeme v některém z příštích článků.

Chcete-li si vše sami vyzkoušet, stáhněte si zdrojový soubor pro Power Query

Napsat komentář

Vaše emailová adresa nebude zveřejněna.

Vyhledávání

Exact matches only
Search in title
Search in content
Search in posts
Search in pages
Filter by Categories
Nezařazené

Nyní otevřený kurz

Jednodenní Excel v praxi

  • 14.3.2017
  • V Praze
Další info / přihláška

Firemní kurzy

  • Přemýšlíte o firemním školení Excelu?
  • Školíme Excel od začátečníků po experty, ale také PowerPoint, PowerPivot nebo Access.
Mám zájem o kurz

Individuální výuka

Mám zájem o výuku

Vývoj aplikací v Excelu (makra - VBA)

  • Vyvíjíme aplikace pro potřeby firemních zákazníků
  • Reporty, vizualizace dat, harmonogramy, plánování
  • Slučování dat ze souborů, agregace, exporty
Zajímám se o vytvoření aplikace

Kontakt

  • Tel: +420 602 274 999
  • Mail: info@vyuka-excelu.cz
Kontaktní formulář

FB CZ

TOPlist