Všeobecný vzorec
=SUBSTITUTE(SUBSTITUTE(B5,INDEX(find,1),INDEX(replace,1)),INDEX(find,2),INDEX(replace,2))
Zhrnutie
Ak chcete nájsť a nahradiť viaceré hodnoty vzorcom, môžete vnoriť viac funkcií SUBSTITUTE dohromady a pomocou funkcie INDEX vložiť páry hľadania / nahradenia z inej tabuľky. V zobrazenom príklade vykonávame 4 samostatné operácie hľadania a nahrádzania. Vzorec v G5 je:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B5,INDEX(find,1),INDEX(replace,1)),INDEX(find,2),INDEX(replace,2)),INDEX(find,3),INDEX(replace,3)),INDEX(find,4),INDEX(replace,4))
kde „find“ je pomenovaný rozsah E5: E8 a „nahradiť“ je pomenovaný rozsah F5: F8. Informácie o tom, ako uľahčiť čítanie tohto vzorca, nájdete nižšie.
Predslov
Neexistuje žiadny vstavaný vzorec na spustenie série operácií hľadania a nahradenia v programe Excel, takže ide o „koncepčný“ vzorec, ktorý ukazuje jeden prístup. Text, ktorý sa má vyhľadať a nahradiť, je uložený priamo na pracovnom hárku v tabuľke a načítaný pomocou funkcie INDEX. Vďaka tomu je riešenie „dynamické“ - ktorákoľvek z týchto hodnôt sa zmení, výsledky sa okamžite aktualizujú. Samozrejme nie je potrebné používať INDEX; môžete hodnoty naprogramovať do vzorca, ak chcete.
Vysvetlenie
Vzorec v jadre používa na vykonanie každej substitúcie funkciu SUBSTITUTE s týmto základným vzorom:
=SUBSTITUTE(text,find,replace)
„Text“ je prichádzajúca hodnota, „nájsť“ je text, ktorý sa má vyhľadať, a „nahradiť“, je text, ktorý sa má nahradiť. Text, ktorý sa má vyhľadať a nahradiť, je uložený v tabuľke vpravo v rozsahu E5: F8, jeden pár na riadok. Hodnoty vľavo sú v pomenovanom rozsahu „nájsť“ a hodnoty vpravo sú v pomenovanom rozsahu „vymeniť“. Funkcia INDEX sa používa na načítanie textu „nájsť“ aj „nahradiť“ takto:
INDEX(find,1) // first "find" value INDEX(replace,1) // first "replace" value
Na spustenie prvej substitúcie (hľadáme „červenú“, nahraďte „ružovou“) používame:
=SUBSTITUTE(B5,INDEX(find,1),INDEX(replace,1))
Celkovo spustíme štyri samostatné substitúcie a každá nasledujúca NÁHRADA začína výsledkom z predchádzajúcej NÁHRADY:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B5,INDEX(find,1),INDEX(replace,1)),INDEX(find,2),INDEX(replace,2)),INDEX(find,3),INDEX(replace,3)),INDEX(find,4),INDEX(replace,4))
Konce riadkov kvôli čitateľnosti
Všimnete si, že tento druh vnoreného vzorca je dosť ťažko čitateľný. Pridaním zalomení riadkov môžeme uľahčiť čítanie a údržbu vzorca:
= SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( B5, INDEX(find,1),INDEX(replace,1)), INDEX(find,2),INDEX(replace,2)), INDEX(find,3),INDEX(replace,3)), INDEX(find,4),INDEX(replace,4))
Panel vzorcov v programe Excel ignoruje ďalšie medzery a zlomy riadkov, takže vyššie uvedený vzorec je možné vložiť priamo:
Mimochodom, existuje klávesová skratka na zväčšenie a zbalenie riadku vzorcov.
Viac striedaní
Do tabuľky je možné pridať viac riadkov, aby bolo možné spracovať viac párov hľadania a nahradenia. Zakaždým, keď je pridaný pár, je potrebné vzorec aktualizovať tak, aby obsahoval nový pár. Je tiež dôležité skontrolovať, či sú pomenované rozsahy (ak ich používate) aktualizované tak, aby obsahovali nové hodnoty podľa potreby. Alternatívne môžete namiesto pomenovaných rozsahov použiť pre dynamické rozsahy správnu tabuľku programu Excel.
Iné použitie
Rovnakým spôsobom je možné vyčistiť text „odstránením“ interpunkčných znakov a iných symbolov z textu sériou substitúcií. Napríklad vzorec na tejto stránke ukazuje, ako vyčistiť a preformátovať telefónne čísla.
