Применение стандартных средств Excel для решения задачи оптимизации

Для решения задач, требующих применения математического аппарата линейного и нелинейного программирования и методов исследования операций, используется надстройка «Поиск решения» в Excel, которая вызывается следующими действиями:

Сервис (на панели задач) –> Поиск решения:

Рис. 4.25

Процедура поиска решения позволяет найти оптимальное значение формулы, содержащейся в ячейке, которая называется целевой. Эта процедура работает с группой ячеек, прямо или косвенно связанных с формулой в целевой ячейке. Чтобы получить по формуле, содержащейся в целевой ячейке, заданный результат, процедура изменяет значения во влияющих ячейках. Чтобы сузить множество значений, используемых в модели, применяются ограничения. Эти ограничения могут ссылаться на другие влияющие ячейки.

Целевая ячейка представляет собой цель. Нам нужно получить либо минимальное, либо максимальное значение целевой ячейки. В некоторых ситуациях может быть несколько целевых ячеек. Положим, в ячейке, которую мы назначаем целевой, содержится функция

g(x ₁, x ₂) = 3 x ₁+2 x ₂,

и нам необходимо найти ее максимальное значение.

Формула в целевой ячейке содержит ссылки на изменяемые ячейки – это ячейки электронной таблицы, которые можно изменять или настраивать, чтобы оптимизировать целевую ячейку. В нашем случае в изменяемых ячейках будут содержаться переменные x ₁, x ₂.

Ограничения устанавливаются для изменяемых ячеек. В большинстве моделей поиска решений существует неявное ограничение, что все изменяемые ячейки должны быть неотрицательными. В модели поиска решений использование каких-либо ограничений необязательно. Допустим, в качестве ограничений мы укажем x ₁ x ₂+1; x ₂ 10.

Любая спецификация изменяемых ячеек, удовлетворяющая ограничениям модели, называется подходящим решением. По существу, «Поиск решения» находит все подходящие решения, с «наилучшим значением» целевой ячейки (максимальным для оптимизации по максимуму, минимальным для оптимизации по минимуму). Такое решение называется оптимальным решением. Для некоторых моделей поиска решения оптимального решения не существует, а для некоторых существует только единственное решение. Для других моделей поиска решения существует несколько (фактически бесконечное количество) оптимальных решений.

Процедуру поиска решения можно использовать для определения значения влияющей ячейки, которое соответствует экстремуму зависимой ячейки.

В нашем случае после выполнения процедуры поиска решений получаем, что максимальным значением целевой ячейки является значение 53 при x ₁ = 11, x ₂ = 10.

Дата добавления: 2014-12-29