Назначение вычисляемых столбцов и полей

Важно :  Данная статья переведена с помощью машинного перевода, см. Отказ от ответственности. Используйте английский вариант этой статьи, который находится здесь, в качестве справочного материала.

При первом знакомстве с Power Pivot большинство пользователей осознают, что его мощные возможности заключаются в агрегатных и других вычислениях. Если в ваших данных есть столбец с числовыми значениями, вы можете легко выполнить с ними статистические вычисления, выбрав их в списке полей сводной таблицы или Power View. Для числовых данных будет автоматически определена сумма, среднее значение, количество или другое выбранное вами значение агрегата. Такие поля называются неявными показателями. Неявные показатели удобно использовать для быстрых и простых статистических вычислений, но они имеют ограничения, которые почти всегда можно обойти при использовании явных показателей и вычисляемых столбцов.

Сначала рассмотрим пример, где мы используем вычисляемый столбец, чтобы добавить новое текстовое значение для каждой строки в таблице "Товар". Каждая строка в таблице "Товар" содержит различную информацию о продаваемых нами товарах. В ней есть столбцы "Название товара", "Цвет", "Размер", "Цена посредника" и т. д. У нас также есть другая связанная таблица "Категория товара", которая включает столбец "НазваниеКатегорииТовара". Мы хотим, чтобы для каждого товара в таблице "Товар" было указано название его категории из таблицы "Категория товара". Для этого в таблице "Товар" можно создать вычисляемый столбец "Категория товара":

Вычисляемый столбец категории продукта

Новая формула "Категория товара" использует функцию DAX RELATED для получения значений из столбца "НазваниеКатегорииТовара" в связанной таблице "Категория товара", а затем вводит эти значения для каждого товара (строки) в таблице "Товар".

Это хороший пример того, как можно использовать вычисляемый столбец для добавления фиксированного значения для каждой строки, которое можно затем использовать в области СТРОКИ, СТОЛБЦЫ или ФИЛЬТРЫ сводной таблицы или в отчете Power View.

Создадим еще один пример, где нужно вычислить удельную прибыль для категорий товаров. Это обычный сценарий, который описывается во многих учебниках. В модели данных есть таблица "Продажи" с данными о транзакциях, которая связана с таблицей "Категория товара". В таблице "Продажи" есть столбец, содержащий суммы продаж, и столбец, в котором указаны затраты.

Мы можем создать вычисляемый столбец, который рассчитывает сумма прибыли для каждой строки путем вычитания значений в столбце "Себестоимость" продаж из значений в столбце "ОбъемПродаж":

Столбец "Прибыль" в Power Pivot

Теперь создадим сводную таблицу и перетащим поле "Категория товара" в область СТОЛБЦЫ, а наше новое поле "Прибыль" — в область ЗНАЧЕНИЯ (столбец в таблице PowerPivot — это поле в списке полей сводной таблицы). В результате будет создан неявный показатель с именем Сумма по полю Прибыль. Это агрегат значений в столбце прибыли для каждой из категорий товаров. Результат выглядит так:

Простая сводная таблица

В этом случае поле "Прибыль" имеет смысл добавлять только в область ЗНАЧЕНИЯ. Если поместить его в область СТОЛБЦОВ, сводная таблица будет выглядеть следующим образом:

Сводная таблица без полезных значений

Поле "Прибыль" не позволяет получить полезные сведения, если оно находится в области ФИЛЬТРЫ, СТРОКИ или СТОЛБЦЫ. Оно имеет смысл только как агрегированное значение в области ЗНАЧЕНИЯ.

Итак, вот что мы сделали: мы создали столбец с названием "Прибыль", который вычисляет удельную прибыль для каждой строки в таблице "Продажи". Затем мы добавили его в область ЗНАЧЕНИЯ в сводной таблице, в результате чего было автоматически создан неявный показатель, в котором результат вычисляется для каждой категории товаров. Если вам кажется, что мы вычислили прибыль по категориям товаров дважды, вы правы. Сначала мы вычислили прибыль для каждой строки в таблице "Продаж", а затем добавили столбец "Прибыль" в область ЗНАЧЕНИЯ, где с ним были выполнены статистические вычисления по категориям товаров. Если вы думаете, что мы могли бы обойтись без вычисляемого столбца "Прибыль", вы опять же правы. Но как вычислить прибыль, не создавая вычисляемый столбец "Прибыль"?

Это лучше делать с помощью явного показателя.

Пока мы оставим вычисляемый столбец "Прибыль" в таблице "Продажи", а также столбцы "Категория товаров" в области СТОЛБЦЫ и "Прибыль" в области ЗНАЧЕНИЯ сводной таблицы, чтоб сравнить результаты.

В области вычислений таблицы "Продажи" создадим показатель с именем Общая прибыль(во избежание конфликтов имен). Это позволит получить те же результаты, что и раньше, но без использования вычисляемого столбца "Прибыль".

Во-первых, в таблице "Продажи" выберем столбец "ОбъемПродаж" и нажмем кнопку "Автосумма" для создания явного показателя Сумма по полю ОбъемПродаж. Явным показателем называется показатель, который создается в области вычислений таблицы в Power Pivot. Сделаем то же самое для столбца "Себестоимость". Переименуем столбцы в Общий объем продажи Общая себестоимость, чтобы их было проще идентифицировать.

Кнопка "Автосумма" в PowerPivot

Затем создадим другой показатель с помощью этой формулы:

Общий доход: = [ Общий объем продаж]-[Общая себестоимость]

Примечание : Эту формулу также можно записать как Общий доход: =СУММ([ОбъемПродаж]) - СУММ([Себестоимость]), но создание отдельных показателей "Общий объем продаж" и "Общая себестоимость" позволяет также использовать их в сводной таблице и в качестве аргументов в других различных формулах показателей.

После изменения формата нового показателя "Общая прибыль" на денежный мы можем добавить его в сводную таблицу.

Сводная таблица

Как можно заметить, новый показатель "Общая прибыль" возвращает те же результаты, что и вычисляемый столбец "Прибыль", добавленный в область ЗНАЧЕНИЯ. Разница заключается в том, что показатель "Общая прибыль" гораздо эффективнее. Кроме того, он делает модель данных более понятной и экономичной, потому что расчет выполняется только для полей, выбираемых для сводной таблицы, во время их выбора. Вычисляемый столбец "Прибыль" оказался не нужен.

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

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

В этом примере необходимо вычислить сумму продаж в процентах от общего объема продаж. Мы создали вычисляемый столбец % продаж в таблице "Продажи":

Вычисляемый столбец доли от продаж

В формуле указано: Для каждой строки в таблице "Продажи" нужно разделить значение в столбце "ОбъемПродаж" на сумму значений в столбце "ОбъемПродаж".

Если создать сводную таблицу, добавить столбец "Категория товара" в область СТОЛБЦЫ, а новый столбец % продаж —в область ЗНАЧЕНИЯ, можно получить сумму процентов продаж для каждой из категорий товаров.

Сводная таблица с суммой процентов продаж для категорий товаров

Хорошо. Пока все идет замечательно. Но давайте добавим срез. Мы добавляем "Календарный год", а затем выбираем "Год". В этом случае выберем 2007. Вот что мы получим.

Неверный результат суммы доли продаж в сводной таблице

На первый взгляд значения опять же кажутся правильными. Но сумма процентов должна равняться 100 %, так как мы хотим знать процент от суммы продаж для каждой из категорий товаров в 2007 г. В чем ошибка?

Столбец "% продаж" вычисляет процент для каждой строки, то есть значение в столбце "ОбъемПродаж", поделенное на общую сумму всех значений в столбце "ОбъемПродаж". Значения в вычисляемом столбце являются фиксированными. Они дают неизменяемый результат для каждой строки в таблице. Когда мы добавили столбец % продаж в сводную таблицу, для него была вычислена сумма всех значений в столбце "ОбъемПродаж". Сумма всех значений в столбце "% продаж" всегда будет равна 100 %.

Совет :  Обязательно прочтите статью Контекст в формулах DAX. В ней понятно объясняется, что такой контекст уровня строки и контекст фильтра (о которых мы сейчас говорим).

Вычисляемый столбец "% от продаж" можно удалить, так как он нам не поможет. Вместо этого мы создадим показатель, который правильно вычисляет процент от суммы продаж независимо от того, применяются ли фильтры или срезы.

Помните показатель "Общий объем продаж", который мы создали ранее (тот, который просто суммирует значения в столбце "ОбъемПродаж")? Мы использовали его в качестве аргумента для показателя "Общая прибыль", а теперь мы используем его в качестве аргумента для нового вычисляемого поля.

Совет :  Явные показатели, такие как "Общий объем продаж" и "Общая себестоимость", удобно использовать не только сами по себе в сводной таблице или отчете, но и как аргументы других показателей. Это делает формулы более эффективными и простыми для понимания. Это хороший прием моделирования данных.

Создадим новый показатель с помощью следующей формулы:

% от суммы продаж: =([Общий объем продаж]) / CALCULATE([Общий объем продаж], ALLSELECTED())

Формула говорит о том, что нужно разделить результат "Общий объем продаж" на сумму значений "ОбъемПродаж" без применения фильтров столбцов и строк (кроме тех, которые определены в сводной таблице).

Совет :  Обязательно почитайте о функциях CALCULATE и ALLSELECTED в справочнике по DAX.

Теперь при добавлении нового столбца % от суммы продаж в сводную таблицу мы получим следующее:

Правильный результат суммы доли продаж в сводной таблице

Так намного лучше. Теперь значение % от суммы продаж для каждой категории товаров вычисляется как процент от суммы продаж в 2007 г. Если в срезе "Календарный год" выбрать другой год или несколько лет, мы получим новые процентные значения для категорий товаров, но общий итог по-прежнему будет составлять 100%. Также можно добавить другие срезы и фильтры. Показатель "% от суммы продаж" всегда будет выдавать процент от суммы продаж независимо от того, какие срезы и фильтры применены. При использовании показателей результат всегда рассчитывается в зависимости от контекста, определяемого полями в областях СТРОКИ и СТОЛБЦЫ, а также всеми примененными фильтрами и срезами. В этом заключается преимущество показателей.

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

Вычисляемые столбцы

  • Если новые данные должны отображаться в областях СТРОКИ, СТОЛБЦЫ и ФИЛЬТРЫ в сводной таблице или в областях ОСЬ, ЛЕГЕНДА или ПЛИТКА в визуализации Power View, необходимо использовать вычисляемый столбец. Как и обычные столбцы данных, вычисляемые столбцы могут использоваться в качестве полей в любой области. Если они имеют числовой тип, с ними также можно выполнять статистические вычисления в области ЗНАЧЕНИЯ.

  • Используйте их, если вы хотите, чтобы новые данные были фиксированными значениями для строки. Например, допустим, что у вас есть таблица дат со столбцом дата и вы хотите добавить столбец, содержащий номер месяца. Вы можете создать вычисляемый столбец, который вычисляет номер месяца из даты в столбце "Дата". Например, =MONTH('Дата'[Дата]).

  • Если вы хотите добавить текстовое значение для каждой строки в таблице, используйте вычисляемый столбец. Поля, содержащие текстовые значения, никогда нельзя вычислять в области ЗНАЧЕНИЯ. Например =FORMAT('Дата'[Дата];"ммммм") дает название месяца для каждой даты в столбце "Дата" таблицы "ДАТА".

Показатели

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

  • Если вам нужно выполнять сложные вычисления, например подсчитать количество на основе какого-либо фильтра либо вычислить значение по годам либо дисперсию, используйте вычисляемые поля.

  • Если вы хотите свести к минимуму размер книги и повысить ее эффективность, по возможности используйте для вычислений показатели. Во многих случаях все вычисления можно выполнять с помощью показателей, что значительно уменьшает размер книги и сокращает время обновления.

Помните, что нет ничего неправильного в том, чтобы создать вычисляемый столбец, как мы сделали со столбцом "Прибыль", а затем выполнить с ним статистическое вычисление в сводной таблице или отчете. Это хороший и простой способ создавать собственные вычисления. Когда вы хорошо изучите эти две мощные функции Power Pivot, вы сможете создавать максимально эффективные и точные модели данных. Надеемся, что вы узнали что-то полезное для себя. Существуют и другие замечательные ресурсы, которые могут вам помочь. Вот лишь несколько примеров: Контекст в формулах DAX, Агрегатные функции в Power Pivot и Центр ресурсов по DAX. И, хотя он более сложен и предназначен для бухгалтеров и финансистов, в образце Моделирование и анализ данных прибылей и убытков с помощью Microsoft Power Pivot в Excel вы найдете отличные примеры моделирования данных и формул.

Примечание : Отказ от ответственности относительно машинного перевода. Данная статья была переведена с помощью компьютерной системы без участия человека. Microsoft предлагает эти машинные переводы, чтобы помочь пользователям, которые не знают английского языка, ознакомиться с материалами о продуктах, услугах и технологиях Microsoft. Поскольку статья была переведена с использованием машинного перевода, она может содержать лексические,синтаксические и грамматические ошибки.

Совершенствование навыков
Перейти к обучению
Первоочередный доступ к новым возможностям
Присоединиться к программе предварительной оценки Office

Были ли сведения полезными?

Спасибо за ваш отзыв!

Благодарим за отзыв! Возможно, будет полезно связать вас с одним из наших специалистов службы поддержки Office.

×