Раздел 4. Решение задач оптимального управления в Excel
Excel позволяет решать задачи оптимального управления, общий вид которых представлен формулами 1-5.
где xj может принимать либо действительные, либо целочисленные, либо булевы значения.
Формула 1 определяет целевую функцию, формула 2 определяет ограничения, а формула 3 определяет граничные условия. В зависимости от вида функции f в формуле 1 задача может относиться либо к классу задач линейного или нелинейного программирования. В зависимости от того, являются ли xj целыми и булевыми или действительными величинами задача может относиться к задачам целочисленного программирования или нет.
Для решения таких задач в Excel предусмотрена надстройка «Поиск решения», которую можно вызвать из меню «Сервис». Если пункта меню «Поиск решения» нет, то нужно вернуться к установке Excel и установить эту надстройку.
Рис.1.Окно диалога надстройки «Поиск решения»
После выбора пункта меню появится диалог надстройки «Поиск решения». Подробную справку по этому диалогу можно получить в справочной системе Excel по ключевым словам «надстройка поиска решения, прерывание»(см.рис.2). Появится следующее окно справки (см. рис. 3).
Рис.2. Поиск информации в Excel по ключевым словам
Постановка задачи и оптимизация модели с помощью процедуры поиска решения
1
В меню Сервис выберите команду Поиск решения. Если
команда Поиск решения отсутствует в меню Сервис, установите соответствующую
надстройку. 
Инструкции
2 В поле Установить целевую ячейку введите адрес или имя ячейки, в которой находится формула оптимизируемой модели.
3 Чтобы максимизировать значение целевой ячейки путем изменения значений влияющих ячеек, установите переключатель в положение максимальному значению. Чтобы минимизировать значение целевой ячейки путем изменения значений влияющих ячеек, установите переключатель в положение минимальному значению. Чтобы установить значение в целевой ячейке равным некоторому числу путем изменения значений влияющих ячеек, установите переключатель в положение значению и введите в соответствующее поле требуемое число.
4 В поле Изменяя ячейки введите имена или адреса изменяемых ячеек, разделяя их запятыми. Изменяемые ячейки должны быть прямо или косвенно связаны с целевой ячейкой. Допускается установка до 200 изменяемых ячеек. Чтобы автоматически найти все ячейки, влияющие на формулу модели, нажмите кнопку Предположить.
5 В поле Ограничения введите все ограничения, накладываемые на поиск решения.
6 Нажмите кнопку Выполнить.
7 Чтобы сохранить найденное решение, установите переключатель в диалоговом окне Результаты поиска решения в положение Сохранить найденное решение. Чтобы восстановить исходные данные, установите переключатель в положение Восстановить исходные значения.
Советы
· Чтобы прервать поиск решения, нажмите клавишу ESC. Microsoft Excel пересчитает лист с учетом найденных значений влияющих ячеек.
·
Чтобы получить более подробные сведения об элементах управления диалогового
окна Поиск решения, нажмите кнопку.
·
Чтобы получить более подробные сведения об элементах управления диалогового
окна Результаты поиска решения, нажмите кнопку .
·
Чтобы получить более подробные сведения о методах поиска, нажмите кнопку
.
Рис.3.Окно справки Excel
Задание:
| Определить с помощью надстройки поиск решения |
|
|
||||
| в каком количестве следует выпускать продукцию четырех типов |
||||||
| для изготовления которой требуются ресурсы трех типов |
|
|||||
| для получения макс прибыли |
|
|
|
|
||
| Таблица коэффициентов |
|
|
|
|
||
| Ресурс | Прод1 | Прод2 | Прод3 | Прод4 | знак | Наличие |
| Прибыль | 60 | 70 | 120 | 130 | мах | ? |
| Труд | 1 | 1 | 1 | 1 | <= | 16 |
| Сырье | 6 | 5 | 4 | 3 | <= | 110 |
| Финансы | 4 | 6 | 10 | 13 | <= | 100 |
|
|
|
|
|
|
|
|
| Получить три сценария для значения наличия финансов 100 120 140 |
||||||
| Построить отчет по сценариям |
|
|
|
|
||
Указания:
Представленная задача относится к классу задач линейного программирования. В этом случае целевая функция и ограничения принимают вид, показанный в формуле (6).
(6)
В нашей задаче целевой функцией является максимум прибыли, объем производства каждого вида продукции – xj, весовые коэффициенты каждого вида продукции в прибыли –cj,, удельные затраты по каждому виду ресурсов для производства каждого вида продукции - aij., наличие ресурсов –bi. Граничные условия трансформируются в требование xj>0, так как выпустить отрицательное количество продукции невозможно.
При решении данной задачи целесообразно к имеющейся таблице (см. задание) добавить четыре ячейки, в которых записать формулы для вычисления значений, показанных в формуле (6) и четыре ячейки для значений объема выпуска каждого вида продукции. Вычисление сумм удобно производить по специальной формуле Excel «СУММПРОИЗВ». После этого можно выбрать пункт меню «Поиск решения».
Для задания ограничений нужно нажать на кнопку «Добавить» в окне диалога «Поиск решения» и в появившемся диалоге «Добавления ограничений» ввести ограничения по ресурсам. Далее, нужно нажать на кнопку параметры и появившемся диалоговом окне (см. рис. 3) отметить нужные опции, обязательно выбрав пункт «Неотрицательные значения».
Рис.3. Окно диалога «Параметры поиска решения»
После чего нужно нажать на кнопку «Выполнить» и появится диалоговое окно «Результаты поиска решения» (см. рис. 4).
Рис.4. Диалоговое окно «Результаты поиска решения»
Для построения отчета по различным значениям наличия финансов необходимо сохранить сценарии решения, нажав на кнопку «Сохранить сценарий».
Построение отчета по сценариям
Для получения отчета по полученным сценариям нужно выбрать пункт «Сценарии»
в меню «Сервис». Появится «Диспетчер сценариев» (см. рис. 5).
Рис.5. Диалоговое окно «Диспетчер сценариев»
Нажав на кнопку «Отчет» и выбрав в дальнейшем диалоге «Отчет по сценарию» пункт «структура, получим таблицу результатов экспериментов.
В версии Excel 97 возможна неправильная работа кнопки «Сохранить сценарий» в надстройке «Поиск решения» из-за чего данные о результатах экспериментов могут быть выведены не полностью.