7. Нелинейные уравнения. Оптимизационные задачи

"Смотри: шатнулся ствол сосны,

На склоне этой крутизны

Лавины жди наверняка!"

Но донеслось издалека:

Excelsior!

Лонгфелло

1. Поиск решения

Excel - это мощный инструмент для решения нелинейных уравнений и ряда оптимизационных задач, например оптимизации и размещения ресурсов. Он может определить, как наилучшим способом использовать небольшие ресурсы так, чтобы максимизировать желаемые цели (прибыль) или минимизировать нежелаемые (расходы).

Общая задача математического программирования формулируется следующим образом: найти вектор , удовлетворяющий системе ограничений типа «равенств»

(1.0)

и (или) «неравенств»

(1.0)

и доставляющий экстремум функции

.

(1.0)

На некоторые переменные часто накладывается условие неотрицательности и целочисленности. Если хотя бы одна из функций в ( 1 .0)- ( 1 .0) является нелинейной, то и задача математического программирования считается нелинейной.

С помощью большинства вычислительных методов можно найти точку локального оптимума, но нельзя установить, является она точкой глобального (абсолютного) или относительного оптимума. Даже если область допустимых решений выпуклая, целевая функция может иметь несколько локальных экстремумов. Если в задачах линейного программирования точка экстремума является вершиной многогранника решений, то в задачах нелинейного программирования она может лежать в вершине многогранника, на ребре (грани) или внутри области. Если задача содержит нелинейные ограничения, то область допустимых решений, как правило, не является выпуклой и кроме глобального могут существовать точки локального оптимума.

Рис. 1.13. Поиск решения

Решая задачу с помощью системы "Поиск Решения" (Сервис/Поиск решения), необходимо указать (рис. 1.13):

целевую ячейку (с формулой, определяющей целевую функцию ( 1 .0)), в которой требуется установить максимальное, минимальное или некоторое определенное значение;

• изменяемые ячейки (до 200), переменные , значения которых определяются методами математического программирования (область «изменяя ячейки»;

ячейки ограничений, значения которых должны лежать в определенных пределах или соответствовать некоторым заданным значениям программирования (область «ограничения»). Для формирования области допустимых значений ( 1 .0), ( 1 .0) можно указать до 500 ограничений, по 2 для каждой переменной плюс 100 дополнительных ограничений.

Excel позволяет устано-вить параметры процесса решения задачи (метод поиска решения, максимальное время решения, предельное число итераций, относительная погрешность и др. (рис. 1.14).

Рис. 1.14. Параметры решения

Ограничения на неотрицательность решения можно задавать не в области ограничений, а в параметрах (см. рис. 1.14).

2. Подбор параметра

Команда Подбор параметра (Сервис/Подбор параметра) используется, как правило, для решения одного нелинейного уравнения с одним неизвестным. Она подбирает значение в определенной ячейке до тех пор, пока формула, зависящая от этой ячейки, не достигнет нужного значения. По существу, данная работа является частным случаем предыдущей при одной варьируемой переменной.