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

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

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

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

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

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

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

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

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

Столбец "Прибыль" в 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 загружается хороший пример моделирования данных и формул.

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

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

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

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

×