Dokumenty, Excel

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:

  1. Klikněte do sloupce s daty ve formátu excelové tabulky.
  2. Na kartě „Data“ najděte „Načíst data“ a vyberte „Z tabulky nebo oblasti“.
  3. Data se načtou do Power Query.
  4. Označte sloupec s daty.
  5. Na kartě „Transformace“ najděte „Rozdělit sloupec“ a vyberte „Rozdělení oddělovačem“.
  6. V možnostech oddělovače vyberte „Vlastní oddělovač“.
  7. Zaškrtněte možnost „Rozdělit pomocí speciálních znaků“.
  8. V nabídce vyberte znak „LF“ (Line Feed).
  9. 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:

  1. Otevřete Power Query.
  2. Smažte poslední krok „Změněný typ“.
  3. Přejmenujte problematický sloupec na obecný název (např. „Hodnoty“).
  4. Klikněte na krok přejmenování sloupce a upravte funkci následovně:

Copy

Table.RenameColumns(Zdroj,{{Table.ColumnNames(Zdroj){1}, "Hodnoty"}})
  1. Potvrďte funkci.
  2. Přiřaďte každému sloupci správný datový typ.
  3. 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:

  1. Nahrajte tabulku do Power Query.
  2. Označte sloupce s produkty.
  3. Na kartě „Transformace“ najděte „Nahradit hodnoty“.
  4. Nahraďte hodnotu „null“ za „0“ (nebo jinou vhodnou hodnotu).
  5. Označte sloupce s produkty.
  6. Na kartě „Transformace“ vyberte „Převést sloupce na řádky“.
  7. Tabulka je nyní ve správném formátu a můžete ji odeslat zpět do Excelu.

Časté dotazy

  1. 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í.
  2. 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.
  3. 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.
  4. 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.
  5. 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.

💡 Snadná správa obchodních aktivit a klientů přímo v Microsoft 365 🚀