Анализ оптимального решения в EXCEL
Цель работы: изучение возможности проведения анализа решения оптимизационных задач в Excel и решения задач целочисленного программирования в Excel.
1.Используя задание предшествующей работы, решить задачу оптимизации.
| Ресурс | Прод 1 | Прод 2 | Прод 3 | Прод 4 | Наличие |
| Прибыль | |||||
| Труд | |||||
| Оборуд. | |||||
| Сырье |
Математическая модель задачи:
F = 15 Х1 + 24 Х2 + 19 Х3 + 27 Х4 → MAX
2 X1 + 2 X2 + 2 X3 + 2 X4 <= 30
12 X1 + 8 X2 + 10 X3 + 6 X4 <= 200
8 X1 +20 X2 + 12 X3 + 26 X4 <= 125
Xj>=0 j=1-4
где Xj - количество выпускаемой продукции j-го типа;
bj - количество располагаемого ресурса i-го вида;
aij - норма расхода i-го ресурса для выпуска единицы продукции j-го типа;
сij - прибыль, получаемая от реализации единицы продукции j-го типа.
После успешного решения задачи на экране появляется диалоговое окно Результат поиска решения. (Решение найдено). С помощью этого окна можно вызвать отчеты трех типов: результаты; устойчивость; пределы.
Отчет по результатам.
Показывает в графе Разница количество неиспользованного ресурса.
Отчет по устойчивости.
Нормированная стоимость - дополнительные переменные в двойственной задаче, показывающие насколько изменится целевая функция при принудительном включении единицы этой продукции в оптимальное решение. Допустимые переменные значения изменения коэффициентов целевой функции и ресурсов, при которых сохраняется оптимальный набор переменных, входящих в оптимальное решение (при выходе за эти границы будет выгоден выпуск другой продукции в иных пропорциях).
Теневая цена - двойственные оценки переменных прямой исходной задачи, которые показывают, как изменится целевая функция при изменении ресурсов на единицу.
Отчет по пределам.Показывает значение целевой функции при выпуске каждого из типов продукции на нижнем пределе (по умолчанию - это ноль, т.е. при невыпуске данной продукции).
2.Параметрический анализ.
Провести серию экспериментов на модели для значений ресурса "Сырье"- 50; 100; 150; 200; 250; 300; 350; 400.
Порядок ввода:
Удалить результат решения - выделить область таблицы, содержащую значения переменных (В3:Е3); нажать клавишу " ← Delete"; убрать выделение (клавиша "Esc" или щелкнуть левой кнопкой мыши по свободному полю).
Ввести в ячейку правой части ограничения по сырью (ячейка Н11) значение 50.
Верхнее меню\ Сервис\Поиск решения\Выполнить.
Результаты поиска решения. Сохранить сценарий.
Ввести имя сценария (сырье=50). ОК. ОК.
Выпонить решение для всех значений ресурса "Сырье".
Представление результатов решения.
Верхнее меню\ Сервис\Сценарии.
Диспетчер сценариев. Отчет. Структура. ОК.
Итоговый сценарий.
3.Решить задачу при назначении граничных условий на все виды выпускаемой продукции 1 ≤ Xj ≤ 5. Ввести эти условия в ячейки для нижней и верхней границ значений переменных (В4:Е4 и В5:Е5).
4.Решить задачу с иной целевой функцией: минимизация используемых ресурсов при заданном результате.
Ввести в модель дополнительные переменные - неиспользованные ресурсы, а функцию цели направить на максимизацию неиспользуемых (сэкономленных) ресурсов.
f(x)=X5+X6+X7 → MAX
2 X1 + 2 X2 + 2 X3 + 2 X4 <= 30
12 X1 + 8 X2 + 10 X3 + 6 X4 <= 200
8 X1 +20 X2 + 12 X3 + 26 X4 <= 125
1<=Xj<=5; j=1,4
Ввести новую целевую функцию в свободную ячейку, например (I4).
5.Решение задач целочисленного программирования
Верхнее меню\ Сервис\Поиск решения\Добавить
Для каждого значения переменных добавить ограничения на челочисленность получаемых результатов:
В3=целое; С3=целое; D3=целое; Е3=целое.
Параметры\Линейная модель\Показывать результаты итераций. ОК
Выполнить\Сохранить сценарий
Ввести номер итерации (итерация 1, 2, ...) как имя сценария. ОК
Продолжить.
Решение найдено. ОК. Сервис\Сценарии
Диспетчер сценариев\Отчет\Структура ОК.
Порядок выполнения работы
1.Изучить методические указания
2.Получитьу преподавателя исходные данные
3.Провести необходимые расчеты на ЭВМ
4.Сделаты выводы и оформить отчет по выполненной работе
Отчет по работе должен содержать
1.Исходные положения
2.Исходные данные
3.Порядок работы на ЭВМ при решении задачи
4.Результаты решения
5.Выводы.