Лабораторная работа 8. Подбор параметра
Подбор параметра является частью блока задач, который используется тогда, когда желаемый результат известен, но неизвестны значения, которые требуется ввести для получения этого результата. Подбор параметра – это способ поиска определенного значения ячейки путем изменения значения в другой ячейке. При подборе параметра значение в ячейке изменяется до тех пор, пока формула, зависящая от этой ячейки, не вернет требуемый результат.
С помощью такой технологии можно, например, решить математическое уравнение. Процесс решения с помощью данного метода распадается на два этапа:
1. Задание на рабочем листе ячеек, содержащих переменные решаемого уравнения (так называемых влияющих ячеек), и ячейки, содержащей формулу уравнения (зависимой или целевой ячейки).
2. Ввод адресов влияющих и целевой ячеек в диалоговое окно Подбор параметра и получение ответа (или сообщения о его отсутствии или невозможности найти).
Пусть надо найти решение уравнения x3-3x2+x=-1. Алгоритм решения задачи:
Занести в ячейку А1 (играет роль переменной х) значение 0.
Занести в ячейку В1 левую часть уравнения, используя в качестве независимой переменной ссылку на ячейку А1: иметь вид =А1^3-3*A1^2+A1.
Вызвать команду Подбор параметра через вкладку Данные, группу опций Работа с данными, опцию Анализ «что-если». В поле Установить в ячейке указать В1, в поле Значение задать значение –1 – это константа из правой части уравнения, в поле Изменяя значение ячейки указать А1, ОК. Табличный процессор будет менять значение переменной х и по формуле в ячейке В1 рассчитывать значение функции, стремясь достичь значение -1.
Посмотреть на результат подбора, отображаемый в диалоговом окне Результат подбора параметра. Нажать ОК, чтобы сохранить полученные значения ячеек, участвовавших в операции.
Таким образом, полученное значение в ячейке А1 – требуемое решение уравнения.
Можно также решать экономические задачи.
Задача 1. Используя режим Подбора параметра, надо определить, при каком значении процента премии общая сумма месячной заработной платы всех сотрудников организации, предназначенная к выдаче, будет равна 250 000 руб.
Исходные данные этого примера приведены ниже на рисунке, где знаком «?» отмечены ячейки с расчетными данными.
Для решения задачи, прежде всего, необходимо произвести расчёты во всех столбцах таблицы:
Для расчета премии использовать зафиксированный в ячейке d5 процент ее начисления, который может меняться со временем: Премия = Оклад * % Премии,
Всего начислено = Оклад + Премия.
Удержания = Всего начислено * % Удержаний (введен в ячейку f5),
К выдаче = Всего начислено — Удержания.
Подвести итоги по столбцам в строке Всего.
По столбцу К выдаче рассчитать среднее, максимальное и минимальное значения (в ячейках с22-с24).
Из расчетов видно, что общая сумма к выдаче при указанных окладах и премии в размере 27 % составляет 104 799,77 руб. чтобы достичь суммы к выдаче в пределах до 250 000 рублей (как задано в задаче), воспользуемся подбором параметра, для чего установим курсор в ячейке общей суммы К выдаче и обратимся к команде Подбор параметра.
В диалоговом окне «Подбор параметра» в строке Установить в ячейке в качестве подбираемого параметра должен находиться адрес той ячейки, в которой необходимо получить заданное значение. В данном случае это общая итоговая сумма зарплаты к выдаче (ячейка G20, на которой установлен курсор). В строке «Значение» вводим желаемое значение параметра, в данном примере это - 250 000, в строке «Изменяя значение ячейки» указываем адрес подбираемого значения «% Премии» (ячейка D5), ОК.
Произойдёт почти моментальный пересчёт всей таблицы, и откроется окно «Результат подбора параметра», в котором даем подтверждение подобранному параметру нажатием ОК.
Итак, произошёл обратный пересчёт «% Премии». Результаты подбора выглядят так: если сумма к выдаче равна 250 000 руб., то процент премии должен быть 203%. Задача 2. Используя режим подбора параметра, определить штатное расписание фирмы. Исходные данные приведены на рисунке ниже:
Общий месячный фонд зарплаты составляет 100 000 руб.
Известно, что каждый оклад является линейной функцией от оклада курьера, а именно: зарплата сотрудника рассчитывается по формуле А*Z+В, где Z — оклад курьера; А и В — коэффициенты, показывающие соответственно, во сколько раз превышается значение Z и на сколько превышается значение Z.
Необходимо узнать, какими должны быть оклады сотрудников фирмы.
Для решения задачи необходимо создать таблицу штатного расписания фирмы по приведённому образцу, выполнив все необходимые расчеты:
В столбце Зарплата сотрудника ввести формулу для расчёта заработной платы по каждой должности.
В столбце Суммарная зарплата определить заработную плату всех работающих в данной должности путем умножения заработной платы работника на количество работающих в этой должности.
В ячейке G12 вычислить суммарный фонд заработной платы фирмы.
Произвести подбор зарплат сотрудников фирмы для суммарной заработной платы в размере 100 000 руб. с помощью команды Подбор параметра:
В поле Установить в ячейке появившегося окна ввести ссылку на ячейку G12, содержащую формулу расчёта фонда заработной платы;
в поле Значение ввести искомый результат — 100000;
в поле Изменяя значение ячейки ввести ссылку на изменяемую ячейку D17, в которой находится значение зарплаты курьера,
щелкнуть ОК.
Произойдёт обратный расчет зарплаты сотрудников по заданному условию при фонде зарплаты, равном 100 000 руб. Итог обработанного пересчета таков: при фонде зарплаты, равном 100 000 руб., зарплата курьера должна составлять 1289,66 руб.
Следует заметить, что если возникает необходимость копировать результаты, полученные подбором параметра, то следует производить копирование полученных данных в виде значений с использованием Специальной вставки. Задание
Выполнить рассмотренные примеры решения двух экономических задач.
Имея исходные данные, рассчитайте сумму премии и сумму к выдаче. При каком проценте премии общая сумма к выдаче будет равна 100000 руб. ? Рассчитайте средний доход за месяц.
Рассчитайте сумму оклада (МРОТ*коэф*разряд), сумму премии (50% от оклада), сумму удержаний в пенсионный фонд (28% от оклада + премия) и сумму к выдаче. Определите, каким должен быть коэффициент, чтобы итоговая сумма к выдаче составила 200000 руб. Исходные данные приведены в таблице ниже:
Показать результат преподавателю
|