Obecný vzorec
=SUBSTITUTE(SUBSTITUTE(B5,INDEX(find,1),INDEX(replace,1)),INDEX(find,2),INDEX(replace,2))
souhrn
Chcete-li najít a nahradit více hodnot vzorcem, můžete vnořit více funkcí SUBSTITUTE dohromady a pomocí funkce INDEX vkládat páry hledání / nahrazování z jiné tabulky. V ukázaném příkladu provádíme 4 samostatné operace hledání a nahrazování. 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 pojmenovaný rozsah E5: E8 a „replace“ je pojmenovaný rozsah F5: F8. Níže naleznete informace o tom, jak usnadnit čtení tohoto vzorce.
Předmluva
Neexistuje žádný předdefinovaný vzorec pro spuštění řady operací hledání a nahrazování v aplikaci Excel, jedná se tedy o „koncepční“ vzorec, který ukazuje jeden přístup. Text, který se má vyhledat a nahradit, je uložen přímo na listu v tabulce a načten pomocí funkce INDEX. Díky tomu je řešení „dynamické“ - každá z těchto hodnot se změní, výsledky se okamžitě aktualizují. Samozřejmě neexistuje žádný požadavek používat INDEX; pokud chcete, můžete do vzorce napevno naprogramovat hodnoty.
Vysvětlení
V jádru používá vzorec funkci SUBSTITUTE k provedení každé substituce s tímto základním vzorem:
=SUBSTITUTE(text,find,replace)
„Text“ je příchozí hodnota, „find“ je text, který má být vyhledán, a „replace“ je text, který má být nahrazen. Text, který je třeba vyhledat a nahradit, je uložen v tabulce vpravo, v rozsahu E5: F8, jeden pár na řádek. Hodnoty vlevo jsou v pojmenovaném rozsahu „najít“ a hodnoty vpravo jsou v pojmenovaném rozsahu „nahradit“. Funkce INDEX se používá k načtení jak textu „find“, tak textu „replace“ takto:
INDEX(find,1) // first "find" value INDEX(replace,1) // first "replace" value
Pro spuštění první substituce (hledejte „červenou“, nahraďte „růžovou“) používáme:
=SUBSTITUTE(B5,INDEX(find,1),INDEX(replace,1))
Celkově spustíme čtyři oddělené substituce a každá následující SUBSTITUTE začíná výsledkem z předchozí SUBSTITUTE:
=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 řádků kvůli čitelnosti
Všimnete si, že tento druh vnořeného vzorce je celkem obtížně čitelný. Přidáním konců řádků můžeme vzorec mnohem snáze číst a udržovat:
= 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))
Řádek vzorců v aplikaci Excel ignoruje nadbytečné mezery a konce řádků, takže výše uvedený vzorec lze vložit přímo:
Mimochodem, existuje klávesová zkratka pro rozbalení a sbalení řádku vzorců.
Více substitucí
Do tabulky lze přidat více řádků, aby bylo možné zpracovat více párů hledání / nahrazení. Při každém přidání páru je třeba vzorec aktualizovat, aby zahrnoval nový pár. Je také důležité zajistit, aby pojmenované rozsahy (pokud je používáte) byly aktualizovány tak, aby obsahovaly nové hodnoty podle potřeby. Alternativně můžete namísto pojmenovaných rozsahů použít pro dynamické rozsahy správnou tabulku Excel.
Jiná použití
Stejný přístup lze použít k vyčištění textu „odstraněním“ interpunkce a dalších symbolů z textu řadou substitucí. Například vzorec na této stránce ukazuje, jak vyčistit a přeformátovat telefonní čísla.
