V dnešním videu si ukážeme, jak můžeme oddělit hodnoty z jedné buňky do řádků. Tedy jak z nestrukturovaných dat vytvořit jednoduše klasický tabulkový formát. A převod si ukážeme pomocí nástroje Power Query, což je nejrychlejší a nejjednodušší způsob, jak můžete takovou tu tabulku upravit.
Excelový soubor ke stažení:
Ve zdrojové tabulce máme v jednom sloupci zaměstnance a ve druhém sloupci evidujeme kurzy, ke kterým mají certifikáty. Bohužel někdo certifikáty eviduje v jedné buňce a odděluje je čárkou. To není ideální struktura pro uchování dat. Většina zaměstnanců má více certifikátů, ale máme zde i zaměstnance, který není držitelem žádného certifikátu.
Naším cílem je převést tabulku na klasickou tabulkovou strukturu, kde budeme mít v jednom sloupci zaměstnance a ve druhém sloupci certifikáty.
K převodu tabulky použijeme Power Query.
Nejprve musíme tabulku na hrát do Power Query. Jelikož se ale do Power Query nahrávají excelové tabulky, tak nejprve klikneme do tabulky a použijeme klávesovou kombinaci CTRL+T. Potvrdíme, že tabulka má záhlaví a z obyčejného rozsahu dat se stane excelová tabulka. Tabulku pojmenujeme jako Zdroj.
Teď klikneme do tabulky a na kartě Data vybereme Načíst data a z tabulky nebo oblasti. Tabulka se po chvilce nahraje do editoru Power Query.
Výslednou tabulku rovnou pojmenujeme jako Certifikáty.
Jednotlivé certifikáty máme oddělené čárkou. Klasicky můžeme k oddělení certifikátů použít oddělení oddělovačem. To uděláme tak, že označíme sloupec, ze kterého chceme oddělovat a na kartě Transformace vybereme Rozdělit sloupce a oddělovačem.
V okně rozdělení si máme možnost vybrat z přednastavených oddělovačů. Naším oddělovačem je čárka, takže vybereme čárku.
Pokud teď toto nastavení potvrdíme, tak se certifikáty oddělení do samostatných sloupců.
Nicméně všimněte si drobného problému. U některých certifikátů máme i nadbytečné mezery a je to proto, že za čárkou byla ještě mezera. Samozřejmě bychom sloupce teď mohli oříznout a zbavit se tím nadbytečných mezer, ale zbytečně bychom přidávali zbytečný krok. Změněný typ zatím nepotřebujeme tak ho smažeme a vrátíme se do rozdělení oddělovačem. Místo čárky si můžeme zvolit vlastní oddělovač, takže vybereme vlastní a jako oddělovač stanovíme čárku a mezeru.
Potvrdíme a teď se certifikáty rozdělili do samostatných sloupců bez nadbytečných mezer.
Další problém, který tímto způsobem vzniká je, že ně každý zaměstnanec má stejný počet certifikátů. Takže ti, co mají méně zaměstnanců, tak u nich máme tyto prázdné sloupce. U zaměstnance, který nemá žádný certifikát máme pouze prázdné buňky. Nicméně s tím si poradíme. Teď když máme certifikáty rozdělené, tak je potřebujeme dostat do jednoho sloupce. A k tomu slouží převod sloupců na řádky. Jelikož chceme na řádky převést všechny sloupce kromě zaměstnance, tak označíme tento sloupec a vybereme Převést ostatní sloupce na řádky.
Tím se všechny certifikáty seřadí pod sebe a prázdné řádky se odstraní. Kromě toho, že se odstranili řádky s prázdnými certifikáty, tak si ale všimněte, že se tímto převodem odstranil i poslední zaměstnanec, který neměl žádný certifikát. Zde záleží na tom, co je vaším cílem, pokud chcete zachovat pouze zaměstnance s certifikáty, pak tento způsob můžete použít.
Navíc zde touto metodou vznikl tento sloupec s atributy, který nepotřebujeme, takže ho označíme a smažeme a teď máme tabulku, kterou jsme chtěli vytvořit. Jako poslední změníme datové typy a naše práce je hotová.
Na tomto způsobu není nic špatného, ale existuje daleko rychlejší a efektivnější způsob, který vám navíc neodstraní zaměstnance, který nemá certifikát.
Duplikujeme tento dotaz a ukážeme si rychlejší rozdělení. Nejprve tabulku přejmenujeme na Report, ať je odlišíme.
Tyto kroky nepotřebujeme, takže je smažeme. Klikneme na druhý krok a vybereme Odstranit kroky až do konce. Zůstane pouze zdrojová tabulka.
Označíme sloupec s certifikáty, karta Transformace a Rozdělit sloupec. Vybereme oddělení oddělovačem. Zde nastavíme opět vlastní oddělovač a vlastní oddělovač je opět čárka a mezera. A trik v rychlém rozdělení do řádků je rozkliknout Upřesnit možnosti a zde vybrat Rozdělit do řádků.
Potvrdíme nastavení a tím se rovnou oddělí certifikáty do řádků. Takže si ušetříte krok s převodem sloupců na řádky. A navíc nám zde tímto způsobem zůstal i zaměstnanec, který nemá žádný certifikát a u něho máme null buňku.
Pokud v tabulce nechcete prázdnou buňku, můžete ji nahradit. Označíme sloupec a na kartě Transformace vybereme Nahradit a za null buňku chceme třeba Bez certifikátů. Potvrdíme.
Stačí změnit datový typ a máme hotovo. Finální tabulku načteme do Excelu a vybereme načtení do tabulky a tabulku umístíme vedle zdrojové tabulky. A máme převedené tabulky, které budou reagovat i na nově přidané zaměstnance.
