Обща формула
=SUBSTITUTE(SUBSTITUTE(B5,INDEX(find,1),INDEX(replace,1)),INDEX(find,2),INDEX(replace,2))
Обобщение
За да намерите и замените множество стойности с формула, можете да вложите множество функции ЗАМЕСТИТЕЛ заедно и да подадете в търсене / замяна на двойки от друга таблица, използвайки функцията INDEX. В показания пример изпълняваме 4 отделни операции за намиране и замяна. Формулата в G5 е:
=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))
където "find" е наименуваният диапазон E5: E8, а "replace" е наименуваният диапазон F5: F8. Вижте по-долу за информация как да направите тази формула по-лесна за четене.
Предговор
Няма вградена формула за провеждане на поредица от операции за намиране и замяна в Excel, така че това е формула "концепция", която да показва един подход. Текстът, който трябва да се търси и замени, се съхранява директно на работния лист в таблица и се извлича с функцията INDEX. Това прави решението „динамично“ - всяка от тези стойности се променя, резултатите се актуализират незабавно. Разбира се, няма изискване да се използва INDEX; можете да въведете стойности във формулата, ако предпочитате.
Обяснение
В основата си формулата използва функцията ЗАМЕСТИТЕЛ за извършване на всяко заместване, с този основен модел:
=SUBSTITUTE(text,find,replace)
"Text" е входящата стойност, "find" е текстът, който трябва да се търси, а "replace" е текстът, с който да се замени. Текстът, който трябва да се търси и замени, се съхранява в таблицата вдясно, в диапазона E5: F8, по една двойка на ред. Стойностите отляво са в именувания диапазон „find“, а стойностите отдясно са в посочения диапазон „replace“. Функцията INDEX се използва за извличане както на текста „намиране“, така и на текста „заместване“ по следния начин:
INDEX(find,1) // first "find" value INDEX(replace,1) // first "replace" value
И така, за да стартираме първата подмяна (потърсете "червено", заменете с "розово") използваме:
=SUBSTITUTE(B5,INDEX(find,1),INDEX(replace,1))
Общо изпълняваме четири отделни замествания и всеки следващ ЗАМЕСТИТЕЛ започва с резултата от предишния ЗАМЕНИТЕЛ:
=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))
Прекъсвания на редове за четливост
Ще забележите, че този вид вложена формула е доста трудна за четене. Чрез добавяне на прекъсвания на редове можем да направим формулата много по-лесна за четене и поддържане:
= 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))
Лентата с формули в Excel игнорира допълнително празно пространство и прекъсвания на редове, така че горната формула може да се постави директно:
Между другото, има клавишна комбинация за разширяване и свиване на лентата с формули.
Повече замени
Към таблицата могат да се добавят още редове, за да се справят с повече двойки за намиране / замяна. Всеки път, когато се добавя чифт, формулата трябва да се актуализира, за да включи новата двойка. Важно е също така да се уверите, че посочените диапазони (ако ги използвате) се актуализират, за да включват нови стойности, ако е необходимо. Като алтернатива можете да използвате подходяща таблица на Excel за динамични диапазони, вместо именувани диапазони.
Други приложения
Същият подход може да се използва за изчистване на текст чрез "премахване" на пунктуация и други символи от текста с поредица от замествания. Например формулата на тази страница показва как да почистите и преформатирате телефонни номера.
