Power Pivot и модель данных: связи и DAX

В этом уроке ты узнаешь

Различать таблицы фактов и измерений и создавать связь «один ко многим»
Объяснять разницу между вычисляемым столбцом и мерой
Писать простые меры с SUM, CALCULATE и DIVIDE
Понимать, как контекст фильтра меняет результат меры

В таблице продаж компании миллионы строк, а клиенты и товары хранятся в отдельных таблицах. Привычный путь — добавить к продажам столбцы «City», «Category», «Price» через VLOOKUP. Итог — огромный медленный файл, да ещё и предел листа в 1 048 576 строк. Модель данных (Data Model) хранит таблицы отдельно, соединяет их связями и работает со сжатыми данными в памяти, а Power Pivot — инструмент для управления этой моделью.

Схема «звезда» и связи

Определение

Таблицы фактов и измерений

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

1
Подготовь таблицы
Преврати каждый список в таблицу Excel (Ctrl+T) и дай имя: Sales, Customers, Products (Table Design › Table Name).
2
Добавь их в модель
В окне Insert › PivotTable отметь Add this data to the Data Model или подключи Power Pivot: File › Options › Add-ins › Manage: COM Add-ins › Go… › Microsoft Power Pivot for Excel; затем Power Pivot › Tables › Add to Data Model.
3
Создай связь
Data › Data Tools › Relationships › New…: Table = Sales, Column (Foreign) = CustID; Related Table = Customers, Related Column (Primary) = CustID. В окне Power Pivot (Diagram View) можно и просто перетащить поле с одной таблицы на другую.

Интерактив

Загрузка симуляции…

Маленькая модель «звезда»: продажи (в манатах) и клиенты; логика связи показана через VLOOKUP.

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

Критерий	Вычисляемый столбец	Мера (measure)
Когда вычисляется	при обновлении, по разу на строку	для каждой ячейки сводной, с учётом её фильтров
Пример	`=Sales[Qty]*RELATED(Products[Price])`	`Total Sales := SUM(Sales[Amount])`
Где используется	`Rows`, `Columns`, фильтры (для разреза)	только `Values`
Память	увеличивает файл	почти не занимает места

Baku Sales := CALCULATE([Total Sales], Customers[City] = "Baku")

где:

[Total Sales]ранее созданная мера: SUM(Sales[Amount])
CALCULATE(expr, filter…)вычисляет выражение в изменённом контексте фильтра: заменяет фильтр по городу на «Baku», остальные фильтры сохраняет

Создание меры: Power Pivot › Calculations › Measures › New Measure… (или правый щелчок по таблице в панели PivotTable Fields › Add Measure…).

Три меры в контексте фильтра

В модели 6 продаж: C1 (Baku) — Laptop 1200, Lamp 80; C2 (Ganja) — Phone 650, Laptop 1150; C3 (Baku) — Chair 300, Phone 700. Laptop и Phone — Electronics, Chair и Lamp — Home. В строках сводной — Products[Category]. Что покажут в каждой строке [Total Sales], [Baku Sales] и [Baku Share] := DIVIDE([Baku Sales],[Total Sales])?

Показать решение

Строка Electronics (фильтр: Category = Electronics):
[Total Sales] = 1200 + 650 + 1150 + 700 = 3700; [Baku Sales] = 1200 + 700 = 1900; доля = 1900 / 3700 ≈ 51,35%.
Строка Home: [Total Sales] = 300 + 80 = 380; [Baku Sales] = 380 (обе продажи в Баку); доля = 100%.
Общий итог: 4080; Baku 2280; доля ≈ 55,88%.
CALCULATE поменяла только фильтр по городу, а фильтр по категории остался — в этом суть контекста фильтра. Если знаменатель равен 0, DIVIDE вернёт пустое значение вместо ошибки.

Text

Total Sales := SUM ( Sales[Amount] )
Total Cost := SUMX ( Sales, Sales[Qty] * RELATED ( Products[UnitCost] ) )
Margin % := DIVIDE ( [Total Sales] - [Total Cost], [Total Sales] )
Baku Sales := CALCULATE ( [Total Sales], Customers[City] = "Baku" )
Share of All Cities := DIVIDE ( [Total Sales], CALCULATE ( [Total Sales], ALL ( Customers[City] ) ) )

Типичный набор мер. SUMX считает построчно и затем суммирует (правильное решение ловушки «Price × Quantity» из урока о сводных), а ALL полностью снимает фильтр по городу — так получается знаменатель для доли.

Преобразовать диапазон в таблицу — первый шаг моделиCtrl+T

Обновить активную сводную таблицуAlt+F5

Обновить всё, включая модель данныхCtrl+Alt+F5

Главное

Модель данных хранит таблицы отдельно и соединяет связями «один ко многим» — столбцы VLOOKUP больше не нужны.
Ключ в таблице измерения должен быть уникальным, а типы данных — совпадать с обеих сторон.
Вычисляемый столбец считается построчно, мера — в контексте фильтра каждой ячейки сводной.
CALCULATE меняет контекст фильтра, DIVIDE делает деление на ноль безопасным, ALL снимает фильтр.
Отношения и доли всегда пиши мерами, а не вычисляемыми столбцами.

Проверь себя

Вопросов: 10. Каждый правильный ответ приносит XP.

1 / 10

Какая сторона связи между Sales и Products — сторона «один»?