Практическая работа
Работа со сводными таблицами
Цель работы: освоить навыки создания, редактирования и анализа данных на основе сводных таблиц.
Задание.
Построить сводную таблицу для расчета месячной заработной платы рабочих при повременной форме оплаты труда, начисления премии и учета удержаний. Премия дифференцирована по разрядам: 2 разряд 20%, 3 разряд 30%, 4 разряд 40% к тарифу, 5 разряд – 50%. Удержания берутся со всех видов начислений (зарплата, премия) и составляют 13% от суммы начислений.
Методика выполнения работы
1. Открыть новую книгу.
2. Переименовать лист в Картотека.
3. Подготовить исходные данные (см. табл. 1)
Таблица 1.
4. Установить курсор в список, выполнить команду меню Вставка→ Сводная таблица для вызова Мастера сводных таблиц и диаграмм.
4. Указать тип источника – Создать таблицу на основе данных, находящихся в списке или базе данных Microsoft Excel. Выбрать вид создаваемого отчета → Сводная таблица.
4. Выполнить проверку диапазон выделенных ячеек списка. Диапазон включает имена столбцов и все заполненные строки таблицы.
4. Разместить поля в макете сводной таблицы:
Фильтр отчета – Профессия, Названия строк – ФИО, Названия столбцов –
Разряд работающего, Значения – Тариф, Операция – Сумма.
Макет сводной таблицы представлен на рисунке 1.
Рис 1. Макет сводной таблицы
8. На ленте Конструктор выполнить команды: Общие итоги → Включить по столбцам; Выбрать стиль сводной таблицы. На ленте Параметры: Сводная таблица → Параметры. В открывшемся окне задать Для пустых ячеек отображать – пробел; Сохранять форматирование ячеек. Нажать кнопку ОК.
В сводной таблице (рис. 2) представлен список всех работающих. Для каждого работающего указан только один тариф, соответствующий его разряду.
Для преобразования сводной таблицы следует:
1. Установить курсор в область сводной таблицы.
2. Выполнить команду Параметры (Анализ – MS Excel 2013) → Формулы → Вычисляемое поле
для создания вычисляемого поля.
Рис. 2. Сводная таблица.
3. На рис. 3 представлено диалоговое окно для формирования вычисляемого поля. Имя поля – Зарплата, Формула вычисления: =Тариф*168.
(Коэффициент 168 зависит от количества рабочих часов в текущем учетном периоде.). Для добавления поля в формулу можно воспользоваться кнопкой Добавить.
Рис. 3. Создание вычисляемого поля
4. Установить курсор в область сводной таблицы.
5. С помощью кнопки Список полей на ленте Параметры откройте макет
сводной таблицы для корректировки.
6. Удалить поле Сумма по полю Тариф (простым перетаскиванием мышкой за поле окна).
6. Установить курсор в области сводной таблицы на поле Сумма по полю Зарплата.
6. На ленте Параметры выполнить команду Активное поле → Параметры поля (рис. 4):
· Изменить имя поля в сводной таблице – Месячная зарплата. Нажать кнопку Числовой формат и указать формат поля – Денежный.
· Нажать кнопку ОК.
Рис. 4. Задание параметров вычисляемого поля
9. Установить курсор в область сводной таблицы на поле Разряд работающего.
10. Создать вычисляемый объект Премия. Премия выплачивается как процент к начисленной заработной плате, дифференцируется по разрядам: 2 разряд – 20%, 3 разряд – 30%, 4 разряд – 40%, 5 разряд – 50%.
· На ленте Параметры выполнить команду Формулы→Вычисляемый объект (рис. 5). Указать имя объекта – Премия.
· Для построения формулы в окне Поля выбрать поле Разряд работающего, в окне Элементы выбрать элементы
· Формула: = ‘2’*0,20+’3’*0,30+’4’*0,4+’5’*0,50
· Нажать кнопку Добавить.
· Закрыть окно – кнопка ОК.
Рис. 5. Создание вычисляемого объекта
11. Установить курсор в область сводной таблицы на поле Разряд работающего. Создать вычисляемый объект Вычеты, сумма вычетов это 13% от суммы заработка и премии.
- Выполнить команду Формулы → Вычисляемый объект. Указать имя объекта – Вычеты (рис. 6).
Рис. 6. Создание вычисляемого объекта
- В окне Поля выбрать поле Разряд работающего, в окне Элементы выбрать элементы для построения формулы вида:
· –0,13*(’2’+’3’+’4’+’5’+Премия)
o Нажать кнопку Добавить.
·
o Закрыть окно – кнопка ОК.
12. Выполнить команду Параметры→Формулы→Вывести формулы для просмотра выражений вычисляемых полей и объектов (рис. 7).
Если потребуется изменить нормативы (количество отработанных часов, % премии, % вычетов), следует отредактировать вычисляемые поля и объекты – команда меню Формулы→Вывести формулы, вызывать поле/объект, внести изменения
Рис. 7. Вывод формул
13. Переименовать лист, содержащий сводную таблицу, присвоив имя, Сводная таблица 1.
14. Поставить курсор внутрь сводной таблицы и на ленте Конструктор выполнить команду Макет отчета. Выбрать тип отчета.
15. Поставить курсор внутрь сводной таблицы и щелкнуть на ленте Параметры кнопку Сводная диаграмма.
16. В готовой диаграмме перетащить Разряд работающего в область Поле ряда. Выбирая вид профессии просмотреть данные по различным профессиям.
17. Сохранить рабочую книгу.