Термины «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 можно автоматически загружать данные сразу со всех листов.
- Убирать полученные данные из «мусора»: лишние столбцы или строки, повторы, служебная информация в «заголовке», лишние пробелы или непечатаемые символы и т.п.
- Перенесите данные в заказ: исправлять регистр, числа как текст, заполнять пробелы, добавлять правильную «шапку» таблицы, разбирать «прилипший» текст на столбцы и склеивать обратно, делить дату на составляющие и т.д.
- всеми возможными способами трансформировать таблицы, приведя их в нужный вид (фильтровать, сортировать, менять порядок столбцов, транспонировать, добавлять итоги, разворачивать кросс-таблицы в плоское и сворачивать обратно).
- Замените данные из одной таблицы в другую, сопоставив один или несколько параметров, т.е. хорошая функция замены. ВПР (ВПР) и его аналоги.
Power Query встречается в двух версиях: как отдельная надстройка для Excel 2010-2013, которую можно скачать с официального сайта Microsoft, и как часть Excel 2016. В первом случае после установки появляется отдельная вкладка в Эксель:
В Excel 2016 весь функционал Power Query уже встроен по умолчанию и находится на вкладке Данные (Дата) как группа Получите и конвертируйте (Получить и преобразовать):
Возможности этих вариантов полностью идентичны.
Фундаментальной особенностью Power Query является то, что все действия по импорту и преобразованию данных сохраняются в виде запроса — последовательности шагов внутреннего языка программирования Power Query, который лаконично называется «М». Шаги всегда можно редактировать и воспроизводить любое количество раз (обновить запрос).
Главное окно Power Query обычно выглядит примерно так:
На мой взгляд, это самое полезное дополнение из перечисленных в этой статье для широкого круга пользователей. Множество задач, для решения которых приходилось либо жутко извращаться с формулами, либо писать макросы, теперь легко и красиво выполняются в Power Query. Да, и с последующим автоматическим обновлением результатов. А учитывая его бесплатность, по соотношению цена-качество Power Query просто вне конкуренции и в наши дни является абсолютной необходимостью для любого пользователя Excel среднего уровня.
PowerPivot
Power Pivot — это также надстройка для Microsoft Excel, но предназначенная для немного других задач. Если Power Query ориентирован на импорт и обработку, то Power Pivot нужен в основном для комплексного анализа больших объемов данных. В первом приближении вы можете думать о Power Pivot как о причудливой сводной таблице.
Общие принципы работы в Power Pivot следующие:
- Мы первые Загрузка данных в Power Pivot — поддерживаются 15 различных источников: общие базы данных (SQL, Oracle, Access…), файлы Excel, текстовые файлы, каналы данных. Кроме того, в качестве источника данных можно использовать Power Query, что делает анализ практически всеядным.
- Затем между загруженными таблицами соединения настроены или, как говорится, создан Модель данных. Это позволит в будущем строить отчеты по любым полям из существующих таблиц, как если бы это была одна таблица. И снова никакого ВПР.
- При необходимости в Модель данных добавляются дополнительные расчеты с помощью вычисляемые столбцы (аналог столбца с формулами в «умной таблице») и меры (аналог вычисляемого поля в сводке). Все это написано на специальном внутреннем языке Power Pivot под названием DAX (Data Analysis eXpressions).
- На листе Excel согласно модели данных строятся интересующие нас отчеты в виде поворотные столы и диаграммы.
Главное окно 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 и т. д.). Подробнее об этом можно прочитать здесь.
Карты силы
Это дополнение впервые появилось в 2013 году и первоначально называлось GeoFlow. Он предназначен для визуализации геоданных, то есть числовой информации на географических картах. Исходные данные для отображения берутся из той же модели данных Power Pivot (см. предыдущий пункт).
Демо-версию Power Map (кстати, почти ничем не отличающуюся от полной) можно скачать совершенно бесплатно опять же с сайта Microsoft. Полная версия включена в некоторые пакеты Microsoft Office 2013-2016 вместе с Power Pivot — в виде кнопки. 3D карта таб Вставить (Вставка — 3D-карта):
Ключевые особенности Power Map:
- Карты может быть как плоским, так и объемным (глобус).
- Вы можете использовать несколько разных типы визуализации (гистограммы, пузырьковые диаграммы, тепловые карты, заливка областей).
- Вы можете добавить измерение времени, то есть анимировать процесс и наблюдать за его развитием.
- Карты загружаются из сервиса Bing Maps, т.е. для просмотра необходимо очень быстрое подключение к Интернету. Иногда возникают трудности с правильным распознаванием адресов, т.к. имена в данных не всегда совпадают с именами в Картах Bing.
- В полной (не демо-версии) Power Map вы можете использовать свои собственные загружаемые картыНапример, визуализировать посетителей торгового центра или цены на квартиры в жилом доме прямо на плане здания.
- На основе созданных геовизуализаций вы можете создавать видеоролики непосредственно в Power Map (пример), чтобы позже поделиться ими с теми, у кого надстройка не установлена, или включить их в презентацию Power Point.
взгляд на власть
Эта надстройка, впервые представленная в Excel 2013, предназначена для оживления ваших данных с помощью интерактивных графиков, диаграмм, карт и таблиц. Иногда для этого используются термины. приборная панель (панель приборов) or приборная панель (оценочная карта). Суть в том, что вы можете вставить в ваш файл Excel специальный лист без ячеек — слайд Power View, куда вы можете добавлять текст, картинки и множество различных типов визуализаций на основе ваших данных из модели данных Power Pivot.
Он будет выглядеть примерно так:
Нюансы здесь такие:
- Исходные данные берутся оттуда же — из модели данных Power Pivot.
- Для работы с Power View вам необходимо установить на компьютер Silverlight – аналог Flash от Microsoft (бесплатно).
На сайте Microsoft, кстати, есть очень приличный курс обучения по Power View в .
Power BI
В отличие от предыдущих, Power BI — это не надстройка для Excel, а отдельный продукт, представляющий собой целый набор инструментов для бизнес-анализа и визуализации. Он состоит из трех ключевых элементов:
1. Рабочий стол Power BI — программа для анализа и визуализации данных, включающая, помимо прочего, весь функционал дополнений Power Query и Power Pivot + улучшенные механизмы визуализации из Power View и Power Map. Вы можете бесплатно скачать и установить его с сайта Microsoft.
В Power BI Desktop вы можете:
- Загрузить данные из более чем 70 различных источников (как в Power Query + дополнительные коннекторы).
- связывать таблицы для моделирования (например, в Power Pivot)
- Добавьте дополнительные вычисления к данным с помощью меры и вычисляемые столбцы в DAX (как в Power Pivot)
- Создавайте красивые на основе данных интерактивные отчеты с разными типами визуализаций (очень похоже на Power View, но еще лучше и мощнее).
- Опубликовать создавал отчеты на сайте службы Power BI (см. следующий пункт) и делился ими с коллегами. Более того, можно давать разные права (чтение, редактирование) разным людям.
2. Онлайн-сервис Power BI. — проще говоря, это сайт, на котором у вас и у каждого пользователя вашей компании будет своя «песочница» (рабочее пространство), куда вы сможете загружать отчеты, созданные в Power BI Desktop. Помимо просмотра, он даже позволяет их редактировать, воспроизводя практически весь функционал Power BI Desktop онлайн. Также здесь можно позаимствовать отдельные визуализации из чужих отчетов, собрав из них свои авторские дашборды.
Это выглядит примерно так:
3. Power BI Мобильная версия — приложение для iOS/Android/Windows для подключения к Сервису Power BI и удобного просмотра (не редактирования) созданных отчетов и информационных панелей прямо на экране вашего телефона или планшета. Скачать его (совершенно бесплатно) можно здесь.
Например, на iPhone созданный выше отчет выглядит так:
И все это с сохранением интерактивности и анимации + заточен под прикосновения и рисование пером на экране. Очень удобно. Таким образом, бизнес-аналитика становится доступной всем ключевым лицам компании в любое время и в любом месте – нужен только доступ в Интернет.
Тарифные планы Power BI. Power BI Desktop и Mobile изначально бесплатны, как и большинство функций службы Power BI. Таким образом, для личного использования или использования в небольшой компании вам не нужно платить ни копейки за все вышеперечисленное, и вы можете спокойно оставаться на плане. Бесплатно. Если вы хотите поделиться отчетами с коллегами и управлять их правами доступа, вам придется перейти на БЕСС (10 долларов США в месяц за пользователя). Есть ли еще Премиум – для крупных компаний (>500 пользователей), которым необходимы отдельные хранилища и серверные мощности для данных.
- Проект диаграммы Ганта в Excel с Power Query
- Как создать базу данных в Excel с помощью Power Pivot
- Визуализация движения по маршруту на карте в Power Map