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

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

Содержание

Что такое Power Query / Pivot / Map / View / BI и зачем они пользователю Excel

Термины «Power Query», «Power Pivot», «Power BI» и прочие «пауэры» все чаще всплывают в статьях и материалах о Microsoft Excel. По моему опыту, далеко не все ясно представляют себе что скрывается за этими понятиями, как они между собой взаимосвязаны и как могут помочь простому пользователю Excel.

Давайте проясним ситуацию.

Power Query

Еще в 2013 году специально созданная группа разработчиков внутри Microsoft выпустила для Excel бесплатную надстройку Power Query (другие названия — Data Explorer, Get&Transform), которая умеет массу полезных для повседневной работы вещей:

  • Загружать данные в Excel из почти 40 различных источников, среди которых базы данных (SQL, Oracle, Access, Teradata. ), корпоративные ERP-системы (SAP, Microsoft Dynamics, 1C. ), интернет-сервисы (Facebook, Google Analytics, почти любые сайты).
  • Собирать данные из файлов всех основных типов данных (XLSX, TXT, CSV, JSON, HTML, XML. ), как поодиночке, так и сразу оптом — из всех файлов указанной папки. Из книг Excel можно автоматически загружать данные сразу со всех листов.
  • Зачищать полученные данные от «мусора»: лишних столбцов или строк, повторов, служебной информации в «шапке», лишних пробелов или непечатаемых символов и т.п.
  • Приводить данные в порядок: исправлять регистр, числа-как-текст, заполнять пробелы, добавлять правильную «шапку» таблицы, разбирать «слипшийся» текст на столбцы и склеивать обратно, делить дату на составляющие и т.д.
  • Всячески трансформировать таблицы, приводя их в желаемый вид (фильтровать, сортировать, менять порядок столбцов, транспонировать, добавлять итоги, разворачивать кросс-таблицы в плоские и сворачивать обратно).
  • Подставлять данные из одной таблицы в другую по совпадению одного или нескольких параметров, т.е. прекрасно заменяет функцию ВПР (VLOOKUP) и ее аналоги.

Power Query встречается в двух вариантах: как отдельная надстройка для Excel 2010-2013, которую можно скачать с официального сайта Microsoft и как часть Excel 2016. В первом случае после установки в Excel появляется отдельная вкладка:

В Excel 2016 весь функционал Power Query уже встроен по умолчанию и находится на вкладке Данные (Data) в виде группы Получить и преобразовать (Get & Transform) :

Возможности этих вариантов совершенно идентичны.

Принципиальной особоенностью Power Query является то, что все действия по импорту и трансформации данных запоминаются в виде запроса — последовательности шагов на внутреннем языке программирования Power Query, который лаконично называется «М». Шаги можно всегда отредактировать и воспроизвести повторно любое количество раз (обновить запрос).

Основное окно Power Query обычно выглядит примерно так:

По моему мнению, это самая полезная для широкого круга пользователей надстройка из всех перечисленных в этой статье. Очень много задач, для которых раньше приходилось либо жутко извращаться с формулами, либо писать макросы — теперь легко и красиво делаются в Power Query. Да еще и с последующим автоматическим обновлением результатов. А учитывая бесплатность, по соотношению «цена-качество» Power Query просто вне конкуренции и абсолютный must have для любого средне-продвинутого пользователя Excel в наши дни.

Power Pivot

Power Pivot — это тоже надстройка для Microsoft Excel, но предназначенная немного для других задач. Если Power Query сосредоточена на импорте и обработке, то Power Pivot нужен, в основном, для сложного анализа больших объемов данных. В первом приближении, можно думать о Power Pivot как о прокачанных сводных таблицах.

Общие принципы работы в Power Pivot следующие:

  1. Сначала мы загружаем данные в Power Pivot — поддерживается 15 различных источников: распространенные БД (SQL, Oracle, Access. ), файлы Excel, текстовые файлы, веб-каналы данных. Кроме того, можно использовать Power Query как источник данных, что делает анализ почти всеядным.
  2. Затем между загруженными таблицами настраиваются связи или, как еще говорят, создается Модель Данных. Это позволит в будущем строить отчеты по любым полям из имеющихся таблиц так, будто это одна таблица. И никаких ВПР опять же.
  3. При необходимости, в Модель Данных добавляют дополнительные вычисления с помощью вычисляемых столбцов (аналог столбца с формулами в «умной таблице») и мер (аналог вычисляемого поля в сводной). Всё это пишется на специальном внутреннем языке Power Pivot, который называется DAX (Data Analysis eXpressions).
  4. На листе Excel по Модели Данных строятся интересующие нас отчеты в виде сводных таблиц и диаграмм.
Читать еще:  Как исправить ошибку Download interrupted, сопровождающую сбой установки расширений в браузер Google Chrome

Главное окно Power Pivot выглядит примерно так:

А так выглядит Модель Данных, т.е. все загруженные таблицы с созданными связями:

У Power Pivot есть ряд особенностей, делающих её уникальным инструментом для некоторых задач:

  • В Power Pivot нет предела по количеству строк (как в Excel). Можно грузить таблицы любого размера и спокойно работать с ними.
  • Power Pivot очень хорошо умеет сжимать данные при загрузке их в Модель. 50 Мб исходный текстовый файл может легко превратиться в 3-5 Мб после загрузки.
  • Поскольку «под капотом» у Power Pivot, по сути, полноценный движок базы данных, то с большими объемами информации он справляется очень быстро. Нужно проанализировать 10-15 млн. записей и построить сводную? И все это на стареньком ноутбуке? Без проблем!

К сожалению, пока что Power Pivot входит не во все версии Excel. Если у вас Excel 2010, то скачать её можно бесплатно с сайта Microsoft. А вот если у вас Excel 2013-2016, то всё зависит от вашей лицензии, т.к. в некоторых вариантах она включена (Office Pro Plus, например), а в некоторых нет (Office 365 Home, Office 365 Personal и т.д.) Подробнее об этом можно почитать тут.

Power Maps

Эта надстройка впервые появилась в 2013 году и первоначально называлась GeoFlow. Она предназначена для визуализации гео-данных, т.е. числовой информации на географических картах. Исходные данные для отображения берутся все из той же Модели Данных Power Pivot (см. предыдущий пункт).

Демо-версию Power Map (почти не отличающуюся от полной по возможностям, кстати) можно совершенно бесплатно загрузить опять же с сайта Microsoft. Полная же версия включена в некоторые пакеты Microsoft Office 2013-2016 вместе с Power Pivot — в виде кнопки 3D-карта на вкладке Вставка (Insert — 3D-map) :

Ключевые особенности Power Map:

  • Карты могут быть как плоскими, так и объемными (земной шар).
  • Можно использовать несколько разных типов визуализации (гистограммы, пузырьковые диаграммы, тепловые карты, заливку областями).
  • Можно добавлять измерение времени, т.е. анимировать процесс и смотреть на него в развитии.
  • Карты подгружаются из сервиса Bing Maps, т.е. для просмотра нужен весьма шустрый доступ в интернет. Иногда возникают сложности с правильным распознаванием адресов, т.к. названия в данных не всегда совпадают с Bing Maps.
  • В полной (не демо) версии Power Map можно использовать собственные загружаемые карты, например визуализировать посетителей торгового центра или цены на квартиры в жилом доме прямо на строительном плане.
  • На основе созданных гео-визуализаций можно прямо в Power Map создавать видеоролики (пример), чтобы поделиться ими потом с теми, у кого надстройка не установлена или включить в презентацию Power Point.

Power View

Эта надстройка появилась впервые в составе Excel 2013 и предназначена для «оживления» ваших данных — построения интерактивных графиков, диаграмм, карт и таблиц. Иногда для этого используют термины дашборд (dashboard) или панель показателей (scorecard) . Суть в том, что вы можете вставить в ваш файл Excel специальный лист без ячеек — слайд Power View, куда добавить текст, картинки и массу различного типа визуализаций по вашим данным из Модели Данных Power Pivot.

Выглядеть это будет примерно так:

Нюансы тут такие:

  • Исходные данные берутся всё оттуда же — из Модели Данных Power Pivot.
  • Для работы с Power View необходимо установить на вашем компьютере Silverlight — майкрософтовский аналог Flash (бесплатный).

На сайте Microsoft, кстати, есть весьма приличный обучающий курс по Power View на русском языке.

Power BI

В отличие от предыдущих, Power BI — это не надстройка для Excel, а отдельный продукт, представляющий собой целый комплекс средств для бизнес- анализа и визуализации. Он состоит из трех ключевых элементов:

1. Power BI Desktop — программа для анализа и визуализации данных, включающая в себя, помимо прочего, весь функционал надстроек Power Query и Power Pivot + улучшенные механизмы визуализации из Power View и Power Map. Скачать и установить её можно совершенно бесплатно с сайта Microsoft.

В Power BI Desktop можно:

  • Загружать данные из более чем 70 различных источников (как в Power Query + дополнительные коннекторы).
  • Связывать таблицы в модель (как в Power Pivot)
  • Добавлять к данным дополнительные вычисления с помощью мер и вычисляемых столбцов на DAX (как в Power Pivot)
  • Создавать на основе данных красивейшие интерактивные отчеты с разного типа визуализациями (очень похоже на Power View, но еще лучше и мощнее).
  • Публиковать созданные отчеты на сайте Power BI Service (см. следующий пункт) и делиться ими с коллегами. Причем есть возможность давать разные права (чтение, редактирование) разным людям.
Читать еще:  Деловая переписка на английском языке: фразы и советы

2. Онлайн-сервис Power BI — упрощенно говоря, это сайт, где у вас и у каждого пользователя в вашей компании будет своя «песочница» (workspace) куда можно загружать созданные в Power BI Desktop отчеты. Помимо просмотра, позволяет их даже редактировать, воспроизводя онлайн почти весь функционал Power BI Desktop. Также сюда можно заимствовать отдельные визуализации из чужих отчетов, собирая из них свои авторские дашборды.

Выглядит это примерно так:

3. Power BI Mobile — приложение для iOS / Android / Windows для подключения к Power BI Service и удобного просмотра (не редактирования) созданных отчетов и дашбордов прямо на экране телефона или планшета. Скачать его (совершенно бесплатно) можно тут.

На iPhone, например, созданный выше отчет выглядит так:

Причем всё это с сохранением интерактивностии и анимации + заточенность под тач и рисование по экрану пером. Очень удобно. Таким образом, бизнес-аналитика становится доступной всем ключевым лицам компании в любой момент и в любом месте — нужен только доступ в интернет.

Тарифные планы Power BI. Power BI Desktop и Mobile бесплатны изначально, большинство функций Power BI Service — тоже. Так что для персонального использования или применения в пределах небольшой компании за всё вышеперечисленное не нужно платить ни копейки и можно смело оставаться на плане Free. Если вы хотите делиться отчетами с коллегами и администрировать их права доступа, то придется перейти на Pro (10$ в месяц за пользователя). Есть еще Premium — для больших компаний (>500 пользователей), которым требуются для данных отдельные хранилища и серверные мощности.

Рубрики

Рассмотрим возможности надстройки 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. Формируем запрос [Отчет-факт] в модели данных

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

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» в «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 по ссылке приведенной ниже:

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

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