Excel/PowerQuery/VBA

Массовая замена текста с помощью Power Query

Время прочтения: 2 мин.

Все мы сталкивались с отчетом где одно и то же слово может быть написано двумя, а то и более вариантами. Не всегда обычная замена («Найти и заменить» или Ctrl+H) может справиться с большим объемом и привести разношёрстный текст в единый стиль. В статье мы рассмотрим решение этой задачи с помощью функция List.Accumulate в Power Query, которая в считанные секунды выполнит преобразование текста и сделает работу с информацией намного комфортнее.

Применение функции рассмотрим на примере списка с адресами, где такие слова, как улица, город и тд. написаны разными вариантами:

Таблица 1
г. Мск, улица Ленина, дом 25
г. МКС, переулок Проточный, дом 24
город Москва, у. Россолимо, дом 7
город Москва, проспект Вернадского, дом 18
г. Москва, район Куркина, улица Воротынская, д. 16
г. Москва, р. Куркина, улица Родионовская, д. 13

А также справочник, где указаны все «неправильные» варианты и варианты, на которые необходимо будет произвести замену:

НайтиЗаменить
МскМосква
МСКМосква
улицаул.
у.ул.
районр-н
р.р-о
домд.
переулокпер.
проспектпр-т
городгор.
г.гор.

Для начала нам нужно загрузить информацию в Power Query. Первым загрузим справочник с вариантами: Выделить справочник – вкладка «Данные»- из таблицы/ диапазона.

Для использования в дальнейшем List.Accumulate, которая будет производить замену текста, необходимо справочник преобразовать в список из записей, где есть два элемента «Найти» и «Заменить».

Преобразование можно выполнить с помощью функции в Power Query – Table.ToRecords(). Данную функцию необходимо дописать в строку формул:

=Table.ToRecords(Excel.CurrentWorkbook() {[Name =»Справочник»]}[Contet])

Далее возвращаемся в Excel – вкладка «Главная» — кнопка «Закрыть и загрузить в» — в окошке «Импорт данных» опция «только создать подключение».

Вторым этапом необходимо загрузить таблицу с адресами в Power Query и выполнять в ней замену по справочнику:

Выделить таблицу адресов – вкладка «Данные»- из таблицы/ диапазона.

Теперь применим функцию List.Accumulate:

Вкладка «Добавление столбца» — Настраиваемый столбец – Настраиваемая формула столбца:

=List.Accumulate(Справочник, [Таблица 1], (state, current) =>Text.Replace(state, current[Найти], current [Заменить])).

Получаем чистый список адресов без траты времени на ручную замену текста:

Таблица 1
гор. Москва, ул. Ленина, д. 25
гор. Москва, пер. Проточный, д. 24
гор. Москва, ул. Россолимо, д. 7
гор. Москва, пр-т Вернадского, д. 18
гор. Москва, р-н Куркина, ул. Воротынская, д. 16
гор. Москва, р. Куркина, ул. Родионовская, д. 13

Функции List.Accumulate в Power Query справляется с массовой заменой текста за несколько секунд без затрат времени и сил, которые ушли бы на ручную замену. Не стоит ограничиваться стандартными функциями Excel, когда есть множество полезных приемов, упрощающих работу.

Советуем почитать