Představte si následující úkol. Máte tabulku zaměstnanců, oddělení, pro které pracují a jejich hrubou mzdu. A dostanete za úkol spočítat, o kolik se mzda jednotlivých zaměstnanců odchyluje od průměrné mzdy oddělení. To znamená, že potřebujete zjistit, jaká je průměrná mzda na jednotlivých odděleních a pak dopočítat, o kolik více nebo méně berou zaměstnanci na příslušném oddělení. Jak byste tento úkol řešili? Já vám v tomto videu ukáži, jak tento příklad vyřešit během několika minut bez použití jediné excelové funkce.
Excelový soubor ke stažení:
Zdrojová tabulka vypadá následovně. Máme v ní sloupce Zaměstnanec, oddělenní a hrubá mzda. Excelová tabulka se jmenuje HR.
Zdrojovou tabulku máme ve formátu excelové tabulky, takže do ní jen klikneme a na kartě Data vybereme Z tabulky nebo oblasti.
Tabulka se po chvilce nahraje do editoru Power Query. V tabulce nepotřebujeme dělat žádné úpravy ani transformace, takže se rovnou vrhneme na výpočet.
Nejprve potřebujeme spočítat průměrnou mzdu pro jednotlivá oddělení. To můžeme udělat pomocí nástroje seskupit podle neboli group by. A chceme seskupovat hodnoty podle sloupce oddělení. Takže sloupec oddělení označíme a na kartě Transformace vybereme Seskupit podle.
Po potvrzení se vrátí seskupená tabulka, kde vidíme průměrné mzdy jednotlivých oddělení. Tuto průměrnou mzdu teď potřebujeme dostat na každý řádek příslušného oddělení. Vrátíme se do kroku seskupení a přepneme se do rozšířeného nastavení.
A přidáme agregaci. Nový sloupec nazveme třeba Informace, jelikož tento sloupec bude obsahovat všechny informace z původní tabulky. A jako operaci vybereme poslední možnost Všechny řádky.
Když to potvrdíme, tak se do seskupené tabulky přidá nový sloupec Informace a když na řádek klikneme, tak vidíme, že pro vybrané oddělení máme zde seskupené všechny řádky, které patří pod vybrané oddělení. Právě k tomu slouží tato agregace Všechny řádky.
Rozbalíme tento sloupec a chceme zaměstnanec a hrubá mzda.
A teď máme na každém řádku zaměstnance, jeho mzdu, a navíc průměrnou mzdu oddělení.
A teď už je to snadné. Máme zjistit odchylku každého zaměstnance od průměrné mzdy jeho oddělení. Takže můžeme označit sloupec hrubá mzda a průměr a sloupce od sebe odečteme. Karta přidání sloupce a standardní výpočet a odečíst.
Sloupec přejmenujeme na Odchylka.
Tabulku můžeme ještě vylepšit seřazením. Můžeme nejprve seřadit oddělení podle nejvyšší průměrné mzdy. A navíc chceme v rámci oddělení seřadit zaměstnance od nejvyšší odchylky. Takže jako druhý sloupec seřadíme hrubou mzdu.
Než pošleme tabulku do Excelu, tak nepotřebujeme informaci o průměrné mzdě oddělení, takže sloupec klidně smažeme. A tabulku pošleme do Excelu. Výhodou je, že pokud se jakákoliv informace změní nebo přidá, tak můžeme pouze aktualizovat propojení a tabulka je aktuální.
