Загальна формула
=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))
де "знайти" - іменований діапазон E5: E8, а "замінити" - іменований діапазон F5: F8. Інформацію про те, як полегшити читання цієї формули, див. Нижче.
Передмова
Немає вбудованої формули для запуску серії операцій пошуку та заміни в Excel, тому ця формула "концепції" відображає один підхід. Текст, який потрібно шукати та замінити, зберігається безпосередньо на аркуші в таблиці та отримується за допомогою функції INDEX. Це робить рішення "динамічним" - будь-яке з цих значень змінюється, результати негайно оновлюються. Звичайно, немає вимоги використовувати INDEX; ви можете жорстко закодувати значення у формулу, якщо хочете.
Пояснення
По суті, формула використовує функцію ЗАМІННИК для виконання кожної заміни з наступним основним шаблоном:
=SUBSTITUTE(text,find,replace)
"Текст" - це вхідне значення, "знайти" - це текст, який потрібно шукати, а "замінити" - текст, на який потрібно замінити. Текст, який потрібно шукати та замінити, зберігається у таблиці праворуч, в діапазоні E5: F8, по одній парі на рядок. Значення ліворуч знаходяться в іменованому діапазоні "знайти", а значення праворуч - у іменованому діапазоні "замінити". Функція 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 для динамічних діапазонів, замість іменованих діапазонів.
Інше використання
Той самий підхід можна використовувати для очищення тексту, "викреслюючи" розділові знаки та інші символи з тексту із низкою замін. Наприклад, формула на цій сторінці показує, як очистити та переформатувати телефонні номери.
