Při práci s daty často vzniká potřeba data před jejich použitím určitým způsobem upravit, protože nám jejich struktura nevyhovuje. Pokud nám někdo pošle nějaký excel nebo si data exportujeme z podnikového systému, tak doufáme, že je nebudeme muset upravovat. Realita je jiná a často musíme použít celou řadu velkého seznamu excel funkcí – například pro extrakci nějakého řetězce typu ID nebo prostě najít a nahradit nějaký znak.
Typy situací kdy potřebujeme najít a nahradit obsah buněk v Excelu
V tomto článku budeme pracovat s příkladem když jsme si vyexportovali tržby za produkty (jablka, hrušky a maliny) viz níže na obrázku. Problém je, že náš podnikový systém nám data vyexportoval s nějakými prefixy.
Na obrázku uvidíte 3 situace se kterými se můžete potkat a každá má trošku jiné řešení:
- Příklad 1 (Sloupec B) – Prefix je pro všechny řádky stejný => nejjednodušší situace na vyřešení
- Příklad 2 (Sloupec C) – Prefix je pro každý řádek jiný (má jiné číslo a poslední znak), ale má stejnou délku (budeme chtít odstranit 6 znaků)
- Příklad 3 (Sloupec F) – Bystré oko si všimne, že prefixy jsou pro každý řádek jiné a mají jinou délku. Nicméně slovo, které hledáme se vždy nachází za znakem “_” a toho využijeme
Cílem příkladu je vymyslet způsob, jak u všech sloupců odstranit nepotřebné znaky před jablky, hruškami a malinami.
Příklad 1 – Odstranění textu z buněk pomocí najít a nahradit
Řešení příkladu 1 – pokud mají znaky, které chceme z buněk odstranit stejný tvar, tak nejjednodušší je stisknutím CTRL+H vyvolat okno pomocí kterého můžeme požadovaný text najít a nahradit.
- Nejprve musíme označit oblast, ve které chceme akci provést (Sloupec B)
- Poté stiskneme CTRL+H
- Hledáme text 0001_ a chceme ho nahradit prázdnou hodnotou
Po potvrzení prefix zmizí.
Příklad 2 – Odstranění textu, který je různý, ale má stejnou délku
Řešení příkladu 2 – Pokud víme, že náš text má stále stejnou délku (6 znaků), ta můžeme použít funkci =ČÁST (anglicky =MID). Pomocí této funkce umíme exceu říct, že chceme z textu vzít pouze 7. znak a všechny následující znaky a prvních 6 znaků tak vynecháme.
Vzorec pro první řádek tedy bude vypadat =MID(C4;6;1000) a výsledkem je hodnota Jablka. Potažením za ouško pořešíme všechny řádky.
Příklad 3 – Odstranění textu do určitého znaku (v našem případě “_”)
Řešení příkladu 3 – Toto je relativně nejsložitější situace, ale samozřejmě má poměrně jednoduché řešení pomocí najít a nahradit:
- Nejprve je potřeba vyhledat znak “_” pomocí funkce =NAJÍT (v angličtině =FIND)
- a pokud známe pozici znaku “_”, tak pomočí funkce =ČÁST (v angličtině =MID) vezmeme pouzy ty znaky, které následují po zjištěném počtu znaků (podobně jako u příkladu 2)
Vzorec pro první řádek vypadá takto =MID(F4;FIND(“_”;F4)+1;1000) a výsledkem je hodnota Jablka, POužili jsme opět funkci =ČÁST (MID) jako v předchozím případě, ale jako 2 argument jsme zadali funkci =NAJÍT (FIND), která vyhledala pozici znaku “_”.
Takto jsme to museli udělat, protože pozice znaku “_” byla pro každou hodnotu odlišná a nemohli jsme ji tedy zadat natvrdo jako v příkladu 2 kdy jsme zadávali pro všechny řádky hodnotu 6.
V praxi se můžete dostat samozřejmě i do jiných situací (někdy i neřešitelných nebo řešitelných obtížně). Obecně platí, že je vždycky důležité se na data podívat a zjistit v nich nějaký patern podobně jako jsme to udělali v příkladech viz výše.
Excel sešit stahujte -> najit a nahradit v excelu.xlsx
Dobrý den, dá se funkce “Najdi a nahraď” použít v komentářích v Excelu? Funguje mi v komentářích pouze funkce Najdi.
Pouze pres VBA https://www.extendoffice.com/documents/excel/680-excel-find-and-replace-in-comments.html
Pěkný článek, možná by čtenáře zajímala varianta, kdy v jedné tabulce mám nějaký text s nějakou formou odkazů/dat a potřebuji je změnit za jiná a v druhé tabulce mám např. “překladovou tabulku”
staré URL; nové URL
staré URL2; nové URL2
staré URL3; nové URL3
Jak jednuduše v textu vyhledat a zaměnit data dle tohoto klíče?