Создание сводной таблицы с итоговыми строками для суммы и среднего с помощью MDX

В этом руководстве мы разберем, как создать сводную таблицу в Excel с использованием MDX (Multidimensional Expressions) для отображения итоговых строк как для суммы, так и для среднего значения в рамках одного отчета. Этот метод превосходит стандартные возможности и позволяет строить более сложные аналитические структуры.

Материал поможет тем, кто хочет освоить сводные таблицы в Excel, быстрее группировать показатели и собирать наглядные отчеты без ручной сводки.

Классическая сводная таблица не позволяет легко комбинировать разные агрегатные функции (например, СУММ и СРЗНАЧ) в одной итоговой строке для разных показателей. Решение через MDX снимает это ограничение.

Пошаговая инструкция

1. Выделите любую ячейку в вашем диапазоне данных и нажмите Ctrl + Т / T, чтобы преобразовать его в таблицу Excel. Убедитесь, что отмечен пункт «Таблица с заголовками». На вкладке «Конструктор» переименуйте таблицу, например, в «Data».
2. Находясь внутри таблицы, выберите «Вставка» → «Сводная таблица». В диалоговом окне создания обязательно отметьте опцию «Добавить эти данные в модель данных». Это критически важно для работы с MDX.
3. В области полей сводной таблицы:
   • Перетащите поле «ПРОИЗВЕД / PRODUCT» в область «Строки».
   • Перетащите поле «Sales» в область «Значения» дважды.
   • Для второго поля «Сумма по полю Sales» измените способ вычисления на «Среднее» и задайте понятное имя, например, «СРЗНАЧ / AVERAGE of Sales».
4. Перетащите элемент «Sigma Values» из области «Столбцы» и поместите его над элементом «ПРОИЗВЕД / PRODUCT» в области «Строки». Если все сделано верно, в таблице появятся 6 строк с суммой, затем 6 строк со средним, а после — две итоговые строки.
5. Скопируйте следующий код MDX (например, из примера в рабочей книге):

   {
   ([Measures].[СУММ / SUM of Sales],[Data].[ПРОИЗВЕД / PRODUCT].Children),
   ([Measures].[СУММ / SUM of Sales],[Data].[ПРОИЗВЕД / PRODUCT].[All]),
   ([Measures].[СРЗНАЧ / AVERAGE of Sales],[Data].[ПРОИЗВЕД / PRODUCT].[All])}

6. На вкладке «Анализ сводной таблицы» раскройте меню «Поля, элементы и наборы» и выберите «Управление наборами».
7. В диалоговом окне «Диспетчер наборов» нажмите на стрелку у кнопки «Создать» и выберите «Создать набор с помощью MDX».
8. Вставьте скопированный код MDX в поле определения. Нажмите кнопку «Проверить MDX», чтобы убедиться в корректности синтаксиса.
9. В нижней части окна установите флажок «Пересчитывать набор при каждом обновлении». Нажмите «ОК», затем «Закрыть».

Завершение настройки

В списке полей сводной таблицы появится новый флажок для «Набор 1». Снимите выделение с исходных полей «ПРОИЗВЕД / PRODUCT» и «Sales», а вместо этого отметьте «Набор 1». Ваша сводная таблица примет вид, показанный в начале статьи, с комбинированными итоговыми строками.

Ключевые преимущества метода

• Гибкость отчетности: Позволяет создавать нестандартные иерархии и итоги, недоступные в обычном режиме.
• Динамичность: Набор MDX пересчитывается при каждом обновлении данных.
• Мощность модели данных: Использование модели данных Excel открывает доступ к расширенным возможностям анализа.

Освоение MDX для сводных таблиц — это шаг к профессиональной аналитике в Excel, позволяющий решать сложные задачи по структурированию и визуализации данных.