Power Pivot — расширение для анализа данных в Excel¶

Power Pivot — расширение для Microsoft Excel, предназначенное для загрузки, моделирования и анализа больших объёмов данных из различных источников. Расширение входит в состав Excel начиная с версии 2010 (как надстройка) и с 2016 года доступно в составе редакций ProPlus и Enterprise, а также входит в набор инструментов Power BI. Основные возможности Power Pivot — работа с таблицами объёмом до нескольких гигабайт, построение сложных связанных моделей данных, использование языка формул DAX и создание сводных таблиц и сводных диаграмм на основе нескольких источников.

¶Возможности и назначение

Power Pivot расширяет стандартные возможности Excel в области работы с данными:

• Объём данных. В отличие от обычного листа Excel, ограниченного 1 048 576 строками, Power Pivot позволяет работать с моделями данных объёмом до нескольких гигабайт оперативной памяти (при 64-разрядной версии Excel).
• Связи между таблицами. Расширение поддерживает создание связей между таблицами по аналогии с реляционными базами данных, что позволяет строить отчёты на основе нескольких взаимосвязанных наборов данных.
• Язык DAX. Для расчётов используется язык формул DAX (Data Analysis Expressions), расширение синтаксиса Excel, поддерживающее функции агрегации, итерации, фильтрации и работу с иерархиями.
• Сжатие данных. Модель данных автоматически сжимается, что существенно уменьшает объём файла и ускоряет вычисления.
• Несколько источников. Данные можно импортировать из баз данных SQL Server, Oracle, Access, текстовых файлов, веб-страниц, служб OData, а также из файлов Excel.

¶История

Power Pivot появился в 2010 году как самостоятельная надстройка для Excel 2010 и был разработан компанией Microsoft на основе технологии, приобретённой вместе с компанией SQLBI. В 2012 году расширение вошло в состав SQL Server 2012 под названием Analysis Services Tabular, что позволило использовать те же модели данных на сервере. В версии Excel 2013 Power Pivot стал доступен пользователям редакций Professional Plus. Начиная с Office 2016 расширение включено в подписку Office 365 ProPlus. Технология Power Pivot легла в основу продукта Power BI — облачной платформы бизнес-аналитики Microsoft, запущенной в 2015 году.

¶Устройство и принцип работы

Power Pivot работает по принципу «загрузка — модель — отчёт». Пользователь загружает данные из одного или нескольких источников в модель данных, определяет связи между таблицами, создаёт вычисляемые поля и столбцы на DAX, после чего строит сводные таблицы и диаграммы. Расчётный движок VertiPaq, разработанный Microsoft, хранит данные в столбчатом сжатом формате, что обеспечивает высокую скорость агрегации и фильтрации.

Для добавления расширения в Excel 2010 требовалась отдельная установка надстройки. В современных версиях Excel оно активируется через меню «Файл» — «Параметры» — «Надстройки» — «Управление: COM-надстройки».

¶DAX: язык формул Power Pivot

DAX — основной язык вычислений в Power Pivot. Он похож на формулы Excel, но работает с целыми столбцами и таблицами, а не с отдельными ячейками. Основные категории функций DAX:

• агрегирующие (SUM, AVERAGE, MIN, MAX, COUNT);
• логические (IF, AND, OR, SWITCH);
• фильтрации (CALCULATE, FILTER, ALL, REMOVEFILTERS);
• временные и иерархические (RELATED, RELATEDTABLE, DATEADD, SAMEPERIODLASTYEAR).

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

¶Power Pivot и Power BI

Power Pivot и Power BI используют один и тот же движок моделирования и язык DAX. Модель данных, созданная в Excel с помощью Power Pivot, может быть экспортирована в Power BI Desktop или опубликована в службе Power BI. Это позволяет переносить готовые модели и отчёты в облачную среду и делиться ими с пользователями.

¶Ограничения

• Power Pivot не поддерживает редактирование ячеек модели данных напрямую — импортированные таблицы доступны только для чтения.
• Расширение не входит в редакции Excel Home и Student.
• Для работы с моделями данных объёмом более 2 ГБ требуется 64-разрядная версия Excel.
• Power Pivot не заменяет полноценные системы бизнес-аналитики и ориентирован на индивидуальное и групповое использование в рамках организации.

¶Применение

Power Pivot применяется в финансовом анализе, маркетинге, управлении запасами, кадровом учёте и других областях, где требуется объединять данные из нескольких источников и строить сводные отчёты. Расширение особенно востребовано среди аналитиков, бухгалтеров и менеджеров, работающих в среде Microsoft Office.

¶Источники

• Microsoft Learn — документация по Power Pivot для Excel
• Microsoft Learn — документация по DAX
• SQLBI — статьи и руководства по Power Pivot и DAX
• Microsoft Press — «Microsoft Excel 2016 Pivot Table Data Crunching»