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