Лабораторная работа 8. Подбор параметра

 

Подбор параметра является частью блока задач, который используется тогда, когда желаемый результат известен, но неизвестны значения, которые требуется ввести для получения этого результата. Подбор параметра – это способ поиска определенного значения ячейки путем изменения значения в другой ячейке. При подборе параметра значение в ячейке изменяется до тех пор, пока формула, зависящая от этой ячейки, не вернет требуемый результат.

С помощью такой технологии можно, например, решить математическое уравнение. Процесс решения с помощью данного метода распадается на два этапа:

1. Задание на рабочем листе ячеек, содержащих переменные решаемого уравне­ния (так называемых влияющих ячеек), и ячейки, содержащей формулу урав­нения (зависимой или целевой ячейки).

2. Ввод адресов влияющих и целевой ячеек в диалоговое окно Подбор параметра и получение ответа (или сообщения о его отсутствии или невозможности найти).

Пусть надо найти решение уравнения x³-3x²+x=-1. Алгоритм решения задачи:

1. Занести в ячейку А1 (играет роль переменной х) значение 0.

2. Занести в ячейку В1 левую часть уравнения, используя в качестве независимой переменной ссылку на ячейку А1: иметь вид =А1^3-3*A1^2+A1.

3. Вызвать команду Подбор параметра через вкладку Данные, группу опций Работа с данными, опцию Анализ « что-если ». В поле Установить в ячейке указать В1, в поле Значение задать значение –1 – это константа из правой части уравнения, в поле Изменяя значение ячейки указать А1, ОК. Табличный процессор будет менять значение переменной х и по формуле в ячейке В1 рассчитывать значение функции, стремясь достичь значение -1.

4. Посмотреть на результат подбора, отображаемый в диалоговом окне Результат подбора параметра. Нажать ОК, чтобы сохранить полученные значения ячеек, участвовавших в операции.

Таким образом, полученное значение в ячейке А1 – требуемое решение уравнения.

Можно такжерешать экономические задачи.

Задача 1. Используя режим Подбора параметра, надо определить, при каком зна­чении процента премии общая сумма месячной заработной платы всех сотрудников организации, предназначенная к выдаче, будет равна 250 000 руб.

Исходные данные этого примера приведены ниже на рисунке, где знаком «?» отмечены ячейки с расчетны­ми данными.

 

Для решения задачи, прежде всего, необходимо произвести расчёты во всех столбцах таблицы:

1. Для расчета премии использовать зафиксированный в ячейке d5 процент ее начисления, который может меняться со временем: Премия = Оклад * % Премии,

2. Всего на­числено = Оклад + Премия.

3. Удержания = Всего начислено * % Удержаний (введен в ячейку f5),

4. К выдаче = Все­го начислено — Удержания.

5. Подвести итоги по столбцам в строке Всего.

6. По столбцу К выдаче рассчитать среднее, максимальное и минимальное значения (в ячейках с22-с24).

Из расчетов видно, что общая сумма к выдаче при указанных окладах и пре­мии в размере 27 % составляет 104 799,77 руб. чтобы достичь суммы к выдаче в пределах до 250 000 рублей (как задано в задаче), воспользуемся подбором параметра, для чего установим курсор в ячейке об­щей суммы К выдаче и обратимся к команде Подбор параметра.

В диалоговом окне «Подбор параметра» в строке Установить в ячейке в качестве подбираемого параметра должен нахо­диться адрес той ячейки, в которой необходимо получить заданное значение. В данном случае это общая итоговая сумма зарплаты к выдаче (ячей­ка G20, на которой установлен курсор). В строке «Значение» вводим желаемое значение параметра, в данном примере это - 250 000, в строке «Изменяя значение ячейки» указываем адрес подбираемого значения «% Премии» (ячейка D5), ОК.

Произойдёт почти моментальный пересчёт всей таблицы, и от­кроется окно «Результат подбора параметра», в котором даем подтверждение подобранному параметру нажатием ОК.

Итак, произошёл обратный пересчёт «% Премии». Результа­ты подбора выглядят так: если сумма к выдаче равна 250 000 руб., то процент премии должен быть 203%.

 

Задача 2. Используя режим подбора параметра, определить штатное распи­сание фирмы. Исходные данные приведены на рисунке ниже :

 

 

Общий месячный фонд зарплаты составляет 100 000 руб.

Известно, что каждый оклад является линейной функцией от оклада курье­ра, а именно: зарплата сотрудника рассчитывается по формуле А*Z+В, где Z — оклад курьера; А и В — коэффициенты, показывающие соответственно, во сколь­ко раз превышается значение Z и на сколько превышается значе­ние Z.

Не­обходимо узнать, какими должны быть оклады сотрудников фирмы.

Для решения задачи необходимо создать таблицу штатного расписания фирмы по приведён­ному образцу, выполнив все необходимые расчеты:

1. В столбце Зарплата сотрудника ввести формулу для расчёта заработной платы по каждой должности.

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

3. В ячейке G12 вычислить суммарный фонд заработной платы фирмы.

4. Произвести подбор зарплат сотрудников фирмы для сум­марной заработной платы в размере 100 000 руб. с помощью команды Подбор параметра:

· В поле Установить в ячейке появившегося окна ввести ссылку на ячейку G12, содержащую формулу расчёта фонда зара­ботной платы;

· в поле Значение ввести искомый результат — 100000;

· в поле Изменяя значение ячейки ввести ссылку на из­меняемую ячейку D17, в которой находится значение зарплаты курьера,

· щелкнуть ОК.

Произойдёт обратный расчет зарплаты сотрудников по заданному условию при фонде зарпла­ты, равном 100 000 руб. Итог обработанного пересчета таков: при фонде зарплаты, равном 100 000 руб., зарплата курьера должна составлять 1289,66 руб.

Следует заметить, что если возникает необходи­мость копировать результаты, полученные подбо­ром параметра, то следует производить копиро­вание полученных данных в виде значений с ис­пользованием Специальной вставки.

Задание

1. Выполнить рассмотренные примеры решения двух экономических задач.

2. Имея исходные данные, рассчитайте сумму премии и сумму к выдаче. При каком проценте премии общая сумма к выдаче будет равна 100000 руб.? Рассчитайте средний доход за месяц.

3. Рассчитайте сумму оклада (МРОТ*коэф*разряд), сумму премии (50% от оклада), сумму удержаний в пенсионный фонд (28% от оклада + премия) и сумму к выдаче. Определите, каким должен быть коэффициент, чтобы итоговая сумма к выдаче составила 200000 руб. Исходные данные приведены в таблице ниже:

 

4. Показать результат преподавателю

⇐ Предыдущая12345678910Следующая ⇒

Поделиться:

Воспользуйтесь поиском по сайту: