Автоматическое обновление запросов в excel

Обновлено: 07.07.2024

В прошлых статьях я рассказал, как сделать выпадающий список в Excel: первая , вторая и видео:

Вернемся к примеру из прошлых статей - мы готовим таблицу для отдела кадров, в которой перечислены все сотрудники, должности и отделы, и хотим, чтобы таблицу было заполнять легко, в то же время, чтобы избежать зоопарка различных названий, типа: "дир.", "директор", "дир-р", "деректор" и тому подобного - всегда удобно работать с чистыми данными.

Все достаточно просто - выбираем диапазон, где хотим сделать выпадющий список и жмем кнопку Проверка данных, указываем список значений - и готово.

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

Можно ли как-то автоматизировать добавление новых отделов не используя макросы? Да! Но сначала нужна небольшая подготовка. Воспользуемся методом, описанным в статье " Только при помощи формул: список уникальных значений и сортировка ", и скопируем на лист "Списки" уникальные значения из колонки "Отделы". Чтобы узнать, как это сделать, пройдите по ссылке выше. Там же вы найдете готовую таблицу Excel для примера.

Мы ранее протягивали формулу массива на несколько десятков строк, чтобы наверняка хватило: с ячейки B3 до ячейки B33 (конечно, можно сделать и меньше, и больше). Этот диапазон можно указать в настройках проверки данных, но тогда в выпадающем списке будет достаточно много пустых строк. Неаккуратненько.

Чтобы у нашего файлы был профессиональный и законченный вид, попробуем сосчитать сколько ячеек в уникальном списке заполнены. Введем в ячейку B1 на листе "Списки" формулу:

=COUNTA($B$3:$B$33)-COUNTBLANK(B3:B33)

COUNTA (СЧЁТЗ) укажет количество ячеек в указаном диапазоне, COUNTBLANK (СЧИТАТЬПУСТОТЫ) укажет количество путых ячеек.

Таким образом мы узнали, сколько уникальных значений в списке. Теперь можно немного модифицировать формулу:

Функция TEXT (ТЕКСТ) преображает числовое значение в текстовый вид и позволяет сцепить его с текстом. И еще немножко доделаем формулу, добавив проверку случая, если наш список пуст.

Таким образом, у нас в ячейке B1 получился в текстовом виде указатель на диапазон, в котором размещен список уникальных значений:

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

Также, Power Query встроен в новый продукт для бизнес аналитики — Power BI, а еще, ходят слухи, что Power Query будет появляться и в составе других продуктов от Microsoft. Т.е. Power Query ждет светлое будущее и самое время для адептов технологий Microsoft (и не только) заняться его освоением.

Как оно работает

После установки Power Query в интерфейсе Excel 2010–2013 появляется отдельная одноименная вкладка.

В новом Excel 2016 функционал Power Query доступен на вкладке Data (данные), в блоке “Get & Transform”.

Сначала, в интерфейсе Excel мы выбираем конкретный источник данных, откуда нам их нужно получить, и перед нами открывается окошко самого Power Query с предпросмотром первых строчек загруженных данных (область 1). В верхней части окошка располагается Ribbon с командами по обработке данных (область 2). И в правой части экрана (область 3) у нас расположена панель с последовательностью всех действий, которые применяются к данным.

Возможности Power Query

У Power Query очень много возможностей и я хочу остановиться на некоторых из числа моих любимых.

Как я уже писал выше, Power Query замечателен тем, что позволяет подключаться к самым разным источникам данных. Так он позволяет загружать данные из CSV, TXT, XML, json файлов. Притом процесс выбора опций загрузки тех-же CSV файлов гибче и удобнее, чем он реализован штатными средствами Excel: кодировка автоматически выбирается часто правильно и можно указать символ разделителя столбцов.

Объединение файлов лежащих в папке

Power Query умеет забирать данные из указанной папки и объединять их содержимое в единые таблицы. Это может быть полезно, например, если вам периодически приходят какие-то специализированные отчеты за отдельный промежуток времени, но данные для анализа нужны в общей таблице. Гифка

Текстовые функции

Разделить столбец по символу или по количеству символов. И в отличие от Excel можно задать максимальное количество столбцов, а также направление откуда нужно считать символы — слева, справа.
Изменить регистр ячеек в столбце
Подсчитать количество символов в ячейках столбца.

Числовые функции

К столбцам с числовыми значениями по нажатию на кнопки на Ribbon можно применять:

Арифметические операции
Возводить в степени, вычислять логарифмы, факториалы, корни
Тригонометрические операции
Округлять до заданных значений
Определять четность и т.д.

Функции для работы с датами, временем и продолжительностью

К столбцам со значениями даты и времени по нажатию на кнопки на Ribbon можно применять:

Автоматическое определение формата вписанной даты (в excel c этим большая боль)
Извлекать в один клик номер месяца, дня недели, количество дней или часов в периоде и т.п.

Unpivot — Pivot

В интерфейсе Power Query есть функция “Unpivot”, которая в один клик позволяет привести данные с одной метрикой разложенные по столбцам по периодам к форме, которая будет удобна для использования в сводных таблицах (понимаю что трудно написал — смотрите пример). Также, есть функция с обратным действие Pivot. Гифка

Операция Merge — смерть ВПР

Функция ВПР (VLOOKUP) одна из наиболее используемых функций в MS Excel. Она позволяет подтягивать данные в одну таблицу из другой таблицы по единому ключу. И вот как раз для этой функции в Power Query есть гораздо более удобная альтернатива — операция Merge. При помощи этой операции соединение таблиц нескольких таблиц в одну по ключу (по простому или по составному ключу, когда соответствие нужно находить по нескольким столбцам) выполняется буквально в 7 кликов мыши без ввода с клавиатуры.

Операция Merge — это аналог join в sql, и ее можно настроить чтобы join был разных типов — Inner (default), Left Outer, Right Outer, Full Outer.

Upd.Мне тут подсказали, что Power Query не умеет делать Aproximate join, а впр умеет. Чистая правда, из коробки альтернатив нет. Гифка

Подключение к различным базам данных. Query Folding.

Power Query также замечателен тем, что умеет цепляться к самым разным базам данных — от MS SQL и MySQL до Postgres и HP Vertica. При этом, вам даже не нужно знать SQL или другой язык базы данных, т.к. предпросмотр данных отображается в интерфейсе Power Query и все те операции, которые выполняются в интерфейсе прозрачно транслируются в язык запросов к базе данных.

А еще в Power Query есть понятие Query Folding: если вы подключены к совместимой базе данных (на текущий момент это MS SQl), то тяжелые операции по обработке данных Power Query будет стараться выполнить на серверной стороне и забирать к себе лишь обработанные данные. Эта возможность радикально улучшает быстродействие многих обработок.

Язык программирования “М”

Надстройка Power Query — это интерпретатор нового, скриптового, специализированного для работы с данными, языка программирования М.

На каждое действие, которое мы выполняем с данными в графическом интерфейсе Power Query, в скрипт у нас пишется новая строчка кода. Отражая это, в панели с последовательностью действий (область 3), создается новый шаг с говорящим названием. Благодаря этому, используя панель с последовательностью действий, мы всегда можем посмотреть как выглядят у нас данные на каждом шаге обработки, можем добавить новые шаги, изменить настройки применяемой операции на конкретном шаге, поменять их порядок или удалить ненужные шаги. Гифка

Также, мы всегда можем посмотреть и отредактировать сам код написанного скрипта. И выглядеть будет он примерно так:

Язык M, к сожалению, не похож ни на язык формул в Excel, ни на MDX и, к счастью, не похож на Visual Basic. Однако, он очень прост в изучении и открывает огромные возможности по манипуляции данными, которые недоступны с использованием графического интерфейса.

Загрузка данных из Яндекс.Метрики, Google Analytics и прочих Api

Немного овладев языком “M” я смог написать программки в Power Query, которые умеют подключаться к API Яндекс.Метрики и Google Analytics и забирать оттуда данные с задаваемыми настройками. Программки PQYandexMetrika и PQGoogleAnalytics я выложил в опенсорс на гитхаб под лицензией GPL. Призываю пользоваться. И я буду очень рад, если эти программы будут дорабатываться энтузиастами.

Для Google Analytics подобного рода экспортеров в разных реализациях достаточно много, но вот для Яндекс.Метрики, насколько я знаю, мой экспортер был первым публично доступным, да еще и бесплатным :)

Power Query умеет формировать headers для post и get запросов и забирать данные из интернета. Благодаря этому, при должном уровне сноровки, Power Query можно подключить практически к любым API. В частности, я для своих исследований дергаю данные по телефонным звонкам клиентов из CallTouch API, из API сервиса по мониторингу активности за компьютером Rescuetime, занимаюсь парсингом нужных мне веб-страничек на предмет извлечения актуальной информации.

Еще раз про повторяемость и про варианты применения

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

Я занимаюсь веб-аналитикой и контекстной рекламой. И так уж получилось, что с момента, как я познакомился с Power Query в ее интерфейсах я провожу больше времени, чем в самом Excel. Мне так удобнее. Вместе с тем возросло и мое потребление другой замечательной надстройки в MS Excel — PowerPivot.

разбираю семантику для Толстых проектов,
Делаю частотные словари,
Создаю веб-аналитические дашборды и отчеты для анализа конкретных срезов,
Восстанавливаю достижение целей в системах веб-аналитики, если они не настроены на проекте,
Сглаживаю прогноз вероятности методами Андрея Белоусова (+Байеса:),
Делаю аудит контекстной рекламы на данных из K50 статистика,
И много других разных ad-hoc analysis задач, которые нужно сделать лишь однажды

Вот bi систему, про которую я рассказывал на Yac/M 2015 (видео) я делал полнстью при помощи Power Query и загружал данные внутрь PowerPivot.

Пару слов про локализацию

На сайте Microsoft для пользователей из России по умолчанию скачивается Power Query с переведенным на русским язык интерфейсом. К счастью, локализаторы до перевода на русский языка программирования (как это сделано с языком формул в excel) не добрались, однако жизнь пользователям неоднозначными переводами сильно усложнили. И я призываю вас скачивать, устанавливать и пользоваться английской версией Power Query. Поверьте, она будет гораздо понятнее.

У меня есть запрос ( Query1 ), загруженный в Excel с использованием соединения из Teradata . Обычно, когда я хочу обновить запрос, я должен щелкнуть его правой кнопкой мыши и выбрать «Изменить».

Оказавшись внутри Power Query Editor , я могу открыть Source и отредактировать SQL-скрипт внутри, чтобы обновить запрос.

Вопрос в том, как мне отредактировать этот сценарий SQL , используя VBA ?

При попытке использовать Macro Recorder , я могу только обновить запрос, используя

Попытка изменить CommandText также не удалась. Вот свойства запроса.

2 ответа

Сначала мы получаем формулу запроса, используя:

Причина, по которой мы должны это сделать, заключается в том, что строка формулы содержит некоторую дополнительную информацию о соединении с нашей базой данных, а не только сам SQL-запрос. Это может выглядеть примерно так:

Чтобы обновить запрос, мы устанавливаем ThisWorkbook.Queries.Item("Query1").Formula в новую строку формулы. Также будьте осторожны при обработке двойных кавычек внутри VBA. Вот скрипт, который я использовал:

Во-первых, замените ActiveWorkbook на ThisWorkbook , поскольку вы хотите работать с этой конкретной книгой (той, в которой хранится ваш код VBA), а не с какой-либо книгой, которая оказывается в настоящее время активен .

.Refresh - это метод соединения OLEDB, которое вы хотите изменить: у вас уже есть ссылка на соединение, для которого вы хотите изменить текст команды - все, что вам нужно, это вызвать метод, другой чем .Refresh против этого, то есть попробуй набрать эту точку и посмотреть, что этот объект может предложить.

Вы можете получить текст команды для этого OLEDBConnection объекта через его свойство CommandText - попробуйте ввести его в панель быстрого доступа (Ctrl + G) и посмотрим, если вы получите тот же результат:

После подключения книги Excel к внешнему источнику данных, такому как база данных SQL Server, база данных Access или другая книга Excel, вы можете поддерживать данные в своей книге в актуальном состоянии, обновляя ссылку на ее источник. Каждый раз, когда вы обновляете соединение, вы видите самые последние данные, включая все, что было новым или было удалено.

Давайте посмотрим, как обновить данные PowerPivot.

Появится окно « Обновление данных» , в котором отображаются все таблицы данных в модели данных и отслеживается ход обновления. После завершения обновления отображается состояние.

Обновите соединения данных

Автоматически обновлять данные

Здесь мы узнаем, как автоматически обновлять данные при открытии рабочей книги.

У вас также есть опция под этим: «Удалить данные из диапазона внешних данных перед сохранением рабочей книги» . Вы можете использовать эту опцию, чтобы сохранить книгу с определением запроса, но без внешних данных.

Каждый раз, когда вы открываете свою рабочую книгу, в вашу рабочую книгу будут загружаться новейшие данные.

Автоматически обновлять данные через равные промежутки времени

Включить фоновое обновление

Для очень больших наборов данных рекомендуется запустить фоновое обновление. Это возвращает вам контроль Excel, вместо того, чтобы заставлять вас ждать несколько минут, пока обновление не закончится. Вы можете использовать эту опцию, когда вы выполняете запрос в фоновом режиме. Однако вы не можете выполнить запрос для любого типа соединения, который извлекает данные для модели данных.

Читайте также: