2 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Подводные камни использования Excel Power Query и MySQL для автоматизации отчетности

Содержание

Подводные камни использования Excel Power Query и MySQL для автоматизации отчетности

Администратор

Группа: Главные администраторы
Сообщений: 14349
Регистрация: 12.10.2007
Из: Twilight Zone
Пользователь №: 1

Наступил новый 2016 год, а значит пора обновить инструменты для упрощения скучной механической работы. Отделы аналитики, маркетинга, продаж часто сталкиваются со следующими трудностями при обновлении отчетности:

1. Данные приходится собирать воедино из нескольких источников.

2. Отчеты составляются в Excel, что накладывает значительные ограничения на объем обрабатываемых данных.

3. Внесение изменений в заранее настроенные разработчиками выгрузки дело как правило не самое быстрое.

Если отчеты нужно обновлять еженедельно или даже ежедневно, то эта процедура становится весьма напряжной даже для самых терпеливых. С помощью надстройки Excel Power Query и записи данных в MySQL можно свести обновление большинства отчетов до простого нажатия кнопки «Обновить»:

1. Данные из любого количества источников импортируются через SQL-запросы в обычные таблицы Excel.

2. Даже из большой базы можно записывать в Excel только небольшую часть данных (например, итоговые суммы за нужный диапазон дат с группировкой только по нужным столбцам).

3. Изменения в отчет можно вносить просто поменяв SQL-запрос. Далее формируем нужный отчет стандартными средствами Excel.

В этой статье я покажу как настраивать и автоматически заполнять простые базы данных MySQL (на примере выгрузки статистики всех ключевых слов из Яндекс Метрики), а потом одной кнопкой обновлять отчеты в Excel, используя надстройку Power Query. Power Query имеет весьма странные особенности работы при составлении SQL-запросов (особенно динамических), которые мы разберем во второй части статьи.

Анализ «план-факт» с помощью надстройки Power Query Excel на примере ТРЦ

Рассмотрим возможности надстройки Power Query Excel для проведения анализа «план-факт» деятельности торгово-развлекательного центра.

План-фактный анализ (анализ «план-факт») актуален для большинства финансовых бюджетов[1]. Если необходимо детально изучить причины отклонений от плана, он применяется и для отдельных операционных и функциональных бюджетов.

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

План-фактный анализ представляет собой сравнение плановых и фактических показателей финансового бюджета и выявление причин отклонений.

Исходные данные для план-фактного анализа

Изучим возможности применения надстройки Power Query для анализа «план-факт» деятельности торгово-развлекательного центра (ТРЦ).

Для анализа «план-факт» будем использовать следующие данные:

1) планируемые величины статей доходов и расходов (бюджет доходов и расходов БДР);

2) фактические величины статей доходов и расходов (Отчет по доходам и расходам).

Аналитика статей доходов и расходов приведена в табл. 1 в разрезе бизнес-единиц и направлений их деятельности.

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

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

Еще одно значительное преимущество Power Query в том, что таблицы (модели данных), сформированные в этой надстройке, менее требовательны к ресурсам ПК за счет формирования таблиц на ссылках — принцип действия надстройки похож на связанные таблицы Excel.

Преимущества Power Query:

1. Можно использовать различные форматы исходных данных: xml, csv, xls, doc.

2. Можно загружать данные в таблицу Excel из различных источников:

  • Интернет;
  • базы данных SQL, Oracle, Access, IBM DB, Mysql, Sybase и т. д.;
  • веб-службы, протоколы, интерфейсы и облачные хранилища.

3. Возможность слияния нескольких таблиц в одну с установкой связей по определенным критериям

4. Поиск данных по каталогам.

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

Эти возможности используются также в надстройке Excel Power BI[3]., а также в надстройке Power Query Add-In, доступной для предыдущих версий Excel.

Основные этапы подключения и преобразования данных в Excel посредством Power Query представлены на схеме.

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

Подключение и преобразование данных

Чтобы использовать возможности надстройки Power Query, необходимо создать запрос (query) в рабочей книге Excel. Запрос позволяет подключить, просмотреть и преобразовать данные из различных источников. Затем преобразованные данные загружаются в таблицу (лист) Excel или во встроенную модель данных в Excel, далее при необходимости данные обновляются. Также есть возможности редактировать и пересылать сформированный запрос.

Анализ «план-факт» деятельности ТРЦ с помощью надстройки Power Query

Этап 1. Подготавливаем исходные данные для анализа «план-факт» в Power Query

Шаг 1. Формируем плановые показатели (лист книги Excel «Модель БДР план»)

Ежегодные показатели доходов и расходов, запланированные планово-экономическим подразделением ТРЦ на 5-летний период, приведены в табл. 3. Диапазону ячеек указанной плановой таблицы присваивается имя «БДР-план» (именованный диапазон для формирования соответствующего запроса [БДР-план]).

Шаг 2. Формируем фактические показатели (лист книги Excel «Модель Отчет факт»)

Ежегодные показатели доходов и расходов, зарегистрированные в отчетности ТРЦ в течение 5-летнего периода, приведены в табл. 3. Диапазону ячеек указанной отчетной таблицы присваивается имя «ОТЧЕТ-факт» именованный диапазон для формирования соответствующего запроса [Отчет-факт]).

Этап 2. Формируем запросы для план-фактного анализа в Power Query

Шаг 3. Формируем запрос [БДР-план] в модели данных

Запрос [БДР-план] включает в себя следующие операции (рис. 1):

1) выбор любой ячейки таблицы на листе «Модель БДР план»;

2) выбор вкладки «Данные» ленточного интерфейса Excel 2016;

3) выбор вида запроса «Из таблицы» в разделе «Скачать & преобразовать».

Power Query формирует проект запроса по данным таблицы «Модель БДР план»;

4) запрос именуется БДР-план, из меню ленты запроса выбирается «Закрыть и загрузить в…», применяя опции «Только создать подключение», «Добавить эти данные в модель данных»;

5) после подтверждения (нажимаем ОК) запрос [БДР план][4] загружается в модель данных[5].

Шаг 4. Формируем запрос [Отчет-факт] в модели данных

Запрос [Отчет-факт] включает следующие операции (аналогично запросу [БДР план]):

Читать еще:  Синхронизация папок с помощью Total Commander

1) выбор таблицы на листе «Модель Отчет факт»;

2) выбор вкладки «Данные» ленточного интерфейса Excel 2016;

3) выбор вида запроса «Из таблицы» в разделе «Скачать & преобразовать».

Power Query формирует проект запроса по данным таблицы «Модель Отчет факт»;

4) запрос именуется Отчет-факт, в меню выбирается «Закрыть и загрузить в…», применяя опции «Только создать подключение», «Добавить эти данные в модель данных». После подтверждения (нажимаем ОК) запрос [Отчет факт] загружается в модель данных.

Этап 3. Объединяем запросы для план-фактного анализа в Power Query

[1] БДР (бюджета доходов и расходов), БДДС (бюджета движения денежных средств), ББЛ (бюджета по балансовому листу).

[2] Дашборд (англ. Dashboard) — аналитический инструмент, наглядное представление информации о бизнес-процессах, о состоянии какого-то объекта в виде динамических онлайн-диаграмм.

[3] Power BI — это комплексное программное обеспечение бизнес-анализа (BI) компании Microsoft, объединяющее несколько программных продуктов, имеющих общий технологический и визуальный дизайн, соединителей (шлюзов), а также web-сервисов. Power BI относится к классу self-service BI, и BI с резидентным вычислением (англ. in-memory computing). Является частью единой платформы Microsoft Power Platform.

Ключевой и самый первый продукт линейки — Power BI Desktop состоит из трех интегрированных компонентов, имеющих каждый свой интерфейс:

  • Power Query (редактор запросов) — выполняет загрузку и очистку данных (ETL);
  • PowerPivot (наборы данных и модели данных) — интерфейс работы с табличными данными в оперативной памяти где выполняются запросы к данным, агрегация, расчёты и т. п.;
  • Power View — подсистема визуализации и построения отчётов (Reporting).

[4] Квадратными скобками (напр., [БДР план]) обозначены объекты модели данных.

[5] Выбор варианта (опции) загрузки запроса «Таблица» создаст дополнительную таблицу в книге Excel. Этот выбор имеет смысл после внесения всех данных анализа «план-факт» в Модель Данных»

Н. Н. Дворец,
аналитик ФГБУ «Росаккредагентство», канд. техн. наук

Материал публикуется частично. Полностью его можно прочитать в журнале «Справочник экономиста» № 6, 2019.

Power Query — что такое и почему её необходимо использовать в работе?

Power Query – специальная надстройка для для Excel 2010 и выше. Начиная с версии Excel 2016 эта надстройка встроена в Excel и все команды расположены на вкладке Данные (Data) -группа Скачать и преобразовать (Get & Transform)

Для 2010 и 2013 после подключения надстройки появится новая вкладка — POWER QUERY
Скачать Power Query для 2010 и 2013 можно по ссылке: http://go.microsoft.com/fwlink/?LinkID=313430
После скачивания необходимо закрыть Excel и запустить скачанный файл. После следующего запуска Excel на панели должна появится новая вкладка — POWER QUERY
Что же дает эта надстройка и почему её надо устанавливать? Если в общих чертах: Power Query является очень мощным инструментом и позволяет получить данные из множества различных источников: Excel, CSV, XML, бд Access и SQL, интернет-страницы, OneDrive, сервисы Google и многие другие:

список доступных подключений может быть изменен на усмотрение Microsoft. Как правило он расширяется

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

А далее с помощью редактора мы можем:

  • двумя щелчками мыши объединить данные двух и более столбцов с указанным разделителем
  • разделить данные одного столбца на несколько отдельных так же по разделителю
  • без создания всяких функций извлечь из даты только месяц, год, день, количество дней, день недели и т.п.
  • быстро объединить данные двух и более таблиц(запросов) по ключу. Грубо говоря, аналог ВПР по точному совпадению
  • нажатием одной кнопки развернуть столбцы таблицы в строки
  • еще куча всего, чего в одном обзоре не рассмотришь 🙂

Конечно, напрашивается вопрос: что будет, если данные в том же файле Excel изменятся? Например, добавятся строки? А все просто — надо будет лишь обновить запрос — он обновит подключение и данные будут опять обработаны и преобразованы с учетом изменений.

Напоследок еще несколько доводов в пользу Power Query:

  • она абсолютна бесплатна
  • раньше, чтобы создать полноценную сводную таблицу из нескольких файлов или листов необходимо было писать код на VBA. С помощью PowerQuery это может сделать каждый без всяких кодов(пошаговая видеоинструкция как это сделать приведена в статье План-фактный анализ в Excel при помощи Power Query)
  • можно легко и просто получить данные из файлов XML, текстовых файлов и CSV, а потом объединить всю информацию в одну таблицу и. Полагаю сами додумаете — главное собрать данные, а что с ними делать мы всегда придумаем исходя из задачи
  • можно получать данные из интернета — и это опять же без написания кодов Visual Basic for Applications(VBA). Тот же курс доллара PowerQuery способна «вытащить» на основании ссылки на сайт и обновлять это автоматически, да еще и «привязать» к расчетам в других таблицах
  • можно легко и быстро получить обновляемую информацию даже из файлов Google sheets, что до этого момента даже из VBA было занятием не из простых
  • есть встроенные возможности получения данных из аккаунта Facebook. Мне лично этого никогда не требовалось, но краем правого уха подмечал, что кому-то это очень было нужно 🙂

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

Статья помогла? Поделись ссылкой с друзьями!

Поиск по меткам

Поделитесь своим мнением

Комментарии, не имеющие отношения к комментируемой статье, могут быть удалены без уведомления и объяснения причин. Если есть вопрос по личной проблеме — добро пожаловать на Форум

Автоматизация отчетности при помощи SQL и Power BI

В своей прошлой статье я рассказывал про программные возможности языка SQL и обещал поделиться кейсом по созданию автоматизированного отчета на основе стека технологий MS SQL Server и Power BI.

Почему именно эти технологии?

За время работы аналитиком, я перепробовал различные варианты сбора отчетности. Начиная с ручной выгрузки данных из кабинетов рекламных систем, с последующим сведением в Excel, и заканчивая созданием специальных отчетов в Google Analytics или дашбордов в Data Studio.

Но ни один из вариантов не был идеальным и каждый имел свои недостатки. Все изменилось, когда я открыл для себя Power BI.

Microsoft Power BI — это один из самых технологичных на данный момент инструментов по визуализации данных, обладающий большим набором коннекторов к различным системам.

Но и Power BI сам по себе не идеален и без грамотного использования будет работать медленно и неэффективно. Приведу два примера:

  1. Если вы попытаетесь собрать модель данных из различных источников, с большим количеством связей и рассчитываемых показателей на стороне Power BI, то отчет будет жутко тормозить, а ведь именно таким принципам работы учит большое количество курсов по данному инструменту.
  2. Еще пример, если вы пытаетесь загрузить в модель данные из Google Analytics при помощи встроенного коннектора, то столкнетесь как минимум с двумя проблемами — ограничениями API GA и долгой выгрузкой данных.

Вышеописанные проблемы привели меня к мысли о загрузке всех данных сначала в базу, моделировании отчета при помощи SQL и только потом их визуализации в Power BI.

Переходим к делу

Для примера возьмем задачу по автоматизации отчета по эффективности контекстной рекламы.

К данному отчету заказчиком предъявляются следующие требования:

  • Отчет должен содержать исторические данные по вчерашний день;
  • Отчет должен обновляться ежедневно в автоматизированном режиме;
  • Помимо Power BI, должна быть возможность подключения к отчету через Excel.

Также отчет должен содержать следующие параметры и показатели:

Читать еще:  Как узнать, кому принадлежит домен? Получаем информацию о доменном имени в сервисе Whois

Естественно, все данные должны быть предварительно загружены в хранилище, но это тема отдельного поста и обычно этим занимаются data-инженеры. Мы же с вами аналитики и используем те данные, которые для нас любезно сложили в DWH (хранилище данных).

В моем случае DWH работает на базе MS SQL Server и содержит следующие таблицы:

  • sessions — данные из Google Analytics загруженные посредством коннектора к Reporting API v4;
  • costs — данные по расходам, предварительно загруженные в Google Analytics;
  • orders — данные по заказам и доходу из внутренней CRM-системы.

Для работы нам потребуется установить:

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

Готовим данные

Итак, задача понятна, инструменты готовы — за дело!

Создаем таблицу

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

Кстати, никакой сквозной аналитики у вас никогда не получится, если вы не умеете грамотно размечать рекламу utm-метками. О том как правильно ставить метки, читайте в одном из уроков бесплатного онлайн-курса «Digital-аналитика для новичков».

Но вернемся к задаче и после некоторых манипуляций с SQL получим вот такой скрипт:

Запустим его и порадуемся получившемуся результату:

Создаем таблицу

Скрипт работает и выдает отчет, в принципе его уже можно использовать для автоматизации вставив в Power BI при помощи встроенного коннектора. Но не советую так делать, потому что если данных в отчете будет много, например заказчик захочет посмотреть как работали рекламные кампании в течение года, на выполнение скрипта может уйти несколько часов.

Гораздо более правильным решением будет создать промежуточную таблицу в базе данных и докладывать туда ежедневно данные за прошедшие сутки. Что мы и сделаем:

Таблица будет иметь следующую структуру (подробнее о типах данных):

При сохранении таблицы укажем название:

И теперь, чтобы получить все данные из нее, достаточно выполнить простой SELECT :

Создаем хранимую процедуру

Отлично! Настало время автоматизации

А поможет нам в этом функционал хранимых процедур (подробнее рассказывал о них тут).

Засучим рукава и обернем наш скрипт в код процедуры:

Теперь протестируем и вручную вызовем процедуру:

Скорость отработки процедуры 3 секунды на одном дне — вполне приемлемо. Проверим появились ли данные в ранее созданной таблице:

Осталось настроить ежедневное обновление.

Настраиваем расписание

Настроим вызов нашей процедуры каждое утро по расписанию, благо в Management Studio для этого предусмотрена специальная служба под названием «Агент SQL Server».

Зайдем в агент и добавим новое задание:

Укажем название и придумаем описание:

Далее создадим новый шаг, в котором будем вызывать процедуру с данными за прошедшие сутки (обратите внимание, объявление переменных с датами из нашего скрипта мы перенесли в расписание и немного изменили):

Настраиваем время запуска, периодичность и сохраняем:

Теперь данные автоматически будут поступать в отчет ежедневно в 9 утра.

Визуализируем данные

Данные готовы, обновление настроено, самое время приступить к визуализации.

Останавливаться на том как установить Power BI и как им пользоваться не буду, так как этой теме посвящен целый урок нашего курса.

Создаем отчет

Заходим в desktop-версию Power BI и открываем коннектор к SQL Server:

Вводим данные для подключения к серверу, название базы данных и наш короткий SQL-запрос к ранее созданной табличке:

И это все! Никаких сложных моделей в Power BI строить не нужно, так как мы уже это сделали на стороне SQL-запроса.

Наиболее правильным считаю подход, когда инструмент визуализации используется именно для этой самой визуализации и еще для создания рассчитываемых показателей (например, CPC, CPO, ROMI). Используйте эти рекомендации и ваши отчеты будут летать.

После того как будет готов дизайн отчета, его нужно загрузить в облако Microsoft:

Настраиваем расписание

Отчет опубликован! Остался финальный шаг, для этого переходим в веб-версию Power BI и настраиваем расписание обновления.

Но перед этим не забываем поставить на компьютер, с которого будет происходить обновление, локальный шлюз Power BI (а лучше всего завести под это дело отдельную виртуальную машину):

Важно так подгадать расписание, чтобы оно запускалось в тот момент, когда на стороне SQL Server уже отработает наша процедура и положит в табличку свежие данные. Плюс нужно заложить небольшой запас времени, на возможные проблемы с сервером при его перегрузке:

Готово. Теперь можем пользоваться отчетом внутри веб-интерфейса, опубликовать отчет в интернете, либо отправить коллегам ссылку на него.

А как же Excel?

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

Для этого зайдем в Excel на вкладку «Данные» и создадим новое подключение к серверу баз данных:

После чего останется только указать SQL-запрос и сохранить:

С этого момента данные из нашей таблицы на сервере станут доступны в Excel.

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

«Power Query» в «Excel» — что это?

В данной статье я расскажу Вам о возможностях надстройки «Power Query».
Насколько мне известно, эта чудесная надстройка была разработана сторонними программистами (не Microsoft) несколько лет назад для программы «Эксель» версий 2013 и выше. В дальнейшем надстройка была приобретена компанией Microsoft и уже в 2016 офисе инструменты «Power Query» стали неотъемлемой частью панели инструментов «Excel»

Для чего нужна и что может надстройка «Power Query»?

«Power Query» помогает пользователям формировать запросы по аналогии с базами данных MS «Access». Что в конечном итоге дает возможность формирования таблиц, в котороые информация загружается из разных источников и выстраивается в нужном порядке.

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

Еще одно значительное преимущество «Power Query» в том, что таблицы, сформированные в этой надстройке, менее требовательны к ресурсам ПК за счет формирования таблиц на ссылках — принцип действия похож на связанные таблицы «Excel».

Возможности «Power Query» «Excel».

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

  • Интернет;
  • Файлы форматов: XML, CSV, таблицы Excel, текстовые документы;
  • Из баз данных: SQL, Oracle, Access, IBM DB, Mysql, Sybase и т.д.;
  • Из Azure;
  • Из веб служб и интерфейсов: facebook, MS Exchange Online, облачных хранилищ;
  • Возможность слияния нескольких таблиц в одну с установкой связей по определенным критериям.

    Выполнять поиск по каталогам.

    Автоматически «собирать» и обновлять таблицу из нескольких расположенных в одной папке.

    Где скачать надстройку «Power Query» для «Excel 2013».

    Скачивается надстройка «Power Query» с официального сайта Microsoft по ссылке приведенной ниже:

    Перед установкой следует обратить внимание на требования к системе ПК.

    Импорт и консолидация таблиц Excel через Power Query

    Power Query умеет подключаться к разным источникам. Далее рассмотрим, как получить данные из книги Excel.

    Таблицы Excel

    Лучше всего данные хранить в таблице Excel, это самый удобный и распространенный источник для Power Query. На ленте даже есть специальная кнопка.

    Чтобы загрузить таблицу в редактор Power Query, достаточно выделить любую ее ячейку и нажать Данные → Получить и преобразовать данные → Из таблицы/диапазона.

    Примечание. В вашей версии Excel расположение кнопок и их названия могут отличаться.

    Если то же самое проделать с обычным диапазоном, то Excel вначале преобразует диапазон в таблицу Excel, а потом запустит Power Query.

    Читать еще:  Как поставить пароль на документ Word

    Запросу присваивается имя таблицы Excel, которая является источником данных. Поэтому желательно сразу дать таблице говорящее название. Не обязательно, конечно, но желательно. В противном случае рекомендуется переименовать сам запрос, чтобы затем его можно было легко найти среди других запросов книги.

    Данные находятся в Power Query. Новые значения, внесенные в исходную таблицу, автоматически попадут в запрос после его обновления. Далее в редакторе Power Query делают обработку данных и выгружают либо в виде таблицы Excel, либо оставляют в памяти Excel в виде подключения.

    Именованный диапазон Excel

    Источником для Power Query может быть не только таблица Excel. Например, вы получили красивый отформатированный отчет и не хотите вносить в него изменения. Тогда нужно использовать именованный диапазон. Самый простой способ создать именованный диапазон – это выделить область на листе и ввести название в поле Имя.

    Либо выполнить команду Формулы → Определенные имена → Присвоить имя. В Excel будет создан новый объект, к которому можно обращаться, например, в формулах. Диапазон виден в Диспетчере имен.

    Здесь перечислены все именованные диапазоны, формулы и таблицы. Среди них есть и только что созданный Отчет.

    Теперь можно стать на любую ячейку внутри именованного диапазона (или выбрать его из выпадающего списка в поле Имя) и вызвать ту же команду: Данные → Получить и преобразовать данные → Из таблицы/диапазона. Произойдет загрузка данных в Power Query.

    Такой способ позволяет «не портить» исходные данные. Но у него есть и очевидный недостаток: новые строки, которые выйдут за пределы именованного диапазона, не попадут в запрос.

    Динамический именованный диапазон Excel

    Решить данную проблему можно, создав динамический именованный диапазон. Это такой диапазон, который задается формулой и автоматически расширяется до последней заполненной ячейки.

    Внести статичное имя в поле Имя на этот раз не получится. Поэтому заходим в Формулы → Определенные имена → Задать имя (или нажимаем Создать в Диспетчере имен), указываем название будущего динамического диапазона ДинамОтчет и внизу вместо ссылки записываем формулу:

    Ко всем ссылкам этой формулы Excel еще автоматически добавит название листа.

    Смысл формулы следующий. Верхняя левая ячейка диапазона фиксируется ($A$2), а правая нижняя определяется формулой, которая возвращает адрес последней заполненной строки в столбце B.

    Но не все так просто. Excel видит это имя лишь как формулу, а не диапазон. Как же его увидит Power Query? Делаем ход конем.

    Создаем пустой запрос Power Query Данные → Получить и преобразовать данные → Получить данные → Из других источников → Пустой запрос. Открывается пустой запрос, где в строке формул нужно ввести:

    После ввода формулы (нажатием Enter) Power Query обратится к текущей книге и выведет все объекты, среди которых есть и наш динамический диапазон ДинамОтчет.

    Название запроса не подхватывается, поэтому придется изменить самостоятельно.

    Чтобы извлечь содержимое объекта, в этой же строке правой кнопкой мыши кликаем по Table, далее выбираем Детализация.

    Power Query разворачивает таблицу и даже делает некоторые шаги обработки: повышает заголовки и задает нужный формат для столбцов.

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

    Вот такие приемы импорта данных в Power Query из книги Excel. Самый распространенный из них – это импорт из таблицы Excel. Тем не менее, в случае необходимости можно прибегнуть к альтернативам, создав именованный или динамический именованный диапазон.

    Консолидация данных из разных таблиц Excel

    Одна из насущных задач, с которыми сталкиваются пользователи, – консолидация данных. Под консолидацией понимается объединение нескольких таблиц в одну. До появления Power Query это была довольно трудоемкая операция, особенно, если процесс требовал автоматизации. Хотя в эксель есть специальная команда Данные → Работа с данными → Консолидация, пользоваться ей не удобно. Мне, по крайней мере. Появление Power Query в корне изменило представление о том, как нужно объединять таблицы.

    Рассмотрим пример. В некоторый файл каждый месяц вносится отчет о продажах в формате таблицы Excel. Каждая таблица при этом имеет соответствующее название: Январь_2018, Февраль_2018 и т.д. Необходимо объединить все таблицы книги в одну. Как бы скопировать и вставить одну под другой, создав при этом дополнительный столбец, указывающий, к какой таблице принадлежит конкретная строка. Задача не одноразовая, а с заделом на будущее, поэтому нужно предусмотреть появление в этом файле новых таблиц.

    Процесс начинается с запуска пустого запроса: Данные → Получить и преобразовать данные → Создать запрос → Из других источников → Пустой запрос

    Затем в строке формул вводим знакомую команду

    Power Query показывает все таблицы в текущей книге.

    Их нужно развернуть кнопкой с двумя стрелками в названии поля Content (на скриншоте ниже выделено красным кружком).

    Если есть лишние столбцы, то их можно не выводить, сняв соответствующую галку. Также лучше убрать галку напротив опции Использовать исходное имя столбца как префикс. Нажимаем Ok.

    Все таблицы находятся на одном листе, а рядом колонка с названием источника, откуда взята каждая строка.

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

    Визуально мы наблюдаем и месяц, и год. Но Power Query такое название воспринимает, как текст. Поэтому делаем следующее.

    Удалим нижнее подчеркивание. Правой кнопкой мыши по названию столбца Name → Замена значений.

    В следующем окне настроек указываем, что меняем _ на пусто, то есть в нижнем поле ничего не указываем.

    Подчеркивание удаляется из названия.

    Поиск и замена здесь работает так же, как и в обычном Excel.

    Далее запускаем команду Преобразование → Столбец «Дата и время» → Дата → Выполнить анализ.

    Power Query распознает дату и меняет формат колонки. Мы также переименовываем столбец на Период.

    Полученную таблицу можно использовать для анализа данных. Выгрузим ее на лист Excel.
    Главная → Закрыть и загрузить.

    Но что-то пошло не так. Во-первых, внизу таблицы пустая строка; во-вторых, при выгрузке произошла одна ошибка. Обновим запрос (справа от названия запроса значок обновления).

    Что-то еще больше пошло не так. Даты исчезли, снизу таблицы добавились новые строки, а количество ошибок уже 19. Спокойствие, только спокойствие! Дело вот в чем.

    Помните, на первом шаге мы получили все таблицы из файла? Так ведь и выгруженная таблица – это тоже таблица! Получается, Power Query взял 3 исходных таблицы, обработал, выгрузил на лист Excel и на следующем круге видит уже 4 таблицы!

    При повторном обновлении запрос захватывает их все, а т.к. таблица выхода имеет другую структуру, то возникают ошибки.

    Короче, из запроса нужно исключить таблицу, которая получается на выходе (Запрос1). Есть разные подходы, самый простой – это добавить шаг фильтрации. Выделяем в правой панели первый шаг Источник, открываем фильтр в колонке с названиями, снимаем галку с таблицы Запрос1 → Ok.

    Снова выгружаем таблицу в Excel и на этот раз все в порядке.

    Сделаем с помощью сводной таблицы маленький отчет по месяцам.

    Прошло время, и в файл добавили новую таблицу с продажами за апрель.

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

    А вот, как это выглядит при использовании Power Query.

    Достаточно два раза нажать кнопку Обновить все (первый раз – для обновления запроса, второй – для сводной таблицы).

    На добавление в отчет новых данных вместе с их обработкой потребовалось несколько секунд.

    Вот за это мы так любим Power Query.

  • Ссылка на основную публикацию
    Статьи c упоминанием слов:
    Adblock
    detector