Excel Power Pivot: как пользоваться, для чего нужна и как работает надстройка

Что такое Power Pivot

Power Pivot — это надстройка Excel, которая упрощает аналитику. В некоторых версиях Эксель Power Pivot уже предустановлена, а в некоторых нужно её скачать.

Работает надстройка на языке DAX (Data Analysis Expressions) — он также используется в Power BI и Analysis Services. В Эксель формулы применяются к отдельным ячейкам, а в DAX — к таблицам.

Power Pivot используют те, кто работает с Excel: она ускоряет обработку данных и позволяет работать с большим объёмом информации из разных источников и сводных таблиц.

Эту надстройку применяют аналитики, так как она:

• анализирует большие таблицы быстрее, чем стандартные инструменты Эксель;

• работает с таблицами, которые включают миллионы строк, без потери производительности;

• подключается к внешним файлам;

• может создавать сложные вычисления и включать в расчёты многомерные данные.

Системные требования для Power Pivot

Чтобы пользоваться Power Pivot, убедитесь, что надстройки поддерживаются версией Excel: они доступны в Excel 2010, 2013, 2016, 2019 и Office 365.

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

• рекомендуемая операционная система: Windows 10;

• ОЗУ: не менее 4 ГБ;

• Эксель: версия с поддержкой надстроек.  

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

Как установить Power Pivot

Следуя этой инструкции, вы сможете добавить Power Pivot в Excel за 10–15 минут и сразу приступить к работе с данными.

Шаг 1: Проверка версии Excel

Современные версии Эксель, такие как Excel 2016 или Office 365, уже включают Power Pivot.

Шаг 2: Установка Power Pivot

Для Эксель 2010 и 2013 необходимо скачать надстройку с официального сайта Microsoft и установить её, следуя инструкциям мастера установки.  

Шаг 3: Активация надстройки в Excel

Чтобы начать работать с Powerpivot, достаточно выполнить простой процесс активации надстройки.

Перейдите в Файл > Параметры > Надстройки > Управление > Надстройки COM.

Нажмите «Перейти», поставьте галочку рядом с Power Pivot и нажмите «ОК».

Перезапустите программу — в верхнем меню появится вкладка «Power Pivot».

Если у вас Excel 2016 и более поздних версий, то Power Pivot уже предустановлен. Чтобы включить его, откройте вкладку «Данные» и нажмите «Управление. Модель данных».

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

Как пользоваться Power Pivot

Рассмотрим, как с помощью Power Pivot объединять данные, находить закономерности и строить динамические отчёты.

Создание модели данных

Объединяет несколько таблиц в единую структуру для анализа данных из различных источников как единое целое.

Представим, что вы работаете аналитиком в розничной компании. С помощью Power Pivot вы свяжете между собой три таблицы, которые включают данные отдельно по продажам, клиентам и товарам. Так вы сможете группировать данные из всех таблиц в одном отчёте, не дублируя информацию, и быстрее анализировать, какие группы клиентов — например, по возрасту или городу — приносят больше прибыли.

Импорт данных из внешних источников

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

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

• Импорт из текстовых файлов. Для этого необходимо преобразовать текстовые данные в CSV-файлы, а затем выгрузить их.

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

Совет: проверьте, чтобы данные были в едином формате.

Построение связей между таблицами

Чтобы провести анализ данных из разных таблиц, необходимо установить связи. Для этого в разделе «Управление моделью данных» откройте вкладку «Схема» и соедините таблицы.

Создание вычисляемых столбцов и мер

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

Меры (метрики) — это динамические, то есть постоянно обновляющиеся расчёты, которые собирают общие данные. Мерами удобно пользоваться, если вы часто меняете значения ячеек.

Представьте, что вы анализируете продажи в разных регионах и вам нужно: 

• узнать средний чек в каждом регионе;

• рассчитать процент прироста продаж за последние месяцы;

• определить прибыль по каждому продукту. 

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

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

Какие ошибки совершают при работе с Power Pivot и как их исправить

Рассмотрим, с какими трудностями сталкиваются при работе с Power Pivot и как их устранить.

Некорректные связи между таблицами

Часто пользователи неправильно устанавливают связи между таблицами, из-за чего расчёты возвращают неверные результаты. Например, поля в таблицах могут не совпадать по типу данных.

Решение: Убедитесь, что в каждой таблице присутствует уникальный идентификатор и типы значений ключевых полей совпадают в обеих таблицах. Воспользуйтесь разделом «Схема» в Power Pivot для наглядной проверки и корректировки связей между таблицами.

Ошибки в формулах DAX

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

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

Дублирование данных в модели

Расчёты будут неточными, если одна таблица случайно импортируется несколько раз или если данные не очищены от дубликатов.

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

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

>>> Автоматизируйте рутинные вычисления и базовую аналитику на курсе «Excel и Google-таблицы: от новичка до эксперта». 

За две недели вы научитесь строить прогнозы и проводить анализ различных сценариев, работать с надстройками Power Query и Power Pivot и создавать собственные мини-программы для решения нестандартных задач.