Вычисляемые поля и элементы

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

Сводная таблица имеет набор параметров, влияющих на внешнее представление. Кнопка Параметры вызывает диалоговое окно настройки формата сводной таблицы и данных внешнего источника (рисунок 4).

Рисунок 4. Параметры сводной таблицы.

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

Дополнительная обработка полей сводной таблицы

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

Таблица 4. Виды дополнительной обработки поля.

Отличие	Содержание обработки
Доля	Отношение значений поля области данных к соответствующим значениям этого поля в указанной строке или столбце сводной таблицы, которые задаются путем выбора поля группировки и его отдельного элемента
Приведенное поле	Отношение разности значения поля области данных и соответствующих значений поля в указанной строке или столбце сводной таблицы
С нарастающим итогом поле	Нарастающий итог значений поля области данных в соответствующих строках или столбцах сводной таблицы, ориентация задается путем выбора поля группировки
Доля от суммы по строке	Значение поля области данных вычисляется в процентах от итога строки
Доля от суммы по столбцу	Значение поля области данных вычисляется в процентах от итога столбца
Доля от общей суммы	Значение поля области данных вычисляется в процентах от общего итога сводной таблицы
Индекс	Значение поля области данных вычисляется по формуле: ((значение поля)*(общий итог))/((итог строки)*(итог столбца))

2. Практическая часть

Задание 1. Построить сводную таблицу для расчета месячной зарплаты рабочих при повременной форме оплаты труда.

Установлена премия по разрядам работающих: 2 разряд 25%, 3 – 32%, 4 – 50% к тарифу. Вычеты из всех видов начислений составляют 13%.

Последовательность действий:

1. Выбрать лист Картотека.

2. Установить курсор в список на листе Картотека.

3. Выполнить команду меню Данные→Сводная таблица для вызова мастера сводных таблиц и диаграмм.

4. На шаге 1 указать тип источника – Создать таблицу на основе данных, находящихся в списке или базе данных Microsoft Excel. Вид создаваемого отчета – Сводная диаграмма (со сводной таблицей).

5. На шаге 2 проверить диапазон ячеек для построения сводной таблицы для списка Картотека.

6. На шаге 3 – нажать кнопку Макет, разместить поле в макете сводной таблицы:

• страница – Профессия, строка – Разряд работающего, столбец – ФИО, данные – Тариф;

• Нажать ОК.

1. Задать параметры сводной таблицы с помощью кнопки Параметры: Общая сумма по столбцам; Автоформат; Сохранять форматирование. Для пустых ячеек отображать –Пробел; Пометить таблицу – в Новый лист.

2. Нажать Готово.

3. Установить курсор в область сводной таблицы.

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

• Имя поля – Зарплата, Формула: =Тариф*168.

• Кнопка Добавить.

• Закрыть окно – ОК.

1. Установить курсор в область сводной таблицы.

2. На панели инструментов Сводная таблица нажать кнопку Мастер сводных таблиц. В окне мастера сводных таблиц на третьим шаге нажать кнопку Макет:

• Убрать из области Данные поле Сумма по полю Тариф.

• Нажать ОК.

• Нажать Готово.

1. Установить курсор в области сводной таблицы на поле Сумма по полю зарплата.

2. На панели инструментов Сводная таблица нажать кнопку Параметры поля:

• Изменить имя поля – Месячная зарплата.

• Выбрать формат поля – Денежный.

• Нажать ОК.

1. Установить курсор в области сводной таблицы на поле Разряд работающего.

2. На панели инструментов Сводная таблица нажать кнопку Сводная таблица.

3. Выполнить команду Контекстного меню Формулы →Вычисляемый объект:

• Ввести имя поля – Премия;

• Выбрать поле Разряд работающего, раскрыть список элементов (двойной щелчок левой кнопкой мыши на поле);

• В окно формулы ввести формулу: = '2'*0,25+ '3'*0,32+ '4'*0,5

• Нажать Добавить;

• Ввести имя поля Вычеты;

• Выбрать поле Разряд работающего, раскрыть список элементов;

• Ввести формулу: = -0,13*('2'*1,25+ '3'*1,32+ '4'*1,5)

• Нажать Добавить;

• Нажать ОК.

18. Для просмотра выражения вычисляемого поля и вычисляемых объектов на панели инструментов Сводные таблицы нажать одноименную кнопку, выбрать команду контекстного меню Формулы→Ввести формулы(таблица 5).

Таблица 5. Лист формул сводной таблицы.

Вычисляемое поле
Порядок решения	Поле	Формула
1	Зарплата	=Тариф*168
Вычисляемый объект
Порядок решения	Объект	Формула
1	Премия	= '2'*0,25+ '3'*0,32+ '4'*0,5
2	Вычеты	= -0,13('2'*1,25+ '3'*1,32+ '4'*1,5)

19. Установить курсор в сводную таблицу.

20. На панели инструментов Сводная таблица нажать кнопку Мастер сводных таблиц. В окне третьего шага кнопка Макет изменить положение полей:

• строка – ФИО, столбец – Разряд работающего;

• ОК.

• Нажать Готово.

21. Установить курсор в области сводной таблицы.

22. На панели инструментов Сводная таблица нажать кнопку Мастер диаграмм для построения диаграммы.

23. Сохранить рабочую книгу.