Время прочтения: 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, когда есть множество полезных приемов, упрощающих работу.