Пример 1. Глава 6. Применение программы Excel для решения задач моделирования и прогнозирования

Глава 6. Применение программы Excel для решения задач моделирования и прогнозирования

Решение обратных задач (подбор параметра)

Часто при решении практических задач возникают ситуации, когда необходимо достичь какой-то конкретной цели. Например, необходимо чтобы себестоимость продукции составляла определенную величину или расходы не превышали заданного размера.

Специфика таких задач состоит в том, что в распоряжении лиц принимающих решение (ЛПР), есть математическая модель исследуемого процесса, например, закон ценообразования или взаимосвязь параметров, определяющих расходы, например, бюджета. Но неизвестно, при каком значении входящего в нее параметра можно достигнуть поставленной цели.

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

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

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

Рассмотрим эту процедуру на примере составления штатного расписания.

Пример 1.

Пусть известно, что в штате больницы состоит 6 санитарок (N1), 8 медсестер (N2), 10 врачей (N3), 3 заведующих отделениями (N4), главный врач (N5), заведующий аптекой (N6), заведующая хозяйством (N7) и заведующий больницей(N8). Общий месячный фонд зарплаты составляет 62 850 руб. Необходимо определить, какими должны быть оклады сотрудников больницы, чтобы не превысить установленный новый уровень месячного фонда оплаты труда размером в 75 000 руб.

Построим математическую модель решения этой задачи. За основу возьмем оклад санитарки, а остальные оклады будем вычислять, исходя из него: во столько-то раз или на столько-то больше. Говоря математическим языком, каждый оклад является линейной функцией от оклада санитарки: A_i*С+В_i, где С - оклад санитарки; А_i и В_i - коэффициенты, которые для каждой должности определяют следующим образом:

· медсестра получает в 1, 5 раза больше санитарки (А₂ =1, 5; В₂ =0);

· врач - в 3 раза больше санитарки (В₃ =0; А₃ =3);

· заведующий отделением - на 250руб. больше, чем врач (А₄ =3; B₄ =30);

· заведующий аптекой - в 2 раза больше санитарки (А₅ =2; В₅ =0);

· заведующий хозяйством - на 250руб. больше медсестры (А₆ =1, 5; В₆ =40);

· главный врач - в 4 раза больше санитарки (А₇ =4; В₇ =0);

· заведующий больницей - на 200руб. больше главного врача (А₈ =4; В₈ =20).

Зная количество человек на каждой должности N_i, нашу модель можно записать как уравнение

N₁*A₁*C+N₂*(A₂*C+B₂)+...+N₈*(A₈*C+B₈) = 62850,

где N₁ - число санитарок, N₂ - число медсестер и т.д.

В этом уравнении нам известны A₁...A₈, B₁...B₈ и N₁... N₈, а С неизвестно.

Анализ уравнения показывает, что задача составления расписания свелась к решению линейного уравнения относительно С. Решим его.

Вводятся исходные данные в рабочий лист электронной таблицы Excel, как показано ниже в табл.6.1. А именно, в текстовую часть таблицы, заданные значения коэффициентов А и В, заданное количество сотрудников по каждой должности и размер на данный момент базовой заработной платы санитарки 900 р. (ячейка D4).

Таблица 6.1

Исходные данные для примера 1

В столбце D вычисляется заработная плата для каждой должности (кроме санитарки). Например, для ячейки D5 формула расчета имеет вид =B5*$D$4+C5.

В столбце F вычисляется заработная плата всех сотрудников по данной должности. Например, для ячейки F4 формула расчета имеет вид =D4*E4.

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

Итак, имеем модель прямой задачи определения месячного фонда заработной платы. Теперь необходимо определите размер оклада санитарки так, чтобы расчетный фонд заработной платы был равен заданной величине 75 000 руб. То есть необходимо, используя сформированную модель, решить обратную задачу. Для решения обратной задачи необходимо:

· выполнить действие «Сервис/Подбор параметра…»;

· в поле «Установить в ячейке» появившегося окна ввести ссылку на ячейку F12, для которой необходимо подобрать требуемый результат;

· в поле «Значение» набирается искомый результат 75000;

· в поле «Изменяя значение ячейки» вводится ссылка на изменяемую ячейку D4 – базовая заработная плата.

Результат этих действий в окне «Подбор параметра» приведен на рис.6.1.

Рис.6.1. Заполненное диалоговое окно «Подбор параметра»

После заполнения диалогового окна «Подбор параметра» нажимается кнопка «ОК» и получаем искомое значение размера оклада санитарки и распределение окладов по должностям, при которых размер месячного фонда оплаты труда составит 75 000 руб. Если полученный результат не удовлетворяет условиям по величине зарплаты, то такие действия можно повторить для подбора параметра в любой ячейке, входящей параметром в математическую модель, например, для ячейки Е6, где указывается количество врачей.