Formuła ogólna
=SUBSTITUTE(SUBSTITUTE(B5,INDEX(find,1),INDEX(replace,1)),INDEX(find,2),INDEX(replace,2))
Podsumowanie
Aby znaleźć i zastąpić wiele wartości formułą, możesz zagnieździć wiele funkcji SUBSTITUTE razem i wprowadzić pary znajdź / zamień z innej tabeli za pomocą funkcji INDEKS. W przedstawionym przykładzie wykonujemy 4 oddzielne operacje znajdź i zamień. Wzór w G5 to:
=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))
gdzie „znajdź” to nazwany zakres E5: E8, a „zastąp” to nazwany zakres F5: F8. Zobacz poniżej, aby dowiedzieć się, jak ułatwić czytanie tej formuły.
Przedmowa
Nie ma wbudowanej formuły do uruchamiania serii operacji znajdowania i zamieniania w programie Excel, więc jest to formuła „koncepcyjna” pokazująca jedno podejście. Tekst do wyszukania i zastąpienia jest przechowywany bezpośrednio w arkuszu w tabeli i pobierany za pomocą funkcji INDEKS. To sprawia, że rozwiązanie jest „dynamiczne” - każda z tych wartości jest zmieniana, wyniki są aktualizowane natychmiast. Oczywiście nie ma wymogu używania INDEKSU; jeśli wolisz, możesz na stałe zakodować wartości w formule.
Wyjaśnienie
W istocie formuła wykorzystuje funkcję SUBSTITUTE do wykonania każdego podstawienia, z następującym podstawowym wzorcem:
=SUBSTITUTE(text,find,replace)
„Tekst” to przychodząca wartość, „znajdź” to tekst do wyszukania, a „zamień” to tekst do zastąpienia. Tekst do wyszukania i zastąpienia jest przechowywany w tabeli po prawej stronie, w zakresie E5: F8, po jednej parze na wiersz. Wartości po lewej stronie należą do nazwanego zakresu „znajdź”, a wartości po prawej stronie do nazwanego zakresu „zamień”. Funkcja INDEKS służy do pobierania zarówno tekstu „znajdź”, jak i tekstu „zamień” w następujący sposób:
INDEX(find,1) // first "find" value INDEX(replace,1) // first "replace" value
Tak więc, aby uruchomić pierwsze podstawienie (poszukaj „czerwonego”, zamień na „różowy”) używamy:
=SUBSTITUTE(B5,INDEX(find,1),INDEX(replace,1))
W sumie wykonujemy cztery oddzielne podstawienia, a każde kolejne PODSTAWA zaczyna się od wyniku z poprzedniej PODSTAWY:
=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))
Podziały wierszy dla czytelności
Zauważysz, że tego rodzaju zagnieżdżona formuła jest dość trudna do odczytania. Dodając podziały wierszy, możemy uczynić formułę znacznie łatwiejszą do odczytania i utrzymania:
= 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))
Pasek formuły w programie Excel ignoruje dodatkowe spacje i podziały wierszy, więc powyższą formułę można wkleić bezpośrednio:
Nawiasem mówiąc, istnieje skrót klawiaturowy do rozwijania i zwijania paska formuły.
Więcej zmian
Do tabeli można dodać więcej wierszy, aby obsłużyć więcej par znajdź / zamień. Za każdym razem, gdy dodawana jest para, formuła musi zostać zaktualizowana, aby uwzględnić nową parę. Ważne jest również, aby upewnić się, że nazwane zakresy (jeśli ich używasz) zostały zaktualizowane, aby w razie potrzeby zawierały nowe wartości. Alternatywnie możesz użyć odpowiedniej tabeli programu Excel dla zakresów dynamicznych zamiast nazwanych zakresów.
Inne zastosowania
To samo podejście można zastosować do czyszczenia tekstu przez „usuwanie” znaków interpunkcyjnych i innych symboli z tekstu za pomocą serii podstawień. Na przykład formuła na tej stronie pokazuje, jak wyczyścić i sformatować numery telefonów.
