Úprava textu v Power Query | Jak v Power Query oddělit jména a tituly

V dnešním videu se podíváme na to, jak pomocí nástroje Power Query v Excelu provést textové úpravy, a to přesněji, jak oddělit jména například od titulů. Jedná se o velmi častý nešvar v Excelu. Představte si, že máte jednu tabulku se jmény zaměstnanců včetně jejich titulů. Ve druhé tabulce máte jména zaměstnanců s jejich mzdami a chcete pomocí excelových vyhledávacích funkcí najít mzdy ke jménu. Problémem je, že ve druhé tabulce, kde máte mzdy, ale zaměstnanci tituly uvedené nemají. A nebo je tam mají, ale třeba v jiném formátu, než v jakém je máte v první tabulce. Vyhledávací funkce vám v tuto chvíli samozřejmě nebudou fungovat. Abyste byli schopní s oběma tabulkami pracovat, tak buď do jedné musíte tituly přidat a nebo je z druhé odebrat. Dnes si ukážeme, jak vyčistit jména v tabulce pomocí Power Query, abyste s nimi byli schopní pracovat.

Excelový soubor ke stažení

Úprava textu v Power Query

Techniku úpravy textu si budeme ukazovat na příklad jmen a titulů, nicméně stejný postup lze použít na jakékoliv textové úpravy, kde potřebujete z textového řetězce vyselektovat pouze určitou část.

Tituly před jménem

V prvním příkladu mají všichni zaměstnanci v tabulce (A3:A8) nějaký titul před jménem. Každý titul je uvedený před jménem a je za ním vždy tečka a mezera. Z této tabulky jmen potřebujeme vyextrahovat pouze jméno a příjmení, abychom byli schopní ke jménům vyhledat mzdy z druhé tabulky (E3:F8), kterou máme vedle a kde máme po každého zaměstnance uvedenou mzdu.

Úprava textu v Power Query 1
Obrázek č.1 Tituly před jménem

Musíme začít tím, že zdrojovou tabulku jmen (A3:A8) nahrajeme do Power Query. Abychom byli schopní nahrát tuto tabulku do Power Query, tak z ní nejprve musíme udělat oficiální excelovou tabulku. Klikneme do tabulky a zmáčkneme klávesovou kombinaci CTRL+T, potvrdíme, že tabula má záhlaví a potvrdíme. Z obyčejné tabulky se stala oficiální excelová tabulka, která má typický proužkovaný vzhled. Pokud chceme zachovat původní formát tabulky, tedy bez formátu, tak klikneme do tabulky, aktivujeme kartu Návrh tabulky a na kartě Návrh tabulky vybereme Styly tabulky a vybereme žádný styl.

Obrázek č.2 Oficiální excelová tabulka a žádný styl

Teď klikneme do tabulky (A3:A8) a na kartě Data vybereme Načíst a Transformovat data a vybereme Z tabulky nebo oblasti. Tabulka se po chvilce nahraje do Power Query. Potřebujeme oddělit tituly od jména, takže potřebujeme provést transformaci textu.

Obrázek č.3 Nahrání tabulky do Power Query

V Power Query vybereme záložku Transformace. Máme na výběr zda sloupec rozdělit a nebo extrahovat hodnotu. Rozdíl je v tom, že u extrakce zůstane ve sloupci pouze to, co extrahujeme a vše ostatní zmizí. Při rozdělení sloupce zůstane zachována i původní hodnota nebo sloupec a vytvoří se hodnota nová. V tomto prvním příkladu postačí, pokud nám v tabulce zůstanou pouze jména bez titulů, takže v tomto případě vybereme Extrahovat. A text za oddělovačem.

Obrázek č.4 Extrakce textu za oddělovačem

V poli oddělovač si můžeme vybrat, dle jakého oddělovače chceme text rozdělit. Můžeme použít tečku, která je za každým titulem a nebo můžeme použít mezeru, která je rovněž za každým titulem. V tomto případě je lepší použít mezeru. Pokud jako oddělovač použijeme tečku, tak budeme muset nakonec ještě oříznout text, jelikož nám před jmény zůstane nadbytečná mezera. 

Obrázek č.5 Rozdělení textu oddělovačem

Kdybychom jako oddělovač použili mezeru, tak si tento poslední krok s ořezáváním textu ušetříme. Klikneme na sloupec, karta Transformace, Extrahovat a Text za oddělovačem. A jako oddělovač vybereme mezeru. Jelikož ale máme mezeru i mezi jménem a příjmením, tak musíme ještě zkontrolovat nastavení v Upřesnit možnosti. Chceme vyhledat oddělovač od začátku vstupu, což je základní nastavení. Máme na výběr ještě od konce vstupu, což by rozdělilo text podle první mezery od konce. Nastavení tedy máme správně, takže potvrdíme. Ve sloupci zůstala pouze jména bez titulů. 

Obrázek č.6 Oddělení textu oddělovačem

Když jsme s úpravami hotoví, nahrajeme tabulku zpátky do Excelu. Horní lišta, záložka Domů a Zavřít a Načíst. Tabulka se po chvilce nahraje do Excelu na nový list. Tabulka se do Excelu nahrála na vlastní list jako oficiální excelová tabulka se kterou teď můžeme dále pracovat. 

Úprava textu v Power Query 7
Obrázek č.7 Nahrání tabulky do Excelu

Vedle tabulky vytvoříme další sloupec, kam napíšeme mzda a pomocí funkce SVYHLEDAT najdeme mzdu ze zdrojové tabulky a ověříme, že očištění pomocí Power Query funguje. Napíšeme funkci SVYHLEDAT, nejprve označíme zaměstnance, kterého hledáme, následuje zdrojová tabulka s příjmy. Mzdu hledáme ve druhém sloupci a hledáme přesnou shodu, takže nulu. Funkci potvrdíme a funkce se správně doplnila mzdy na všechny řádky.

Úprava textu v Power Query 8
Obrázek č.8 Funkce SVYHLEDAT na vyhledání mzdy

Tituly za jménem

V dalším příkladu mají zaměstnanci tituly za jmény. Tentokrát má někdo za příjmením čárku a následuje titul a někdo má za příjmením pouze mezeru a titul. Opět začneme tím, že z obyčejné tabulky uděláme oficiální excelovou tabulku. Klikneme do tabulky a zmáčkneme klávesovou kombinaci CTRL+T po vytvoření oficiální excelové tabulky, potvrdíme a rovnou změníme opět styl tabulky. Návrh tabulky, Styl tabulky a žádný styl.

Úprava textu v Power Query 9
Obrázek č.9 Tituly za jmény

Teď můžeme tabulku nahrát do Power Query. Na kartě Data vybereme Načíst a Transformovat z Tabulky nebo oblasti. Označíme sloupec se jmény a opět vybereme na kartě Transformace možnost Extrahovat. V záložce Extrahovat vybereme Text před oddělovačem. V tomto případě nemá každý za jménem čárku, takže jako oddělovač použijeme mezeru. 

Úprava textu v Power Query 10
Obrázek č.10 Transformace > Extrahovat Text před oddělovačem

Do pole oddělovač napíšeme mezeru a znovu zkontrolujeme další možnosti. Tentokrát musíme oddělit text od konce, takže v okně vybereme Od konce vstupu. Potvrdíme a tituly zmizely. 

Úprava textu v Power Query 11
Obrázek č.11 Text před oddělovačem

Nicméně u některých jmen nám zůstaly čárky za příjmením. Označíme sloupec a vybereme na kartě Transformace Nahradit hodnoty. Hledaná hodnota je čárka a nahrazujeme ji ničím. Potvrdíme.

Úprava textu v Power Query 12
Obrázek č.12 Nahrazení hodnoty

Úpravy jsou hotové, takže tabulku pošleme zpátky do Excelu. Karta Domů a Začít a Načíst. 

S titulem i bez titulu

V posledním příkladu máme v tabulce zaměstnance, přitom někteří mají tituly před jménem a někteří tituly nemají vůbec. Naposledy klikneme do tabulky a vytvoříme z ní oficiální excelovou tabulku. Klávesová kombinace CTRL+T, potvrdíme. Změníme formát stylu tabulky na žádný. Tabulku nahrajeme do Power Query, takže karta Data, kde vybereme Načíst z tabulky.

Obrázek č.13 Zadání příkladu

Jelikož někteří zaměstnanci titul mají a někteří nemají, tak musíme k řešení přistoupit trochu jinak. První, co můžeme udělat je, že se zkusíme zbavit titulů před jmény zaměstnanců. V tomto případě ale nemůžeme použít na kartě Transformace možnost Extrahovat. Pokud bychom vybrali Extrahovat a text za oddělovačem, tak by jména, která titul nemají kompletně zmizela. Vybereme místo toho tedy Rozdělit sloupec. Jako oddělovač použijeme tečku, kterou ovšem nemáme v nabídce, takže vybereme Vlastní oddělovač a napíšeme tečku. Jelikož nechceme, aby před jménem zůstala nadbytečná mezera tak ještě za tečku napíšeme mezeru. To je náš oddělovač a potvrdíme.

Úprava textu v Power Query 15
Obrázek č.14 Transformace > Rozdělení sloupce

V původním sloupci zůstala jména, která titul neměla a v novém sloupci jsou jména, která měla titul a teď už ho nemají. Teď bychom potřebovali tyto dva sloupce spojit dohromady s určitou podmínkou. Naštěstí Power Query takovou možnost dovoluje. 

Vybereme kartu Přidání sloupce a zde vybereme Podmíněný sloupec. Podmíněný sloupec přidá sloupec na základě podmínky, kterou ji zadáme. Něco jako funkce KDYŽ v Excelu. Název nového sloupce bude Jméno a podmínka bude, že pokud hodnota ve sloupci Zaměstnanec.2, což je název nově vzniklého sloupce je rovno null (prázdná buňka), tak chceme vrátit hodnotu ze sloupce Zaměstnanec.1. Toto pravidlo tedy říká, že pokud je ve druhém sloupci prázdno, tak že se má na řádek vrátit jméno z prvního sloupce, což budou všichni zaměstnanci, kteří neměli titul. A pokud podmínka splněná nebude, tak zde dole nastavíme, co chceme vrátit, v našem případě hodnotu ze sloupce Zaměstnanec.2. Potvrdíme a podíváme se, co z toho vzniklo.

Obrázek č.15 Podmíněný sloupec

Do tabulky se přidal nový sloupec Jméno, kde máme všechna jména zaměstnanců očištěná bez titulů. První dva sloupce nepotřebujeme, takže je klidně smažeme. Označíme sloupce Zaměstnanec.1 a Zaměstnanec.2, klikneme na ně pravým tlačítkem a vybereme Odebrat. Ještě změníme datový typ sloupce na text. Úpravy jsou hotové, takže tuto tabulku pošleme zpátky do Excelu. Karta Domů a Zavřít a Načíst.

Úprava textu v Power Query 17
Obrázek č.16 Odebrání sloupců

Všechny tři tabulky mají stále aktivní připojení na Power Query a na kroky, které jsme v každé tabulce provedli. Pokud tedy do tabulky přidáme nové jméno, tak se změny uplatní i na nově přidané jméno. 

MOHLO BY VÁS ZAJÍMAT

Jedna odpověď

Napsat komentář

Vaše e-mailová adresa nebude zveřejněna. Vyžadované informace jsou označeny *