Как добавить и использовать вычисляемые поля сводной таблицы Excel

Как правило, после создания сводной таблицы необходимо расширить анализ и включить в него больше данных/расчетов.

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

Что такое вычисляемое поле сводной таблицы?

Начнем с простого примера сводной таблицы.

Предположим, у вас есть набор данных о розничных продавцах, и вы создаете сводную таблицу, подобную этой:

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

Теперь, что, если вы также хотите знать, какова норма прибыли этих розничных продавцов (маржа прибыли — это «прибыль», деленная на «продажи»)?

Есть несколько способов сделать это:

1. Вернитесь к исходному набору данных и добавьте эту новую точку данных.Таким образом, вы можете вставить новую колонку в исходные данные и рассчитать в ней размер прибыли.Как только это будет сделано, вам нужно обновить исходные данные сводной таблицы, чтобы получить этот новый столбец как ее часть.
   • Хотя этот подход работает, вам нужно вручную вернуться к набору данных и выполнить вычисления.Например, вы можете добавить еще один столбец для расчета средних продаж на единицу (продажи/количество).Опять же, вам нужно добавить этот столбец в исходные данные, а затем обновить сводную таблицу.
   • Этот метод также раздувает сводную таблицу по мере добавления в нее новых данных.
2. Добавьте расчеты вне сводных таблиц.Выберите этот вариант, если структура сводной таблицы вряд ли изменится.Однако если вы измените сводную таблицу, расчеты могут не обновиться соответствующим образом и могут дать вам неправильные результаты или ошибки.Как показано ниже, я рассчитал размер прибыли, когда в строке есть розничные продавцы.Но когда я меняю его с клиента на область, формула работает неправильно.
3. Расчет полей с помощью сводных таблиц.этоИспользуйте существующие данные сводной таблицы и рассчитайтесамый эффективный способ.Думайте о вычисляемых полях как о виртуальных столбцах, которые вы добавляете, используя существующие столбцы в сводной таблице.Использование вычисляемых полей сводной таблицы имеет много преимуществ (как мы увидим позже):
   • Вам не нужно работать с формулами или обновлять исходные данные.
   • Его можно расширять, поскольку он автоматически учитывает любые новые данные, которые вы можете добавить в сводную таблицу.После добавления вычисляемого поля его можно использовать как любое другое поле в сводной таблице.
   • Его легко обновлять и управлять.Например, если изменяется показатель или вам нужно изменить расчет, вы можете легко сделать это из самой сводной таблицы.

Добавить вычисляемые поля в сводную таблицу

Давайте посмотрим, как добавить вычисляемое поле сводной таблицы в существующую сводную таблицу.

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

Вот шаги, чтобы добавить вычисляемое поле сводной таблицы:

После добавления вычисляемого поля оно появится как одно из полей в списке полей сводной таблицы.

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

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

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

 

В приведенном выше примере я использовал простую формулу (=Прибыль/Продажи), чтобы вставить вычисляемое поле.Однако вы также можете использовать некоторые расширенные формулы.

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

• Вы не можете использовать ссылки или именованные диапазоны при создании вычисляемых полей сводной таблицы.Это исключит многие формулы, такие как ВПР, ИНДЕКС, СМЕЩ и т. д.Однако вы можете использовать формулы, которые работают без ссылок (например, СУММ, ЕСЛИ, СЧЕТ и т. д.).
• Вы можете использовать константы в формулах.Например, если вы хотите узнать прогнозируемый рост продаж, который, как ожидается, увеличится на 10%, вы должны использовать формулу =Продажи*1.1 (где 1.1 — константа).
• Следуйте порядку приоритета в формулах, которые генерируют вычисляемые поля.Рекомендуется использовать круглые скобки, чтобы вам не приходилось запоминать приоритет.

Теперь давайте рассмотрим пример создания вычисляемого поля с помощью расширенной формулы.

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

Для прогнозных значений вы должны использовать увеличение продаж на 300% для крупных розничных продавцов (более 5 миллионов продаж) и увеличение продаж на 300% для малых и средних розничных продавцов (менее 10 миллионов продаж).

Примечание. Цифры продаж здесь являются поддельными и использовались для иллюстрации примеров в этом руководстве.

Вот как это сделать:

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

Проблема вычисляемого поля сводной таблицы

Вычисляемые поля — это замечательная функция, которая действительно увеличивает ценность сводных таблиц с полевыми вычислениями, сохраняя при этом масштабируемость и управляемость.

Однако существует проблема с вычисляемыми полями сводной таблицы, которую необходимо понять перед их использованием.

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

Обратите внимание, что промежуточные итоги и итоги неверны.

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

Таким образом, для South Total, хотя значение должно быть 22,824,000 22,287,000 XNUMX, South Total неправильно сообщает о нем как XNUMX XNUMX XNUMX.Это происходит потому, что для получения значения используется формула 21,225,800 1.05 XNUMX * XNUMX.

К сожалению, вы не можете исправить эту проблему.

Лучший способ справиться с этим — удалить промежуточные и общие итоги из сводной таблицы.

Вы также можете пройти Представлено ДебройНекоторые инновационные обходные пути для решения этой проблемы.

Как изменить или удалить вычисляемое поле сводной таблицы?

После создания вычисляемого поля сводной таблицы вы можете изменить формулу или удалить ее, выполнив следующие действия:

Как получить список всех формул вычисляемого поля?

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

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

Вот шаги, чтобы быстро получить список всех формул вычисляемого поля:

• Выберите любую ячейку в сводной таблице.
• Перейдите в Инструменты сводной таблицы -> Анализ -> Поля, элементы и наборы -> Формулы списка.

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

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