V dnešním videu se podíváme na to, jak se pomocí Power Query můžeme do Excelu napojit na nejrůznější zdrojové soubory. Představte si, že pracujete třeba na oddělení prodeje a máte analyzovat měsíční prodejní data. Jedna tabulka vám přijde v Excelu, druhá vám přijde ve formátu csv a souhrnná data o klientech máte dokonce v textovém souboru. A ceník produktů máte navíc v PDF. Se všemi těmito daty ale musíte pracovat v Excelu. Jak s těmito zdroji efektivně pracovat bez toho abyste hodiny něco kopírovali a ručně někam opisovali? To si právě ukážeme v dnešním videu.
Excelové zdrojové soubory:
Excelový soubor k aktualizaci:
V dnešním příkladu máme tři různé typy zdrojových souborů. Na tyto soubory se potřebujeme napojit. Ve složce Data máme tři excelové soubory, každý soubor obsahuje data pro jeden měsíc. Do této složky budou každý měsíc přibývat nové soubory. Dále máme excelový soubor, kde máme informace k jednotlivým produktům. A posledním zdrojovým souborem je textový soubor, kde máme informace o slevových kódech.
Na všechny tyto soubory se potřebujeme napojit a soubory propojit. Jelikož máme odpovědět na následující otázky:
- Jaké tržby mají pobočky po slevě celkem?
- Kolik celkem slev jsme rozdali na jednotlivých slevových kódech?
- Kolik byly celkem tržby pro jednotlivé kategorie produktů jak po slevě tak před slevou
- Které produktové značky se prodalo nejvíce kusů?
Abychom našly na tyto otázky odpovědi, tak musíme propojit všechny tyto soubory dohromady. Abychom dopočítali tržby po slevě, tak musíme propojit tabulku slev z textového souboru s prodejními daty. Abychom odpověděli na to, kolik slev jsme rozdali, tak to zase musíme propojit se slevami a dopočítat celkové slevy z objednávek. Abychom odpověděli na to, jaké tržby mají kategorie produktů, tak to musíme propojit s prodejními daty a slevovými kódy a abychom zjistili značku, tak to rovněž musíme propojit s číselníkem.
Příprava zdrojových dat
Nejprve se napojíme na složku, kde máme zdrojové prodejní soubory, které musíme spojit a upravit. Otevřeme prázdný excelový soubor a na kartě Data vybereme Načíst data > Ze souboru a Ze složky.
V okně navigátoru najdeme složku, na kterou se chceme napojit a vybereme Otevřít. Tím se dostaneme do následujícího zobrazení, kde se nám zobrazí všechny soubory, které máme ve složce. Rozklikneme tlačítko Kombinovat a vybereme Sloučit a transformovat data.
Sloučení souborů probíhá na základě ukázkového souboru, což je v základním nastavení první soubor ve složce. V levém panelu vybereme, že se chceme v každém Excelu napojit na excelovou tabulku Data. V pravém okně vidíme náhled dat.
Po potvrzení se sloučené soubory načtou do editoru Power Query.
První úprava, kterou musíme provést je vyplnit datum dolů na všechny řádky. Takže označíme sloupec Datum a na kartě Transformace vybereme Vyplnit a Vyplnit dolů.
První sloupec v datech nepotřebujeme, takže ho smažeme. Označíme sloupec a buď vybereme Odebrat a nebo použijeme klávesu DELETE.
Pobočky potřebujeme převést ze sloupců na řádky. Takže označíme sloupce, které převádět nechceme, což jsou sloupec Produkt ID, ID objednávky a Datum a vybereme pravé tlačítko myši a v nabídce vybereme Převést ostatní sloupce na řádky.
Převedený sloupec přejmenujeme z Atributu na Pobočky a Hodnotu na Tržba.
Z pobočky potřebujeme oddělit jen název pobočky, takže vybereme sloupec, karta Transformace a zde yvbereme Extrahovat. A chceme extrahovat pomocí oddělovače, takže vybereme Text za oddělovačem.
Jako oddělovač vybereme pomlčku a mezerami.
Teď je tabulka vyčištěná a můžeme načíst druhou zdrojovou tabulku. Ta je v excelovém souboru, takže vybereme z karty Domů Nový zdroj > Sešit Excel.
Najdeme excelový soubor, na který se chceme napojit a vybereme tabulku a potvrdíme napojení.
S tabulkou produktového číselníku nepotřebujeme nic dělat, takže můžeme nahrát další zdroj.
Další zdrojový soubor máme ve formátu textového souboru, takže na kartě Domů vybereme Nový Zdroj > Text/CSV.
Power Query dokáže určit u textového souboru oddělovač a textový soubor rozdělí do sloupců podle oddělovače.
Ani s touto zdrojovou tabulkou nemusíme nic provádět, můžeme ji jen přejmenovat na Slevové kódy.
A můžeme se vrhnout na odpovídání otázek.
Jaké tržby měly pobočky po slevě?
Abychom dokázali odpovědět na první otázku, tak musíme do zdrojové tabulky data dostat informace o slevách. Vytvoříme odkaz na zdrojová data a zde sloučíme data. Chceme slučovat tabulku Data s tabulkou Slevové kódy a společným sloupcem je sloupec ID objednávky.
A z tabulky potřebujeme rozbalit výši slevy.
Jelikož ne každá objednávka měla slevu, tak u objednávek bez slev máme prázdné buňky, tak je nahradíme nulami. Karta Transformace a Nahradit hodnoty a nahrazujeme null a nahrazujeme nulou.
A teď spočítáme tržbu po slevě pomocí vlastního sloupce.
A následně sloučíme tabulku pro pobočky. Slučujeme dle sloupce Pobočka a chceme nový sloupec Tržba po slevě celkem a chceme sčítat sloupec Tržba po slevě.
A tím se nám sloučí celá tabulka podle poboček a máme odpověď na první otázku.
Kolik slev jsme rozdali po jednotlivých kódech?
Abychom odpověděli na druhou otázku, tak vytvoříme odkaz na slevové kódy. A dotaz pojmenujeme jako Report 2.
Tuto tabulku seskupíme podle sloupce Kupon, kde máme název slevové akce. A jelikož co řádek, to jedna sleva, tak nám stačí jako výpočet spočítat řádky.
A tímto jednoduchým úkonem máme spočítáno, kolik slev jsme rozdali podle jednotlivých kódů.
Jaké byly tržby pro jednotlivé kategorie produktů před slevou i po slevě?
Další dotaz se ptá, kolik byly tržby po slevě i před slevou pro jednotlivé kategorie produktů. K tomu vytvoříme třetí dotaz, který nazveme Report 3. A abychom byli schopní odpovědět na tuto otázku, tak nejprve do dotazu musíme dostat informace o kategorii produktu, což je informace v tabulce Produkty. A společným sloupcem je sloupec ID produktu.
Z tohoto spojení potřebujeme rozbalit informaci pouze pro Kategorii produktu.
A jelikož musíme dopočítat i tržby po slevě, tak do tabulky musíme dostat i informaci o výši slevy. Takže report spojíme ještě s tabulkou slevové kódy, kde je společným sloupcem ID objednávky.
Následně rozbalíme sloupec sleva a jelikož zde jsou zase prázdné řádky, tak je nahradíme nulami. Následně dopočítáme pomocí vlastního sloupce tržbu po slevě.
A teď když máme všechny informace, které potřebujeme, tak tabulku seskupíme. A seskupujeme tabulku podle Kategorie. A potřebujeme dva výpočty – Tržbu celkem a Tržbu slevě celkem, takže dva součty dvou různých sloupců.
A máme odpověď na třetí dotaz, kolik byly celkové tržby po slevě i před slevou pro jednotlivé kategorie produktů.
Které produktové značky se prodalo nejvíce kusů?
Ještě musíme odpovědět na poslední otázku, kolik se prodalo kusů od jednotlivých produktových značek. Takže vytvoříme čtvrtý report, kde potřebujeme sloučit dotazy Report 4 a produktovou tabulku, kde máme informaci o značkách.
A tím že rozbalíme sloupec Značka, tak můžeme následně sloučit tabulku podle Značky. Výpočtem bude počet řádků a tím dostaneme počet prodaných produktů po jednotlivých značkách.
A teď máme odpovědi na všechny dotazy a tabulky můžeme nahrát do Excelu.
Jedna odpověď
Dobrý den,
zhlédl jsem Váš návod na problematiku „Jak v Excelu spojit data z různých zdrojů“. Předem chci uvést že si vážím Vaší práce, která mi rozšiřuje způsoby použití Excelu a dalších nástrojů. Nicméně narazil jsme na jednu nepřesnost a to při řešení 2. otázky – Počet vydaných kupónů. Tady mi vadí fakt, že tabulka Slevové kódy má pouze rozsah období od ledna do března a již zde není duben. Proto je výsledek zkreslující – pokud tedy věříme tomu, že v dubnu nebyly žádné slevy :-). V reálu by bylo potřeba slevové kódy načítat ze složky obdobně jako Data. Abych odstranil možné nepřesnosti, upravil jsem řešení tohoto příkladu tak, že jsem Sloučil Data s Slevovými kódy a připojil k nim sloupec Kupon, ze kterého jsem odstranil prázdné hodnoty a ze sloupce ID_objednavky jsem odstranil duplicity a až z těchto dat jsem udělal seskupení počtu Kuponů.
Nicméně děkuji za zajímavý příklad a těším se na další.
V úctě
Jindřich Kufa