Общая формула
=SUBSTITUTE(SUBSTITUTE(B5,INDEX(find,1),INDEX(replace,1)),INDEX(find,2),INDEX(replace,2))
Резюме
Чтобы найти и заменить несколько значений формулой, вы можете вложить несколько функций SUBSTITUTE вместе и передать пары поиска / замены из другой таблицы с помощью функции 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 не обязательно; при желании вы можете жестко закодировать значения в формуле.
Объяснение
По сути, формула использует функцию ЗАМЕНА для выполнения каждой подстановки с помощью этого базового шаблона:
=SUBSTITUTE(text,find,replace)
«Текст» - это входящее значение, «найти» - это текст, который нужно искать, а «заменить» - это текст, которым нужно заменить. Текст, который нужно найти и заменить, хранится в таблице справа в диапазоне E5: F8, по одной паре на строку. Значения слева находятся в именованном диапазоне «найти», а значения справа - в именованном диапазоне «заменить». Функция ИНДЕКС используется для получения как текста «найти», так и текста «заменить» следующим образом:
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 для динамических диапазонов вместо именованных диапазонов.
Другое использование
Тот же подход можно использовать для очистки текста, «удаляя» знаки пунктуации и другие символы из текста с помощью ряда замен. Например, формула на этой странице показывает, как очистить и переформатировать телефонные номера.
