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

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

В сводной таблице, показанной на рис. 5.20, указывается общий объем продаж для определенных торговых периодов (поле Торговый период).

Рис. 5.20. Вам необходимо сравнить объем продаж за шесть последних периодов с объемом продаж семи предыдущих периодов

Рис. 5.20. Вам необходимо сравнить объем продаж за шесть последних периодов с объемом продаж семи предыдущих периодов

Представьте себе, что нужно сравнить средний объем продаж последних шести периодов со средним объемом продаж предыдущих семи периодов. Если быть более точным, то нам требуется определить среднее для периодов Р01-Р07 и сравнить его со средним для периодов Р08 — Р13.

Поместите указатель в любую ячейку поля Торговый период и щелкните в разделе Вычисления контекстной вкладки ленты Параметры на кнопке Поля, элементы и наборы. В открывшемся меню выберите команду Вычисляемый объект (Calculated Item) (рис. 5.21).

Рис. 5.21. Начните создание вычисляемого элемента с выполнения этой команды

Рис. 5.21. Начните создание вычисляемого элемента с выполнения этой команды

Откроется диалоговое окно Вставка вычисляемого элемента (Insert Calculated Item), показанное на рис. 5.22. Обратите внимание на то, что в верхней части диалогового окна указано, с каким полем вы работаете. В данном случае это поле Торговый период. Кроме того, список содержит все элементы поля Торговый период.

Рис. 5.22. Диалоговое окно Вставка вычисляемого элемента поможет вам в создании вычисляемого элемента

Рис. 5.22. Диалоговое окно Вставка вычисляемого элемента поможет вам в создании вычисляемого элемента

Ваша цель заключается в том, чтобы присвоить вычисляемому элементу имя, а затем создать формулу, указав необходимую комбинацию элементов данных и операторов, которые обеспечат правильный результат. Вычисляемому элементу в нашем примере присвоено имя Среднее по периодам Р01-Р07 (рис. 5.23).

Рис. 5.23. Вычисляемому элементу присвойте описательное имя

Рис. 5.23. Вычисляемому элементу присвойте описательное имя

Как уже отмечалось, поле ввода формулы изначально содержит выражение = 0. Перед непосредственным вводом формулы удалите нуль. Формула вводится в поле Формула, а необходимые элементы данных, применяемые в ней, выбираются в списке Элементы. В нашем примере необходимо создать следующую формулу: СРЗНАЧ (Р01, Р02, РОЗ, Р04, РОБ, Р06, Р07). На этом этапе ваше диалоговое окно должно выглядеть примерно так, как показано на рис. 5.24.

Рис. 5.24. Введенная формула вычисляет среднее значение периодов Р01-Р07

Рис. 5.24. Введенная формула вычисляет среднее значение периодов Р01-Р07

Таким образом, вы получили требуемый вычисляемый элемент. Щелкните на кнопке ОК для активизации нового вычисляемого элемента. Вы успешно добавили элемент данных Среднее по периодам Р1-Р7, показанный на рис. 5.25.

Рис. 5.25. Вычисляемый элемент успешно добавлен в сводную таблицу

Рис. 5.25. Вычисляемый элемент успешно добавлен в сводную таблицу

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

Создайте вычисляемый элемент, который будет представлять средний объем продаж для периодов Р08-Р13, как показано на рис. 5.26.

Рис. 5.26. Создайте второй вычисляемый элемент

Рис. 5.26. Создайте второй вычисляемый элемент

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

Рис. 5.27. Теперь можно быстро сравнить предыдущие семь периодов с текущими шестью периодами

Рис. 5.27. Теперь можно быстро сравнить предыдущие семь периодов с текущими шестью периодами

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


Top