Обычно данные из Power Pivot получают с помощью сводных таблиц — это привычный и удобный способ. Однако есть и другой вариант — использовать кубические формулы. Они позволяют напрямую обращаться к модели данных Power Pivot без создания сводных таблиц.
Советуем получше узнать эти формулы, вы точно найдёте им применение. С помощью кубических формул в Excel можно делать гибкие вычисления, извлекать точечные показатели. Кубические формулы можно применять в любом месте файла и привязывать к срезам. При этом значения будут автоматически обновляться при изменении данных.
Наиболее часто применяемая формула кубов — это КУБЗНАЧЕНИЕ (CUBEVALUE).
В приложенном файле Excel с помощью формулы КУБЗНАЧЕНИЕ сделаны карточки показателей. Файл можно скачать и посмотреть, как устроены формулы. Подробности про файл можно посмотреть с статье «Дашборд в Excel с кроссфильтрацией».
Быстрый способ создания формул кубов
Самый простой способ начать пользоваться кубическими формулами — построить сводную таблицу на основе Power Pivot и преобразовать её в формулы. Для этого нужно перейти на вкладку Анализ сводной таблицы → Средства OLAP → Преобразовать в формулы.
Сводная таблица превратится в таблицу с формулами кубов, а вы сможете посмотреть, как они устроены. И использовать эти формулы в обычных вычислениях. Если к сводным таблицам на странице применяются срезы, то в формуле тоже будут приведены названия срезов.
Формула КУБЗНАЧЕНИЕ
Формула КУБЗНАЧЕНИЕ – это основная формула кубов. Она возвращает значения из куба или модели данных Power Pivot. Синтаксис формулы для получения данных из модели Power Pivot:
КУБЗНАЧЕНИЕ ( Подключение; Мера [; Элемент1] [; Элемент2] [; Элемент…] )
Подключение – имя подключения к модели называется «ThisWorkbookDataModel».
Для модели данных указывается мера.
Элемент – это выражение, определяющее элементы куба для вычисления меры.
Например, это может быть:
- фильтр по таблице модели данных, например
"[Dates].[Год].[2023]" - имя среза;
- множество, определенное функцией КУБМНОЖ.
Выражения элементов (кроме срезов) записываются в кавычках.
Пример. Вычисление с мерой Прибыль, к которой применяется фильтр по 2019 году.
= КУБЗНАЧЕНИЕ ( "ThisWorkbookDataModel"; "[Measures].[Прибыль]"; "[Calendar].[Год].&[2019]" )
Автозаполнение в строке формул
Сначала при работе с формулами кубов может показаться, что нужно иметь перед глазами модель данных Power Pivot. Но всё гораздо проще, в формуле много подсказок — чтобы что-то добавить начинайте текст с кавычек или с точки, а затем выбирайте нужный элемент в списке.
1. Подключение к модели данных называется «ThisWorkbookDataModel».
2. При вводе кавычек появляется список таблиц и мер модели (Measures).
3. Поставив точку, в появившемся списке можно выбрать меру или поле таблицы.
Получается, что формула КУБЗНАЧЕНИЕ записывается очень просто, если применить автозаполнение и подсказки в строке формул. Точно так же можно добавить фильтры и срезы.
4. Выбор из списка значения для фильтрации по году в таблице Calendar.
5. Названия срезов записываются без кавычек и начинаются со слова Срез_
Ещё один вариант записи формулы — создать ссылку на сводную таблицу с помощью ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ и переписать её параметры в КУБЗНАЧЕНИЕ, применив синтаксис.
Ссылка на ячейку в формуле
Самый интересный вариант применения формулы КУБЗНАЧЕНИЕ — выполнить расчёты с помощью ссылок на ячейки на листе. Ссылку на ячейку можно создать одним из способов — указав столбец таблицы в модели данных, в котором находится значение ячейки, или с помощью формулы КУБЭЛЕМЕНТ.
Способ 1. Ссылка на ячейку записывается в кавычках с амперсантами, с указанием названия таблицы и столбца — на рисунке "[Продажи].[Товары].["&F3&"]"
Способ 2. «Просто» ссылка на ячейку с формулой КУБЭЛЕМЕНТ.
Синтаксис формулы для получения данных из модели Power Pivot:
КУБЭЛЕМЕНТ ( Подключение; Выражение_элемента; [Подпись] )
Подключение – имя подключения к модели называется «ThisWorkbookDataModel».
Выражение_элемента – выражение для элемента, например на мера или элемент таблицы.
Подпись – (необязательно) строка, которая отображается в ячейке вместо подписи из куба.
Формулы кубов
Формулы кубов — это удобная опция, которая позволяет создавать гибкие отчёты, извлекая данные прямо из модели Power Pivot, минуя сводные таблицы. Кроме формул КУБЗНАЧЕНИЕ и КУБЭЛЕМЕНТ, в Excel есть ещё кубические формулы, применяя которые можно настраивать различные вычисления.
| Формула | Для чего нужна |
| КУБЗНАЧЕНИЕ (CUBEVALUE) |
Возвращает данные из куба или модели данных. |
| КУБЭЛЕМЕНТ (CUBEMEMBER) |
Возвращает элемент или кортеж из куба. |
| КУБПОРЭЛЕМЕНТ (CUBERANKEDMEMBER) |
Возвращает n-ый по порядку элемент множества. |
| КУБСВОЙСТВОЭЛЕМЕНТА (CUBEMEMBERPROPERTY) |
Возвращает значение свойства элемента из куба. |
| КУБЭЛЕМЕНТКИП (CUBEKPIMEMBER) |
Возвращает свойство ключевого показателя эффективности (KPI) и выводит имя KPI в ячейке. |
| КУБМНОЖ (CUBESET) |
Определяет вычисляемое множество элементов или кортежей, отправляя выражение множества в куб, а затем возвращает его в Excel. |
| КУБЧИСЛОЭЛМНОЖ (CUBESETCOUNT) |
Определяет число элементов множества. |
