Power Pivot в Excel: руководство для начинающих

Power Pivot расширяет возможности Excel для работы с миллионами строк этих из нескольких источников. Вместо ограничения в 1 048 576 строк обычного листа Power Pivot обрабатывает десятки миллионов записей с помощью столбчатого сжатия. Разберем создание модели этих с нуля. Ключи Office в нашем каталоге Office.

Что такое Power Pivot и зачем он нужен

Power Pivot — надстройка для анализа данных, встроенная в Excel (Professional Plus, Microsoft 365 и standalone-версии 2016+). Она решает три ключевые проблемы обычного Excel:

• Ограничение строк — обычный лист Excel содержит максимум 1 048 576 строк. Power Pivot работает с десятками миллионов
• Множество таблиц — вместо ВПР (VLOOKUP) между листами создаются связи как в базе данных
• Производительность — столбчатое сжатие in-memory значительно ускоряет вычисления

Сценарии использования:

• Анализ продаж за несколько лет (миллионы транзакций)
• Объединение этих из CRM, ERP и бухгалтерии в одной модели
• Создание KPI-дашбордов с автоматическим обновлением
• Сравнительный анализ периодов (год к году, месяц к месяцу)

Включение Power Pivot: Файл > Параметры > Надстройки > Управление: Надстройки COM > Перейти > отметьте «Microsoft Power Pivot for Excel» > OK. После включения на ленте появится вкладка «Power Pivot».

Если надстройка отсутствует, ваша редакция Excel не поддерживает Power Pivot. Требуется Professional Plus, Microsoft 365 или отдельная лицензия. Ключи Office доступны в нашем каталоге Office.

Создание модели этих и импорт таблиц

Модель этих — набор таблиц со связями между ними. Это основа Power Pivot.

Добавление таблиц в модель:

Способ 1 — из рабочего листа Excel: выделите таблицу этих > Power Pivot > Добавить в модель данных. Таблица появится в окне Power Pivot.

Способ 2 — импорт внешних данных: Power Pivot > Управление > Из базы этих / Из файла данных. Поддерживаемые источники:

• SQL Server, Access, Oracle, MySQL
• Текстовые файлы (CSV, TXT)
• Каналы этих (OData)
• Excel-файлы

Пример: создание модели продаж. Импортируйте три таблицы:

1. Продажи (факты): Дата, ID_Продукта, ID_Клиента, Количество, Сумма
2. Продукты (справочник): ID_Продукта, Название, Категория, Цена
3. Клиенты (справочник): ID_Клиента, Имя, Город, Сегмент

Создание связей: Power Pivot > Управление > Представление диаграммы. Перетащите поле ID_Продукта из таблицы Продажи на ID_Продукта таблицы Продукты. Повторите для ID_Клиента. Связи дают возможность создавать сводные таблицы, объединяющие эти из всех трех таблиц без ВПР.

Формулы DAX: вычисляемые столбцы и меры

DAX (Data Analysis Expressions) — язык формул Power Pivot. Он похож на формулы Excel, но работает с таблицами и контекстом фильтрации.

Вычисляемые столбцы — создают новый столбец в таблице. В окне Power Pivot:

=RELATED(Продукты[Категория])

Функция RELATED извлекает значение из связанной таблицы (аналог ВПР).

=Продажи[Количество] * RELATED(Продукты[Цена])

Вычисляет выручку для каждой строки продаж.

Меры (Measures) — агрегирующие вычисления для сводных таблиц:

Общая выручка:=SUM(Продажи[Сумма])
Средний чек:=AVERAGE(Продажи[Сумма])
Количество клиентов:=DISTINCTCOUNT(Продажи[ID_Клиента])

Продвинутые меры с функциями Time Intelligence:

Выручка прошлого года:=CALCULATE(SUM(Продажи[Сумма]); SAMEPERIODLASTYEAR(Дата[Дата]))
Рост год к году:=DIVIDE([Общая выручка] - [Выручка прошлого года]; [Выручка прошлого года])

Ключевые функции DAX:

• CALCULATE — изменяет контекст фильтрации для вычисления
• FILTER — фильтрует таблицу по условию
• ALL — убирает все фильтры (для вычисления доли от общего)
• RELATED — значение из связанной таблицы
• SUMX/AVERAGEX — итерирующие агрегаты (вычисление по строкам)

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

Результаты Power Pivot отображаются через стандартные сводные таблицы Excel, но с расширенными возможностями.

Создание сводной таблицы из модели:

1. Power Pivot > Сводная таблица (или Вставка > Сводная таблица > «Использовать модель этих этой книги»)
2. В панели полей появятся все таблицы модели
3. Перетащите поля из разных таблиц в области Строки, Столбцы, Значения
4. Меры автоматически появляются в области Значения

Преимущество: вы можете комбинировать поля из разных таблиц без ВПР. Например, строки из Продукты[Категория], столбцы из Клиенты[Город], значения из мер модели данных.

Добавление KPI (ключевых показателей):

1. В окне Power Pivot выберите меру
2. Power Pivot > KPI > Создать KPI
3. Задайте целевое значение и пороги (красный/желтый/зеленый)
4. В сводной таблице KPI отображается иконками светофора

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

Срезы (Slicers) для интерактивной фильтрации: Вставка > Срез > выберите поле. Срезы дают возможность пользователям фильтровать эти кликом по кнопкам вместо стандартных фильтров.

Обновление этих и оптимизация производительности

Модель этих Power Pivot загружает эти в оперативную память. При изменении источника необходимо обновить модель.

Обновление данных:

• Power Pivot > Управление > Обновить все (обновляет все импортированные таблицы)
• Правый клик по таблице > Обновить (обновляет одну таблицу)
• Для автоматического обновления при открытии: Эти > Свойства подключения > Обновлять при открытии файла

Оптимизация размера файла:

• Power Pivot использует столбчатое сжатие. Чем меньше уникальных значений в столбце, тем лучше сжатие
• Удалите ненужные столбцы из модели (они все равно загружаются в память)
• Используйте целочисленные ключи вместо текстовых для связей
• Для дат создайте отдельную таблицу-календарь с помощью DAX: =CALENDAR(DATE(2020;1;1); DATE(2026;12;31))

Мониторинг размера модели: Power Pivot > Управление > нижняя панель показывает количество строк в каждой таблице. Окно «Дополнительно» > «Размер модели данных» показывает занятую память.

Рекомендации по производительности:

• Фильтруйте эти при импорте (не загружайте ненужные годы или столбцы)
• Предпочитайте меры вычисляемым столбцам (меры вычисляются при запросе, столбцы хранятся постоянно)
• Для моделей более 100 млн строк рассмотрите переход на Power BI Desktop

Power Pivot доступен в редакциях Excel с поддержкой модели данных. Лицензии Office Professional Plus в нашем каталоге Office.

Полезные ссылки и материалы

Для решения задач, описанных в этой статье, могут пригодиться:

• Руководство по настройке Office 2021
• Руководство по настройке макросов VBA в Excel

Официальная документация: Microsoft Learn: состояние выпусков Windows.

Часто задаваемые вопросы

Чем Power Pivot отличается от Power Query?

Power Query загружает и трансформирует эти (ETL — извлечение, преобразование, загрузка). Power Pivot анализирует эти через модель и DAX. Они дополняют друг друга: Power Query готовит данные, Power Pivot анализирует.

Нужна ли лицензия для Power Pivot?

Power Pivot встроен в Excel Professional Plus, Microsoft 365 Business и Enterprise. В Excel Home и Standard он недоступен. Для полных возможностей используйте Power BI Desktop (бесплатный).

Можно ли поделиться файлом с Power Pivot?

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

Power Pivot тормозит на большом файле. Что делать?

Удалите неиспользуемые столбцы из модели, фильтруйте эти при импорте, замените вычисляемые столбцы мерами где возможно. Если файл более 200 МБ, рассмотрите Power BI Desktop.