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

Содержание

Что такое вычисляемое поле сводной таблицы?
Добавление вычисляемого поля в сводную таблицу
Проблема с вычисляемыми полями сводной таблицы
Как изменить или удалить вычисляемое поле сводной таблицы?
Как получить список всех формул вычисляемого поля?

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

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

Загрузите набор данных и следуйте инструкциям.

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

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

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

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

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

Сделать это можно двумя способами:

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

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

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

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

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

Выберите любую ячейку в сводной таблице.
Перейдите в Инструменты сводной таблицы -> Анализ -> Вычисления -> Поля, элементы и наборы.
В раскрывающемся списке выберите Расчетное поле.
В диалоговом окне «Вставить рассчитанный файл»:
- Дайте ему имя, введя его в поле «Имя».
- В поле Формула создайте формулу для вычисляемого поля. Обратите внимание, что вы можете выбрать одно из названий полей, перечисленных под ним. В данном случае формула: «= Прибыль / Продажи». Вы можете ввести имена полей вручную или дважды щелкнуть имя поля, указанное в поле «Поля».
Щелкните Добавить и закройте диалоговое окно.

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

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

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

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

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

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

Вы НЕ МОЖЕТЕ использовать ссылки или именованные диапазоны при создании вычисляемого поля сводной таблицы. Это исключило бы множество формул, таких как ВПР, ИНДЕКС, СМЕЩЕНИЕ и т. Д. Однако вы можете использовать формулы, которые могут работать без ссылок (например, SUM, IF, COUNT и т. Д.…).
В формуле можно использовать константу. Например, если вы хотите узнать прогнозируемые продажи, при которых прогнозируется рост на 10%, вы можете использовать формулу = Продажи * 1,1 (где 1,1 - константа).
Порядок приоритета соблюдается в формуле, составляющей вычисляемое поле. Рекомендуется использовать круглые скобки, чтобы не запоминать порядок приоритета.

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

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

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

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

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

Выберите любую ячейку в сводной таблице.
Перейдите в Инструменты сводной таблицы -> Анализ -> Вычисления -> Поля, элементы и наборы.
В раскрывающемся списке выберите Расчетное поле.
В диалоговом окне «Вставить рассчитанный файл»:
- Дайте ему имя, введя его в поле «Имя».
- В поле Формула используйте следующую формулу: = ЕСЛИ (Регион = «Юг», Продажи * 1,05, Продажи * 1,1).
Щелкните Добавить и закройте диалоговое окно.

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

Щелкните здесь, чтобы загрузить набор данных.

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

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

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

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

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

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

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

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

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

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

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

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

Выберите любую ячейку в сводной таблице.
Перейдите в Инструменты сводной таблицы -> Анализ -> Расчеты -> Поля, элементы и наборы.
В раскрывающемся списке выберите Расчетное поле.
В поле «Имя» щелкните стрелку раскрывающегося списка (маленькая стрелка вниз в конце поля).
Из списка выберите вычисляемое поле, которое вы хотите удалить или изменить.
Измените формулу, если вы хотите ее изменить, или нажмите «Удалить», если вы хотите ее удалить.

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

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

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

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

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

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