 |
Работа в диалоговом окне Поиск решения
|
|
|
|
Решение задач на оптимизацию средствами Excel
Microsoft Excel имеет прекрасные возможности для решения задач на оптимизацию, т.е. задач на отыскание максимума или минимума, иначе на нахождение наибольших и наименьших значений каких-то величин. Возможности Excel продемонстрируем на примере решения задачи распределения ресурсов.
Задача распределения ресурсов
Если финансы, оборудование, сырье и даже людей полагать ресурсами, то значительное число задач в экономике можно рассматривать как задачи распределения ресурсов. Достаточно часто математической моделью таких задач является задача линейного программирования. Рассмотрим следующий пример.
Требуется определить, в каком количестве надо выпускать продукцию четырех типов: Прод1, Прод2, Прод3, Прод4, для изготовления которой требуются ресурсы трех видов: трудовые, сырье, финансы. Количество ресурсов каждого вида, необходимое для выпуска единицы продукции данного типа, называется нормой расхода. Нормы расхода, а также прибыль, получаемая от реализации единицы каждого типа продукции, приведены в табл. 1. Там же приведено наличие располагаемого ресурса.
Таблица 1
| Ресурс | Прод1 | Прод2 | Прод3 | Прод4 | Наличие |
| Трудовые | |||||
| Сырье | |||||
| Финансы | |||||
| Прибыль | ––– |
Составим математическую модель. Введем следующие обозначения:
xj – количество выпускаемой продукции j -гo типа, 
;
bi – количество располагаемого ресурса i -го вида, 
;
aij – норма расхода i -го ресурса для выпуска единицы продукции j -гo типа;
сj – прибыль, получаемая от реализации единицы продукции j -го типа.
Как видно из табл. 1, для выпуска единицы Прод1 требуется 6 единиц сырья, значит, для выпуска всей продукции Прод1 требуется 6x1 единиц сырья, где x1 – количество выпускаемой продукции Прод1. С учетом того, что для других видов продукции зависимости аналогичны, ограничение по сырью будет иметь вид: 6х1+5х2+4хз+3х4 ≤ 110. В этом ограничении левая часть равна величине потребного ресурса, а правая показывает количество имеющегося ресурса.
|
|
|
Аналогично можно составить ограничения для остальных ресурсов и написать зависимость для целевой функции (ЦФ). Тогда математическая модель задачи будет иметь вид:
(1)
Аналитически данный тип задач линейного программирования решается симплекс-методом. Изложим основные идеи этого метода, которые реализованы в Excel.
Ввод условий задачи
1. Запустите табличный процессор Excel
2. Создайте форму для ввода условий задачи (рис. 1) 
Рис.2.Форма для ввода условий задачи
Весь текст, представленный на рис. 1, является комментарием и на решение задачи не влияет.
3.
 |
Введите исходные данные из табл. 1 и зависимости из математической модели (1) в форму (рис.1). Для наглядности можно перейти к режиму представления формул. Ввод данных приводится на рис.2, а режим представления формул на рис.3.
Рис.2.Форма ввода данных
Рис.3. Форма ввода данных в режиме представления формул
3.1. Введите зависимость для целевой функции (ЦФ):
3.2. Установите курсор в ячейку F6
3.3. 
Нажмите кнопку Мастер функций на панели Стандартная
На экране: диалоговое окно Мастер функций [1] – шаг 1 из 2
3.4. Курсор в окно Категория на категорию Математические
3.5. Курсор в окно Функции на СУММПРОИЗВ
3.6. Ok. На экране: диалоговое окно (рис.4)
Рис.4. Мастер функций – шаг 2 из 2
3.7. В массив 1 введите В$3:E$3
3.8. В массив 2 введите В6:Е6
3.9. Ок. На экране: рис.2, рис.3 (в F6 введены значения ЦФ)
4. Введите зависимости для левых частей ограничений:
|
|
|
4.1. Курсор в F6
4.2. Копировать в буфер
4.3. Курсор в F9
4.4. Вставить из буфера. На экране: в F9 введена функция, как это показано на рис.3
4.5. Скопировать F9 в F10:F11. На экране: в F10:F11 введены функции, как это показано на рис.3. На этом ввод данных в таблицы (рис.3, рис.4) закончен.
Работа в диалоговом окне Поиск решения
1. 
Выполните команды Сервис, Поиск решения… На экране диалоговое окно Поиск решения (рис.5)
Рис.5. Диалоговое окно Поиск решения…
2. Введите ЦФ:
– Курсор в поле Установить целевую ячейку
– Введите адрес F6
– Введите направление ЦФ: Максимальному значению
3. Введите адреса искомых переменных:
– Курсор в поле Изменяя ячейки
– Введите адреса: $B$3:$E$3
4. Нажмите кнопку Добавить… 
На экране диалоговое окно Добавление ограничения (рис.6)
Рис.6. Диалоговое окно Добавление ограничения
5. Введите граничные условия на переменные (Прод1 - Прод4)≥0: В3≥В4, С3≥С4, D3≥D4, E3≥E4
– 
В окно Ссылка на ячейку ввести В3
– Нажмите на кнопку со стрелкой. На экране: знаки для ввода ограничения
– Курсор на знак > =
– Курсор в правое окно
– Введите В4
– Нажмите кнопку Добавить… На экране: опять диалоговое окно Добавление ограничения (рис.5). Аналогично ввести граничные условия для остальных переменных.
6. Аналогично введите ограничения: F9≤H9, F10≤H10, F11≤H11
– После ввода последнего ограничения вместо Добавить… нажмите Ок
На экране: диалоговое окно Поиск решения с введенными условиями (рис.5)
Если при вводе ограничений возникает необходимость в изменении или удалении внесенных ограничений или граничных условий, то это делается с помощью команд Изменить…, Удалить.
Решение задачи
1. Нажмите кнопку Параметры… На экране: диалоговое окно Параметры поиска решения.
С помощью команд, находящихся в этом диалоговом окне, можно вводить условия для решения задач оптимизации всех классов. Мы познакомимся с командами, которые могут вводиться при решении задач всех классов. Вместе с тем, команды, используемые по умолчанию, подходят для решения большей части практических задач. Максимальное время служит для назначения времени в секундах, выделяемого на поиск решения задачи. Значение 100, используемое по умолчанию, подходит для решения большинства задач. Предельное число итераций служит для назначения числа итераций. Значение 100, используемое по умолчанию, подходит для решения большинства задач.
|
|
|
2. Установите флажок Линейная модель, что обеспечивает применение симплекс-метода
3. Ок
На экране: диалоговое окно Поиск решения (рис.5)
4. Выполнить.
 |
На экране: диалоговое окно Результаты поиска решения. Решение найдено (рис.7) и результаты оптимального решения задачи приведены на рис.6
Рис.7. Диалоговое окно Результаты поиска решения
Рис.8. Оптимальное решение в таблице Excel
На рис.8 видно, что в оптимальном решении Прод1 =В3=10, Прод2 =С3=0, Прод3 =D3=6, Прод4 =Е3=0. При этом максимальная прибыль будет составлять F6=1320, а количество использованных ресурсов равно: трудовых =F9=16, сырья =F10=84, финансов =F11=100. Таково оптимальное решение рассматриваемой задачи распределения ресурсов. Однако решение задачи находится не всегда. Если условия задачи несовместны, на экране появляется диалоговое окно, сообщающее о том, что поиск не может найти подходящего решения.
Если ЦФ не ограничена, то на экране появится диалоговое окно, сообщающее о том, что значения целевой ячейки не сходятся.
Изменим условие задачи, сохранив значения переменных, которые мы получили в оптимальном решении Прод1 =10, Прод3 =6,и дополнительно назначим Прод2 =5.
Очевидно, что для выпуска такого количества продукции располагаемых ресурсов будет недостаточно. Рассмотрим, как решаются такие несовместные задачи с помощью Excel. Введем изменение условий задачи.
1. Перейдите на исходную таблицу (рис.1)
2. Как мы делали ранее, вызовите диалоговое окно Поиск решения
3. Измените граничные условия для Прод1:
– В окне ограничения курсор на строку $В$3>=$В$4
– Изменить. На экране: диалоговое окно Изменить ограничение
– Введите изменение: $В$3=10
– Ок
4. Аналогично ввести значение для Прод3: D3=6
5. Введите дополнительные условия для Прод2:
– Добавить
– Введите:С3=5
– Ок
6. Решите задачу, как мы это делали раньше. На экране: диалоговое окно, сообщающее о том, что поиск не может найти подходящего решения.
|
|
|
Появление этого окна – признак несовместного решения. Чтобы решить подобную задачу, рассмотрим ее математическую модель:
(2)
Введем дополнительные необходимые ресурсы ti и запишем систему в виде:
(3)
Такая постановка задачи дает возможность определить минимальное значение дополнительных необходимых ресурсов t1, t2, t3. Математическая модель задачи примет вид:
(4)
|
|
|
