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

Припустимо, у вас є список, в якому з різним ступенем «прямолінійності» записані вихідні дані – наприклад, адреси або назви компаній:

Чітко видно, що одне й те саме місто чи компанія присутні тут у строкатих варіантах, що, очевидно, створить масу проблем при роботі з цими таблицями в майбутньому. А якщо трохи подумати, то можна знайти масу прикладів подібних завдань з інших областей.

А тепер уявіть, що такі хибні дані надходять до вас регулярно, тобто це не одноразова історія «виправте вручну, забудьте», а проблема виникає регулярно і у великій кількості комірок.

Що робити? Не замінюйте вручну кривий текст 100500 разів на правильний через поле «Знайти та замінити» або клацанням Ctrl+H?

Перше, що спадає на думку в такій ситуації, це зробити масову заміну за заздалегідь складеним довідником зіставлення неправильних і правильних варіантів – так:

На жаль, при очевидній поширеності такого завдання, Microsoft Excel не має простих вбудованих методів її вирішення. Для початку давайте розберемося, як це зробити за допомогою формул, не залучаючи «важку артилерію» у вигляді макросів у VBA або Power Query.

Кейс 1. Масова повна заміна

Почнемо з порівняно простого випадку – ситуації, коли потрібно замінити старий кривий текст на новий. повністю.

Припустимо, у нас є дві таблиці:

У першому – оригінальні строкаті назви компаній. У другому – довідник листування. Якщо ми знайдемо в назві компанії в першій таблиці будь-яке слово зі стовпця Знайти, то потрібно повністю замінити цю криву назву на правильну – з колонки Замінити друга таблиця пошуку.

Для зручності:

• Обидві таблиці перетворюються на динамічні («розумні») за допомогою комбінації клавіш Ctrl+T або команда Вставка – Таблиця (Вставка — Таблиця).
  
• На вкладці, що з’явиться Конструктор (Дизайн) перша таблиця ім дані, а друга опорна таблиця – Заміни.

Щоб пояснити логіку формули, підемо трохи здалеку.

Взявши за приклад першу компанію з комірки А2 і тимчасово забувши про решту компаній, спробуємо визначити, який варіант зі стовпця Знайти зустрічається там. Для цього виділіть будь-яку порожню комірку у вільній частині аркуша і введіть туди функцію ЗНАЙТИ (ЗНАЙТИ):

Ця функція визначає, чи включений даний підрядок (перший аргумент - це всі значення зі стовпця Знайти) у вихідний текст (перша компанія з таблиці даних) і повинна вивести або порядковий номер символу, з якого було знайдено текст, або помилку, якщо підрядок не знайдено.

Хитрість тут полягає в тому, що так як ми вказали не одне, а кілька значень в якості першого аргументу, ця функція також буде повертати в результаті не одне значення, а масив з 3 елементів. Якщо у вас немає останньої версії Office 365, яка підтримує динамічні масиви, то після введення цієї формули та натискання на Що натомість? Створіть віртуальну версію себе у ви побачите цей масив прямо на аркуші:

Якщо у вас попередні версії Excel, то після натискання на Що натомість? Створіть віртуальну версію себе у ми побачимо лише перше значення з масиву результатів, тобто помилку #VALUE! (#VALUE!).

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

Отриманий масив результатів означає, що в оригіналі криво назва компанії (ВАТ Морозко Г.К.) усіх значень у стовпці Знайти знайшов лише другий (Морозко), і починаючи з 4-го символу поспіль.

Тепер давайте додамо функцію до нашої формули Поглянути(ШУК):

Ця функція має три аргументи:

1. Бажане значення – можна використовувати будь-яке досить велике число (головне, щоб воно перевищувало довжину будь-якого тексту у вихідних даних)
   
2. Viewed_vector – діапазон або масив, де ми шукаємо потрібне значення. Ось введена раніше функція ЗНАЙТИ, який повертає масив {#VALUE!:4:#VALUE!}
   
3. Vector_результати – діапазон, з якого ми хочемо повернути значення, якщо потрібне значення знайдено у відповідній клітинці. Ось правильні імена з колонки Замінити наша довідкова таблиця.
   

Головною і неочевидною особливістю тут є функція Поглянути якщо точної відповідності немає, завжди шукає найближче найменше (попереднє) значення. Тому, вказавши в якості бажаного значення будь-яке велике число (наприклад, 9999), ми змусимо Поглянути знайдіть клітинку з найближчим найменшим числом (4) в масиві {#VALUE!:4:#VALUE!} і поверніть відповідне значення з вектора результату, тобто правильну назву компанії зі стовпця Замінити.

Другий нюанс полягає в тому, що технічно наша формула є формулою масиву, оскільки функція ЗНАЙТИ повертає як результати не одне, а масив із трьох значень. Але оскільки функція Поглянути підтримує масиви з коробки, то нам не потрібно вводити цю формулу як класичну формулу масиву – за допомогою комбінації клавіш Ctrl+Shift+ Що натомість? Створіть віртуальну версію себе у . Досить буде простого Що натомість? Створіть віртуальну версію себе у .

Це все. Сподіваюся, ви зрозуміли логіку.

Залишилося перенести готову формулу в першу комірку В2 стовпця Виправлено – І наше завдання вирішено!

Звичайно, зі звичайними (не розумними) таблицями ця формула теж працює чудово (тільки не забудьте про ключ F4 та виправлення відповідних посилань):

Випадок 2. Масова часткова заміна

Цей випадок трохи складніший. Знову маємо дві «розумні» таблиці:

Перша таблиця з криво написаними адресами, яку потрібно виправити (я її назвав Дані2). Друга таблиця - довідкова, по якій потрібно зробити часткову заміну підрядка всередині адреси (я назвав цю таблицю Заміни2).

Принципова відмінність тут полягає в тому, що потрібно замінити лише фрагмент вихідних даних – наприклад, перша адреса має неправильний “Св. Петербург” праворуч “Св. Петербург”, залишивши решту адреси (поштовий індекс, вулиця, будинок) як є.

Готова формула буде виглядати так (для зручності сприйняття я розділив її на скільки рядків за допомогою інший+ Що натомість? Створіть віртуальну версію себе у ):

Основну роботу тут виконує стандартна текстова функція Excel ЗАМІННИК (ЗАМІНА), який має 3 аргументи:

1. Вихідний текст – перша крива адреса з колонки Адреса
2. Що ми шукаємо – тут ми використовуємо трюк із функцією Поглянути (ШУК)з попереднього способу отримати значення зі стовпця Знайти, який включено як фрагмент у вигнуту адресу.
3. Чим замінити – таким же чином знаходимо відповідне йому правильне значення зі стовпця Замінити.

Введіть цю формулу за допомогою Ctrl+Shift+ Що натомість? Створіть віртуальну версію себе у тут також не потрібен, хоча це, по суті, формула масиву.

І чітко видно (див. помилки #N/A на попередньому малюнку), що така формула, при всій своїй елегантності, має кілька недоліків:

• функція SUBSTITUTE чутливий до регістру, тому «Спб» в передостанньому рядку не виявилося в таблиці замін. Щоб вирішити цю проблему, ви можете скористатися функцією ЗАМЕНИТИ (ЗАМІНИТИ), або попередньо звести обидві таблиці в один реєстр.
• Якщо текст спочатку правильний або в ньому немає фрагмента для заміни (останній рядок), тоді наша формула видає помилку. Цей момент можна нейтралізувати шляхом перехоплення та заміни помилок за допомогою функції ПОМИЛКА (ЯКЩО ПОМИЛКА):

  

• Якщо оригінальний текст містить відразу кілька фрагментів з каталогу, то наша формула замінює тільки останню (у 8 рядку Ліговський «Проспект« змінено на “пр-т”, Але «S-Pb» on “Св. Петербург” більше не, тому що «S-Pb” вище в каталозі). Цю проблему можна вирішити шляхом повторного запуску нашої власної формули, але вже по колонці Виправлено:

  
  

Не ідеально і місцями громіздко, але набагато краще, ніж та ж ручна заміна, чи не так? 🙂

PS

У наступній статті ми з’ясуємо, як реалізувати таку масову заміну за допомогою макросів і Power Query.

• Як функція SUBSTITUTE працює для заміни тексту
  
• Пошук точних збігів тексту за допомогою функції EXACT
• Пошук і заміна з урахуванням регістру (VLOOKUP з урахуванням регістру)