Используем Power Query

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

Несмотря на появление новых технологий, Excel как один из инструментов анализа данных, сегодня остается крайне популярным.

            Развиваются не только альтернативные технологии, но расширяется и функционал самого Excel. В частности — бесплатная надстройка Power Query, которая доступна для версий Excel 2010 и выше. Начиная с 2016 версии надстройка встроена в Excel (вкладка Данные, группа Скачать и преобразовать), для версий 2010 и 2013 – доступна для скачивания с сайта Microsoft (после подключения появляется новая вкладка Power Query).

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

Предметом данной статьи будут примеры часто используемых автором функций Excel, которые быстро и просто реализуются в Power Query.

1. Альтернатива функции ВПР.

Особенностью функции ВПР является длительность ее расчета, а также отсутствие универсальности.

Рассмотрим пример: требуется заполнить ФИО руководителя в Таблице 1 пользуясь данными Таблицы 2

Формула переноса данных в самом простом случае для строки 3 будет выглядеть следующим образом:

функция = ВПР (A3; $A$13 : $C$20; 3; 0)

Если же столбец «Руководитель» будет расположен слева от ИНН, то функция ВПР уже  неприменима. Здесь должна использоваться более сложная формула, которая сочетает в себе две функции:

функция = ИНДЕКС ( $G$13 : $H$20 ; ПОИСКПОЗ ( A3; $H$13:$H$20; 0 ); 1)

Ту же задачу можно решить буквально в 10 кликов мыши с использованием функции «Объединить запросы» в надстройке Excel Power Query: главная – объединить – объединить запросы.  Выбрать таблицы и столбец для слияния (в примере —  таблица 1 и таблица 3, столбец «ИНН»).

Развернем полученные таблицы, выберем нужный столбец из таблицы 3 (в примере – «Руководитель»). Получаем результат:

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

2. Извлечение текста.

Часто требуется выбрать и проанализировать лишь часть текста. Предположим, при анализе выписки по расчетному счету, требуется извлечь из поля «назначение платежа» искомую информацию. Обычно именно это поле является наименее структурированным. Например, для извлечения номера и даты договора из поля

«оплата по договору № 55 от 24.04.2020»

расположенного в ячейке Excel A1, нам потребуется написать такую формулу:

функция = ПСТР (A1; НАЙТИ ("№"; A1) + 2; ДЛСТР (A1) – НАЙТИ ("№"; A1 ) -  1)

Надстройка Power Query позволит решить ту же задачу без вычислений. Меню: преобразование – столбец «Текст» – разделить столбец – по разделителю – задать необходимый разделитель (в примере – «№»).

Чтобы извлечь только номер договора из поля, выберем меню: преобразование – столбец «Текст» –  извлечь – текст между разделителями (в примере – «№» и « от»).

Получим результат 55, изменим тип данных на целое число.

3.  Преобразование данных из двумерной таблицы в одномерную и обратно.

Допустим, мы имеем такую таблицу:

Такой вид таблицы не всегда удобен для анализа. Преобразуем его в одномерную таблицу.

Зафиксировав нужный столбец (в примере — «ФИО»), выбираем в меню Power Query функцию пользовательского меню: преобразование – любой столбец – отменить свертывание других столбцов, получаем развернутую таблицу:

Справедливо и обратное действие. Выбираем столбец с признаком для сведения (в нашем случае «Атрибут»), пункт меню: преобразование – любой столбец – столбец сведения. Указываем столбец с данными для сведения (в примере — столбец «Значение»).

Клик – и получаем результат.

Мы рассмотрели некоторые примеры функций, которые позволяет реализовывать Power Query. Используя пиктограммы пользовательского меню, оформленные в интуитивно понятном  интерфейсе, родственно схожем со знакомым всем Excel, можно решить массу рабочих задач и сэкономить время. Здесь будет актуально #cаморазвитие. В интернете достаточно платных и бесплатных ресурсов для изучения не только простых пользовательских функций надстройки, но и языка М, знание которого позволит максимально освоить функционал Power Query. Мне нравится YouTube канал «Товарищ Excel» — короткие, систематизированные бесплатные уроки, для изучения которых возможно и достаточно выделить от 10 до 30 минут в день.