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

Диспетчер сценариев способен запомнить несколько ре­шений, найденных данным средством и сгенерировать на этой основе отчет.

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

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

  • каковы переменные модели (для определения каких величин строится модель)?
  • в чем состоит цель, для достижения которой из множества всех допустимых значений переменных выбираются оптимальные?
  • каким ограничениям должны удовлетворять неизвестные?

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

Например:

  • модели с несколькими периодами времени величина материального ресурса на начало следующего периода должна равняться  величине этого  ресурса на конец  предыдущего  периода;
  • в модели поставок величина запаса на начало периода плюс количество полученного должна равняться величине запаса на конец периода плюс количество отправленного;
  • многие величины в модели по своему физическому смыслу не могут быть отрицательными, например, количество полученных единиц товара.

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

Сюда же входит преодоление несовместности, а также неограниченности целевой функции: при максимизации целевой функции область допустимых решений должна быть ограничена сверху, при минимизации — ограничена снизу.

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

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

Решая задачи с нелинейными зависимостями, следует:

  • ввести предварительно предположительные значения искомых переменных (иногда легко получить графическое представление решения и сделать приблизительные выводы о решении);
  • в окне Параметры поиска решения снять (если установлен) флажок Линейная модель.

Решая задачи целочисленного программирования, не следует забывать также о требованиях целочисленности.

Приведем описание диалоговых окон надстройки Поиск решения.

Опции настройки диалогового окна Поиск решения.

Опция

Описание

Установить целевую ячейку

Указывается ячейка, содержащая целевую функцию (критерий оптимизации) рассматриваемой задачи

Равной

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

Изменяя ячейки

Указываются ячейки, которые должны изменяться в процессе поиска решения задачи (т.е. ячейки, которые являются переменными задачи)

Ограничения

Отображаются ограничения, налагаемые на переменные задачи. Допускаются ограничения в виде равенств, неравенств, а также – требование целочисленности переменных. Ограничения добавляются по одному с помощью кнопки Добавить

кнопка Параметры

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

При нажатии кнопки Параметры в окне Поиск решения открывается окно Параметры поиска решения. 

Опции настройки диалогового окна Параметры поиска

Опция

Описание

Максимальное время

Ограничивает время, отпускаемое на поиск решения задачи

Предельное число операций

Ограничивает число промежуточных вычислений

Относительная погрешность

Определяют точность, с которой ищется решение

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

Допустимое отклонение

Линейная модель

Служит для поиска решения линейной задачи оптимизации или нелинейной аппроксимации нелинейной задачи. В случае нелинейной задачи флажок Линейная модель должен быть сброшен, в случае линейной задачи – установлен, т.к. иначе возможно получение неверного результата.

Показывать результаты итераций

Для приостановки поиска решений и просмотров отдельных итераций

Автоматическое масштабирование

Предназначен для включения автоматической нормализации входных и выходных значений, качественно различающихся по величине

Оценки

Служит для выбора метода экстраполяции

Разности

Группа предназначена для выбора метода численного дифференцирования

Метод поиска

Служит для выбора алгоритма оптимизации

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

Отчеты бывают трех типов: Результаты, Устойчивость, Пределы.

Тип отчета выбирается по окончании поиска решения в окне Результаты поиска решения в списке Тип отчета (можно выбрать сразу два или три типа).

  • Отчет типа Результаты содержит окончательные значения параметров задачи целевой функции и ограничений.
  • Отчет типа Устойчивость показывает результаты малых изменений параметров поиска решения.
  • Отчет типа Пределы показывает изменения решения при поочередной максимизации и минимизации каждой переменной при неизменных других переменных.