3 skvělé triky pro transformaci dat v Excelu pomocí Power Query
Power Query je výkonný nástroj v Excelu, který umožňuje efektivně zpracovávat a transformovat data. V tomto článku se podíváme na tři užitečné triky, které vám pomohou při práci s daty v Power Query.
1. Oddělení hodnot v buňce podle zalomení řádku
Často se setkáváme s daty, kde jsou v jedné buňce uvedeny různé informace oddělené zalomením řádku. Takto strukturovaná data jsou obtížně zpracovatelná, proto je potřeba je rozdělit do samostatných sloupců.
Postup:
- Klikněte do sloupce s daty ve formátu excelové tabulky.
- Na kartě „Data“ najděte „Načíst data“ a vyberte „Z tabulky nebo oblasti“.
- Data se načtou do Power Query.
- Označte sloupec s daty.
- Na kartě „Transformace“ najděte „Rozdělit sloupec“ a vyberte „Rozdělení oddělovačem“.
- V možnostech oddělovače vyberte „Vlastní oddělovač“.
- Zaškrtněte možnost „Rozdělit pomocí speciálních znaků“.
- V nabídce vyberte znak „LF“ (Line Feed).
- Potvrďte oddělovač.
Sloupec se nyní rozdělí podle zalomení řádku. Můžete jednotlivé sloupce přejmenovat podle druhu informace, kterou obsahují, přejmenovat tabulku a odeslat ji zpět do Excelu.
2. Jak zabránit rozbití propojení při změně názvu sloupce ve zdrojové tabulce
Tento trik je užitečný, když pracujete s daty, kde se názvy sloupců pravidelně mění (např. měsíční reporty). Běžně by změna názvu sloupce ve zdrojové tabulce způsobila chybu při aktualizaci Power Query.
Postup:
- Otevřete Power Query.
- Smažte poslední krok „Změněný typ“.
- Přejmenujte problematický sloupec na obecný název (např. „Hodnoty“).
- Klikněte na krok přejmenování sloupce a upravte funkci následovně:
Copy
Table.RenameColumns(Zdroj,{{Table.ColumnNames(Zdroj){1}, "Hodnoty"}})
- Potvrďte funkci.
- Přiřaďte každému sloupci správný datový typ.
- Odešlete tabulku zpět do excelového listu.
Nyní můžete měnit název sloupce ve zdrojové tabulce a propojení se nerozbije.
3. Převod sloupců na řádky se zachováním prázdných buněk
Tento trik je užitečný, když potřebujete převést data ze sloupcového formátu na řádkový, ale zároveň chcete zachovat i řádky s prázdnými hodnotami.
Postup:
- Nahrajte tabulku do Power Query.
- Označte sloupce s produkty.
- Na kartě „Transformace“ najděte „Nahradit hodnoty“.
- Nahraďte hodnotu „null“ za „0“ (nebo jinou vhodnou hodnotu).
- Označte sloupce s produkty.
- Na kartě „Transformace“ vyberte „Převést sloupce na řádky“.
- Tabulka je nyní ve správném formátu a můžete ji odeslat zpět do Excelu.
Časté dotazy
- Proč je důležité oddělit hodnoty v buňce podle zalomení řádku?
Oddělení hodnot umožňuje lépe pracovat s daty, filtrovat je a analyzovat. Každý typ informace je ve vlastním sloupci, což usnadňuje další zpracování. - Jak často se musí aktualizovat propojení při změně názvů sloupců ve zdrojové tabulce?
Při použití triku s přejmenováním sloupců podle pozice se propojení aktualizuje automaticky a není potřeba ho manuálně upravovat. - Proč je důležité zachovat prázdné buňky při převodu sloupců na řádky?
Zachování prázdných buněk může být klíčové pro správnou analýzu dat. Jejich odstranění by mohlo vést k nesprávným výsledkům nebo chybějícím informacím. - Je možné tyto triky kombinovat?
Ano, tyto triky lze kombinovat podle potřeby. Můžete například nejprve oddělit hodnoty v buňce, poté zabezpečit propojení a nakonec převést sloupce na řádky. - Jsou tyto triky použitelné i v jiných verzích Excelu?
Tyto triky jsou primárně určeny pro novější verze Excelu s Power Query. Starší verze Excelu nemusí mít všechny tyto funkce k dispozici.