Power Pivot в Excel: пошаговое руководство

Анализ больших файлов Excel часто приводит к замедлению работы. 

Power Pivot предлагает иной подход. Он связывает таблицы и выполняет вычисления без ущерба для производительности. Вместо борьбы с цепочками VLOOKUP() и вспомогательными столбцами вы работаете со структурированной системой, встроенной прямо в Excel.

В этом руководстве вы научитесь настраивать модели данных, создавать связи между таблицами, писать формулы DAX и строить интерактивные отчёты с помощью Power Pivot.

Что такое Power Pivot и чем он полезен?

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

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

Вместо хранения данных непосредственно на листе Power Pivot загружает всё во внутреннюю модель данных Excel. 

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

Реляционная структура вместо цепочек VLOOKUP

После загрузки данных в модель вы можете связывать таблицы по ключам — как в облегчённой базе данных. Нет нужды уплощать всё в один гигантский лист и использовать вложенные VLOOKUP() для склейки таблиц. Power Pivot позволяет анализировать связанные таблицы бок о бок — чисто и надёжно.

Более мощные вычисления на DAX

Power Pivot использует DAX (Data Analysis Expressions) — язык формул, специально созданный для аналитики. С его помощью можно создавать меры, выходящие далеко за рамки стандартной сводной таблицы: от простых сумм до временных метрик, коэффициентов, скользящих окон и других продвинутых расчётов.

Примеры сценариев

Два примера того, как бизнес использует Power Pivot в работе: 

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

Проще говоря, Power Pivot даёт вам опыт работы «как с базой данных» внутри Excel. Если вы работаете с большими или многотабличными наборами данных, он способен превратить хаотичные процессы отчётности в быстрые, масштабируемые модели, на которые можно опираться.

Настройка Power Pivot в Excel

Давайте посмотрим, как начать работу с Power Pivot в Excel. 

Включение Power Pivot

Скачивать Power Pivot не нужно — он уже есть в Excel. Чтобы его включить:

1. Откройте лист Excel 
2. Нажмите Файл на ленте
3. Выберите Параметры > Надстройки 
4. Затем выберите Надстройки COM в выпадающем списке и нажмите Перейти
5. Появится всплывающее окно. Здесь выберите Microsoft Power Pivot for Excel, затем нажмите ОК

Теперь Power Pivot появится на вашей ленте.

Включите надстройку Power Pivot в Excel. Изображение автора.

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

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

Теперь вы можете импортировать данные из различных источников: файла Excel, CSV или даже базы данных SQL Server. 

В этом примере у нас есть два набора данных в файле .xlsb:

1. sales.xlsb 

2. customer.xlsb 

Чтобы импортировать их в Power Pivot:

1. Перейдите на вкладку Power Pivot и выберите Управление. Откроется новое окно
2. Перейдите в Главная, затем нажмите Получить внешние данные и выберите Из других источников
3. Прокрутите вниз и нажмите Файл Excel

Загрузка данных из других источников. Изображение автора.

4. Во всплывающем окне нажмите Обзор и выберите файл customer.xlsb 

5. Отметьте флажок Использовать первую строку как заголовки столбцов и нажмите Далее

Импорт файла Excel в Power Pivot. Изображение автора.

В следующем окне нажмите Просмотр и фильтр, чтобы увидеть, как будут выглядеть данные перед импортом. Если всё устраивает, нажмите ОК, после чего отобразится, что все строки успешно перенесены. Затем нажмите Закрыть.

Предпросмотр выбранных данных. Изображение автора. 

Повторите те же действия для файла sales.xlsb. Затем внизу экрана оба файла будут отображаться как импортированные. Дважды щёлкните по ним и переименуйте. 

Оба файла импортированы. Изображение автора.

Построение связей и моделей данных

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

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

Чтобы создать связь между таблицами Sales и Customers:

1. На вкладке Главная нажмите Режим диаграммы. Вы увидите обе импортированные таблицы
2. Щёлкните CustomerID в таблице Sales
3. Перетащите его на CustomerID в таблице Customer, чтобы создать связь между двумя таблицами

Примечание: чтобы изменить связь, щёлкните правой кнопкой по линии и выберите Изменить связь.. В открывшемся окне выберите столбцы, по которым нужно связать таблицы.

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

В этой связи один клиент может встречаться много раз в таблице Sales, но каждый клиент присутствует только один раз в таблице Customers. Это простая связь «один-ко-многим», которая позволяет использовать поля из обеих таблиц в сводных таблицах и выполнять вычисления без поисковых формул.

Проектирование по звёздной схеме

Звёздная схема — один из самых простых способов структурировать модель Power Pivot. Она упорядочивает таблицы и делает вычисления предсказуемыми.

Сначала нужно выбрать фактическую таблицу. В нашем случае фактом служит Sales, поскольку в ней содержатся транзакционные записи: дата, клиент, продукт, количество и сумма.

Затем определите измерения, описывающие данные в Sales. Часто это, например:

• Customers (первичный ключ: CustomerID)
• Products (первичный ключ: ProductID)
• Regions (первичный ключ: RegionID)

Каждая таблица-измерение имеет первичный ключ. Его нужно связать с соответствующим внешним ключом в таблице фактов:

• Customers.CustomerID → Sales.CustomerID
• Products.ProductID → Sales.ProductID
• Regions.RegionID → Customers.RegionID

После связывания таблица Sales находится в центре, а таблицы-измерения расходятся вокруг неё — это и есть «звезда». Такая структура делает модель понятной, ускоряет вычисления и повышает согласованность отчётности.

Создайте звёздную схему. Изображение автора.

Добавление вычисляемых столбцов

Когда связи настроены, можно создавать новые поля прямо в модели данных.

1. Переключитесь в Режим данных

2. Выберите пустое поле Добавить столбец в конце таблицы.

3. Введите = [TotalAmount] / [Qty] и нажмите Enter, чтобы Excel заполнил весь столбец

4. Переименуйте заголовок в PricePerUnit

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

Добавьте дополнительный вычисляемый столбец. Изображение автора.

Написание формул DAX для анализа

Теперь, когда модель готова, можно приступать к созданию формул DAX для анализа данных. Эти формулы помогают строить итоги, сравнения и расчёты по времени прямо в отчётах.

Создание мер

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

Чтобы создать меру:

1. Откройте окно Power Pivot

2. Перейдите в Главная > Вычисления > Новая мера

3. Введите формулу, например = SUM(Sales[TotalAmount])

4. Назовите её Total Sales и нажмите ОК

Создание мер. Изображение автора. 

Добавление меры «доля от итога»

Также можно использовать эту формулу, чтобы добавить меру процента от итога:

= DIVIDE([Total Sales], CALCULATE([Total Sales], ALL(Regions)))

Она показывает долю общей выручки по каждому региону.

Добавьте меру процента от итога. Изображение автора.

Использование функций работы со временем

Функции работы со временем в DAX «понимают», как данные меняются по дням, месяцам, кварталам и годам. Они позволяют считать показатели «с начала года», сравнивать результаты с предыдущими периодами и оценивать тренды без ручной настройки фильтров.

Чтобы эти функции работали в вашей модели, сначала нужна корректная таблица дат.

Настройка таблицы дат

Чтобы настроить таблицу: 

1. Перейдите в Power Pivot > Добавить в модель данных
2. В Power Pivot выберите таблицу и укажите Конструктор > Пометить как таблицу дат

Создайте таблицу данных. Изображение автора.

3. Теперь в Главная > Режим диаграммы свяжите Date[Date] → Sales[OrderDate].

Свяжите Date Table[Date] с Sales[OrderDate]. Изображение автора.

Создание мер для анализа по времени

Когда таблица Date готова, можно строить меры для оценки показателей по периодам.

С начала года (YTD):

Total Sales YTD :=
TOTALYTD([Total Sales], 'Date Table'[Date])

Сравнение с прошлым годом:

Sales Last Year :=
CALCULATE([Total Sales], SAMEPERIODLASTYEAR('Date Table'[Date]))

Выполните расчёты по времени. Изображение автора. 

Когда меры готовы, вернитесь в Excel и создайте сводную таблицу, используя модель данных. Затем поместите поля из таблицы дат в область Строк и добавьте Total Sales, Total Sales YTD и Sales Last Year в Значения. 

Это демонстрирует, как меры работы со временем функционируют с таблицей Date внутри модели.

Сводная таблица с Total Sales, YTD и Sales Last Year. Изображение автора. 

Типовые паттерны DAX

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

Среднее по категории:

Average Sales Per Category :=
AVERAGE(Sales[TotalAmount])

Накопительный итог по датам:

Running Total Sales :=
CALCULATE(
    [Total Sales],
    FILTER(ALL('Date'), 'Date'[Date] <= MAX('Date'[Date]))
)

Создавая меры, придерживайтесь нескольких правил: 

• Давайте им понятные имена
• Пишите формулы читабельно
• Используйте переменные (VAR), когда мера становится длинной. 

Так модель легче понять, когда вы вернётесь к ней позже.

Визуализация и работа с моделью

Когда модель и меры готовы, превратим данные в визуализации, которые можно исследовать и настраивать в реальном времени.

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

Вот как вставить сводную таблицу из модели данных, чтобы работать напрямую со связанными таблицами:

1. Откройте лист Excel
2. Перейдите в Вставка > Сводная таблица > Из модели данных
3. Выберите Новый лист

В области Поля сводной таблицы теперь можно брать поля из любой таблицы. Например:

• Перетащите RegionName из таблицы Regions в Строки
• Перетащите Total Sales в Значения

Поскольку ранее мы настроили связи, Excel автоматически всё объединит.

Создайте сводную таблицу на основе данных Power Pivot. Изображение автора.

Если нужна визуализация, щёлкните в пределах сводной таблицы, перейдите в Вставка > Сводная диаграмма, выберите тип диаграммы (например, Гистограмма с группировкой) и подтвердите. Диаграмма остаётся связанной со сводной таблицей, поэтому всё обновляется синхронно.

Добавьте сводную диаграмму. Изображение автора.

Добавление слайсеров и фильтров

Слайсеры — это удобные фильтры-кнопки, делающие отчёт интерактивным. Чтобы добавить их:

1. Щёлкните по своей сводной таблице
2. Перейдите в Вставка > Срез
3. Выберите поля, например RegionName или ProductName

Слайсер появляется в виде блока на листе. При клике по элементам сводная таблица и диаграмма мгновенно обновляются. Если у вас несколько сводных таблиц, один слайсер можно подключить ко всем для единых фильтров на странице.

Добавьте слайсеры. Изображение автора.

Построение KPI

KPI помогают видеть выполнение цели без дополнительных расчётов на листе. Чтобы создать свои:

1. В окне Power Pivot перейдите в KPI > Новый KPI
2. Задайте Total Sales как базовую меру
3. Выберите Абсолютное значение, введите целевое значение (например, 4000), настройте пороги и выберите стиль значков
4. Нажмите ОК, чтобы создать KPI

Задайте KPI для меры. Изображение автора.

5. В области полей сводной таблицы раскройте таблицу Sales, затем раскройте Total Sales
6. Оттуда перетащите Total Sales и Status в поле Значения

Теперь вы видите, как фактические значения соотносятся с порогом. 

Отображение статуса KPI в сводной таблице Excel. Изображение автора.

Оптимизация производительности Power Pivot

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

Уменьшение размера модели

Чем легче модель, тем быстрее она работает, поэтому удаляйте всё лишнее.

Неиспользуемые столбцы можно удалить в режиме данных. Даже если столбец не используется в сводной таблице, он занимает память, поэтому «обрезка» делает модель чище.

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

Старайтесь избегать вычисляемых столбцов без необходимости: они хранят значение для каждой строки, что быстро увеличивает размер файла. Напротив, меры эффективнее — они вычисляются только по требованию сводной таблицы. 

Выбор эффективных типов данных

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

В Режиме данных выберите столбец и укажите наиболее подходящий тип в разделе Тип данных на ленте. Например:

• Целые числа > Целое число
• Десятичные значения > Десятичное число
• Идентификаторы или коды, не используемые в вычислениях > Текст

При выборе корректного типа Power Pivot лучше сжимает столбец, что уменьшает размер и ускоряет вычисления.

Проверьте и используйте корректный тип данных. Изображение автора.

Обработка проблем обновления и вычислений

Если сводные таблицы не отражают свежие данные, перейдите на вкладку Power Pivot и нажмите Обновить всё. Это перезагрузит данные из исходных файлов.

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

Если вы столкнулись с ошибкой DAX, особенно в сложных мерах, часто это означает косвенную самоссылку формулы. Перепишите меру с более простой логикой или используйте блоки VAR для устранения циклической ссылки.

Интеграция с Power Query и Power BI

Одним из преимуществ Power Pivot является простая работа с остальным стеком данных Microsoft. Можно использовать Power Query для очистки и преобразования данных до попадания в модель или перенести всю модель в Power BI, когда вам нужны интерактивные дашборды.

Очистка и преобразование данных в Power Query

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

Откройте Power Query через Данные > Из текста/CSV > Преобразовать. Данные откроются в редакторе, где можно:

• Удалять дублирующиеся строки
• Переименовывать и упорядочивать столбцы
• Фильтровать ненужные значения
• Менять типы данных до их загрузки в модель

Power Query записывает каждый шаг справа в окне. Значит, очистка выполняется автоматически при каждом обновлении файла.

Когда всё готово, выберите Закрыть и загрузить в, затем — Модель данных. Очищенные данные загрузятся прямо в Power Pivot.

Экспорт моделей в Power BI

Вы также можете перенести модель Power Pivot в Power BI, когда нужны более насыщенные визуализации или общие дашборды. Действуйте так:

1. Сохраните книгу Excel
2. Откройте Power BI Desktop
3. Перейдите в Получить данные > Книга Excel
4. Выберите свой файл

Power BI импортирует таблицы и связи в точности так, как они заданы в Power Pivot. Далее вы можете строить дашборды, работать совместно с командой и настроить плановое обновление, чтобы отчёты автоматически оставались актуальными.

Лучшие практики для устойчивых моделей

По мере роста модели порядок упрощает обновление, отладку и развитие в будущем. Вот несколько привычек, которые помогут сохранять модель чистой и надёжной со временем:

Именование и организация

Понятные имена сильно помогают, когда возвращаетесь к файлу через недели или месяцы. Поэтому используйте читаемые имена мер, такие как Total_Sales, Total_Quantity или Profit_Margin — чтобы всегда было ясно, что именно они означают.

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

Проверка данных

Прежде чем доверять цифрам, выполните несколько быстрых проверок:

• Сравните итоги из исходных данных с итогами в сводных таблицах
• Используйте простые проверки DAX, например:

• COUNTROWS() — чтобы подтвердить количество строк в таблице

• DISTINCTCOUNT() — чтобы проверить уникальные значения, например клиентов или товары

Эти небольшие тесты помогают выявить отсутствующие связи, неверные фильтры или проблемы с данными до того, как они приведут к крупным ошибкам.

Сопровождение и обновление моделей

Когда появляются новые данные, на вкладке Power Pivot выберите Обновить или Обновить всё — Power Pivot перезагрузит всё из подключенных источников.

Перед серьёзными структурными изменениями — добавлением новых связей или переписыванием ключевых мер — сохраните резервную копию файла. Это даст безопасный откат, если что-то пойдёт не так.

Заключение

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

Если вы хотите освоить весь набор инструментов Excel, обратите внимание на наш трек Data Analysis with Excel Power Tools, а также (разумеется) на курс Power Pivot in Excel.