Vyhledávání na webu

Power Query je revoluční funkce Excelu, kterou ještě nepoužíváte—zde je 5 způsobů, jak ji využít

Když se ohlédnu na své staré tabulky Excelu, nemohu uvěřit, že jsem přežil bez Power Query. Přeměňuje složitou automatizaci na něco překvapivě uživatelsky přívětivého. Pokud jste se mu vyhýbali, protože vypadá zastrašující, tento průvodce je pro vás.

Provedu vás pěti transformačními „tahy“ s použitím stejné datové sady - začneme extrakcí a čištěním textu, a poté přejdeme k shrnování velkých datových sad bez kontingenční tabulky.

Chcete-li při čtení této příručky sledovat postup, stáhněte si bezplatnou kopii sešitu Excel použitého v příkladech. Po kliknutí na odkaz najdete tlačítko pro stažení v pravém horním rohu obrazovky.

Zlepšete a opravte data ve vašem aktuálním listu

Převeďte neuspořádaný text a shrňte hodnoty

Power Query je jedním z nejsilnějších nástrojů v Excelu pro opravu špinavých dat přímo před vámi. Místo psaní dlouhých, zanořených vzorců, které jsou křehké a těžko se udržují, můžete použít editor Power Query k zaznamenání série transformačních kroků. To udržuje vaše čištění konzistentní, opakovatelné a snadno auditovatelné.

Předpokládejme, že vám byla zaslána tato formátovaná tabulka Excel s názvem T_Sales. Obsahuje neuspořádaný sloupec SKU, který vyžaduje chirurgickou přesnost, spolu s dlouhým seznamem transakcí, který chcete shrnout.

Nejprve načtěte svou tabulku do editoru Power Query. Vyberte libovolnou buňku v datech a na kartě Data klikněte na Z tabulky/rozsahu.

Nyní můžete vidět svou tabulku v novém okně.

Pracovní postup 1: Izolace a úprava konkrétních datových bodů

Extrahujte a vyčistěte data pro lepší reportování

Váš cíl: Vzít surový řetězec jako TSHIRT_XL_RED_001 a převést jej do tří samostatných, čistých sloupců: jednoho pro správně napsaný název položky ("Tričko"), jednoho pro velikost ("XL") a jednoho pro barvu ("Červená").

Extrahování názvu položky nebo velikosti z nepřehledného SKU obvykle vyžaduje bolestivou kombinaci funkcí LEFT, MID a FIND. V Power Query však můžete provést chirurgické čištění během několika sekund.

Krok 1: Rozdělte a přejmenujte své sloupce

Prvním krokem je rozdělit sloupec SKU na položku, velikost, barvu a ID a dát jim jasné názvy:

  1. Vyberte sloupec SKU.
  2. Na kartě Domů klikněte na Rozdělit sloupec > Podle oddělovače.
  3. Vyberte Vlastní jako typ oddělovače, zadejte podtržítko (_), vyberte Každý výskyt oddělovače a klikněte na OK.
  4. Poklepejte na hlavičku každého nového sloupce a přejmenujte je na Položka, Velikost, Barva a ID.

Když dokončujete tyto akce, podívejte se na panel Použité kroky napravo. Power Query zaznamenává každý klik jeden po druhém, takže pokud uděláte chybu, můžete jednoduše kliknout na X vedle kroku, abyste ji vrátili zpět a zkusili to znovu.

Krok 2: Odstraňte zbytečný sloupec ID

Místo standardního mazání vytvořte „chráněný seznam“ sloupců, které chcete ponechat. To říká Power Query, aby se do budoucna dívalo pouze na tyto konkrétní sloupce. Pokud někdo později přidá do vašich zdrojových dat sloupec Poznámky, Power Query ignoruje tento nadbytečný šum, čímž udržuje váš report přehledný a odolný vůči změnám schématu.

  1. Podržte Ctrl a klikněte na všechny záhlaví sloupců, s výjimkou ID.
  2. Klikněte pravým tlačítkem na jeden z vybraných záhlaví a klikněte na Odebrat ostatní sloupce.

Krok 3: Opravte své formátování

Teď, když je struktura nastavena, můžete opravit formátování sloupce Položka. Jedná se o pohyb ve dvou fázích: první je jednoduchá transformace, která převádí velká písmena na standardní kapitalizaci (například „TSHIRT“ na „Tshirt“), a druhá je přeměnit „Tshirt“ na „T-shirt“.

  1. Vyberte sloupec Položka.
  2. Na kartě Transformovat klikněte na Formát > Velká písmena u každého slova.
  3. Klikněte pravým tlačítkem myši na záhlaví sloupce Položka a vyberte Nahradit hodnoty.
  4. Vyměňte Tshirt za T-shirt, potom klikněte na OK.

Krok 4: Přeložte barevné kódy

Nakonec musíte převést třípísmenné kódové barvy na správná jména. Mohli byste znovu použít nástroj Nahrazení hodnot, ale byl by to časově náročný proces, který by vytvořil několik kroků. Místo toho přidejte Podmíněný sloupec:

  1. Na kartě Přidat sloupec klikněte na Podmíněný sloupec.
  2. Pojmenujte nový sloupec Správná barva.
  3. Přidejte své první pravidlo: Pokud Barva se rovná BÍLÁ, pak vypište Bílá.
  4. Klikněte na Přidat ustanovení, tento postup opakujte pro ostatní barvy a klikněte na OK.
  5. Klikněte a přetáhněte nový sloupec doleva, aby byl třetím sloupcem.
  6. Klikněte pravým tlačítkem myši na záhlaví původního, chaotického sloupce Barva a vyberte Odstranit.

Na rozdíl od standardní mřížky Excelu odstranění zdrojového sloupce v Power Query nezpůsobí selhání následujících kroků, pokud na něj tyto kroky neodkazují. Je to proto, že Power Query zpracovává transformace v definovaném pořadí. Vzhledem k tomu, že jste již použili Odstranit ostatní sloupce, je jednoduché odstranění zde bezpečné.

Vaše data jsou nyní čistá, konzistentní a standardizovaná, a jste připraveni přejít na workflow 2.

Pracovní postup 2: Shrnutí obrovských datových sad bez kontingenční tabulky

Sčítání celkových částek transakcí pro sestavy na vysoké úrovni

Váš cíl: Seskupit váš seznam transakcí tak, abyste místo toho, abyste viděli každý jednotlivý řádek, viděli celkovou hodnotu "Prodáno jednotek" pro každou kategorii.

Než provedete konečný krok, musíte rozdělit svá data do dvou samostatných dotazů. To zachovává úklidovou práci, kterou jste již provedli, jako základní vrstvu, což vám umožňuje stavět souhrn na jejím vrcholu.

  1. V podokně Dotazy vlevo klikněte pravým tlačítkem myši na svůj existující dotaz a vyberte Odkaz.
  2. Dvojitým kliknutím na tento nový dotaz jej přejmenujte na Summary_Report.
  3. S tímto novým vybraným dotazem jste připraveni provést závěrečný Krok 5.

Krok 5: Seskupit podle kategorie

Funkce Seskupit podle v Power Query vám umožňuje provádět agregaci, kterou byste obvykle vyhradili pro kontingenční tabulky. Zatímco kontingenční tabulky jsou skvělé pro interaktivní analýzu, Power Query je lépe vhodné pro opakovatelný, aktualizovatelný pracovní tok reportování a pro vyhýbání se duplicitním datovým strukturám. Osobně mi přijde editor Power Query snazší na používání než podokno polí kontingenční tabulky.

  1. Vyberte sloupec Kategorie.
  2. Na kartě Transformovat (nebo na kartě Domů v některých verzích) klikněte na Seskupit podle.
  3. V dialogovém okně se ujistěte, že je vybrána možnost Základní, a nastavte Název nového sloupce na Celkový počet prodaných jednotek.
  4. Nastavte Operaci na Součet a Sloupec na Prodané jednotky.
  5. Klikněte na OK.

Nyní jsou vaše data stručně shrnuta do snadno čitelné tabulky.

Nakonec klikněte na horní polovinu rozděleného tlačítka Zavřít a načíst na kartě Domů, abyste načetli své nové tabulky na samostatné listy. Pokud se vaše zdrojová data změní, klikněte na Data > Obnovit vše, abyste aktualizovali oba dotazy.

Používání Power Query k automatizaci vašeho pracovního postupu je skvělý způsob, jak získat zpět svůj čas v Excelu. Jde o chytřejší práci tím, že necháte software vykonávat opakující se úkoly za vás. Jakmile tyto techniky zvládnete, zjistíte, že použití Pythonu v Excelu je dalším vynikajícím způsobem, jak čistit a opravovat data, když se vaše datové sady stanou obzvlášť složitými.

Související články