В данной статье я хочу рассказать о некоторых возможностях бесплатной и крайне полезной, но пока еще мало известной надстройки над MS Excel под названием Power Query.
Power Query позволяет забирать данные из самых разных источников (таких как csv, xls, json, текстовых файлов, папок с этими файлами, самых разных баз данных, различных api вроде Facebook opengraph, Google Analytics, Яндекс.Метрика, CallTouch и много чего еще), создавать повторяемые последовательности обработки этих данных и загружать их внутрь таблиц Excel или самого data model.
И вот под катом вы можете найти подробности всего этого великолепия возможностей.
Совместимость и технические подробности
Power Query доступен бесплатно для всех версий Windows Excel 2010, 2013 и встроен по умолчанию в Windows Excel 2016. Для пользователей MacOS X Power Query недоступен (впрочем, даже без этого маковский Excel отвратителен на ощупь и продвинутые пользователи, включая меня, чаще всего работают с нормальным Excel через Parallels или запуская его на удаленной виндовой машинке).
React Query за 10 минут! Быстрый курс
Также, Power Query встроен в новый продукт для бизнес аналитики — Power BI, а еще, ходят слухи, что Power Query будет появляться и в составе других продуктов от Microsoft. Т.е. Power Query ждет светлое будущее и самое время для адептов технологий Microsoft (и не только) заняться его освоением.
Как оно работает
После установки Power Query в интерфейсе Excel 2010–2013 появляется отдельная одноименная вкладка.
В новом Excel 2016 функционал Power Query доступен на вкладке Data (данные), в блоке “Get https://habr.com/ru/articles/271019/» target=»_blank»]habr.com[/mask_link]
Power Query: простая работа с данными
Что это? Power Query – это бесплатная надстройка для продуктов «Майкрософт», которая значительно облегчает работу с данными: сбор, подготовку, преобразование. Ее не нужно скачивать и как-то специально настраивать.
Как использовать? Интерфейс PQ интуитивно понятен, разобраться в нём не составит труда. Чтобы понять, на что способна эта надстройка, и получить базовые навыки работы в ней, читайте наш материал.
- Возможности Power Query
- Подключение к данным в Power Query
- Предварительная настройка Power Query
- Редактор запросов Power Query
- Преобразование данных в Power Query
- Строка формул и расширенный редактор
- Типы данных Power Query
- Импорт данных из Power Query
- Использование Power Query в продуктах и службах «Майкрософт»
Пройди тест и узнай, какая сфера тебе подходит:
Что такое Query Builder
айти, дизайн или маркетинг.
Бесплатно от Geekbrains
Возможности Power Query
В статьях о MS Excel в последнее время часто встречаются малопонятные термины, начинающиеся со слова Power. Из контекста этих материалов не всегда получается понять, что подразумевается под Power Query, Power Pivot, Power BI и т. п. Самое главное — неясно, каким образом эти понятия связаны между собой и какую пользу они приносят простому пользователю табличного редактора.
Между тем еще в 2013 году в Microsoft была создана специальная группа разработчиков с целью выпуска бесплатной надстройки для Excel. Разработка получила несколько названий: Power Query, Data Explorer, Get
61 проверенный канал
из закрытой подборки
по поиску работы в IT
Гарантированно найдете работу за 1-2 дня
Чтобы получить подарок, заполните информацию в открывшемся окне
- Очищение полученных данных от лишних элементов
Надстройка удаляет в частности пустые столбцы и строки, лишние пробелы, непечатаемые символы, различную служебную информацию, а также повторяющиеся значения в ячейках.
- Приведение данных к нужному виду
С помощью PQ можно менять регистр на правильный, приводить цифры к числовому формату, заполнять пробелы, исправлять заголовки таблиц, разделять текстовые фрагменты на столбцы и склеивать их снова в единый текст, выполнять некоторые другие полезные операции.
- Приведение таблицы к нужному виду
Среди возможных операций над таблицами — фильтрация, сортировка и смена порядка столбцов, транспонирование значений, генерация итогов, трансформация кросс-таблиц в обычный плоский формат и обратно.
- Подстановка значений из одной таблицы в другую
Эта операция выполняется по совпадению одного или нескольких параметров. Таким образом, она является полной заменой встроенной в Excel функции ВПР (VLOOKUP) и ее аналогов.
Надстройка представлена в двух вариантах: как отдельный модуль, доступный для скачивания с официального сайта Microsoft (для Excel 2010-13), и как сервисная функция в составе редактора (для Excel 2016). В первом варианте установка PQ добавляет в таблицу новую вкладку. Во втором же случае весь функционал надстройки уже имеется в табличном редакторе по умолчанию — он доступен как группа Get
Источник: gb.ru
Используем Power Query
Несмотря на появление новых технологий, Excel как один из инструментов анализа данных, сегодня остается крайне популярным.
Развиваются не только альтернативные технологии, но расширяется и функционал самого Excel. В частности — бесплатная надстройка Power Query, которая доступна для версий Excel 2010 и выше. Начиная с 2016 версии надстройка встроена в Excel (вкладка Данные, группа Скачать и преобразовать), для версий 2010 и 2013 – доступна для скачивания с сайта Microsoft (после подключения появляется новая вкладка Power Query).
Power Query предлагает набор функций, с помощью которых можно решить те же задачи, что и в классическом Excel, но быстрее и проще — в несколько кликов мыши, с использованием пользовательских пиктограмм. Краткое знакомство с интерфейсом надстройки, а также ряд способов загрузки таблиц для обработки в надстройке ранее освещались в сообществе здесь и здесь .
Предметом данной статьи будут примеры часто используемых автором функций Excel, которые быстро и просто реализуются в Power Query.
Особенностью функции ВПР является длительность ее расчета, а также отсутствие универсальности.
Рассмотрим пример: требуется заполнить ФИО руководителя в Таблице 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 (в примере – «Руководитель»). Получаем результат:
Аналогично решается задача слияния по множественным критериям, в этом случае нужно указать несколько столбцов для слияния.
Часто требуется выбрать и проанализировать лишь часть текста. Предположим, при анализе выписки по расчетному счету, требуется извлечь из поля «назначение платежа» искомую информацию. Обычно именно это поле является наименее структурированным. Например, для извлечения номера и даты договора из поля
«оплата по договору № 55 от 24.04.2020»
расположенного в ячейке Excel A1, нам потребуется написать такую формулу:
функция = ПСТР (A1; НАЙТИ («№»; A1) + 2; ДЛСТР (A1) – НАЙТИ («№»; A1 ) — 1)
Надстройка Power Query позволит решить ту же задачу без вычислений. Меню: преобразование – столбец «Текст» – разделить столбец – по разделителю – задать необходимый разделитель (в примере – «№»).
Чтобы извлечь только номер договора из поля, выберем меню: преобразование – столбец «Текст» – извлечь – текст между разделителями (в примере – «№» и « от»).
Получим результат 55, изменим тип данных на целое число.
- Преобразование данных из двумерной таблицы в одномерную и обратно.
Допустим, мы имеем такую таблицу:
| ФИО | Январь | Февраль | Март |
| Иванов И.И. | 100 | 400 | 700 |
| Петров П.П. | 200 | 500 | 800 |
| Сидоров С.С. | 300 | 600 | 900 |
Такой вид таблицы не всегда удобен для анализа. Преобразуем его в одномерную таблицу.
Зафиксировав нужный столбец (в примере — «ФИО»), выбираем в меню Power Query функцию пользовательского меню: преобразование – любой столбец – отменить свертывание других столбцов, получаем развернутую таблицу:
Справедливо и обратное действие. Выбираем столбец с признаком для сведения (в нашем случае «Атрибут»), пункт меню: преобразование – любой столбец – столбец сведения. Указываем столбец с данными для сведения (в примере — столбец «Значение»).
Клик – и получаем результат.
