Power Pivot в Excel: модель данных, связи таблиц и первые меры DAX
Power Pivot превращает набор отдельных таблиц Excel в связанную модель данных: справочники фильтруют таблицу операций, а меры DAX рассчитывают результат в контексте отчёта. Начинать лучше не со сложных формул, а с чистых ключей, звёздной схемы и трёх проверяемых мер. Power Query при этом подготавливает данные, а Power Pivot связывает и анализирует их.
Когда нужен Power Pivot
Обычная сводная таблица удобна, пока все поля находятся в одном плоском источнике. Если продажи, товары, клиенты и календарь хранятся отдельно, пользователи часто соединяют их формулами, копируют названия и создают широкую таблицу. Power Pivot позволяет загрузить несколько таблиц в модель данных, связать их по ключам и строить отчёт без физического размножения справочных полей.
Технология полезна при большом числе строк, нескольких источниках, повторяющихся отчётах и необходимости единых расчётов. Microsoft описывает Power Pivot как средство моделирования данных в Excel: оно создаёт модели, отношения и вычисления. В современной экосистеме Excel Power Query обычно загружает и преобразует данные, модель данных хранит связи, а Power Pivot добавляет расширенное моделирование и DAX.
Power Pivot не исправляет плохую структуру автоматически. Дубли в ключах, несогласованные типы, смешение фактов и справочников приводят к пустым результатам или неверным итогам. Сначала проектируют модель, затем пишут меры.
Power Query, модель данных и Power Pivot
| Компонент | Основная задача | Пример |
|---|---|---|
| Power Query | Получить и преобразовать данные | Объединить файлы, изменить типы, удалить лишние столбцы |
| Модель данных | Хранить таблицы и связи | Связать продажи с товарами и календарём |
| Power Pivot | Управлять моделью и расчётами | Создать меры DAX и иерархии |
| Сводная таблица | Показать и фильтровать результат | Выручка по месяцам и категориям |
Правильный поток выглядит так: источник → Power Query → модель данных → связи и DAX → сводная таблица. Не загружайте промежуточный результат на лист только ради повторного импорта, если его можно сразу направить в модель. Но сохраняйте понятный слой контроля, где видны количество строк, ключевые суммы и дата обновления.
Наличие вкладки Power Pivot зависит от версии и редакции Excel и настройки надстройки. Проверьте состав вашей установки и включение COM-надстройки. Сам файл с моделью может открываться на другом компьютере, но возможности редактирования и обновления зависят от поддерживаемой среды.
Как устроена звёздная схема
В центре модели находится таблица фактов: каждая строка представляет операцию на согласованном уровне детализации. Например, одна строка — одна товарная позиция заказа. В ней есть числовые показатели и ключи: дата, товар, клиент, заказ. Вокруг располагаются справочники, или измерения: календарь, товары, клиенты, подразделения.
Связь обычно строится от уникального ключа справочника к повторяющемуся ключу таблицы фактов. Один товар встречается в справочнике один раз и во множестве продаж. Справочник фильтрует факты, поэтому название категории берут из таблицы товаров, а сумму — из продаж.
- Одна таблица содержит факты одного уровня детализации.
- В справочнике ключ уникален и не пуст.
- Ключи по обе стороны связи имеют одинаковый тип.
- Описание товара не дублируется в каждой операции без необходимости.
- Календарь содержит непрерывный диапазон дат.
- Итоги хранятся в мерах, а не в строках источника.
Схема «всё со всем» усложняет фильтрацию. Для первого проекта избегайте двунаправленных и неоднозначных путей, пока не понимаете их эффект. Простая звезда легче проверяется и обычно даёт предсказуемый контекст.
Как создать и проверить связи
Загрузите таблицы в модель и откройте представление диаграммы. Соедините `Товары[ТоварID]` с `Продажи[ТоварID]`, `Клиенты[КлиентID]` с `Продажи[КлиентID]`, `Календарь[Дата]` с `Продажи[Дата]`. На стороне справочника значение должно быть уникальным; на стороне фактов оно может повторяться.
Если связь не создаётся, проверьте типы данных. Текстовый код `00125` не равен числу `125`, а дата-время может не совпасть с чистой датой из календаря. Приводите тип в Power Query до загрузки. Не удаляйте ведущие нули из идентификатора, если они являются частью кода.
Проверка связи — это не только отсутствие ошибки в окне. Создайте сводную таблицу: строки из справочника, мера из факта. Добавьте отдельный контроль «не найдено в справочнике» через запрос или антисоединение. Если часть продаж не получила товар или клиента, итог по общей сумме может сохраниться, но разрез будет неполным.
Меры и вычисляемые столбцы
Вычисляемый столбец рассчитывается для каждой строки и хранит результат в модели. Мера вычисляется при запросе в текущем контексте фильтров: по выбранному году, категории, региону и строке сводной таблицы. Для агрегатов отчёта обычно предпочтительнее мера.
Первая мера выручки: `Выручка := SUM(Продажи[Сумма])`. Мера количества: `Количество := SUM(Продажи[Количество])`. Средняя цена: `Средняя цена := DIVIDE([Выручка];[Количество])`. Функция `DIVIDE` безопаснее прямого деления, потому что умеет корректно обрабатывать нулевой знаменатель.
Названия функций DAX обычно пишутся на английском, а разделитель аргументов зависит от региональных настроек. Ссылайтесь на меры в квадратных скобках, а на столбцы — с именем таблицы. Отдельная таблица «Меры» помогает хранить расчёты в одном месте, но не меняет логику модели.
| Нужно получить | Выбор | Почему |
|---|---|---|
| Сумму в сводной | Мера | Меняется с фильтрами |
| Категорию каждой строки | Столбец или источник | Результат на уровне строки |
| Долю от общего итога | Мера | Нужен контекст отчёта |
| Ключ объединения | Источник или Power Query | Часть структуры данных |
Контекст фильтра и CALCULATE
Одна и та же мера `Выручка` показывает разные числа в строках сводной таблицы, потому что каждая строка создаёт свой контекст фильтра. Категория «Оборудование» фильтрует справочник товаров, связь передаёт фильтр на продажи, а `SUM` складывает только оставшиеся строки.
`CALCULATE` изменяет контекст вычисления. Например, мера `Выручка выбранного канала := CALCULATE([Выручка];Каналы[Тип] = "Онлайн")` добавляет фильтр канала. Сложность DAX возникает не из-за арифметики, а из-за того, какие строки видит выражение. Перед формулой проговорите контекст словами.
Не используйте `CALCULATE` как случайную попытку исправить итог. Сначала проверьте схему и базовую меру. Затем добавляйте один фильтр и сравнивайте результат с ручной выборкой. Если итог строки верен, а общий итог кажется неожиданным, помните: мера пересчитывается в контексте итога, а не всегда складывает видимые ячейки.
Мини-модель продаж и контрольный расчёт
Таблица `Продажи` содержит четыре строки: товар A — 2 единицы на 1 000 рублей, товар B — 1 единица на 800 рублей, товар A — 3 единицы на 1 500 рублей, товар C — 4 единицы на 2 000 рублей. В `Товары` записаны A и B в категории «Базовая», C — в категории «Премиум». Общая выручка равна 5 300 рублей, количество — 10, средняя цена — 530 рублей.
После связи по коду товара сводная должна показать 3 300 рублей для базовой категории и 2 000 рублей для премиальной. Контроль: 3 300 + 2 000 = 5 300. Если товар C отсутствует в справочнике, общая мера по фактам может остаться 5 300, но строка по категории потеряет 2 000. Именно поэтому нужен отчёт по несвязанным ключам.
Добавьте календарь и разнесите операции по двум месяцам. Мера выручки не меняется, а разрез по месяцу появляется без нового столбца в таблице продаж. Затем проверьте фильтр категории и месяца одновременно. Это демонстрирует главное преимущество модели: один расчёт работает во всех согласованных разрезах.
Типичные ошибки модели
| Симптом | Причина | Исправление |
|---|---|---|
| Связь не создаётся | Дубли на стороне «один» | Очистить справочник и определить ключ |
| Пустые категории | Нет соответствующего ключа | Отчёт несопоставленных значений |
| Завышенный итог | Неверная детализация или дубли фактов | Определить grain и сверить строки |
| Фильтр не действует | Нет активного пути связи | Проверить диаграмму и направление |
| Файл разрастается | Лишние столбцы и высокая кардинальность | Удалить ненужное до загрузки |
| Мера даёт неожиданный итог | Контекст пересчитывается | Разобрать фильтры и формулу |
Не скрывайте пустые элементы сразу фильтром. Сначала выясните, это допустимое «не определено» или потерянная связь. У каждого справочника полезно иметь техническую строку «неизвестно» только при осознанной политике обработки, а не как способ замаскировать качество данных.
Удаляйте из модели длинные текстовые поля, технические GUID и точные временные метки, если они не нужны в анализе. Чем выше число уникальных значений, тем хуже сжатие. При этом не удаляйте ключ, необходимый для трассировки и контроля.
Порядок создания первой рабочей модели
- Опишите вопрос отчёта и уровень детализации факта.
- Загрузите источники через Power Query и назначьте типы.
- Удалите ненужные столбцы до загрузки в модель.
- Проверьте уникальность ключей справочников.
- Создайте простые связи «один ко многим».
- Добавьте базовые меры суммы, количества и отношения.
- Соберите сводную и сравните с ручным контрольным примером.
- Проверьте несвязанные ключи и пустые значения.
- Только затем добавляйте `CALCULATE` и временную аналитику.
Архитектуру и возможности сверяйте по официальному обзору Power Pivot от Microsoft. Различия мер, вычисляемых столбцов и контекста описаны в официальном обзоре DAX, а совместную роль подготовки и моделирования — в материале Power Query и Power Pivot.
Постройте связанную модель Excel и научитесь проверять меры DAX.
На курсе по Excel и Google Таблицам вы освоите подготовку, моделирование и анализ данных и соберёте расчёты, которые сохраняют логику при обновлении отчёта.
Хорошая модель начинается с уровня детализации, чистых ключей и связей; DAX становится предсказуемым, когда структура данных уже проверена.
Вопросы и ответы
- Чем Power Pivot отличается от обычной сводной таблицы?
Power Pivot создаёт связанную модель из нескольких таблиц и хранит меры DAX, а сводная таблица показывает результат этой модели.
- Чем Power Query отличается от Power Pivot?
Power Query получает и преобразует данные, а Power Pivot управляет связями, моделью и аналитическими расчётами DAX.
- Какая связь нужна между справочником и продажами?
Обычно это связь «один ко многим»: уникальный ключ справочника связывается с повторяющимся ключом таблицы фактов.
- Что лучше использовать: меру или вычисляемый столбец?
Для агрегатов отчёта чаще нужна мера, меняющаяся с фильтрами; столбец нужен для результата на уровне каждой строки.
- Почему итог меры не равен сумме видимых строк?
DAX пересчитывает выражение в контексте общего итога, а не обязательно складывает уже показанные значения отдельных строк.
- Почему в сводной появляются пустые категории?
Часть ключей факта может отсутствовать в справочнике или иметь другой тип; проверьте несвязанные значения и качество ключей.