Вычисляемые поля, заданные в обычной сводной таблице, всегда просчитываются по строкам исходного набора данных. Один из подписчиков сайта MrExcel попытался вычислить диапазон цен на товары с помощью формулы МАКС(Цена) – МИН(Цена), заданной в обычной сводной таблице. При создании вычисляемого поля в сводной таблице Excel просматривает каждую строку исходных данных, и вычисляет максимальное значение в этой строке (рис. 32). Поскольку в каждой строке отображается лишь единственное значение цены, максимальное значение в каждой строке исходных данных будет соответствовать единственному значению цены. То же самое можно сказать и о минимальном значении. Другими словами, МАКС – МИН = Цена – Цена, то бишь 0. В результате для каждого товара, отображаемого в сводной таблице, разница между наибольшей и наименьшей ценами будет равно нулю. Этот абсолютно некорректный результат получен из-за правил, присущих вычисляемым полям в обычных сводных таблицах (рис. 33).
Рис. 32. Вычисляемое поле обрабатывает исходные строки данных
Рис. 33. Обычная сводная таблица включает вычисляемые столбцы, которые используются на уровне значений
Воспользуйтесь возможностями DAX для решения этой задачи.
1. Выделите исходный набор данных. Нажмите комбинацию клавиш Ctrl+T, чтобы преобразовать выделенный набор данных в таблицу.
2. С помощью набора контекстных вкладок Работа с таблицами (Конструктор) присвойте новой таблице имя Продажи.
3. Выполните команду Вставка g Сводная таблица и установите флажок Добавить эти данные в модель данных.
4. Добавьте поле Товар в область СТРОКИ.
5. Дважды добавьте поле Цена в область ЗНАЧЕНИЯ.
6. В нижней части списка полей сводной таблицы раскройте список для первого поля Цена и выберите пункт Параметры полей значений. Измените итоговую функцию на Минимум.
7. Аналогично для второго поля Цена выберите итоговую функцию Максимум.
8. При выполнении пп. 6 и 7 фактически создается неявно заданное вычисляемое поле. Эти поля подробно рассматриваются в следующем разделе.
9. Выполните команду PowerPivot g Вычисляемые поля g Создание вычисляемого поля, чтобы создать вычисляемое поле. Присвойте этому полю имя Диапазон. В окне ввода формулы введите имя таблицы Продажи. В списке полей найдите поле Продажи [Максимум в столбце Цена] и нажмите клавишу <Таb>. Введите знак минуса. Снова введите Продажи. Найдите поле Продажи [Минимум в столбце Цена] и нажмите клавишу <Таb>, чтобы вставить его в формулу. Теперь формула Диапазон принимает вид =[Максимум в столбце Цена]-[Минимум в столбце Цена].
10. Чтобы добавить формулу в модель данных, щелкните на кнопке ОК. После возврата в окно Excel найдите новое поле Диапазон в таблице Продажи. Кликните на этом поле. Теперь диапазон цен вычисляется корректно (рис. 34).
Рис. 34. С помощью DАХ можно создавать формулы, вычисляющие итоги
Создание вычисляемых полей DAX путем добавления полей в область ЗНАЧЕНИЯ. После добавления поля Цена в область ЗНАЧЕНИЯ и выбора функции Минимум PowerPivot автоматически создает вычисляемое поле DAX, отображающее полученный результат. Благодаря этому вам не придется использовать формулу =MINX('Продажи';'Продажи'[Цена]). Можно просмотреть неявные вычисляемые поля в окне PowerPivot. Выполните команду PowerPivot g Управление и на вкладке Дополнительно щелкните на кнопке Показать неявные вычисляемые поля. Новые поля появятся в строках, отображенных в нижней части окна (рис. 35).
Рис. 35. Просмотр формул в неявных вычисляемых полях
Если установить указатель мыши над нижней частью окна, появится подсказка «PowerPivot автоматически сформировал это вычисляемое поле путем добавления поля к области значений в списке полей Excel. Поле доступно только для чтения и будет автоматически удалено при удалении столбца».
При создании формулы DAX проще временно добавить поле в область значений, чтобы сгенерировать неявное поле. Это поле может использоваться при создании других формул DAX. После создания новой формулы в сводной таблице можно удалить неявное поле из нее. Эти поля остаются в окне PowerPivot, поскольку на них ссылаются новые меры DAX.
Чтобы вычислить диапазон цен, можно создать следующую формулу DAX:
=Мах('Продажи'; 'Продажи'[Цена])-Minx('Продажи'; 'Продажи'[Цена])
