Excel

Nájdite a nahraďte viacero hodnôt

Find Replace Multiple Values

Vzorec programu Excel: Nájdite a nahraďte viac hodnôtGenerický vzorec | _+_ | Zhrnutie

Ak chcete nájsť a nahradiť viacero hodnôt vzorcom, môžete do seba vnoriť viacero funkcií SUBSTITUTE a pomocou funkcie INDEX vkladať páry find/replace z inej tabuľky. V uvedenom príklade vykonávame 4 samostatné operácie hľadania a nahradenia. Vzorec v G5 je:



= SUBSTITUTE ( SUBSTITUTE (B5, INDEX (find,1), INDEX (replace,1)), INDEX (find,2), INDEX (replace,2))

kde „nájsť“ je pomenovaný rozsah E5: E8 a „nahradiť“ je pomenovaný rozsah F5: F8. Nižšie nájdete informácie o tom, ako uľahčiť čítanie tohto vzorca.

Predslov

V programe Excel nie je vstavaný vzorec na spustenie série operácií hľadania a nahradenia, takže je to „koncepčný“ vzorec, ktorý ukazuje jeden prístup. Text, ktorý chcete 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é“ - všetky tieto hodnoty sa zmenia a výsledky sa okamžite aktualizujú. Samozrejme, nie je žiadna požiadavka používať INDEX, ak chcete, môžete hodnoty pevne zakódovať do vzorca.





Vysvetlenie

V jadre vzorec používa funkciu SUBSTITUT na vykonanie každej substitúcie podľa tohto základného vzoru:

 
= 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))

„Text“ je prichádzajúca hodnota, „nájsť“ je hľadaný text a „nahradiť“ je text, ktorý chcete nahradiť. Text, ktorý chcete 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 pomenovaný rozsah „find“ a hodnoty vpravo sú v pomenovanom rozsahu „nahradiť“. Funkcia INDEX sa používa na získanie textu „nájsť“ aj „nahradenia“ takto:



ako preskočiť na nasledujúci riadok v programe Excel
 
= SUBSTITUTE (text,find,replace)

Na spustenie prvej substitúcie (hľadajte „červenú“, nahraďte „ružovou“) však používame:

 
 INDEX (find,1) // first 'find' value  INDEX (replace,1) // first 'replace' value

Celkovo vykonáme štyri samostatné substitúcie a každé nasledujúce SUBSTITUTE začína výsledkom z predchádzajúceho SUBSTITUTU:

ako vložiť to isté do viacerých buniek v programe Excel
 
= SUBSTITUTE (B5, INDEX (find,1), INDEX (replace,1))

Zlomky riadkov kvôli čitateľnosti

Všimnite si, že tento druh vnoreného vzorca je dosť ťažko čitateľný. Pridaním zalomení riadkov môžeme výrazne 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))

Riadok vzorcov v programe Excel ignoruje nadbytočné medzery a riadky, takže vyššie uvedený vzorec je možné priamo prilepiť:

Za účelom čitateľnosti a údržby boli do riadka vzorcov pridané riadky

Mimochodom, existuje a klávesová skratka na rozbalenie a zbalenie panela vzorcov.

Viac striedaní

Do tabuľky je možné pridať viac riadkov, aby bolo možné spracovať viac párov hľadania/nahradenia. Zakaždým, keď je pridaný pár, je potrebné vzorec aktualizovať, aby zahŕňal nový pár. Je tiež dôležité zabezpečiť, aby boli pomenované rozsahy (ak ich používate) aktualizované tak, aby podľa potreby obsahovali nové hodnoty. Alternatívne môžete použiť a správna tabuľka programu Excel pre dynamické rozsahy namiesto pomenovaných rozsahov.

Iné použitie

Rovnaký prístup je možné použiť aj na čistenie textu „odstraňovaním“ interpunkcie a iných symbolov z textu pomocou série substitúcií. Vzorec na tejto stránke napríklad ukazuje, ako na to vyčistiť a preformátovať telefónne čísla .

Autor Dave Bruns


^