Сводные таблицы в Microsoft Office Excel 2007

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

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

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

Дадим основные определения.

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

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

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

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

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

Чтобы создать такое поле, выполните следующие действия.

1. Поместите табличный курсор в любую ячейку сводной таблицы.
2. Выберите команду Работа со сводными таблицами→Параметры→ Сервис→ Формулы→Вычисляемое поле. Откроется диалоговое окно Вставка вычисляемого поля.
3. Введите имя поля и определите формулу. В формуле можно использовать другие поля. В данном примере имя поля — Средняя стоимость, а формула имеет такой вид:

=Продажи/'Продано единиц'

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

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

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

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

Вставка вычисляемого элемента

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

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

• Квартал 1: 10% от суммарных продаж в январе, феврале и марте.
• Кварта! 2: 11% от суммарных продаж в апреле, мае и июне.
• Квартал 3: 12% от суммарных продаж в июле, ав1усте и сентябре.
• Квартал 4: 12,5% от суммарных продаж в октябре, ноябре и декабре.

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

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

1. Установите табличный курсор в область сводной таблицы Названия строк или Названия столбцов. Выберите команду Работа со сводными таблицами^→Параметры^→Сервис→формулы→ Вычисляемый объект. Откроется диалоговое окно Вставка вычисляемого элемента.
2. Введите имя нового элемента в поле Имя и задайте формулу в поле Формула В формуле можно использовать элементы других полей, но нельзя вводить функции рабочего листа. В этом примере новый элемент назван Кв-л1 Комиссионные, который вычисляется по формуле