V dnešním videu si představíme nové funkce, které vám v Excelu usnadní čištění dat, jako žádný jiný nástroj nebo funkce před nimi. Jedná se o funkce REGEX neboli funkce, které konečně v Excelu dovolují práci s regulérními výrazy, tzv. regular expressions, neboli regex. Až doposud jsme v Excelu mohli s částečnou textovou shodou pracovat pomocí zástupných znaků, nicméně tyto možnosti byly pro úpravu textu dosti omezené. REGEX funkce posouvají možnosti čištění a extrakce dat v Excelu na kompletně novou úroveň. Co když potřebujete z textu vyselektovat pouze telefon nebo datum? Nahradit nechtěné znaky? Nebo vyselektovat jen čísla nebo text z buňky? Až doposud nemyslitelné v Excelu, s funkcemi REGEX je to ale záležitost jedné funkce. A pro milovníky AI si i ukážeme, jak AI vyžít u práce s těmito funkcemi.
Excelový soubor ke stažení:
Regulérní výraz neboli v angličtině regular expression, zkráceně regex, je speciální způsob zápisu vzoru, podle kterého hledáme text. Můžeme si ho představit jako pokročilé vyhledávání, které umí najít složitější textové řetězce. Takové zástupné znaky na steroidech. Díky regulérním výrazům jste schopni v textovém řetězci najít kombinaci znaků, kterou zadáte. Regulérní výrazy mají svůj vlastní styl zápisu. Toto video nemá za cíl vysvětlit dopodrobna každý zápis regulérního výrazu, ale cílem videa je vám ukázat možnosti REGEX funkcí a co všechno s nimi můžete dokázat.
Funkce REGEX jsou dostupné pro předplatitele Microsoft 365 a jedná se o tři funkce – REGEXTEST, REGEXEXTRAHOVAT a REGEXNAHRADIT.
REGEXTEST
Funkce REGEXTEST testuje, zda se regulérní výraz v buňce nachází. Pokud ano, funkce vrátí pravdu, v opačném případě vrátí nepravdu. Funkci zkusíme na velmi jednoduchém příkladu.
V buňce máme tři čísla. Chceme ověřit, zda se v buňce nachází jakékoliv číslo. Napíšeme REGEXTEST, v prvním parametru funkcí se označuje buňka s textem a jako druhý parametr následuje regulérní výraz. Chceme ověřit, že je v buňce číslo. Každý regulérní výraz se uvádí do uvozovek a výraz je [0-9]. Funkce vrátí pravdu.
Kdybychom ale do buňky napsali abc, tak se vrátí nepravda, jelikož zde nebude číslo.
A když napíšeme kombinaci? Stále se vrátí pravda, jelikož se čísla v buňce vyskytují.
Přepíšeme číslo na 1. Co kdybychom chtěli ověřit, že jsou v buňce za sebou dvě čísla? Napíšeme [0-9][0-9]. A vrátí se nepravda, jelikož v buňce máme jen jedno číslo.
Napíšeme 19 a vrátí se pravda.
Co se regulérních výrazů týče, tak existuje několik různých zápisů, které znamenají to samé. Takže zápis [0-9][0-9] je to samé co [0-9]{2}, což znamená, že se hledají dvě po sobě jdoucí číslice.
Funkce REGEXTEST tedy vrací pravdu, pokud je regulérní výraz nalezený a nepravdu, pokud nalezený není.
REGEXNAHRADIT
Funkce REGEXNAHRADIT neboli REGEXREPLACE zase dovoluje nahradit regulérní výraz za něco jiného. V buňce máme kombinaci textu a čísel. Prvním úkolem je, že potřebujeme vyselektovat z první buňky pouze písmena. V podstatě je naším cílem nahradit jakékoliv znaky, které nejsou písmeny ničím, čímž nám zůstanou pouze písmena. K tomu použijeme funkci REGEXNAHRADIT neboli REGEXREPLACE. Napíšeme rovná se a najdeme funkci. V parametru text označíme textovou buňku a pak následuje regulérní výraz. A jelikož chceme, aby nám v buňce zbyl pouze text, tak chceme nahradit všechna čísla v buňce a chceme je nahradit ničím. Regulérní výraz tedy bude vypadat v uvozovkách, a v hranatých závorkách uvedeme nulu až devítku. A v dalším parametru uvedeme, čím chceme tato čísla nahradit. A chceme je nahradit ničím. Ukončíme funkci a funkce vrátila pouze písmena bez čísel.
A opět bychom zápis mohli provést několika různými způsoby. V příkladu jsem uvedla hranaté závorky a v nich nulu až devítku, což znamená čísla od nuly do 9. Nicméně zápis by fungoval i s dalším regulérním výrazem, „\d“, což znamená, jakékoliv číselné hodnoty.
Stejně tak by fungovalo [0123456789].
To samé můžeme udělat obráceně, v další buňce máme text a čísla a naopak chceme vyselektovat pouze čísla. Použijeme stejný postup, jen nahradíme písmena. Takže regulérní výraz bude [a-z].
A co kdybychom v buňce měli velké písmeno? Museli bychom napsat [aA-zZ].
A co kdybychom chtěli nahradit v buňce tři po sobě jdoucí čísla ničím? Tedy aby tato tři po sobě jdoucí čísla z buňky zmizela? Použijeme opět funkci REGEXNAHRADIT, tentokrát se zápisem “[0-9]{3}.
S funkcí REGEXNAHRADIT lze dělat doslova divy. V příkladu máme tabulku, kde máme jména, pozici a telefon a místo pracoviště, nicméně mezi slovy jsou vyexportované i různé znaky. Text potřebujeme od těchto znaků očistit. Použijeme funkci REGEXNAHRADIT. A jelikož si třeba nejsme jistí, jak napsat regulérní výraz, který nám slova očistí, tak můžeme použít AI. Regulérní výrazy totiž mohou velmi brzy přerůst v komplikované zápisy. A i přesto, že určitě není na škodu, se základy regulérních výrazů naučit, není potřeba trávit hodiny zápisy a pokusy a omyly. Právě k takovýmto výpomocem používám AI.
Tento zápis v podstatě znamená, že funkce REGEXNAHRADIT najde všechny znaky, které jsou uvedené v závorce a nahradí je ničím, znaménko plus za závorkou znamená, že se tyto znaky mohou v buňce vyskytovat opakovaně.
REGEXEXTRAHOVAT
A co kdybychom z těchto buněk chtěli dostat telefon? Použijeme poslední funkci REGEXEXTRAHOVAT neboli REGEXEXTRACT. A opět i pomůžeme AI.
Pořád nejste přesvědčení o úžasnosti funkcí?
V příkladu máme v tabulce vyexportované komentáře, které hodnotí kvalitu produktu, který dodáváme. V každém komentáři je uvedený i datum, a právě toto datum nás zajímá a potřebujeme ho vyselektovat do samostatné buňky. Úkol, který by se v Excelu pomocí klasických funkcí zdál neřešitelný. S funkcí REGEXEXTRAHOVAT to ale není vůbec komplikované. Chceme do samostatné buňky dostat datum, takže použijeme funkci REGEXEXTRAHOVAT, kde jako první označíme text a jak bude vypadat výraz? Pokud nevíme, můžeme použít buď ChatGPT nebo COPILOTa, podle toho, co využíváte.
Stejně dobře by příklad vyřešil i COPILOT.
