Технология решения оптимизационных задач

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

Определить оптимальное соотношение между расходами всего на- _ч селения Пензенской области, затраченными на покупку товаров (х!) и рас- 1 ходами, затраченными на оплату услуг (х₂) таким образом, чтобы сум­марные расходы населения (у) не превышали заданного (установленного). значения.

Для рассматриваемого примера, в качестве заданного (установленного) значения расходов принимаем 1000 млн. рублей.

>

49

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

Рисунок 20 - Диалоговое окно Поиск решения

Элементами диалогового окна Поиск решения являются. 1. Поле Установить целевую ячейку необходимо для ссылки на ячейку с функцией, для которой ищется минимальное (максимальное), либо заданное значение. Для рассматриваемого примера в качестве функции используется по­лученное ранее регрессионное уравнение (3). Тип взаимосвязи между решени­ем и целевой функцией задается путем установки переключателя в группе Рав­ной. Для нахождения максимального или минимального значения целевой функции этот переключатель необходимо установить в положение Макси­мальному значению или Минимальному значению, соответственною. Для на­хождения заданного значения целевой функции переключатель необходимо ус­тановить в положение Значению и ввести заданное значение целевой функции. Последнее соглашение является условием поставленной задачи оптимизации рассматриваемого примера, поэтому необходимо ввести значение 1000.

2. Поле Изменяя ячейки предназначено для ссылки на ячейки, являю­щиеся оптимизируемыми переменными Для рассматриваемого примера это параметры х ₁ и х₂.

3. В поле Ограничения необходимо указать ссылки на ячейки, в кото­рых указаны ограничения, накладываемые на оптимизируемые переменные. Ограничения добавляются по одному при нажатии на кнопку Добавить и мо­гут быть как в виде равенств, неравенств, так и в виде целочисленных пере­менных. Диалоговое окно Добавление ограничений показано на рисунке21.

50

Добавление ограничения

Ссылка на ячейку

Рисунок 21 - Диалоговое окно Добавление ограничений.

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

<=

вводится тип соотношения между левой и правой частями ограничения. Для рассматриваемого примера, на основе анализа данных таблицы 7, ограничения принимаем в следующем виде 4 00 <х1 < 600, 100< х₂ < 200.

Рисунок 22 - Окно диалога

4. После нажатия на кнопку Параметры диалогового окна Поиск реше­ния (рисунок 20) открывается диалоговое окно Параметры поиска решения (рисунок 22). В данном окне можно изменять условия и варианты поиска оп­тимального решения, а также загружать и сохранять оптимизируемые модели. Значения и состояния элементов управления, используемые по умолчанию, подходят для решения большинства задач. Поле Максимальное время служит для ограничения времени, отпускаемого на поиск решения. Поле Предельное число итераций служит для ограничения числа промежуточных вычислений.

51

Поля Относительная погрешность и Допустимое отклонение предна-значены для задания точности, с которой ищется решение. Рекомендуется, по-сле нахождения решения с величинами данных параметров, установленными по умолчанию, повторить вычисления с большей точностью и меньшим допусти-мым отклонением и сравнить с первоначальным решением. Данная проверка необходима для того, чтобы убедится в достоверности найденного оптималь-ного решения. Флажок Линейная модель служит для поиска решения при ис-пользовании линейной математической модели или же линейной аппроксима-ции нелинейной модели. При использовании нелинейной математической мо-дели это флажок должен быть отключен. Флажок Показывать результаты итераций предназначен для приостановки поиска решения и просмотра про-межуточных результатов решения. Флажок Автоматическое масштабирова-ние служит для включения автоматической нормализации (масштабирования) входных и выходных параметров модели, различающихся по величине, напри-мер, при максимизации прибыли в процентах по отношению к вложениям, ис-численным в миллионах рублей. Группа Оценки предназначена для выбора метода экстраполяции. Группа Разности служит для выбора метода численно-го дифференцирования. Группа Метод поиска предназначена для задания ме-тода оптимизации.

После установки окончательного формирования Диалогового окна Поиск ре-шения (рисунок20) и нажатия кнопки Выполнить открывается окно Резуль-таты поиска решения рисунок23.

Рисунок 23 - Диалоговое окно Результаты поиска решения

Для того чтобы вывести отчет о результатах решения оптимизационной задачи необходимо выбрать соответствующий тип отчета: Результаты, Ус-тойчивость, Пределы.

Отчет Результаты приведен на рисунке 24. Отчет содержит целевую ячейку, список изменяемых ячеек и ограничений. Отчет также содержит ин-формацию о таких параметрах каждого ограничения, как статус и разница. Статус может принимать три состояния: Связанное, Не связанное или Не выполненное. Связанное ограничение – это ограничение, для которого значе-

52

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

Рисунок 24 - Отчет Результаты

Отчет Устойчивость показан на рисунке 25

М1сго50Й Ехсе1 8.0а Отчет по устойчивости №6очий лист: [Книга1]Лист1 ВЯчет создан: 27.11.00 22:11:58

И зменяемые ячейки __________________________________________________

Результ. Нормир.  Целевой Допустимое Допустимое Ячейк а Имя значение стоимость Ко эффици ентУвел ичениеУменьшение

Ш5 х1 ________ 400_______ 0                0      1Е+30 _________ 0

Щ5 х2    194,11007 _______ 0_________ 0________ 0       1Е+30

Рисунок 25 - Отчет Устойчивость

53

Отчет по устойчивости содержит информацию о том, насколько целевая функция (ячейка) чувствительна к изменениям ограничений и переменных. Этот отчет имеет два раздела: один для изменяемых ячеек, а второй для огра-ничений. На рисунке 25 показана только первая часть отчета для линейной модели. Для изменяемых ячеек столбец Нормир. стоимость показывает уве-личение значения в целевой ячейке в ответ на изменение значения в изменяе-мой ячейке на одну единицу. Столбец Целевой коэффициент показывает сте-пень зависимости между изменяемой ячейкой и целевой ячейкой. Столбцы Допустимое увеличение и Допустимое уменьшение показывают величину изменения целевого коэффициента до момента увеличения или уменьшения оптимальных значений в изменяемых ячейках.

Отчет Пределы показан на рисунке 26.

егозой Ехсе1 8.0а Отчет по пределам бочий лист: [Книга1]Лист1 _ичет создан: 27.11.00 22:11:58

Целевое
Я чейка   Имя _____ значение

$ С$7 Функция цели ______ 1000