Как найти и заменить несколько значений в Excel

Для этого введите старые значения в D2:D4 и новые значения в E2:E4, как показано на снимке экрана ниже. Затем поместите приведенную ниже формулу в ячейку B2 и нажмите Enter:

=ЗАМЕНИТЬ(ЗАМЕНИТЬ(ЗАМЕНИТЬ(A2:A10, D2, E2), D3, E3), D4, E4)

…и все замены будут выполнены сразу:

Обратите внимание, что описанный выше подход работает только в Excel 365, который поддерживает динамические массивы.

В преддинамических версиях Excel 2019, Excel 2016 и более ранних версиях формулу необходимо написать для самой верхней ячейки (B2), а затем скопировать в нижние ячейки:

=ЗАМЕНИТЬ(ЗАМЕНИТЬ(ЗАМЕНИТЬ(A2, $D$2, $E$2), $D$3, $E$3), $D$4, $E$4)

Обратите внимание, что в этом случае мы блокируем значения замены абсолютными ссылками на ячейки, чтобы они не сместились при копировании формулы вниз.

Примечание. Функция ПОДСТАВИТЬ чувствительна к регистру, то есть вы должны вводить старые значения (old_text) в том же регистре, что и в исходных данных.

Каким бы простым ни был этот метод, у него есть существенный недостаток: когда вам нужно заменить десятки элементов, управлять вложенными функциями становится довольно сложно.

Преимущества: простота реализации; поддерживается во всех версиях Excel

Недостатки: лучше всего использовать для ограниченного числа значений поиска/замены

Поиск и замена нескольких записей с помощью XLOOKUP

В ситуации, когда вы хотите заменить все содержимое ячейки, а не ее часть, пригодится функция XLOOKUP.

Допустим, у вас есть список стран в столбце A, и вы хотите заменить все сокращения соответствующими полными названиями. Как и в предыдущем примере, вы начинаете с ввода элементов «Найти» и «Заменить» в отдельных столбцах (D и E соответственно), а затем вводите эту формулу в B2:

=XLOOKUP(A2, $D$2:$D$4, $E$2:$E$4, A2)

В переводе с языка Excel на человеческий язык формула делает следующее:

Поиск значения A2 (искомое_значение) в D2:D4 (искомый_массив) и возврат совпадения из E2:E4 (возвратный_массив). Если не найдено, извлеките исходное значение из A2.

Дважды щелкните маркер заполнения, чтобы скопировать формулу в ячейки ниже, и результат не заставит себя ждать:

Поскольку функция XLOOKUP доступна только в Excel 365, приведенная выше формула не будет работать в более ранних версиях. Однако вы можете легко имитировать это поведение с помощью комбинации IFERROR или IFNA и VLOOKUP:

=IFNA(ВПР(A2, $D$2:$E$4, 2, ЛОЖЬ), A2)

Примечание. В отличие от ПОДСТАВКИ, функции ССПР и ВПР не чувствительны к регистру, то есть они ищут значения поиска, игнорируя регистр букв. Например, в нашей формуле и Франция, и fr будут заменены на Франция.

Преимущества: необычное использование привычных функций; работает во всех версиях Excel

Недостатки: работает на уровне ячейки, не может заменить часть содержимого ячейки

Многократная замена с использованием рекурсивной функции LAMBDA

Для подписчиков Microsoft 365 Excel предоставляет специальную функцию, которая позволяет создавать пользовательские функции с использованием традиционного языка формул. Да, я говорю о LAMBDA. Прелесть этого метода в том, что он может преобразовать очень длинную и сложную формулу в очень компактную и простую. Кроме того, он позволяет создавать собственные функции, которых нет в Excel, что раньше было возможно только в VBA.

Подробную информацию о создании и использовании пользовательских функций LAMBDA см. в этом руководстве: Как писать функции LAMBDA в Excel. Здесь мы обсудим пару практических примеров.

Преимущества: в результате получается элегантная и удивительно простая в использовании функция, независимо от количества пар замены

Недостатки: доступно только в Excel 365; зависит от книги и не может использоваться повторно в разных книгах

Пример 1. Поиск и замена нескольких слов/строк одновременно

Чтобы заменить несколько слов или текста за один раз, мы создали пользовательскую функцию LAMBDA с именем MultiReplace, которая может принимать одну из следующих форм:

=LAMBDA(текст, старый, новый, ЕСЛИ(старый<>"", Множественная замена(ПОДСТАВИТЬ(текст, старый, новый), СМЕЩ(старый, 1, 0), СМЕЩ(новый, 1, 0)), текст))

=LAMBDA(текст, старый, новый, ЕСЛИ(старый="", текст, Множественная замена(ЗАМЕНА(текст, старый, новый), СМЕЩ(старый, 1, 0), СМЕЩ(новый, 1, 0)) ))

Оба являются рекурсивными функциями, которые вызывают сами себя. Разница только в том, как устанавливается точка выхода.

В первой формуле функция ЕСЛИ проверяет, не является ли список старый пустым (старый<>""). Если TRUE, вызывается функция MultiReplace. Если FALSE, функция возвращает текст в его текущей форме и завершает работу.

Во второй формуле используется обратная логика: если old пусто (old=""), возвращается текст и выход; в противном случае вызовите MultiReplace.

Самая сложная часть выполнена! Вам осталось назвать функцию MultiReplace в диспетчере имен, как показано на снимке экрана ниже. Подробные рекомендации см. в разделе Как назвать функцию LAMBDA.

После того, как функция получит имя, вы сможете использовать ее так же, как и любую другую встроенную функцию.

Какой бы из двух вариантов формулы вы ни выбрали, с точки зрения конечного пользователя синтаксис будет таким же простым:

• Текст — исходные данные
• Старый — значения для поиска
• Новый — значения для замены

Расширяя предыдущий пример, давайте заменим не только аббревиатуры стран, но и аббревиатуры штатов. Для этого введите сокращения (старые значения) в столбец D, начиная с D2, и полные имена (новые значения) в столбец E, начиная с E2.

В B2 введите функцию MultiReplace:

=Несколько замен(A2:A10, D2, E2)

Нажмите Enter и наслаждайтесь результатами :)

Как работает эта формула

Ключом к пониманию формулы является понимание рекурсии. Это может показаться сложным, но принцип довольно прост. С каждой итерацией рекурсивная функция решает один маленький экземпляр более крупной проблемы. В нашем случае функция MultiReplace перебирает старые и новые значения и в каждом цикле выполняет одну замену:

MultiReplace (ЗАМЕНА(текст, старое, новое), СМЕЩЕНИЕ(старое, 1, 0), СМЕЩЕНИЕ(новое, 1, 0))

Как и во вложенных функциях SUBSTITUTE, результат предыдущей SUBSTITUTE становится параметром text для следующей SUBSTITUTE. Другими словами, при каждом последующем вызове MultiReplace функция ПОДСТАВИТЬ обрабатывает не исходную текстовую строку, а вывод предыдущего вызова.

Чтобы обработать все элементы в старом списке, мы начинаем с самой верхней ячейки и используем функцию OFFSET для перемещения на 1 строку вниз при каждом взаимодействии:

То же самое делается для нового списка:

Крайне важно обеспечить точку выхода, чтобы предотвратить бесконечное выполнение рекурсивных вызовов. Это делается с помощью функции ЕСЛИ - если старая ячейка пуста, функция возвращает текст в его нынешнем виде и завершает работу:

=LAMBDA(текст, старый, новый, ЕСЛИ(старый="", текст, Множественная замена(…)))

=LAMBDA(текст, старый, новый, ЕСЛИ(старый<>"", MultiReplace(…), текст))

Пример 2. Замена нескольких символов в Excel

В принципе, функция MultiReplace, обсуждавшаяся в предыдущем примере, может обрабатывать и отдельные символы, при условии, что каждый старый и новый символ вводятся в отдельной ячейке, точно так же, как сокращенное и полное имена в скриншоты выше.

Если вы предпочитаете вводить старые символы в одну ячейку, а новые символы — в другую или вводить их непосредственно в формулу, вы можете создать еще одну пользовательскую функцию с именем ReplaceChars, выполнив используя одну из следующих формул:

=LAMBDA(текст, старые_символы, новые_символы, ЕСЛИ(старые_символы<>"", ReplaceChars(ПОДСТАВИТЬ(текст, ЛЕВЫЙ(старые_символы), ЛЕВЫЙ(новые_символы)), ПРАВЫЙ(старые_символы, ДЛСТР(старые_символы)-1), ПРАВЫЙ (new_chars, LEN(new_chars)-1)), текст))

=LAMBDA(текст, старые_символы, новые_символы, ЕСЛИ(старые_символы="", текст, ReplaceChars(ПОДСТАВИТЬ(текст, ЛЕВЫЙ(старые_символы), ЛЕВЫЙ(новые_символы)), ПРАВЫЙ(старые_символы, ДЛСТР(старые_символы)-1), ПРАВО(новые_символы, ДЛСТР(новые_символы)-1))))

Не забудьте назвать новую функцию Lambda в диспетчере имен, как обычно:

И ваша новая пользовательская функция готова к использованию:

• Текст — исходные строки
• Старый — символы для поиска
• Новый — символы для замены

Для проверки в полевых условиях давайте проделаем то, что часто делается с импортированными данными, — заменим умные кавычки и умные апострофы прямыми кавычками и прямыми апострофами.

Сначала мы вводим умные кавычки и умные апострофы в D2, прямые кавычки и прямые апострофы в E2, разделяя символы пробелами для удобства чтения. (Поскольку мы используем один и тот же разделитель в обеих ячейках, это не повлияет на результат — Excel просто заменит пробел пробелом.)

После этого мы вводим эту формулу в B2:

=ReplaceChars(A2:A4, D2, E2)

И получить именно те результаты, которые мы искали:

Также можно вводить символы непосредственно в формулу. В нашем случае просто не забудьте «продублировать» прямые кавычки следующим образом:

Как работает эта формула

Функция ReplaceChars перебирает строки old_chars и new_chars и выполняет по одной замене, начиная с первого символа слева. Эта часть выполняется функцией ПОДСТАВИТЬ:

ЗАМЕНИТЬ(текст, НАЛЕВО(старые_символы), НАЛЕВО(новые_символы))

С каждой итерацией функция RIGHT удаляет один символ слева как из строк old_chars, так и из строк new_chars, чтобы LEFT могла получить следующую пару символов для замена:

ReplaceChars(SUBSTITUTE(текст, LEFT(старые_символы), LEFT(новые_символы)), RIGHT(старые_символы, LEN(old_chars)-1), RIGHT(новые_символы, LEN(new_chars)-1))

Перед каждым рекурсивным вызовом функция ЕСЛИ оценивает строку old_chars. Если он не пуст, функция вызывает сама себя. Как только последний символ будет заменен, итерационный процесс завершается, формула возвращает текст в его нынешнем виде и завершает работу.

Примечание. Поскольку функция ПОДСТАВИТЬ, используемая в наших основных формулах, чувствительна к регистру, обе лямбда-выражения (MultiReplace и ReplaceChars) обрабатывают прописные и строчные буквы как разные символы.

Массовый поиск и замена с помощью UDF

Если функция LAMBDA недоступна в вашем Excel, вы можете написать определяемую пользователем функцию для множественной замены традиционным способом с помощью VBA.

Чтобы отличить пользовательскую функцию от определяемой LAMBDA функции MultiReplace, мы назовем ее по-другому, скажем, MassReplace. Код функции выглядит следующим образом:

Подобно функциям, определенным в LAMBDA, пользовательские функции распространяются на всю книгу. Это означает, что функция MassReplace будет работать только в книге, в которую вы вставили код. Если вы не знаете, как это сделать правильно, выполните действия, описанные в разделе Как вставить код VBA в Excel.

После добавления кода в книгу функция появится в формуле intellisense — только имя функции, а не аргументы! Хотя я считаю, что запомнить синтаксис не так уж и сложно:

• Input_range — исходный диапазон, в котором вы хотите заменить значения.
• Диапазон поиска — символы, строки или слова для поиска.
• Replace_range — символы, строки или слова для замены.

В Excel 365 из-за поддержки динамических массивов это работает как обычная формула, которую нужно ввести только в верхнюю ячейку (B2):

=Массовая замена(A2:A10, D2:D4, E2:E4)

В преддинамическом Excel это работает как формула массива CSE в старом стиле: вы выбираете весь исходный диапазон (B2:B10), вводите формулу и одновременно нажимаете клавиши Ctrl + Shift + Enter, чтобы завершить ее.

Преимущества: достойная альтернатива пользовательской функции LAMBDA в Excel 2019, Excel 2016 и более ранних версиях

Недостаток: книгу необходимо сохранить в виде файла .xlsm с поддержкой макросов

Массовая замена в Excel макросом VBA

Если вам нравится автоматизировать стандартные задачи с помощью макросов, вы можете использовать следующий код VBA для поиска и замены нескольких значений в диапазоне.

Чтобы сразу же использовать макрос, вы можете загрузить наш образец книги, содержащий код. Или вы можете вставить код в свою книгу.

Как использовать макрос

Перед запуском макроса введите старое и новое значения в два соседних столбца, как показано на изображении ниже (C2:D4).

Затем выберите исходные данные, нажмите Alt + F8 , выберите макрос BulkReplace и нажмите Выполнить.

Поскольку источник ярости выбран заранее, просто проверьте ссылку и нажмите OK:

После этого выберите диапазон замены и нажмите OK:

Готово!

Преимущества: настройте один раз, используйте повторно в любое время

Недостатки: макрос нужно запускать при каждом изменении данных

Множественный поиск и замена в Excel с помощью инструмента «Подстрока»

В самом первом примере я упомянул, что вложенная ПОДСТАВКА — это самый простой способ заменить несколько значений в Excel. Я признаю, что ошибался. С нашим Ultimate Suite все еще проще!

Чтобы выполнить массовую замену на листе, перейдите на вкладку Данные Ablebits и нажмите Инструменты работы с подстроками > Заменить подстроки.

Появится диалоговое окно Заменить подстроки, в котором вам будет предложено определить диапазон Исходный и диапазон Подстроки.

Выбрав два диапазона, нажмите кнопку Заменить и найдите результаты в новом столбце, вставленном справа от исходных данных. Да, это так просто!

Совет. Прежде чем нажимать Заменить, обратите внимание на один важный момент — поле с учетом регистра. Обязательно выберите его, если вы хотите обрабатывать прописные и строчные буквы как разные символы. В этом примере мы отмечаем этот параметр, потому что хотим заменить только строки, написанные с заглавной буквы, и оставить нетронутыми подстроки, такие как "fr", "uk" или "ak", в других словах.

Если вам интересно узнать, какие еще массовые операции можно выполнять со строками, ознакомьтесь с другими инструментами работы с подстроками, включенными в наш Ultimate Suite. Или, что еще лучше, загрузите ознакомительную версию ниже и попробуйте!

Вот как можно найти и заменить сразу несколько слов и символов в Excel. Я благодарю вас за чтение и надеюсь увидеть вас в нашем блоге на следующей неделе!

В Excel можно одновременно найти и заменить несколько ячеек, содержащих определенное значение. Допустим, у вас есть следующий набор данных с данными о продажах.

Вы хотите заменить все вхождения слова Веб-камера на Камера в столбце B (ячейки B7, B13 и B19).

<р>2. В окне "Найти и заменить" введите значение, которое нужно найти (Веб-камера), и значение, которое заменит его (Камера), и нажмите "Заменить все".< /p>

<р>3. Вы получите всплывающее окно с информацией о том, сколько ячеек было заменено новым значением (в данном случае 3). Нажмите "ОК".

<р>4. Нажмите «Закрыть», чтобы закрыть окно «Найти и заменить».

В результате все ячейки, в которых была Веб-камера, теперь заменены на Камера (B7, B13 и B19).

Примечание. Другой способ замены нескольких значений — использование функции ПОДСТАВИТЬ. Кроме того, вы можете использовать код VBA для поиска и замены значений.

Найти и заменить в Google Таблицах

Как и в Excel, вы также можете находить и заменять несколько значений в Google Таблицах.

<р>2. В окне "Найти и заменить" введите значение, которое нужно найти (Веб-камера), и значение, которое заменит его (Камера), и нажмите "Заменить все".< /p>

<р>3. В нижней части окна вы получите информацию о том, сколько замен было сделано (в данном случае 3). Нажмите «Готово», чтобы закрыть окно.

Результат тот же, что и в Excel, все вхождения Веб-камера в столбце B теперь заменены на Камера.

Чтобы найти и заменить несколько значений в Excel, мы можем использовать функцию ПОДСТАВИТЬ во вложенной формуле:

= ЗАМЕНИТЬ ( ЗАМЕНИТЬ ( ЗАМЕНИТЬ ( B3 , "Бобби" , "Боб" ) , "Саманта" , "Сэм" ) , "Роберт" , "Роб" )

Как работает формула?

Эта формула объединяет несколько функций ПОДСТАВИТЬ, чтобы найти и заменить значения по одному. Результат первой подстановки передается в следующую функцию ПОДСТАВИТЬ и так далее. В нашем примере производится три замены. Чтобы добавить больше подстанций, просто вставьте в формулу функции ПОДСТАВИТЬ.

Замена нескольких значений ссылкой

Другой подход заключается в использовании эталонного диапазона вместо ввода значений в формулу.

= ПОДСТАВИТЬ ( ЗАМЕНИТЬ ( ЗАМЕНИТЬ ( B3 , ИНДЕКС ( $ E $ 3 : $ E $ 5 , 1 ) ), ИНДЕКС ( $ F $ 3 : $ F $ 5 , 1 ) ),

ИНДЕКС ($E$3:$E$5,2), ИНДЕКС($F$3:$F$5,2)), ИНДЕКС($E$3:$E$5,3), ИНДЕКС ($ F $ 3 : $ F $ 5, 3 ))

Этот метод работает так же, как и предыдущий метод, за исключением того, что он использует функцию ИНДЕКС для ссылки на диапазон поиска и замены со значениями. Для каждой ПОДСТАВКИ необходимы две функции ИНДЕКС (одна для значения поиска, а другая для значения замены).

Подстановка нескольких значений в Google Таблицах

Эти формулы работают точно так же в Google Таблицах, как и в Excel.

Практический лист Excel

Попрактикуйтесь в функциях и формулах Excel с помощью наших 100 % бесплатных тренировочных листов!

Читайте также:

  
• Указанный файл не может быть найден при установке драйвера
  
• Аппаратное ускорение браузера Obs
  
• При нажатии на выделенный значок в ms word текст будет
  
• Программа сравнения файлов
  
• Как удалить каждую вторую строку в Excel