Динамические массивы и функции кубов

Это перевод главы книги Bill Jelen. Excel Dynamic Arrays Straight to the Point. К содержанию.

Недавно я обновлял 6-е издание книги Билл Джелен, Майкл Александер. Сводные таблицы в Microsoft Excel.[1] Я хотел расширить главу об инструментах OLAP, чтобы показать, как преобразовать сводную таблицу в формулы кубов. Оказывается, это довольно сложно. Первые шаги даются легко:

  1. Выделите ячейку на листе данных.
  2. Выберите Вставить –> Сводная Таблица.
  3. В диалоговом окне Создание сводной таблицы выберите Добавить эти данные в модель данных (подробнее о модели данных см. Роб Колли. Формулы DAX для Power Pivot):

Рис. 96. Использование модели данных

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

  1. Постройте сводную таблицу с Customer в строках, Revenue в значениях и Sector в виде среза.

Рис. 97. Сводная таблица и срез

  1. На вкладке Анализ выберите Средства OLAP –> Преобразовать в формулы.

Рис. 98. Преобразование сводной таблицы в формулы кубов

Сводная таблица будет преобразована в диапазон, а в ячейках А6:А33 и В5:В33 появятся формулы. Три из них показаны на рис. ниже. Подробнее о формулах кубов и их синтаксисе см. Аналитические функции в Excel (функции кубов).

Рис. 99. Преобразование сводной таблицы в формулы не является гибким

Функции кубов не так гибки, как сводные таблицы

Если изменить значения в исходных данных, размещенных в Таблица1 (см. рис. 96), и затем обновить модель данных, формулы кубов отработают эти изменения. Если же добавить в исходные данные нового клиента, вид продукции и т.п., функции кубов не добавят новые строчки в отчет.

Еще одна проблема заключается в том, что список клиентов не сворачивается при изменении среза. Сумма Revenue становится равной нулю, но сами строки не исчезают.

Вместо использования средства OLAP Преобразовать в формулы вы можете построить формулы кубов самостоятельно, как описано в книге Роба Колли. Но и в этом случае тот или иной выбор в срезе не будет исключать строки из отчета. А вот формулы динамических массивов обеспечивают это.

Одно из решений состоит в том, чтобы использовать результат преобразования сводной таблицы в формулы кубов в качестве промежуточного. Далее на его основе с помощью функции ФИЛЬТР() можно вернуть только тех клиентов, у которых длина строки в столбце Revenue больше нуля: =ФИЛЬТР(A6:B33;ДЛСТР(B6:B33)>0;"Нет данных"). При выборе различных значений из среза Sector отчет обновляется:

Рис. 100. Функция ФИЛЬТР() возвращает клиентов в соответствии с установками среза

Обратите внимание, что во втором аргументе функции ФИЛЬТР() используется отбор по условию ДЛСТР(B6:B33)>0. Почему-то B6:B33>0 не работает.

[1] Рассказ от первого лица – Билла Джелена. – Прим. Багузина.

Добавить комментарий

Ваш адрес email не будет опубликован.