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

Настройка вычислений в сводных таблицах

62615 20.11.2012 Скачать пример

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

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

Другие функции расчета вместо банальной суммы

Если щелкнуть правой кнопкой мыши по расчетному полю в области данных и выбрать из контекстного меню команду Параметры поля (Field Settings) или в версии Excel 2007 – Параметры полей значений (Value Field Settings), то откроется очень полезное окно, используя которое можно задать кучу интересных настроек:

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

По умолчанию, для числовых данных Excel всегда автоматически выбирает суммирование (Sum), а для нечисловых (даже если из тысячи ячеек с числами попадется хотя бы одна пустая или с текстом или с числом в текстовом формате) – функцию подсчета количества значений (Count).

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

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

Долевые проценты

Если в этом же окне Параметры поля нажать кнопку Дополнительно (Options) или перейти на вкладку Дополнительные вычисления (в Excel 2007-2010), то станет доступен выпадающий список Дополнительные вычисления (Show data as):

В этом списке, например, можно выбрать варианты

Доля от суммы по строке (% of row), Доля от суммы по столбцу (% of column) или Доля от общей суммы (% of total), чтобы автоматически подсчитать проценты для каждого товара или города. Вот так, например, будет выглядеть наша сводная таблица с включенной функцией Доля от суммы по столбцу:

Динамика продаж

Если в выпадающем списке Дополнительные вычисления (Show data as) выбрать вариант Отличие (Difference), а в нижних окнах Поле (Base field) и Элемент (Base item) выбрать Месяц и Назад (в родной англоязычной версии вместо этого странного слова было более понятное Previous, т.е. предыдущий):

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

А если заменить Отличие (Difference) на Приведенное отличие (% of difference) и добавить условное форматирование для выделения отрицательных значений красным цветом — то получим то же самое, но не в рублях, а в процентах:

P.

S.

В Microsoft Excel 2010 все вышеперечисленные настройки вычислений можно проделать еще проще — щелкнув правой кнопкой мыши по любому полю и выбрав в контекстном меню команды Итоги по (Summarize Values By):

… и Дополнительные вычисления (Show Data as):

Также в версии Excel 2010 к этому набору добавились несколько новых функций:

Ссылки по теме

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

 

Создание вычисляемого поля сводной таблицы Excel 2010 — Сводные таблицы Excel 2010

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

Теперь, когда в нашем распоряжении имеется сводная таблица, наступило время для создания, вычисляемого поля. Чтобы создать вычисляемое поле, активизируйте диалоговое окно Вставка вычисляемого поля (Insert Calculated Field).

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

Чтобы открыть его, на контекстной вкладке ленты Параметры (Options), выбранной в группе контекстных вкладок Работа со сводными таблицами (PivotTable Tools), перейдите в группу Вычисления (Calculations), щелкните на кнопке Поля, элементы и наборы (Fields, Items & Sets) и выберите в раскрывающемся меню команду Вычисляемое поле (Calculated Field), как показано на рис. 5.7.

Рис. 5.7. Создание вычисляемого поля начинается с этого раскрывающегося меню

На экране появится диалоговое окно Вставка вычисляемого поля (Insert Calculation Field), как показано на рис. 5.8.

Рис. 5.8. Диалоговое окно Вставка вычисляемого поля поможет в создании вычисляемого поля для сводной таблицы

В верхней части диалогового окна имеются два текстовых поля: Имя (Name) и Формула (Formula). В этих полях следует задать имя вычисляемого поля и создать формулу, указав необходимые поля данных и математические операторы. На рис. 5.9 показано, что вычисляемому полю присвоено описательное имя Средняя выручка за час. Это имя должно точно характеризовать тип выполняемой математической операции.

Рис. 5.9. Выбор для вычисляемого поля описательного имени

Далее перейдите к списку Поля (Fields) и дважды щелкните на поле Объем продаж. Введите символ косой черты /, чтобы указать программе Excel, что вы будете делить значение поля Объем продаж на другой элемент.

По умолчанию текстовое поле Формула диалогового окна Вставка вычисляемого поля содержит выражение = 0. Перед вводом собственной формулы следует удалить нуль.

С этого момента диалоговое окно должно выглядеть так, как показано на рис. 5.10.

Рис. 5.10. Начните ввод формулы со значения = ‘Объем продаж’/

Закончите ввод формулы, дважды щелкнув на поле Период продаж (в часах). Готовая формула показана на рис. 5.11.

Рис 5.11. Полная формула = ‘Объем продаж’/’Период продаж (в часах)’ отображает требуемое вычисляемое поле

Щелкните на кнопке Добавить (Add), а затем — на кнопке ОК для активизации нового вычисляемого поля. Как видно на рис. 5.12, в результате вы получите вычисляемое поле внутри сводной таблицы.

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

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

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

Означает ли это, что вы добавили столбец в источник данных? Нет. Вычисляемые поля похожи на элементы Промежуточная сумма (Subtotal) и Общая сумма (Grand Total) сводной таблицы, задаваемые по умолчанию, так как все они являются математическими функциями, которые выполняют перерасчет данных при изменении или обновлении сводной таблицы. Вычисляемые поля просто имитируют строго заданные поля в источнике данных. Можете перетаскивать их, изменять настройки полей, а также использовать вместе с другими вычисляемыми полями.

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

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

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

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

Это руководство охватывает:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

• Вы НЕ МОЖЕТЕ использовать ссылки или именованные диапазоны при создании вычисляемого поля сводной таблицы. Это исключило бы множество формул, таких как ВПР, ИНДЕКС, СМЕЩ и т. д. Однако вы можете использовать формулы, которые могут работать без ссылок (например, СУММ, ЕСЛИ, СЧЕТ и т. д.).
• В формуле можно использовать константу. Например, если вы хотите узнать прогнозируемый объем продаж, при котором прогнозируется рост на 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 автоматически вставит новый рабочий лист, содержащий сведения обо всех вычисляемых полях/элементах, которые вы использовали в сводной таблице.

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

Вы также можете найти следующие полезные руководства по сводным таблицам:

• Подготовка исходных данных для сводной таблицы.
• Использование срезов в сводной таблице Excel: руководство для начинающих.
• Как группировать даты в сводных таблицах в Excel.
• Как сгруппировать числа в сводной таблице в Excel.
• Как фильтровать данные в сводной таблице в Excel.
• Как заменить пустые ячейки нулями в сводных таблицах Excel.
• Как применить условное форматирование к сводной таблице в Excel.
• Сводной кэш в Excel — что это такое и как его лучше всего использовать?
• Как удалить сводную таблицу в Excel
• Как показать список полей сводной таблицы? (Вернуться в меню сводной таблицы)

Добавить вычисляемое поле и элемент (формулы)

Главная ➜ Сводная таблица ➜ Вычисляемое поле и элемент (формулы) в сводной таблице

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

Вычисляемые элементы 9000 в сводной таблице

Что нужно помнить

Подробнее о сводных таблицах

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

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

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

ПРИМЕЧАНИЕ. Работа со сводными таблицами — это один из ПРОМЕЖУТОЧНЫХ НАВЫКОВ EXCEL.

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

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

1. Прежде всего, вам нужна простая сводная таблица для добавления вычисляемого поля.
   
2. Просто щелкните любое поле в сводной таблице. На ленте вы увидите параметр сводной таблицы, который имеет еще два параметра 9.0005 (Анализ и дизайн) Нажмите на опцию анализа, затем на Поля, элементы и наборы . Далее вы получите список опций, просто нажмите на вычисляемое поле.
   
3. После нажатия на вычисляемое поле появится всплывающее меню, как показано ниже. Это всплывающее меню содержит два параметра ввода (имя и формула) и вариант выбора.
   
4. Имя: Имя вычисляемого поля, которое будет отображаться в вашей сводной таблице.
5. Формула: Параметр ввода для вставки формулы для вычисляемого поля.
6. Поля: Опция раскрывающегося списка для выбора других полей из исходных данных для расчета нового поля.

Вычисляемые элементы в сводной таблице

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

1. Просто щелкните любой элемент сводной таблицы. На ленте вы увидите опцию сводной таблицы с двумя дополнительными опциями («Анализ» и «Дизайн»).
2. Нажмите «Анализ», затем «Поля, элементы и наборы».
3. Далее вы получите список опций, просто нажмите «Вычисляемый элемент».
   
4. После нажатия на вычисляемый элемент вы получите всплывающее меню, как и выше. Это всплывающее меню содержит два параметра ввода (имя и формула) и два параметра выбора (поле и элементы).
5. Мы уже обсуждали « Имя, формула и поля » в вычисляемых полях.
6. Элементы : Для выбора элементов для расчета.
7. В этом примере мы собираемся рассчитать среднюю цену продажи, и формула будет = количество/количество.
8. В опции «Поля» выберите «Сумма» и нажмите «Вставить», затем вставьте оператор деления «/» и после этого введите количество.
9. Нажмите OK.
10. Теперь в сводной таблице появилось новое поле.
    
11. Новое вычисляемое поле создано без числового формата.
    

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

=среднее(январь, февраль, март, апрель, май, июнь)

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

Но подождите минутку. Что это? Общая сумма изменена с 1 506 и 311 820 долларов США на 1 746 и 361 600 долларов США.

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

Что нужно помнить

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