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.
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.
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.
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.
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.
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ů.
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.
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.
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.
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.
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.
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.
Ú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.
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.
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.
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.
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.
Nezapomeňte se podívat na navazující bonusové video, kde vám ukáži, jak z textového řetězce oddělit prostřední část textu.
Jedna odpověď
😊👍