Глава 10. Power Pivot. Мышление в нескольких таблицах

Рубрика: 7. Полезняшки Excel

Это продолжение перевода книги Роб Колли. Формулы DAX для Power Pivot. Главы не являются независимыми, поэтому рекомендую читать последовательно.

Предыдущая глава       Содержание    Следующая глава

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

Откройте Excel-файл ch10_MultipleTables.xlsx, перейдите на вкладку Power Pivot, и кликните кнопку Управление. В окне Power Pivot перейдите на вкладку Конструктор и кликните кнопку Создание связи. В окне Создание связи выберите таблицы и столбцы, как указано ниже:

Ris. 10.1. Okno Sozdanie svyazi v Power Pivot

Рис. 10.1. Окно Создание связи в Power Pivot; чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите Открыть картинку в новой вкладке

Скачать заметку в формате Word или pdf

Представление Диаграммы

В Power Pivot существует два основных представления: данных и диаграммы. Переключение осуществляется кнопками на ленте (меню Главная –> Просмотр) или внизу в правой части окна:

Ris. 10.2. Knopki pereklyucheniya otobrazheniya

Рис. 10.2. Кнопки переключения отображения

Ris. 10.3. Predstavlenie Diagrammy. Otobrazhayutsya tri tablitsy dve iz kotoryh svyazany

Рис. 10.3. Представление Диаграммы. Отображаются три таблицы, две из которых связаны

Обратите внимание, что при выделении связи показываются поля, по которым осуществляется связь. Звездочка означает множество, а 1 – единицу. В нашем примере установлена связь «один ко многим» от таблицы Products к таблице Sales. Т.е., значения в поле ProductKey являются уникальными в таблице Products, но будут повторяться в таблице Sales.

В Excel 2010 и 2013 дизайн окна Создание связи немного отличался: явно определялась основная таблица и таблица подстановки (аналогично функции ВПР в Excel). Попытка выбрать в верхнем поле таблицу Products, а в нижнем – Sales, приводило к ошибке. В Excel 2016 такой ошибки не возникает. Power Pivot сам исправляет ситуацию, и в любом случае создаст связь «один ко многим», как на рис. 10.3.

Использование связанных таблиц Power Pivot в сводной таблице Excel

Создадим сводную таблицу, которая использует [ProductKey] в строках:

Ris. 10.4. ProductKey ne neset osobogo smysla

Рис. 10.4. [ProductKey] не несет особого смысла

Удалим [ProductKey] и добавим [ProductName] из таблицы Products:

Ris. 10.5. C ProductName tablitsa stala yasnee

Рис. 10.5. C [ProductName] таблица стала яснее

Благодаря установленной связи мы можем использовать в нашей сводной таблице любые поля из исходных таблиц Sales и Products (рис. 10.6). Почему это работает? Потому что набор фильтров передается из таблицы в таблицу.

Ris. 10.6. Gruppirovka po tsvetam

Рис. 10.6. Группировка по цветам (еще одному столбцу в таблице Products)

Рассмотрим ячейку С7 меры [Normal Sales] и проследим, как работает фильтр. Сначала фильтр Color=»Red» применяется к таблице Products:

Ris. 10.7. Tablitsa Products otfiltrovannaya po ColorRed

Рис. 10.7. Фрагмент таблицы Products, отфильтрованной по Color=»Red»

Столбец [ProductKey] не фильтруется напрямую, но он был уменьшен до подмножества из 63 значений, соответствующих фильтру Color=»Red» в таблице Products. Этот отфильтрованный набор [ProductKey] через связь между таблицами передается в таблицу Sales. А затем выполняется агрегирование по отфильтрованной по столбцу [ProductKey] таблице Sales. Мы можем прояснить шаг 4 золотого правила С: фильтры следуют за связями. Другими словами, фильтры передаются из таблицы в таблицу, если последние связаны.

И еще. В обратном порядке (из таблицы Sales в таблицу Products) фильтр не передается. Фильтр следует за связью, направление которой явным образом указано на рис. 10.3 (Products–>Sales).

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

Вернемся к нашим трем таблицам (см. рис. 10.3) и создадим связь Customers–>Sales. На этот раз мы сделаем это в представлении диаграммы. Для этого кликните на поле CastomKey в таблице Customers и перетащите его в таблицу Sales на поле с именем CastomKey. Можно сделать и наоборот: перетащить поле CastomKey из таблицы Sales в таблицу Customers. Как и в окне Создание связи Power Pivot сам определяет правильное направление связи и настроит ее соответствующим образом.

Ris. 10.8. Dve tablitsy poiska obe nad tablitsej dannyh dlya kotoroj oni postavlyayut filtry

Рис. 10.8. Две таблицы поиска, обе над таблицей данных, для которой они поставляют фильтры

Давайте применим фильтры из всех связанных таблиц поиска в таблице данных:

Ris. 10.9. Prodazhi v razreze subkategorij i semejnogo polozheniya

Рис. 10.9. Продажи в разрезе субкатегорий товаров и семейного положения (М – семейные, S – одинокие)

Фильтры функции CALCULATE() также передаются по направлению связей

Давайте определим новую меру, используя столбец [NumberChildrenAtHome] в таблице подстановки [Customers]:

[Sales to Parents] = CALCULATE([Total Sales]; Customers[NumberChildrenAtHome] > 0 )

И сравните ее с базовой мерой – [Total Sales]:

Ris. 10.10. Filtry funktsii CALCULATE takzhe peredayutsya po napravleniyu svyazej

Рис. 10.10. Фильтры функции CALCULATE() также передаются по направлению связей

Мы видим, что [Sales to Parents] возвращает меньшие числа, чем базовая мера [Total Sales]. Фильтры в CALCULATE() применяются до того, как они будут проходить через связи. Таким образом, мы можем уточнить 4-й шаг третьего золотого правила мер DAX: примените фильтры к таблицам подстановки; это приведет к тому, что в таблице данных будут отобраны только строки, соответствующие набору всех фильтров.

Глава 11. Что вы знаете к этому моменту

К этому моменту вы довольно много знаете о Power Pivot и формулах DAX. Если вы хотите, вы можете остановиться. Вы уже способны улучшить обработку данных, в том числе и больших. Однако, если вам интересно, продолжите изучение. Оно не будет слишком сложным, но вас ждет настоящая магия))


Прокомментировать