Методика выполнения работы

1. Открыть новую книгу.

2. Переименовать лист в Картотека.

3. Подготовить исходные данные (табл. 5.12).

Таблица 5.12

ФИО	Табельный №	Профессия	Разряд работающего	Тариф	Льготы
Иванов А.П.		Кладовщик		5,76р.
Колесов В.И.		Грузчик		5,76р.
Крылов А.Р.		Грузчик		6,79р.
Михайлов П.Р.		Грузчик		5,76р.
Смирнов И.А.		Кладовщик		6,79р.
Соколов Р.В.		Уборщик		4,95р.
Котов А.А.		Весовщик		7,78р.
Павлов Ф.Ф.		Весовщик		6,79р.
Соловьев А.П.		Упаковщик		5,76р.
Крылов А.Н.		Грузчик		7,78р.
Ильин А.Е.		Упаковщик		7,78р.

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

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

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

7. Разместить поля в макете сводной таблицы:

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

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

Рисунок 5.75 – Макет сводной таблицы

8. На ленте Конструктор выполнить команды: Общие итогиÞ Включить по столбцам; Выбрать стиль сводной таблицы. На ленте Параметры: Сводная таблицаÞ Параметры. В открывшемся окне задать Для пустых ячеек отображать – пробел; Сохранять форматирование ячеек. Нажать кнопку ОК.

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

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

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

2. Выполнить команду Параметры Þ Формулы Þ Вычисляемое поле для создания вычисляемого поля.

Рисунок 5.76 – Сводная таблица

3. На рис. 5.77 представлено диалоговое окно для формирования вычисляемого поля. Имя поля – Зарплата, Формула вычисления: =Тариф*168. (Коэффициент 168 зависит от количества рабочих часов в текущем учетном периоде.). Для добавления поля в формулу можно воспользоваться кнопкой Добавить.

Рисунок 5.77 – Создание вычисляемого поля

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

5. С помощью кнопки Список полей на ленте Параметры откройте макет сводной таблицы для корректировки.

6. Удалить поле Сумма по полю Тариф (простым перетаскиванием мышкой за поле окна).

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

8. На ленте Параметры выполнить команду Активное поле Þ Параметры поля (рис. 5.78):

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

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

Рисунок 5.78 – Задание параметров вычисляемого поля

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

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

- На ленте Параметры в ыполнить команду Формулы ÞВычисляемый объект (рис. 5.79). Указать имя объекта – Премия.

- Для построения формулы в окне Поля выбрать поле Разряд работающего, в окне Элементы выбрать элементы

- Формула: = ‘2’*0,25+’3’*0,32+’4’*0,5+’5’*0,55

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

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

Рисунок 5.79 – Создание вычисляемого объекта

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

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

Рисунок 5.80 – Создание вычисляемого объекта

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

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

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

12. Выполнить команду ПараметрыÞФормулыÞВывести формулы для просмотра выражений вычисляемых полей и объектов (рис. 5.81).

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

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

Рисунок 5.81 – Вывод формул

13. Переименовать лист, содержащий сводную таблицу, присвоив имя, Сводная таблица 1.

14. Поставить курсор внутрь сводной таблицы и на ленте Конструктор выполнить команду Макет отчета. Выбрать тип отчета.

15. Поставить курсор внутрь сводной таблицы и щелкнуть на ленте Параметры кнопку Сводная диаграмма.

16. В готовой диаграмме перетащить Разряд работающего в область Поле ряда. Выбирая вид профессии просмотреть данные по различным профессиям.

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

Самостоятельное задание

1. На основании данных из лабораторной работы №3 (рис. 5.50) построить сводную таблицу следующего вида:

Сумма по полю Продажа
Категория	Магазин	Всего
Боевик	Восток	146,5
	Запад	64,5
	Север	479,5
	Юг
Боевик Всего	830,5
Драма	Восток
	Запад
	Север
	Юг
Драма Всего

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

Лабораторная работа №8. Анализ «Что-Если»

Цель работы: освоить начальные навыки экономического анализа данных с помощью специальных инструментов Excel.

Задание

1. Рассчитать ежемесячную выплату при изменяющейся ставке и сумме кредита.

2. Применить к ячейкам с отрицательными значениями Условное форматирование.

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

Дата добавления: 2017-02-01