V dnešním videu si ukážeme způsob, jak z českých slov v Excelu odstranit diakritiku nebo jakékoliv speciální znaky. Tato technika se vám ve skutečnosti může hodit v několika praktických příkladech, které si v tomto videu ukážeme.
Excelový soubor ke stažení
Jak odstranit háčky, čárky a nechtěné znaky
V příkladu máme několik jmen zaměstnanců. Tabulka obsahuje jména a příjmení včetně diakritiky, tedy českých háčků a čárek. Z těchto jmen zaprvé potřebujeme vytvořit emailové adresy. Emailová adresa bude vždy ve formátu jméno, příjmení, zavináč a název společnosti. Abychom ze jmen mohli udělat emailové adresy, tak potřebujeme ze jmen nejprve odstranit všechny háčky a čárky. Kromě emailových adres máme ještě druhou tabulku, ze které musíme k zaměstnancům dohledat mzdy a ve druhé tabulce jsou bohužel jména zaměstnanců bez háčků a čárek. Tyto tabulky samozřejmě v tomto stavu nepůjdou spojit bez toho abychom neodstranili diakritiku nebo ji do druhé tabulky nepřidali.
Abychom mohli z buněk diakritiku odstranit, tak budeme potřebovat pomocnou tabulku. V pomocné tabulce budou dva sloupce. V prvním sloupci máme písmena včetně háčků a čárek, to jsou písmena, která chceme nahradit. Ve druhém sloupci máme uvedená písmena bez háčků a čárek, tedy písmena, kterými chceme písmena z prvního sloupce nahradit. Funkce DOSADIT rozlišuje velká a malá písmena, takže pokud chceme nahrazovat háčky a čárky i u velkých písmen, tak musíme zahrnout i tato písmena do tabulky.
Háčky a čárky nahradíme pomocí několikanásobné funkce DOSADIT, anglicky funkce SUBSTITUTE. Začneme první funkcí DOSADIT. Funkce DOSADIT dovoluje nahrazovat hodnoty za jiné hodnoty. Prvním parametrem funkce DOSADIT je text. Tedy text, ve kterém chceme nějaký znak nahrazovat. Takže označíme první jméno v tabulce. Následuje parametr starý, což je starý znak, který chceme nahrazovat. To je první písmeno v pomocné tabulce. A posledním povinným parametrem je nový, což je znak, kterým chceme starý znak nahradit. To bude první písmeno ve druhém sloupci. To je celá funkce DOSADIT. Funkce dosadit teď v první buňce nahradí dlouhé á písmenem a. Funkce potvrdíme a jak vidíme, tak se ve jméně a příjmení nahradilo první písmeno. Jelikož budeme chtít funkci stahovat pro všechny řádky dolů, tak zafixujeme parametr starý a nový.
Teď musíme stejným způsobem nahradit všechna písmena, tím, že budeme řetězit funkci DOSADIT. Takže první funkci DOSADIT zabalíme do další funkce DOSADIT, kde parametrem text je první funkce DOSADIT, starý text je druhé písmeno s háčkem a parametr nový je druhé písmeno z druhého sloupce, tedy písmeno bez háčku. A opět poslední dva parametry plně zafixujeme klávesou F4. Funkci potvrdíme a teď se nahradilo i další písmeno. Stejným způsobem nahradíme všechna písmena, která máme v tabulce.
Zkusíme to ještě na dalším písmeně. Funkce DOSADIT zabalíme do další funkce ZABALIT, kde textem jsou původní funkce. Parametr starý je další písmeno, které chceme nahradit a parametr nový je písmeno, kterým ho nahrazujeme. Zafixujeme buňky a potvrdíme funkci. Takhle budeme pokračovat.
Když máme všechny funkce DOSADIT hotové, tak funkci potvrdíme a máme nahrazená všechna písmena. Teď můžeme funkci stáhnout dolů pro všechny řádky. A máme nahrazená všechna písmena.
Teď když máme funkci hotovou, tak můžeme vytvořit emailové adresy. K vytvoření emailových adres použijeme nástroje dynamického doplňování. Vytvoříme první emailovou adresu, takže napíšeme jméno, příjmení, zavináč a email třeba společnost abc.cz. Potvrdíme klávesou ENTER a začneme tvořit druhou adresu, u druhé nebo třetí adresy by mělo naskočit dynamické doplnění a Excel by nám měl nabídnou doplnění emailů.
Pokud se tak nestane, tak máte vždy možnost označit první dvě emailové vytvořené adresy, označit zbytek prázdných buněk a na kartě Data najít Dynamické doplňování a po potvrzení Excel doplní zbylé adresy. Následně pomocí dynamického doplňování vytvoříme emaily.
Teď ještě dohledáme mzdu z druhé tabulky, kde máme jména a příjmení bez diakritiky. Použijeme třeba kombinaci vyhledávacích funkcí INDEX & POZVYHEDAT. Napíšeme funkci INDEX, kde označíme co hledáme, což je sloupec se mzdami. Následuje funkce POZVYHLEDAT, anglicky funkce MATCH, kde označíme, co hledáme, což je jméno zaměstnance, kde zaměstnance hledáme a hledáme přesnou shodu. Ukončíme závorky a funkce potvrdíme a stáhneme je dolů.
Tuto samou techniku pro odstranění háčků a čárek můžete použít i když chcete nahradit jakékoliv znaky z textu nebo čísel. V další tabulce máme čísla, která se ale špatně exportovala a máme mezi nimi i nechtěné znaky. Odstraníme je stejnou technikou. V pomocné tabulce vypíšeme všechny znaky, které chceme nahradit. Akorát, že teď nahradíme nechtěné znaky ničím, takže nám bude stačit pouze jeden sloupec v tabulce. Začneme tvořit funkci DOSADIT. Opět napíšeme funkci DOSADIT, kde nejprve označíme text, což je buňka s nechtěnými znaky. Následuje starý znak, což je první nahrazovaný znak, plně zafixovaný klávesou F4 a jako poslední vyplníme nový znak, a jelikož ho chceme nahradit ničím, tak vyplníme znak dvou uvozovek. A takto budeme pokračovat. Funkci DOSADIT zabalíme do druhé funkce DOSADIT, kde textem je první funkce DOSADIT a starým znakem je druhý nechtěný znak, plně zafixovaný a nový znak je znak pro nic. A takto bychom pokračovali, až dokud bychom neoznačili a nenahradili všechny znaky. Když máme funkci hotovou, tak ji potvrdíme a stáhneme dolů. Nechtěné znaky máme nahrazené, ale všimli jste si něčeho důležitého?
Výsledné hodnoty nejsou číslem, což poznáme podle zarovnání čísel vlevo v buňce. S těmito čísly by nešlo počítat, jelikož je Excel vnímá jako text. Abychom je převedli na čísla, tak musíme funkci vynásobit jedničkou. Po vynásobení se textové hodnoty převedou na čísla.
Hotovo. Stejnou technikou můžete odstranit jakékoliv nechtěné znaky, diakritiku a písmena z jakéhokoliv textu.
Jedna odpověď
😊