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

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

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

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

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

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

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

Можно сделать это по-разному:

1. Вернуться к изначальной таблице и добавить эти данные. Таким образом, вы можете вставить новый столбец в исходные данные и рассчитать в нем маржу прибыли.
   • Можно сделать так, но вам придется сделать все это вручную. Вам может понадобиться добавить еще один столбец для расчета средней продажи на единицу (Sales/Quantity).
   • Существенный минус такого метода — расширение таблицы, когда данных очень много это может быть неудобно.
2. Вы можете добавить вычисления вне сводной таблицы, а в ней уже отобразить результат и делать расчеты дальше. Но в этом способе существует риск того, что если вы измените исходные данные таблица не сможет обновится и выдаст ошибку.
3. А еще есть вариант создать вычисляемое поле в сводной таблице. На мой взгляд, это самый эффективный способ делать расчеты. Вычисляемое поле — это как виртуальный столбец, вы видите только результат, а расчеты производятся “внутри”. Этот метод имеет много преимуществ:
   • Он не требует от вас работы с формулами или обновления исходных данных.
   • Он масштабируем, поскольку автоматически учитывает все новые данные, которые вы будете добавлять в сводную таблицу.
   • В нем легко менять данные. Например, вам нужно изменить расчет, вы можете легко сделать это из самой сводной таблицы.

Как добавить вычисляемое поле

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

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

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

• Выберите любую ячейку в сводной таблице;

• Перейдите в раздел «Анализ» -> «Поля, элементы и наборы»;

• Из выпадающего списка выберите «Вычисляемое поле»;

• В окне «Вставка вычисляемого поля»:
  • Введите название поля
  • И пропишите формулу для него. Вы можете выбирать из имен полей, перечисленных слева. В данном случае формула будет ’= Прибыль/Продажи’. Вы можете ввести имена полей вручную, либо выбрать поле и нажать «Добавить поле».

• Нажмите кнопку «Добавить» и подтвердите.

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

Теперь вы можете использовать созданное вычисляемое поле.

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

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

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

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

• При создании вычисляемого поля нельзя использовать ссылки или диапазоны элементов. Это исключает многие формулы, такие как VLOOKUP, INDEX, OFFSET и так далее. Однако вы можете использовать формулы, которые могут работать без ссылок (например, SUM, IF, COUNT и так далее…).
• В формуле можно использовать константу. Например, если вы хотите узнать прогнозируемый объем продаж, где прогнозируется его рост на 10%, вы можете использовать формулу =Sales*1.1 (где 1.1 — константа).
• В формуле, составляющей вычисляемое поле, соблюдается порядок математики, при проведении расчетов(первое действие будет в скобках, потом умножение/деление и так далее).

Теперь давайте рассмотрим пример использования более сложных формул.

Предположим, нам нужно рассчитать прогнозируемое значение продаж.

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

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

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

• Из выпадающего списка выберите Вычисляемое поле;

• В диалоговом окне «Вставка вычисляемого поля»:
  • Введите название.
  • И наберите такую формулу: =ЕСЛИ(Sales > 3000000,Sales *1.05,Sales *1.1);

• Нажмите кнопку «Добавить» и подтвердите.

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

Проблема c вычисляемым полем

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

Однако она не идеальна и с ней бывают проблемы.

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

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

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

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

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

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

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

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

• Выберите любую ячейку в таблице Pivot Table;

• Перейдите в раздел «Анализ» -> «Поля, элементы и наборы»;

• В раскрывающемся списке выберите Вычисляемое поле;

• В поле Имя нажмите на выпадающую стрелку (маленькая стрелка вниз);

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

• Если вы хотите изменить формулу, сначала поменяйте её, а потом нажмите кнопку «Изменить» или нажмите «Удалить», если вы хотите удалить ее.

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

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

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

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

• Выберите любую ячейку в сводной таблице;

• Перейдите в раздел «Анализ» -> «Поля, элементы и наборы»;

• Нажмите на кнопку «Вывести формулы».

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

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