Сводная таблица Microsoft Excel

Сводная таблица обеспечивает формирование сводной (агрегированной) информации в структурированном виде. Источниками данных для формирования сводной таблицы являются:

• Список (база данных) Excel на листе текущей рабочей книги.

• Внешний источник данных (текстовый файл, содержащий табличные данные, реляционная база данных).

• Диапазоны ячеек для консолидации текущей рабочей книги.

• Другая сводная таблица.

Построение сводной таблицы осуществляется с помощью Мастера сводных таблиц – команда меню ДанныеСводная таблица:

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

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

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

• На третьем шаге осуществляется формирование структуры сводной таблицы. Для этого нажимается кнопка Макет. Дополнительная настройка параметров сводной таблицы выполняется при нажатии кнопки Параметры.

Макет сводной таблицы включает области:

• Страница – для размещения полей фильтрации (отбора) записей, отображаемых в сводной таблице;

• Строка – для размещения полей группирования, учитывается последовательность полей для создания вложенных групп, подгрупп и т.д.;

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

• Данные - для размещения полей итогов.

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

Элементы полей группирования могут объединяться в новые группы. Для группы полей осуществляется выбор типа итога, скрытие/отображение деталей подчиненных полей.

Правило размещения сводной таблицы:

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

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

В табл. 20 приведены ограничения, которые действуют для сводных таблиц Microsoft Excel 2000.

Таблица 20

Для работы со сводной таблицы используется панель инструментов Сводные таблицы – рис. 19.

Рисунок 19

• Сводная таблица – вызов меню команд для выбора:

• Формат отчета – выбор формата отчета для сводной таблицы.

• Сводная диаграмма – построение диаграммы по сводной таблице.

• Мастер сводных таблиц – переход к 3 шагу построения сводной таблицы (изменение макета, настройка параметров, выбор места размещения сводной таблицы).

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

• Обновить данные сводной таблицы при изменении источника.

• Настройка клиент-сервера при использовании OLAP-куба для построения сводной таблицы.

• Выделить отдельные части сводной таблицы: заголовки, данные или таблицу целиком.

• Формулы – создание вычисляемых полей в области данных или вычисляемых объектов/элементов в области заголовков.

• Параметры поля – настройка параметров поля сводной таблицы.

• Параметры таблицы – настройка параметров таблицы.

• Отобразить страницы – на отдельных листах книги.

Другие кнопки соответствуют командам: Формат отчета, Мастер диаграмм, Мастер сводных таблиц, Скрыть/Отобразить детали (для выделенного заголовка), Обновить данные, Параметры поля, Отобразить поля.

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

1. Вставить новый лист – команда меню ВставкаЛист, если необходимо.

2. Переименовать лист в Картотека – команда меню ФорматЛистПереименовать.

3. Подготовить исходные данные – табл. 21.

Таблица 21

4. Установить курсор в список, выполнить команду меню ДанныеСводная таблицадля вызова Мастера сводных таблиц и диаграмм.

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

6. На шаге 2 – выполнить проверку диапазон выделенных ячеек списка .

 Диапазон включает имена столбцов и все заполненные строки таблицы.

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

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

• Нажать кнопку ОК.

Макет сводной таблицы представлен на рис. 20.

Рисунок 20

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

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

• Нажать кнопку Готово.

В сводной таблице (рис. 21) представлен список всех работающих. Для каждого работающего указан только один тариф, соответствующий его разряду.

Рисунок 21

Для преобразования сводной таблицы следует:

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

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

• Имя поля – Зарплата, Формула вычисления: =Тариф*168.

 Коэффициент (168) зависит от количества рабочих часов в текущем учетном периоде.

На рис. 22 представлено диалоговое окно для формирования вычисляемого поля.

Рисунок 22

• Нажать кнопку Добавить для добавления нового поля в сводную таблицу.

3. Нажать кнопку ОК для закрытия диалогового окна.

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

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

• Удалить поле Сумма по полю Тариф.

• Нажать кнопку ОК.

• Нажать кнопку Готово.

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

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

• Изменить имя поля в сводной таблице – Месячная зарплата. Нажать кнопку Формат и указать формат поля – Денежный.

• Нажать кнопку ОК.

Рисунок 23

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

9. Создать вычисляемый объект Премия. Премия выплачивается как процент к начисленной заработной плате, дифференцируется по разрядам: 2 разряд – 25%, 3 разряд – 32%, 4 разряд – 50%, 5 разряд – 55%.

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

Рисунок 24

• Выполнить команду ФормулыВычисляемый объект (рис. 24). Указать имя объекта – Премия.

• Для построения формулы в окне Поля выбрать поле Разряд работающего, в окне Элементы выбрать элементы. Формула: = ‘2’*0,25+’3’*0,32+’4’*0,5+’5’*0,55

• Нажать кнопку Добавить.

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

10. Установить курсор в область сводной таблицы на поле Разряд работающего. Создать вычисляемый объект Вычеты, сумма вычетов это 13% от суммы заработка и премии.

• Нажать кнопку Сводная таблица. Выполнить команду ФормулыВычисляемый объект (25). Указать имя объекта – Вычеты.

Рисунок 25

• В окне Поля выбрать поле Разряд работающего, в окне Элементы выбрать элементы для построения формулы вида: = –0,13(’2’+’3’+’4’+’5’+Премия)

• Нажать кнопку Добавить.

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

11. Нажать кнопку Сводные таблицы, выполнить команду меню ФормулыВывести формулы для просмотра выражений вычисляемых полей и объектов (рис. 26).

 Если потребуется изменить нормативы (количество отработанных часов, % премии, % вычетов), следует отредактировать вычисляемые поля и объекты – команда меню ФормулыВывести формулы, вызывать поле/объект, внести изменения.

12. Перейти на лист диаграммы и просмотреть диаграмму для определенного вида профессии. Изменить тип диаграммы.

Рисунок 26

Сводную таблицу легко структурировать. Элементы поля группировки в строке/столбце можно отсортировать. Курсором выделяется имя поля группировки, выполняется команда меню ДанныеСортировка.

Выделенные элементы полей группирования (в т.ч. и несмежные) области строк/столбцов можно объединить в новые группы – команда меню ДанныеГруппы и структураГруппировать. В результате создается новый иерархический уровень – новое поле сводной таблицы.

 Если элементы имеют числовое значение или тип даты, автоматически определяется диапазон их значений и предлагается шаг группировки, который можно изменять. Для поля даты шаг группировки – несколько дней, месяц, квартал, год; для числовых полей – число.

Для отказа от группировки выполняется команда меню ДанныеГруппа и структураРазгруппировать. В дополнение к группированию существует возможность отобразить/скрыть детали сводной таблицы. В сводных таблицах можно изменять, дополнительно настраивать итоги и поля (переименовать, группировать значения полей, формировать дополнительные итоги).

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

Таблица 22