макет MS Excel. Лабораторная работа 1 Редактирование рабочей книги. Построение диаграмм
 Скачать 0.97 Mb.
|
Лабораторная работа № 7 Работа со сводными таблицамиЦель работы: освоить навыки создания, редактирования и анализа данных на основе сводных таблиц. Задание. Построить сводную таблицу для расчета месячной заработной платы рабочих при повременной форме оплаты труда, начисления премии и учета удержаний. Премия дифференцирована по разрядам: 2 разряд 25%, 3 разряд 32%, 4 разряд 50% к тарифу, 5 разряд – 50%. Удержания берутся со всех видов начислений (зарплата, премия) и составляют 13% от суммы начислений. Методика выполнения работы Открыть новую книгу. Переименовать лист в Картотека. Подготовить исходные данные (табл. 5.12). Таблица 5.12
Установить курсор в список, выполнить команду меню ВставкаСводная таблица для вызова Мастера сводных таблиц и диаграмм. Указать тип источника – Создать таблицу на основе данных, находящихся в списке или базе данных Microsoft Excel. Выбрать вид создаваемого отчета – Сводная таблица. Выполнить проверку диапазон выделенных ячеек списка. Диапазон включает имена столбцов и все заполненные строки таблицы. Разместить поля в макете сводной таблицы: Фильтр отчета – Профессия, Названия строк – ФИО, Названия столбцов – Разряд работающего, Значения – Тариф, Операция – Сумма. Макет сводной таблицы представлен на рисунке 5.75.  Рисунок 5.75 – Макет сводной таблицы На ленте Конструктор выполнить команды:Общие итоги Включить по столбцам; Выбрать стиль сводной таблицы. На ленте Параметры: Сводная таблица Параметры. В открывшемся окне задать Для пустых ячеек отображать – пробел; Сохранять форматирование ячеек. Нажать кнопку ОК. В сводной таблице (рис. 5.76) представлен список всех работающих. Для каждого работающего указан только один тариф, соответствующий его разряду. Для преобразования сводной таблицы следует: Установить курсор в область сводной таблицы. Выполнить команду Параметры Формулы Вычисляемое поле для создания вычисляемого поля.  Рисунок 5.76 – Сводная таблица На рис. 5.77 представлено диалоговое окно для формирования вычисляемого поля. Имя поля – Зарплата, Формула вычисления: =Тариф*168. (Коэффициент 168 зависит от количества рабочих часов в текущем учетном периоде.). Для добавления поля в формулу можно воспользоваться кнопкой Добавить.  Рисунок 5.77 – Создание вычисляемого поля Установить курсор в область сводной таблицы. С помощью кнопки Список полейна ленте Параметры откройте макет сводной таблицы для корректировки. Удалить поле Сумма по полю Тариф (простым перетаскиванием мышкой за поле окна). Установить курсор в области сводной таблицы на поле Сумма по полю Зарплата. На ленте Параметры выполнить команду Активное поле Параметры поля(рис. 5.78): Изменить имя поля в сводной таблице – Месячная зарплата. Нажать кнопку Числовой формат и указать формат поля – Денежный. Нажать кнопку ОК.  Рисунок 5.78 – Задание параметров вычисляемого поля Установить курсор в область сводной таблицы на поле Разряд работающего. Создать вычисляемый объект Премия. Премия выплачивается как процент к начисленной заработной плате, дифференцируется по разрядам: 2 разряд – 25%, 3 разряд – 32%, 4 разряд – 50%, 5 разряд – 55%. На ленте Параметры выполнить командуФормулыВычисляемый объект(рис. 5.79). Указать имя объекта – Премия. Для построения формулы в окне Поля выбрать поле Разряд работающего, в окне Элементы выбрать элементы Формула: = ‘2’*0,25+’3’*0,32+’4’*0,5+’5’*0,55 Нажать кнопку Добавить. Закрыть окно – кнопка ОК.  Рисунок 5.79 – Создание вычисляемого объекта Установить курсор в область сводной таблицы на поле Разряд работающего. Создать вычисляемый объект Вычеты, сумма вычетов это 13% от суммы заработка и премии. Выполнить командуФормулы Вычисляемый объект. Указать имя объекта – Вычеты (рис. 5.80).  Рисунок 5.80 – Создание вычисляемого объекта В окне Поля выбрать поле Разряд работающего, в окне Элементы выбрать элементы для построения формулы вида: = –0,13*(’2’+’3’+’4’+’5’+Премия) Нажать кнопку Добавить. Закрыть окно – кнопка ОК. Выполнить команду ПараметрыФормулыВывести формулы для просмотра выражений вычисляемых полей и объектов (рис. 5.81). Если потребуется изменить нормативы (количество отработанных часов, % премии, % вычетов), следует отредактировать вычисляемые поля и объекты – команда меню ФормулыВывести формулы, вызывать поле/объект, внести изменения
Рисунок 5.81 – Вывод формул Переименовать лист, содержащий сводную таблицу, присвоив имя, Сводная таблица 1. Поставить курсор внутрь сводной таблицы и на ленте Конструктор выполнить команду Макет отчета. Выбрать тип отчета. Поставить курсор внутрь сводной таблицы и щелкнуть на ленте Параметры кнопку Сводная диаграмма. В готовой диаграмме перетащить Разряд работающего в область Поле ряда. Выбирая вид профессии просмотреть данные по различным профессиям. Сохранить рабочую книгу. Самостоятельное задание 1. На основании данных из лабораторной работы №3 (рис. 5.50) построить сводную таблицу следующего вида:
2. В построенной сводной таблице добавить среднее значение суммы продаж по магазинам для каждой категории фильма. Для этого воспользуйтесь созданием вычисляемого поля. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||