Создание вычисляемых полей и элементов

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

Прежде всего, приведем основные понятия.

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

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

Формулы, применяемые для создания вычисляемых полей и элементов, не являются стандартными формулами Excel. Другими словами, эти формулы не вводятся в ячейки – для их ввода используется специальное диалоговое окно. Эти формулы хранятся вместе с другими данными сводной таблицы.

В примерах, приведенных ниже, используется таблица базы данных рабочего листа, показанная на рисунке. Таблица содержит 5 полей и 48 записей. Каждая запись представляет информацию о месячных продажах некоторого торгового представителя. Например, Эльза, торговый представитель в северном регионе, продала за январь 239 единиц продукции на общую сумму 23049 долларов.

На рисунке ниже показана простая сводная таблица, созданная на основе этих данных. В ней показаны объемы продаж (область Значения), сгруппированные по месяцам (область Названия строк) и торговым представителям (область Названия столбцов).

В представленных далее примерах продемонстрировано следующее:

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

Создание вычисляемого поля

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

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

Примечание

Вычисляемые поля можно использовать только в области Значения сводной таблицы. В областях Названия столбцов, Названия строк и Фильтр отчета использовать их нельзя.

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

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

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

2. Выберите команду Работа со сводными таблицамиПараметрыВычисленияПоля, элементы и наборыВычисляемое поле.

3. В открывшемся диалоговом окне введите описательное имя поля и нужную формулу. Формула может содержать функции рабочего листа и другие поля источника данных. В примере мы назовем поле Средняя цена и введем следующую формулу:

=Продажи/’Единиц продано’

4. Щелкните на кнопке Добавить, и новое поле будет добавлено в список.

5. Щелкните на кнопке ОК, чтобы закрыть диалоговое окно.

Примечание

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

Программа Excel добавляет созданное вычисляемое поле в область Значения сводной таблицы (оно также появится на панели списка полей). С ним можно обращаться так же, как и с любым другим полем, за одним исключением: его нельзя переместить в область Фильтр таблицы, Названия строк или Названия столбца – оно должно оставаться в области Значения.

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

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

Вставка в сводную таблицу вычисляемого элемента

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

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

квартал 1 – 10% от объема продаж за январь, февраль и март;
квартал 2 – 11% от объема продаж за апрель, май и июнь;
квартал 3 – 12% от объема продаж за июль, август и сентябрь;
квартал 4 – 12,5% от объема продаж за октябрь, ноябрь и декабрь.

Примечание

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

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

1. Переместите курсор ячейки в область Названия строк или Названия столбцов сводной таблицы и выберите команду Работа со сводными таблицамиПараметрыВычисленияПоля, элементы и наборыВычисляемый объект.

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

=10%*(Янв+Фев+Мар)

3. Щелкните на кнопке Добавить.

4. Повторите пп. 2-3 для создания трех остальных вычисляемых элементов, используя следующие формулы:

• комиссия 2 кв.=11%*(Апр+Май+Июн);
• комиссия 3 кв.=12%*(Июл+Авг+Сен);
• комиссия 4 кв.=12,5%*(Окт+Ноя+Дек).

5. Щелкните на кнопке ОК, чтобы закрыть диалоговое окно.

Примечание

Вычисляемые элементы, в отличие от вычисляемых полей, не отображаются на панели списка полей.

Предупреждение

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

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

Обратите внимание, что по умолчанию вычисляемые элементы добавляются в конец списка. Однако при желании можно перетащить вычисляемые строки и заголовки в другое место. Альтернативой является создание для вычисляемых элементов отдельных групп. На рисунке ниже показаны две группы: одна – для объемов продаж и другая – для комиссионных вознаграждений. Это позволяет вычислять промежуточные итоги по каждой группе.

В начало

Полезное

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *