2.3.Выполнить.
На экране: диалоговое окно Результаты поиска решения (рис. 3.3.10).
Сохранить сценарий...
На экране: диалоговое окно Сохранение сценария (рис. 3.4.5).
2.5. Ввести имя сценарияфинансы=50.
2.6.ОК.
На экране: диалоговое окно Результаты поиска решения (рис. 3.3.10).
2.7. ОК.
На экране: результат решения задачи для данного варианта финансы=50 (рис. 3.4.6).
Рис. 3.4.6
3. Решение задачи для последующих вариантов.
3.1. Ввести в Н11 значения финансов из рис. 3.4.4 для следующего варианта.
3.2. Выполнить п. 2.2—п. 2.7, при этом в п. 2.5 вводить имя сценария, соответствующее значению финансов.
4. Представление результатов решения.
Сервис, Сценарии...
На экране: диалоговое окно Диспетчер сценариев (рис. 3.4.7).
Рис. 3.4.7
4.2.Отчет...
На экране: диалоговое окноОтчет по сценарию (рис. 3.4.8).
Рис. 3.4.8
4.3. Структура.
4.4. ОК.
На экране:отчетИтоговый сценарий (рис. 3.4.9).
Рис. 3.4.9
На этом рисунке приведены результаты решения задачи для всех значений финансов, принятых в таблице вариантов (рис. 3.4.4).
Для удобства дальнейшей работы выполним редактирование Итогового сценария.
Алгоритм 3.4.3. Редактирование Итоговогосценария
1. Для размещения на экране всего отчета Итоговый сценарий в окне масштаба назначить 50%.
2. Удалить столбцы B и D.
3. Удалить строки 5 и 10.
4. Ввести:
Ø Прод1:Прод4 в ячейки С5:С8.
Ø Прибыль в C9.
Ø Виды ресурсов: трудовые, сырье, финансы в ячейки C10:C12.
5. Увеличить ширину столбца C.
6. Для наглядного представления данных на диаграммах:
Ø В дробных значениях Прод1:Прод4 назначить 2 знака после запятой.
Ø Дробные значения в строках Прибыль, трудовые, сырье и финансы округлить до целых чисел.
7. Убрать Примечание.
После этого отчет Итоговый сценарий будет выглядеть так, как показано на рис. 3.4.10.
Рис. 3.4.10
Для наглядного представления результатов параметрического анализа на основании отредактированной таблицы (рис. 3.4.10) построим графики.
Алгоритм 3.4.4. Построение гистограммы
для искомых переменных
1. Выделить C3:H8.
2. Построить гистограмму по алг. 2.2.1.
3. Выполнить форматирование гистограммы по алг. 2.2.4.
На экране: рис. 3.4.11, на основании которого можно сделать следующие выводы:
rПри различном финансировании в план входит продукция различных видов, однако ни в один вариант не входит выпуск продукции Прод2. Это объясняется тем, что при высоком потреблении ресурсов прибыль от ее производства ниже, чем от производства других видов продукции.
rДля значений финансов 50, 150, 200 величина выпускаемой продукции является дробной. Такое положение допустимо при планировании, например, выпуска ткани, добычи нефти и т. д. При выпуске штучной продукции очевидно, что в плане должны быть целые числа. Для получения такого плана следует решать задачу целочисленного программирования, которая рассмотрена в главе 4.
Рис. 3.4.11
Алгоритм 3.4.5. Построение смешанной диаграммы для целевой функции и требуемого сырья
1. Выделить C3:H3, C9:H9, C11:H11.
2. Построить смешанную диаграмму по алгоритму, аналогичному алг. 2.2.1.
3. Выполнить форматирование диаграммы по алгоритму, аналогичному алг. 2.2.4 или алг. 2.2.5.
На экране: диаграмма (рис. 3.4.12), на основании которой можно сделать следующие выводы:
rУвеличение финансирования дает увеличение прибыли, что вполне естественно.
rПри увеличении финансирования, начиная со 150, происходит уменьшение потребляемого сырья. Такой результат является неожиданным, но это не ошибка. Это следствие того, что выпуск Прод3, Прод4, обеспечивающих увеличение прибыли, требует при этом меньшего потребления сырья.
Рис. 3.4.12
Из приведенного видно, что параметрический анализ является мощным средством, помогающим принять оптимальное решение.
3.4.3. Решение по нескольким целевым функциям
В разделе 3.2.4 мы уже говорили, что в задаче распределения ресурсов возможны две постановки:
rмаксимизация результата при заданных ресурсах;
rминимизация используемых ресурсов при заданном результате.
Как решать такие задачи в Excel, посмотрим на примере задачи, приведенной на рис. 3.3.4, к которой добавим следующие условия:
1. Назначим граничные условия на все виды выпускаемой продукции 1 Ј Прод* Ј 5, которые введем в ячейки В6:Е7 (рис. 3.4.13).
Рис. 3.4.13
2. Сформулируем целевые функции для решения задачи в двух постановках.
Целевая функция при максимизации прибыли, которая остается без изменений, введена в I8 (рис. 3.4.13).
Для решения задачи при минимизации используемых ресурсов в нашу математическую модель (3.1.8) введем дополнительные переменные и запишем задачу в следующем виде:
(3.4.1)
В системе (3.4.1) величины у1, у2, у3 — это дополнительные переменные, которые определяют величину неиспользованного ресурса, что было подробно рассмотрено в 3.1.3. Следовательно, если мы хотим минимизировать используемые ресурсы, то должны максимизировать неиспользуемые ресурсы. Целевая функция для такой постановки введена в ячейку I6 (рис. 3.4.13). Решение такой задачи производится по следующему алгоритму.
Алгоритм 3.4.6. Решение задачи
по нескольким целевым функциям
1. Ввести условия задачи и все целевые функции в таблицу для ввода условий задачи (рис. 3.4.13), руководствуясь алг. 3.3.1.
2. Назначить первую целевую функцию (I8) и ввести ограничения и граничные условия по алг. 3.3.2.
3. Решить задачу по алг. 3.3.3.
На экране: рис. 3.4.14.
Рис. 3.4.14
Далее решить задачу последовательно по всем целевым функциям (в нашем примере две целевые функции) аналогично параметрическому анализу по алг. 3.4.2.
Сохранить сценарий...
5. Ввести имя Макс приб.