Ukázky zajímavých úprav dat v Power Query
S tímto tématem se setkáte na našich kurzech ExcelTown.
Aktuálně: kurzy můžete absolvovat jak online, tak prezenčně.
Autor: Miroslav Lorenc
Někdy se nám dostanou do ruky tabulky, se kterými se velmi špatně pracuje. Ne že by se v nich nedalo zjistit informace, ale jsou uzpůsobené spíše pro čtení člověkem či tisk než pro systematickou práci v Excelu či obdobném nástroji.
Aby šlo data analyzovat, vytvářet různé sestavy a vizualizace, potřebujeme data v dobře strukturované tabulce. Zdrojová tabulka níže je sestavena nesystematicky, navíc jde o dvě tabulky (prázdný řádek odděluje tabulky). Dále předpokládáme, že časem budeme mít v obdobné struktuře i data za další měsíce.
Naším cílem tedy bude vzít neuspořádanou skupinu buněk, vyčistit jí a mít z ní jednoduchou, čistou, tabulku nebo kontingenční tabulku.
V článku si ukážeme:
- Odebírání prázdných a mezisoučtových řádků
- Vyplňování prázdných buněk
- Spojování sloupců
- Převádění sloupců na řádky
- Načítání dotazů Power Query do kontingenční tabulky
- Počítanou položku použitou v kontingenční tabulce
Jak na úpravu tabulky
Označíme všechna data v oblasti A2:H16 a poté zadáme příkaz Data - Načíst a transformovat data - Z tabulky nebo oblasti. V dialogovém okně nebudeme zaškrtávat, že se má použít záhlaví.
Otevře se Power Query, kde provedeme následující operace:
1. Označíme Sloupec1 a zadáme Transformace - Vyplnit dolů.
2. Odebereme sumarizační řádky pomocí filtru Sloupec1
3. Nemáme možnost vyplnit názvy měsíců doprava, proto provedeme transponování dat - nabídka Transformace - Tabulka - Transponovat.
4. U prvního sloupce provedeme opět vyplnění údajů (Transformace - Vyplnit dolů).
5. V tabulce chceme mít jen jedno záhlaví, proto označíme Sloupec1 a Sloupec2 a pomocí příkazu Transformace - Sloupec text - Sloučit sloupce spojíme texty obou sloupců, přičemž použijeme libovolný oddělovač a název sloučeného sloupce.
6. Tabulku transponujeme do původní podoby.
7. Jako názvy polí použijeme první řádek (Transformace - Tabulka - Použít první řádek jako záhlaví) a u prvních dvou sloupců upravíme texty.
8. Označíme první dva sloupce a zadáme příkaz z místní nabídky Převést další sloupce na řádky (nebo v pásu karet Transformace - Libovolný sloupec - Převést sloupce na řádky - Převést další sloupce na řádky).
9. Vybereme třetí sloupec a rozdělíme jej pomocí oddělovače (Transformace - Sloupec text - Rozdělit sloupec - Oddělovačem) a jako oddělovač zvolíme dvojtečku.
10. Pojmenujeme sloupce (dvojklikem na záhlaví sloupce).
11. Pomocí filtru ve sloupci Skutečnost / Plán odebereme položku Odchylka. To už jsme mohli udělat - např. v kroku 4 nebo odstraněním dvou sloupců v bodu 6. Odchylky, součty tržeb a nákladů a zisk/ztrátu z dat odstraňujeme proto, že tyto údaje si můžeme snadno vypočítat - nemá je tedy smysl přebírat.
12. Nyní máme z původní data tranformována do podoby, použitelné pro další analýzu. Zvolíme příkaz Domů - Zavřít a načíst.
Poklepáním na vlastnosti dotazu se můžeme kdykoliv vrátit k postupu nebo ho upravit.
Nyní v Excelu vytvoříme z transformované tabulky kontingenční tabulku - do oblasti sloupců dáme pole Tržba / Náklad a Položka, do oblasti řádků Měsíc a Skutečnost / Plán a ho oblasti Hodnot dáme pole Hodnota.
V dalším kroku naformátujeme vhodně čísla, upravíme popisky apod.
Následně do kontingenční tabulky vložíme počítanou položku Odchylka - ta bude rozdílem plánovaných a skutečných nákladů a tržeb.
Pozor - sčítání odchylek tržeb a nákladů nedává smysl.
Připojení výsledků za další měsíce:
- Zkopírujeme data za další měsíce ze skrytého listu Data_III-XII (buňky C2:AF16) za tabulku na listu Zdroj (do buňky I3 vložíme kopírované buňky jako hodnoty a tabulka by měla nové údaje “vstřebat”).
- Na listu Transformace klikneme do tabulky a poté Data - Dotazy a připojení - Aktualizovat vše.
- Na listu KT klikneme do tabulky a poté Analýza kontingenční tabulky - Data - Aktualizovat.
- Měsíce můžeme seřadit od A-Z (seřadí se podle logického pořadí ne podle abecedy, protože jsou ve vlastních seznamech).
Ke kontingenční tabulce přidáme průřezy a vyzkoušíme různé pohledy na data.
Dostali jsme nejen lepší pohled na dat, ale tento pohled si můžeme kdykoliv jednoduše změnit a data za další období můžeme jednoduše aktualizovat.
S tímto tématem se setkáte na našich kurzech ExcelTown.
Aktuálně: kurzy můžete absolvovat jak online, tak prezenčně.