Решение прикладных задач в Excel


Задачи оптимизации


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

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

Кроме того, иногда интересует не конкретный результат, а минимально или максимально возможный. Например, как минимизировать затраты на содержание персонала или максимизировать прибыли от реализации продукции?

Такие задачи в Excel решают с помощью Поиска решения.

7.2.1 Задача линейного программирования

    Если математическая модель исследуемого процесса и ограничения на значения ее параметров линейны, то задача достижения цели является задачей линейного программирования.

Познакомимся с решением этих задач на следующем примере.

7.2.1.1 Составление штатного расписания

Усложним рассмотренную в предыдущей главе задачу. Пусть известно, что для нормальной работы больницы необходимо 5-7 санитарок, 8-10 медсестер, 10 врачей, 3 заведующих отделениями, главный врач, заведующий аптекой, заведующая хозяйством и заведующий больницей. Общий месячный фонд зарплаты должен быть минимален. Необходимо определить, какими должны быть оклады сотрудников больницы, при условии, что оклад санитарки не должен быть меньше прожиточного минимума 80 грн.

В качестве модели решения этой задачи возьмем, как и в первой главе, линейную. Запишем ее так:

N1*A1*C+N2*(A2*C+B2)+...+N8*(A8*C+B8) = Минимум.

В этом уравнении нам не известно число санитарок (N1), медсестер (N2), врачей (N3) и оклад санитарки (С).

Используя Поиск решения, найдем их.

    Откройте созданный в предыдущей главе файл hospital.xls.

    В меню Сервис активизируйте команду Поиск решения.

    В окне Установить целевую ячейку укажите ячейку F12, содержащую модель.

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

    Используя кнопку Добавить, опишите ограничения задачи.

    Окончательно окно Поиска решения будет выглядеть так:

    Опишите Параметры поиска, как показано на рис. 7.1.

    Щелкните на кнопке ОК, а затем - Выполнить.




    Начало  Назад  Вперед