Практическая работа №5
(4 часа)
Тема: Подбор параметра. Обратный расчет.
Цель работы: Изучение технологии подбора параметра при обратных расчетах.
Постановка задачи: используя теоретические сведения, выполнить задания лабораторной работы и подготовить отчет на основании файла «Зарплата», созданного в работе №3
Теоретические сведения.
1. Использование операции Подбор параметра в MS Excel позволяет производить обратный расчет, когда задается конкретное значение рассчитанного параметра, и по этому значению подбирается некоторое удовлетворяющее заданным значениям, значение исходного параметра расчета. Подбор параметра осуществляется командой Сервис / Подбор параметра:
/>
Рисунок 1 – Задание параметров подбора параметра
Функция подбора параметра есть не что иное как численное решение линейных уравнений с одной переменной.
2. для копирования результатов расчетов в виде значений необходимо выделить копируемые данные, произвести запись в буфер памяти (Правка / Копировать), установить курсор в соответствующую ячейку таблицы ответов, задать режим специальной вставки (Правка / Специальная вставка), отметить в качестве объекта вставки – значения (рисунок 2). Такая специальная вставка позволяет копировать значения, полученные в результате расчетов, без дальнейших их зависимостей от пересчета формул.
/>
Рисунок 2 – Специальная вставка значений данных
Задание
1. Используя режим подбора параметра, определить при каком значении %Премии общая сумма зарплаты за октябрь будет равна 250000 рублей. (на основании файла «Зарплата», созданного ранее).
a. Скопировать содержимое листа «Зарплата за октябрь» на новый лист электронной книги. Присвоить скопированному листу имя «Подбор параметра».
b. Осуществить подбор параметра. В диалоговом окне Подбор параметра в качестве подбираемого параметра указать адрес общей итоговой суммы зарплаты, во второй ячейке указать заданное значение 250000, на третьей строке указать адрес подбираемого значения %Премии. Результат подбора привести в отчете.
2. Используя режим подбора параметра, определить штатное расписание фирмы. Исходные данные приведены на рисунке 1.
Известно, что штат работников фирмы включает в себя:
6 курьеров
8 младших менеджеров
10 менеджеров
3 заведующих отделами
Главный бухгалтер
Программист
Системный аналитик
Генеральный директор.
Общий месячный фонд зарплаты составляет 100000 рублей. Определить, какими должны быть оклады работников фирмы. Каждый оклад является линейной функцией от оклада курьера, а именно:
Зарплата = Ai*x + Bi
(1)
х – оклад курьера
Ai, Bi – коэффициенты, показывающие: A – во сколькораз превышается значение х, В – на сколько превышается значение х.
/>
Рисунок 1 – Исходные данные для задания 2.
а. Осуществить подбор параметра. В поле Установить в ячейке ввести ссылку на ячейку, содержащую формулу расчета фонда заработной платы. В поле Значение набрать искомый результат 100000. В поле Изменяя значение ячейки ввести ссылку на изменяемую ячейку, в которой находится значение зарплаты курьера.
b. Присвоить рабочему листу название «Штатное расписание 1». Сохранить созданную электронную книгу под именем «Штатное расписание».
3. Используя режим подбора параметра и таблицу расчета штатного расписания (см. задание 2), определить заработные платы сотрудников фирмы для ряда заданных значений фонда зарплаты (таблица 1). Последовательно определить зарплату сотрудников фирмы для различных значений фонда зарплаты. Результаты подбора значений зарплат скопировать в таблицу 2 в виде специальной вставки.
Таблица 1.
Должность
Вариант 1
Вариант 2
Вариант 3
ё
Вариант 5
КоэфА
КоэфВ
КоэфА
КоэфВ
КоэфА
КоэфВ
КоэфА
КоэфВ
Курьер
1
0
1
0
1
0
1
0
Младший менеджер
1,2
500
1,3
0
1,3
700
1,45
500
Менеджер
2,5
800
2,6
500
2,7
700
2,5
1000
Зав.отделом
3
1500
3,1
1200
3,2
800
3,1
1000
Гл.Бух
4
1000
4,1
1200
4,2
500
4,2
1200
Программист
1,5
1200
1,6
800
1,7
500
1,5
1300
Системный аналитик
3,5
0
3,6
500
3,7
800
3,5
1500
Ген.директор
5
2500
5,2
2000
5,3
1500
5,4
3000
Таблица 2.
Фонд зарплаты
100000
150000
200000
250000
300000
350000
400000
Должность
Зарплата
Зарплата
Зарплата
Зарплата
Зарплата
Зарплата
Зарплата
Курьер
?
?
?
?
?
?
?
Младший менеджер
?
?
?
?
?
?
?
Менеджер
?
?
?
?
?
?
?
Зав.отделом
?
?
?
?
?
?
?
Гл.Бух
?
?
?
?
?
?
?
Программист
?
?
?
?
?
?
?
Системный аналитик
?
?
?
?
?
?
?
Ген.директор
?
?
?
?
?
?
?
Содержание отчета:
Пошаговое описание процесса выполнения задания на лабораторную работу с использованием скриншотов.