Предположим, у вас есть список, в котором с разной степенью «прямолинейности» записаны исходные данные — например, адреса или названия компаний:
 |  |
Хорошо видно, что один и тот же город или компания присутствует здесь в разношерстных вариантах, что, очевидно, создаст массу проблем при работе с этими таблицами в дальнейшем. А если немного подумать, то можно найти массу примеров подобных задач из других областей.
А теперь представьте, что такие кривые данные приходят к вам регулярно, т.е. это не разовая история «почини вручную, забудь», а проблема на регулярной основе и в большом количестве ячеек.
Что делать? Не заменяйте вручную 100500 раз кривой текст на правильный через поле «Найти и заменить» или нажав кнопку Ctrl+H?
Первое, что приходит на ум в такой ситуации, — произвести массовую замену по заранее составленному справочнику сопоставления неправильных и правильных вариантов — вот так:
К сожалению, при очевидной распространенности такой задачи, Microsoft Excel не имеет простых встроенных методов ее решения. Для начала разберемся, как это сделать с помощью формул, не привлекая «тяжелую артиллерию» в виде макросов в VBA или Power Query.
Случай 1. Массовая полная замена
Начнем с относительно простого случая – ситуации, когда вам необходимо заменить старый кривый текст на новый. полностью.
Допустим, у нас есть две таблицы:
В первом – оригинальные пестрые названия компаний. Во втором – справочник переписки. Если мы найдем в названии компании в первой таблице любое слово из столбца Найти, то нужно полностью заменить это кривое имя на правильное – из графы Замена вторая таблица поиска.
Для удобства:
- Обе таблицы преобразуются в динамические («умные») с помощью сочетания клавиш. Ctrl+T или команда Вставка – Таблица (Вставка — Таблица).
- На появившейся вкладке Конструктор (Дизайн) первая таблица с именем Данные, а вторая справочная таблица – Замены.
Чтобы объяснить логику формулы, зайдём немного издалека.
На примере первой компании из ячейки А2 и на время забыв об остальных компаниях попробуем определить, какой вариант из столбца Найти встречается там. Для этого выделите любую пустую ячейку в свободной части листа и введите туда функцию НАЙТИ (НАХОДИТЬ):
Эта функция определяет, включена ли данная подстрока (первый аргумент — все значения из столбца Найти) в исходный текст (первая компания из таблицы данных) и должен выводить либо порядковый номер символа, от которого был найден текст, либо ошибку, если подстрока не найдена.
Хитрость здесь в том, что поскольку мы указали в качестве первого аргумента не одно, а несколько значений, то эта функция также вернет в результате не одно значение, а массив из 3-х элементов. Если у вас нет последней версии Office 365, поддерживающей динамические массивы, то после ввода этой формулы и нажатия Enter вы увидите этот массив прямо на листе:
Если у вас есть предыдущие версии Excel, то после нажатия на Enter мы увидим только первое значение из массива результатов, т.е. ошибку #ЗНАЧ! (#ЦЕНИТЬ!).
Бояться не стоит 🙂 На самом деле наша формула работает и вы все равно можете увидеть весь массив результатов, если выделите введенную функцию в строке формул и нажмите клавишу F9(только не забудьте нажать Escвернемся к формуле):
Полученный массив результатов означает, что в исходном кривом названии компании (ОАО ГК Морозко) всех значений в столбце Найти нашел только второй (Морозко), и начиная с 4-го символа подряд.
Теперь добавим в нашу формулу функцию Перейти к товару(УВАЖАТЬ):
Эта функция имеет три аргумента:
- Желаемое значение — можно использовать любое достаточно большое число (главное, чтобы оно превышало длину любого текста в исходных данных)
- Просмотренный_вектор — диапазон или массив, в котором мы ищем искомое значение. Вот ранее введенная функция НАЙТИ, который возвращает массив {#VALUE!:4:#VALUE!}
- Вектор_Результаты — диапазон, из которого мы хотим вернуть значение, если искомое значение найдено в соответствующей ячейке. Вот правильные имена из столбца Замена наша справочная таблица.
Главной и неочевидной особенностью здесь является то, что функция Перейти к товару если точного совпадения нет, всегда ищет ближайшее наименьшее (предыдущее) значение. Поэтому, указав в качестве искомого значения любое здоровенное число (например, 9999), мы заставим Перейти к товару найдите ячейку с ближайшим наименьшим числом (4) в массиве {#VALUE!:4:#VALUE!} и верните соответствующее значение из вектора результата, т.е. правильное название компании из столбца Замена.
Второй нюанс заключается в том, что технически наша формула является формулой массива, поскольку функция НАЙТИ возвращает в качестве результата не одно, а массив из трех значений. Но поскольку функция Перейти к товару поддерживает массивы «из коробки», то нам не придется вводить эту формулу как классическую формулу массива – с помощью сочетания клавиш Ctrl+Shift+Enter. Простого будет достаточно Enter.
Вот и все. Надеюсь, вы уловили логику.
Осталось перенести готовую формулу в первую ячейку В2 столбца Исправлена – и наша задача решена!
Конечно, и с обычными (не умными) таблицами эта формула тоже прекрасно работает (только не забывайте про ключ F4 и исправление соответствующих ссылок):
Случай 2. Массовая частичная замена
Этот случай немного сложнее. И снова у нас есть две «умные» таблицы:
Первая таблица с криво написанными адресами, которую нужно исправить (я назвал ее Data2). Вторая таблица — справочник, по которому нужно произвести частичную замену подстроки внутри адреса (я назвал эту таблицу Замены2).
Принципиальное отличие здесь в том, что вам нужно заменить только фрагмент исходных данных — например, первый адрес имеет неверный адрес. «Св. Петербург» справа «Св. Петербург», оставив остальную часть адреса (почтовый индекс, улицу, дом) как есть.
Готовая формула будет выглядеть так (для удобства восприятия я разделил ее на количество строк с помощью другой+Enter):
Основную работу здесь выполняет стандартная текстовая функция Excel. ЗАМЕНА (ЗАМЕНЯТЬ), который имеет 3 аргумента:
- Исходный текст — первый кривой адрес из графы «Адрес»
- Что мы ищем – здесь мы используем трюк с функцией Перейти к товару (УВАЖАТЬ)из предыдущего способа получить значение из столбца Найти, который включен как фрагмент в изогнутый адрес.
- На что заменить – аналогичным образом находим соответствующее ему правильное значение из столбца Замена.
Введите эту формулу с помощью Ctrl+Shift+Enter здесь тоже не нужна, хотя по сути это формула массива.
И хорошо видно (см. ошибки #Н/Д на предыдущей картинке), что такая формула при всей своей элегантности имеет пару недостатков:
- Функция ЗАМЕНА чувствительна к регистру, поэтому «Спб» в предпоследней строке в таблице замен не обнаружено. Чтобы решить эту проблему, вы можете использовать функцию ЗАМЕНИТЬ (ЗАМЕНЯТЬ), либо предварительно занести обе таблицы в один регистр.
- Если текст изначально правильный или в нем нет фрагмента для замены (последняя строка), то наша формула выдает ошибку. Этот момент можно нейтрализовать перехватом и заменой ошибок с помощью функции IFERROR (ЕСЛИОШИБКА):
- Если исходный текст содержит сразу несколько фрагментов из каталога, то наша формула заменяет только последнюю (в 8-й строке Лиговский «Проспект« изменился на «пр-т», Но «С-Пб» on «Св. Петербург» больше нет, потому что «С-Пбнаходится выше в каталоге). Эту проблему можно решить, перезапустив собственную формулу, но уже по столбцу Исправлена:
Не идеально и местами громоздко, но гораздо лучше той же ручной замены, не так ли? 🙂
PS
В следующей статье мы разберемся, как реализовать такую массовую замену с помощью макросов и Power Query.
- Как работает функция ЗАМЕНИТЬ для замены текста
- Поиск точных совпадений текста с помощью функции ТОЧНО
- Поиск и замена с учетом регистра (VLOOKUP с учетом регистра)