Построение нелинейной модели
Ячейки В3:ЕЗ резервированы для цен продукции, являющихся оптимизируемыми переменными. В ячейках В4:Е4 записаны значения затрат производства по видам продукции. В ячейки В5:Е5 внесены формулы для расчета единичной прибыли как разности между ценой и затратами производства. Так, в ячейку B5 внесена формула =В3-В4.
В ячейки В7:Е7 внесены формулы для расчета величины спроса по уровню цены соответствующего вида продукции. Исходный вид этих формул соответствует выражению (7.7). В ячейку B7, например, будет записана формула =300-0,106*B3.
В ячейках G11:G13 указываются формулы для расчета потребности в ресурсах по их видам. Так, в ячейку G11 будет записана формула =B7*B11+C7*C11+D7*D11+E7*E11.
В ячейках Н11:Н13 указываются величины имеющихся объемов ресурсов каждого вида.
Целевая функция, рассчитываемая по формуле
= B5*B7+C5*C7+D5*D7+E5*E7,
помещается в ячейку G9.
Для решения модели выбираем команду ПОИСК РЕШЕНИЯ меню СЕРВИС. В появившемся окне (Рис. 7.14) указываем местонахождение целевой функции, направление оптимизации (минимизация или максимизация), изменяемые ячейки (содержащие значения оптимизируемых переменных) и используемые ограничения.
Рис. 7.14. Диалоговое окно "Поиск решения"
В данной постановке нам потребуются три ограничения:
G11:G13< = H11:H13,
В7:Е7 > = 0,
В3:Е3 < 2500.
Первое из них выражает условие ограниченности используемых ресурсов. Второе - требование неотрицательности оптимизируемых величин спроса на выпускаемую продукцию по ее видам. Третье ограничение содержит определенное на стадии предварительных маркетинговых исследований условие конкурентоспособности выпускаемой продукции в отношении её цен. Войдя в окно "Параметры поиска решения" (открываемого нажатием на кнопку "Параметры" диалогового окна "Поиск решения"), следует отказаться от использования линейной модели и выбрать необходимые опции для решения нелинейной задачи (см Рис. 7.15).
Рис. 7. 15. Диалоговое окно "Параметры поиска решения"
После решения модели Вы получите возможность заказать отчеты по полученным результатам и затем увидите свою модель в окончательном виде (см. Табл. 7.24).
Как видно из полученного решения, ни один из ресурсов не используется полностью (т. е. не сдерживает компанию от получения дополнительной прибыли). Это говорит о том, что оптимальное решение находится не на границе, а внутри области допустимых значений оптимизируемых переменных.
При решении нелинейных задач EXCEL 5.0 выдает два типа отчетов: отчеты по устойчивости и отчеты по пределам.
Таблица 7.24
Окончательный вид решения нелинейной модели
Отчеты по пределам практически ничем не отличаются от соответствующих отчетов по пределам, выдаваемых для линейных задач. В случае отчетов по устойчивости имеются некоторые различия, которые мы рассмотрим на примере сравнения двух отчетов по решению линейной задачи, приведенной нами выше в Табл. 7.13. Один из этих отчетов по устойчивости, изображенный на Рис. 7.16, получен при решении данной задачи симплекс методом (включаемым кнопкой Линейная модель в диалоговом окне Параметры поиска решения). Второй - получен при решении данной задачи методами нелинейного программирования (см. Рис. 7.17).
Microsoft Excel 5.0 Отчет по устойчивоcти
Изменяемые ячейки
Рис. 7. 16. Отчет об устойчивости для линейных задач
Microsoft Excel 5.0 Отчет по устойчивости
Изменяемые ячейки
Рис. 7.17. Отчет об устойчивости для нелинейных задач
Как видно из сравниваемых отчетов, они иллюстрируют одно и тоже полученное решение. Это значит, что использование обоих методов оптимизации (линейного и нелинейного) привело к получению одного и того же решения. Здесь нет ничего удивительного, поскольку данная задача имеет единственное решение. Однако, в случае, если рассматриваемая задача имеет несколько решений, нет никаких гарантий того, что оба метода оптимизации выберут одно и тоже решение.
Нетрудно также заметить, сравнивая данные отчеты, что значения, указанные в колонках "Редуцированная стоимость" и "Теневая цена" на Рис. 7.16 частично совпадают с величинами, содержащимися в столбцах "Нормир. Градиент" и "Множитель Лагранжа" на Рис. 7.17. При рассмотрении отчетов задач, решаемых симплекс методом, мы определили, что теневые цены ограничений вычисляют предельную стоимость дополнительной единицы ресурса, выражаемого данным ограничением, или величину улучшения целевой функции, при уменьшении имеющегося объема ресурсов данного вида на единицу. Подобная интерпретация может быть отнесена также и к множителям Лагранжа. Главное отличие теневых цен от множителей Лагранжа связано с наличием у первых из них диапазона изменения объемов имеющихся ресурсов, в пределах которого этот показатель сохраняют своё значение. Таким образом, используя симплекс метод, мы могли определить допустимое увеличение или уменьшение объема имеющихся ресурсов, в пределах которых теневая цена ограничения сохраняет своё значение. Мы смогли делать это, поскольку целевая функция и ограничения задачи были линейны, что облегчало расчет изменения целевой функции при изменении объемов имеющихся ресурсов. При использовании нелинейных методов возможности определения допустимых изменений объемов имеющихся ресурсов отсутствуют. Поэтому в таких случаях мы не можем указать диапазон изменения объемов имеющихся ресурсов, в пределах которого множители Лагранжа для каждого ограничения сохраняют своё значение. Множители Лагранжа, таким образом, могут использоваться только для приблизительной оценки влияния на целевую функцию единичных изменений объема имеющихся ресурсов по каждому из ограничений.
Как уже было замечено при решении линейных задач, редуцированная стоимость переменной, показывающая расположение решения относительно верхней и нижней границ, определяет прирост (сокращение) целевой функции при допустимом увеличении этой переменной на единицу. Подобная интерпретация, но в несколько более приближенном смысле, может быть дана показателю "Нормир. градиент" (Reduced gradient). Действительно, ненулевое значение нормированного градиента выражает влияние на целевую функцию малых изменений данной переменной. Так, например, увеличение объема производства продукции второго вида П2 на единицу уменьшает значение целевой функции на две единицы, о чем говорит соответствующее значение редуцированных затрат (Рис. 7.12) и нормированного градиента (Рис. 7.13).
На Рис. 7.18 и 7.19 приведены отчеты по пределам и устойчивости для полученного оптимального решения нелинейной задачи определения цен на производимую продукцию (см. Табл. 7.24).
Microsoft Excel 3.0 Отчет по пределам
Рис. 7.18. Отчет по пределам для решения нелинейной задачи определения цен
Microsoft Excel 5.0 Отчет по устойчивости
Изменяемые ячейки
Рис. 7.19. Отчет по устойчивости для решения нелинейной задачи определения цен
Команда ПОИСК РЕШЕНИЯ меню СЕРВИС предлагает пользователю ряд опций для контроля за решением нелинейных моделей, которые находятся в нижней части диалогового окна Параметры поиска решения (см. Рис. 7.20). Таких опций три: Оценка, Производные и Метод. Выбранные по умолчанию значения этих опций обеспечивают устойчивую работу ЭТ во многих случаях. Однако, если при решении не линейных моделей у Вас возникают проблемы, Вы можете изменить стратегию поиска оптимального решения.
Рис. 7.20. Диалоговое окно "Параметры поиска решения"
Опция Оценка определяет способ оценки управляемых переменных в процессе улучшения решения. Используемое по умолчанию значение этой опции Линейная оценивает переменные с использованием техники линейной экстраполяции. Второе значение опции - Квадратичная - использует технику нелинейной экстраполяции.
Опция Производные определяет способ оценки производных. При использовании значения Центральные иногда удается улучшить рассчитанные в принимаемом по умолчанию режиме Прямые значения первых производных за счет большего времени расчета. Однако в большинстве случаев степень улучшения не окупает дополнительных усилий.
Опция Метод определяет выбор метода поиска допустимого решения, улучшающего целевую функцию. Выбираемый по умолчанию метод Ньютона отличается более низкой скоростью движения к оптимальному решению, но требует значительно меньше памяти, чем метод сопряженных градиентов.
Использование опции Автоматическое масштабирование также часто дает возможность облегчить процесс нахождения оптимального решения нелинейной задачи.
При поиске решения нелинейных задач многое зависит от величин, с которых стартует поиск решения. Поэтому, начиная решение нелинейной задачи, следует начинать поиск оптимального решения с тех величин, которые получены в предыдущем решении (может быть аналогичной линейной задачи), или близкие к тем, которые Вы предполагаете получить.
ЗАМЕЧАНИЕ. Команда РЕШИТЬ УРАВНЕНИЕ меню СЕРВИС дает также возможность решать задачу "Как сделать, чтобы?" для наиболее общего ее случая. Задав величину целевой функции в диалоговом окне ПОИСК РЕШЕНИЯ, Вы можете найти значения независимых переменных, обеспечивающих её получение при соблюдении введенных Вами ограничений.