Надстройки Excel: Power Query, Pivot, Maps, View

Если вы часто работаете в MS Excel, то возможно знаете о таких надстройках, как Power Query, Pivot, Maps, View. Надстройки расширяют диапазон команд и возможностей офисного пакета.

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

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 следующие:

Сначала мы загружаем данные в 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 и т.д.).

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.