Это перевод главы книги Bill Jelen. Excel Dynamic Arrays Straight to the Point. К содержанию.
Недавно я обновлял 6-е издание книги Билл Джелен, Майкл Александер. Сводные таблицы в Microsoft Excel.[1] Я хотел расширить главу об инструментах OLAP, чтобы показать, как преобразовать сводную таблицу в формулы кубов. Оказывается, это довольно сложно. Первые шаги даются легко:
- Выделите ячейку на листе данных.
- Выберите Вставить –> Сводная Таблица.
- В диалоговом окне Создание сводной таблицы выберите Добавить эти данные в модель данных (подробнее о модели данных см. Роб Колли. Формулы DAX для Power Pivot):
Рис. 96. Использование модели данных
Скачать заметку в формате Word или pdf, примеры в формате Excel
- Постройте сводную таблицу с Customer в строках, Revenue в значениях и Sector в виде среза.
Рис. 97. Сводная таблица и срез
- На вкладке Анализ выберите Средства 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] Рассказ от первого лица – Билла Джелена. – Прим. Багузина.