Массовая замена текста формулами

Предположим, что у вас имеется список, в котором с разной степенью "пряморукости" записаны исходные данные - например, адреса или названия компаний:

Исходный кривой текст            Кривые названия компаний

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

А теперь представьте, что к вам такие кривые данные попадают регулярно, т.е. это не единичная история из серии "исправил вручную, забыл", а проблема на регулярной основе и в большом количестве ячеек.

Что же делать? Не заменять же вручную 100500 раз кривой текст на правильный через окошко "Найти и заменить" или нажимая Ctrl+H?

Первое, что приходит в голову в подобной ситуации - произвести массовую замену по составленному заранее справочнику соответствия неправильных и правильных вариантов - вроде такого:

Справочник подстановки

К сожалению, при очевидной распространенности подобной задачи, в Microsoft Excel не существует простых встроенных способов для её решения. Для начала, давайте разберёмся, как это делать формулами, без привлечения "тяжелой артиллерии" в виде макросов на VBA или Power Query.

Случай 1. Массовая полная замена

Начнём с относительно простого случая - ситуации, когда нужно заменить старый кривой текст на новый полностью.

Предположим, что у нас есть две таблицы:

Данные и замены по компаниям

В первой - исходные разномастные названия компаний. Во второй - справочник соответствия. Если находим в названии компании в первой таблице любое слово из столбца Найти, то нужно полностью заменить это кривое название на правильное - из столбца Заменить второй таблицы-справочника.

Для удобства:

  • Обе таблицы преобразованы в динамические ("умные") с помощью сочетания клавиш Ctrl+T или командой Вставка - Таблица (Insert - Table).
  • На появившейся вкладке Конструктор (Design) первой таблице присвоено имя Данные, а второй таблице-справочнику - Замены.

Чтобы объяснить логику формулы зайдём чуть издалека.

Взяв в качестве примера первую компанию из ячейки A2 и забыв временно про остальные компании, попробуем определить какой именно вариант из столбца Найти там встречается. Для этого выделим любую пустую ячейку в свободной части листа и введём туда функцию НАЙТИ (FIND):

Ищем вхождения

Эта функция определяет входит ли заданная подстрока (первый аргумент - все значения из столбца Найти) в исходный текст (первая компания из таблицы данных) и должна вывести на выходе либо порядковый номер символа, начиная с которого текст был найден, либо ошибку если подстрока не обнаружена.

Хитрость тут в том, что поскольку первым аргументом мы указали не одно, а несколько значений - эта функция будет возвращать в качестве результата тоже не одно значение, а массив из 3 элементов. Если у вас не последняя версия Office 365 с поддержкой динамических массивов, то после ввода этой формулы и нажатия на Enter вы этот массив увидите прямо на листе:

Динамический массив результатов в Office 365

Если же у вас предыдущие версии Excel, то после нажатия на Enter мы увидим только первое значение из массива результатов, т.е. ошибку #ЗНАЧ! (#VALUE!).

Пугаться не стоит :) На самом деле наша формула работает и увидеть весь массив результатов всё равно можно, если выделить введённую функцию в строке формул и нажать клавишу F9(только не забудьте потом нажать Esc, чтобы вернуться обратно к формуле):

Массив результатов в строке формул Excel

Полученный массив результатов означает, что в исходном кривом названии компании (ГК Морозко ОАО) из всех значений в столбце Найти нашлось только второе (Морозко), причём начиная с 4-го по счёту символа.

Теперь добавим к нашей формуле функцию ПРОСМОТР (LOOKUP):

Добавляем функцию ПРОСМОТР

У этой функции три аргумента:

  1. Искомое значение - можно использовать любое достаточно большое число (главное, чтобы оно превышало длину любого текста в исходных данных)
  2. Просматриваемый_вектор - тот диапазон или массив, где мы ищем искомое значение. Здесь это введённая ранее функция НАЙТИ, возвращающая массив {#ЗНАЧ!:4:#ЗНАЧ!}
  3. Вектор_результатов - диапазон, откуда мы хотим вернуть значение, если искомое значение найдено в соответствующей ячейке. Здесь это правильные названия из столбца Заменить нашей таблицы-справочника.

Главная и неочевидная фишка тут в том, что функция ПРОСМОТР при отсутствии точного совпадения всегда ищет ближайшее наименьшее (предыдущее) значение. Поэтому, указав в качестве искомого значения любое здоровенное число (например 9999), мы заставим ПРОСМОТР находить ячейку с ближайшим наименьшим числом (4) в массиве {#ЗНАЧ!:4:#ЗНАЧ!} и выдавать соответствующее ей значение из вектора результатов, т.е. правильное название компании из столбца Заменить.

Второй нюанс заключается в том, что, технически, наша формула является формулой массива, т.к. функция НАЙТИ возвращает в качестве результатов не одно, а массив из трёх значений. Но поскольку функция ПРОСМОТР поддерживает массивы "из коробки", то нам не придётся вводить эту формулу как классическую формулу массива - с помощью сочетания клавиш Ctrl+Shift+Enter. Достаточно будет простого Enter.

Вот и всё. Надеюсь вы ухватили логику.

Осталось перенести готовую формулу первую ячейку B2 столбца Исправлено - и наша задача решена!

Готовая формула

Само-собой, с обычными (не умными) таблицами эта формула тоже замечательно работает (только не забудьте про клавишу F4 и закрепление соответствующих ссылок):

На обычных таблицах

Случай 2. Массовая частичная замена

Этот случай чуть похитрее. Снова имеем две "умных" таблицы:

Исходные данные для частичной замены

Первая таблица с криво записанными адресами, которые нужно исправить (я назвал её Данные2). Вторая таблица - справочник, по которому нужно произвести частичную замену подстроки внутри адреса (я назвал эту таблицу Замены2).

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

Готовая формула будет выглядеть так (для удобства восприятия я разделил её на насколько строк с помощью Alt+Enter):

Частичная массовая замена

Основную работу здесь выполняет стандартная Excel'евская текстовая функция ПОДСТАВИТЬ (SUBSTITUTE), у которой 3 аргумента:

  1. Исходный текст - первый кривой адрес из столбца Адрес
  2. Что ищем - тут мы используем трюк с функцией ПРОСМОТР (LOOKUP) из предыдущего способа, чтобы вытащить значение из столбца Найти, которое входит как фрагмент в кривой адрес.
  3. На что заменить - аналогичным образом находим соответствующее ему правильное значение из столбца Заменить.

Вводить эту формулу с Ctrl+Shift+Enter здесь тоже не нужно, хотя она и является, по-сути, формулой массива.

И хорошо видно (см. ошибки #Н/Д на предыдущей картинке), что такая формула, при всей её элегантности, обладает и парой недостатков:

  • Функция ПОДСТАВИТЬ является регистрочувствительной, поэтому "Спб" в предпоследней строке так и не нашлось в таблице замен. Для решения этой проблемы можно либо использовать функцию ЗАМЕНИТЬ (REPLACE), либо предварительно привести обе таблицы к одному регистру.

  • Если текст изначально правильный или в нём нет ни одного фрагмента на замену (последняя строка), то наша формула выдает ошибку. Этот момент можно нейтрализовать перехватом и заменой ошибок с помощью функции ЕСЛИОШИБКА (IFERROR):

    Перехват ошибок

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

    Повторный прогон

Не идеально и, местами, громоздко, но гораздо лучше, чем однообразная замена вручную, правда? :)

P.S.

В следующей статье разберёмся, как реализовать подобную массовую подстановку с помощью макросов и Power Query.

Ссылки по теме



17.08.2020 05:01:26
Николай реально какой-то excel-маг и волшебник.
17.08.2020 22:26:41
Изощренность Николая и Excel'а восхищает, но по-моему всё-таки проще банальным макросом из авторекордера:
   Cells.Replace What:="СПб", Replacement:="Санкт-Петербург", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
   Cells.Replace What:="С-Пб", Replacement:="Санкт-Петербург", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
   Cells.Replace What:="Питер", Replacement:="Санкт-Петербург", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
18.08.2020 11:40:30
А если кроме Питера у вас ещё 100 городов и по каждому 3-5 вариантов написания? :)
02.10.2020 14:20:04
К вашему PS: когда же? жду - не дождусь варианта макроса, который поможет все необходимые замены в ячейке произвести (без "дополнительного прогона" по уже исправленному тексту).
Спасибо вам, Николай, за ваш труд!
Наверх