V dnešním videu si ukážeme, jak v Power Query pracovat s možností Seskupit podle. Jedná se o skvělý nástroj, který dokáže nahradit funkci jako SUMIFS nebo COUNTIFS. Takže pokud potřebujete pro seskupovaná data sčítat, počítat nebo i průměrovat, tak se vám tato znalost bude hodit. Na webu Akademie Excelu máme o Power Query již několik videí na téma třeba 12 triků v Power Query, čištění a úprava dat v Power Query nebo úprava textu v Power Query.
A pro členy Akademie Excelu máme na webu i navazující video, kde si ukážeme jak pomocí Power Query seskupovat čas.
Excelový soubor ke stažení:
Seskupit podle v Power Query
V příkladu máme tabulku, kde máme uvedené produkty, datumy, pobočky a tržby. Máme celkem tři úkoly, které máme vyřešit. Naším úkolem je sečíst celkové tržby pro jednotlivé produkty. Druhým úkolem je sečíst tržby pro jednotlivé měsíce. Třetím úkolem je spočítat, kolik tržeb je v tabulce celkem pro jednotlivé pobočky. Navíc máme produkt vždy uvedený pouze na prvním řádku v tabulce, a nikoliv na všech řádcích. Příklad by samozřejmě šel vyřešit pomocí excelových funkcí. My si dnes řešení ukážeme pomocí Power Query.
První, co uděláme je, že tabulku změníme na excelovou tabulku. Klikneme do tabulky a použijeme klávesovou kombinaci CTRL+T, potvrdíme, že tabulka má záhlaví a tabulka se změnila na excelovou tabulku. Teď můžete tuto tabulku nahrát do Power Query. Klikneme do tabulky a na kartě Data najdeme Načíst a transformovat. Zde vybereme Z tabulky nebo oblasti. Tabulka se po chvilce nahraje do Power Query.
Než začneme s jakýmikoliv výpočty, tak nejprve zkontrolujeme, že má každý sloupec správný datový typ. Produkt je ve formátu textu, ale datum je ve formátu datum a čas, což je zbytečné, jelikož zde žádný čas nemáme. Takže změníme u sloupce Datum datový typ na pouze Datum. Ostatní dva sloupce mají datové typy správně.
Prvním úkolem bylo sečíst celkové tržby pro jednotlivé produkty. Než se do toho pustíme, tak vyřešíme to, že nemáme produkt uvedený na každém řádku. V Power Query existuje jednoduchá možnost kopírování dolů, kterou najdeme na liště Transformace. Označíme sloupec s produkty a na kartě Transformace najdeme Vyplnit a vybereme Vyplnit dolů. Tím se zkopíruje název produktu na řádky po sebe a máme teď správný produkt na každém řádku tabulky.
Teď když máme tabulku kompletní, tak můžeme začít počítat tržby pro produkty. Na rozdíl od Excelu zde nemusíme používat žádné funkce, ale jednoduše stačí na kartě Domů vybrat Seskupit podle. Otevře se okno, kde máme možnost nastavit podle jakého pole chceme tabulku seskupit.
Nejprve chceme tabulku seskupit tak, aby na řádcích byl jedinečný seznam produktů. Vybereme tedy seskupení podle sloupce Produkt. V dalším poli máme možnost nový sloupec pojmenovat. Chceme sčítat tržby, takže název sloupce bude Tržba celkem. A co v tomto novém sloupci chceme? Chceme součet, takže jako operaci vybereme Součet a jaký sloupec chceme sčítat? Chceme sčítat sloupec s Tržbami.
Potvrdíme a zdrojová tabulka se seskupila podle produktů a ve sloupci Tržba celkem sečetla celkové tržby. První úkol je hotový.
Druhým úkolem bylo sčíst tržby podle jednotlivých měsíců. Abychom se nemuseli vracet do Excelu a znovu načítat zdrojovou tabulku do Power Query, tak se můžeme na tento původní dotaz odkázat. Tento první dotaz řeší celkové tržby po produktech, takže nazveme tabulku Tržba po produktech. Teď na tento dotaz klikneme v levém ovládacím panelu pravým tlačítkem myši a vybereme Duplikovat.
Tím se dotaz duplikuje a my máme možnost provést další úpravy na tomto novém dotazu. Rovnou ho přejmenujeme, ať se nám to neplete. Teď potřebujeme Tržby po měsících. V tomto dotazu jsou stejné kroky jako v minulém, takže můžeme smazat jen ty, které nepotřebujeme. Smažeme poslední krok, který seskupoval data, čímž se vrátíme k původní upravené tabulce.
Potřebujeme data sečíst podle měsíců, takže musíme do této tabulky přidat nový sloupec, kde určíme, o jaký měsíc se jedná. Označíme sloupec s Datumem a na kartě Přidání sloupce vybereme Datum a Měsíc a v nabídce vybereme Název měsíce. Jelikož v tabulce máme jen data za jeden rok, tak nemusíme řešit, do kterého roku měsíc patří. Do tabulky se přidal název měsíce a teď máme vše co potřebujeme k seskupení dat.
Na kartě Domů vybereme Seskupit podle. Tentokrát chceme tabulku seskupit podle sloupec Název měsíce. Nový sloupec můžeme opět nazvat jako Tržba celkem. Jako operaci opět chceme součet a opět chceme sčítat sloupec s Tržbami.
Potvrdíme a tabulka se seskupila do tří řádků, jelikož máme tři měsíce a pro každý měsíc se sečetli tržby.
Poslední úkol říkal, že máme spočítat, kolik tržeb v tabulce je pro každou pobočku. Opět se můžeme odkázat na první dotaz a duplikovat ho. Proč na první dotaz? V zásadě je jedno, který dotaz budete duplikovat, ale jelikož v druhém dotazu máme navíc sloupec s názvem měsíce, který nepotřebujeme, tak je praktičtější se odkázat na první dotaz. Klikneme na něj pravým tlačítkem myši a vybereme Duplikovat. Dotaz rovnou přejmenujeme na Počet pobočky.
Teoreticky poslední krok seskupení nemusíme mazat, ale můžeme ho pouze upravit. Klikneme v pravém panelu vedle posledního kroku na ozubené kolečko a tím se otevře úprava seskupení. Chceme seskupovat podle sloupce s Pobočkami. Tentokrát nebudeme sčítat tržby ale počítat, takže nový sloupec můžeme nazvat jako Počet celkem. Operace není součet, ale počet a chceme spočítat počet výskytu tržby, takže vybereme Počet řádků. U této možnosti se sloupec nevybírá, jelikož počet řádků bude stejný pro každý sloupec.
Potvrdíme a vrátila se tabulka, kde na řádcích jsou pobočky a ve sloupci Počet celkem je počet tržeb ve zdrojové tabulce.
Všechny tři úkoly jsou vyřešené. Takže můžeme všechny tři tabulky nahrát zpátky do Excelu.
Na liště Domů vybereme Zavřít a Načíst a tabulky se načtou do Excelu na samostatné listy, které budou nazvané stejně jako tabulky.
Na propojení přes Power Query je nejlepší snadná aktualizace zdrojových dat. Do zdrojové tabulky přidáme data, která máme schovaná pod tabulkou. V těchto nových datech máme nejen nový produkt, ale i nový měsíc a dvě nové pobočky. Uvidíme, jak si s aktualizací poradí Power Query. Zkopírujeme data a vložíme je do excelové tabulky. Teď se překlikneme na jakýkoliv list s tabulkami a vybereme na kartě Data Aktualizovat a Aktualizovat vše.
Jak vidíme, tak se aktualizovalo propojení mezi zdrojovou tabulkou a Power Query a provedly se všechny úpravy. V tabulce s pobočkami máme nové pobočky a celkový počet tržeb. V tabulce tržby po měsících přibyl nový měsíc a v tabulce tržba pro produktech přibyl nový produkt včetně celkových tržeb.
Jedna odpověď
😊👍👍