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

Процедура поиска решения позволяет найти оптимальное значение формулы, содержащейся в ячейке, которая называется целевой. При этом чтобы получить нужный результат, происходят изменения в группе ячеек, прямо или косвенно связанных с формулой в целевой ячейке. Такие ячейки называются влияющими ячейками. Процедура одновременного изменения значений в нескольких влияющих ячейках достаточно трудоемкая, и, чтобы сузить множество значений, используемых при поиске решения, накладываются ограничения. Эти ограничения могут ссылаться на другие влияющие ячейки. Чтобы использовать процедуру поиска решения, программа должна быть соответствующим образом настроена. Для настройки нужно выбрать команду “Сервис”->“Надстройки”. В диалоговом окне следует установить флажок у элемента “Поиск решения”. Чтобы начать поиск решения, необходимо выбрать команду “Сервис”->“Поиск решения”. Будет открыт диалог настройки параметров поиска (Рис. 10.2).

Поле ввода “Установить целевую ячейку” используется для указания целевой ячейки, решение в которой нужно найти. Переключатель “Равной” определяет цель поиска: максимальное, минимальное или конкретное значение. Поле ввода “Изменяя ячейки” служит для указания ячеек, значения которых изменяются в процессе поиска решения до тех пор, пока не будут выполнены наложенные ограничения и условие оптимизации значения ячейки, указанной в поле “Установить целевую ячейку”. Можно ввести ячейки с помощью клавиатуры или выбрать ячейки с помощью мыши. Также можно нажать кнопку “Предположить”, и программа сама попробует найти ячейки, влияющие на формулу. Список “Ограничения” служит для отображения граничных условий поставленной задачи.

Рис. 8.6. Поиск решения. Рис8.7. Параметры поиска решения.

Если нужно уточнить параметры поиска решения, следует нажать кнопку “Параметры”. Будет открыт диалог дополнительной настройки (рис. 10.3). Поле ввода “Максимальное время” используется для ограничения времени (в секундах), отпускаемого на поиск решения задачи. Если при поиске решения это время превышено, поиск будет остановлен. Поле ввода “Предельное число итераций” служит для ограничения числа промежуточных вычислений. Поле ввода “Относительная погрешность” определяет точность, с которой определяется соответствие ячейки целевому значению (от нуля до единицы). Поле ввода “Допустимое отклонение” используется для задания допуска на отклонение от оптимального решения (в процентах). При указании большего допуска поиск решения заканчивается быстрее. Поле ввода “Сходимость” используется для определения условия окончания поиска. Если относительное изменение значения в целевой ячейке за последние пять итераций становится меньше числа, указанного в этом поле, поиск прекращается. Если установлен флажок “Линейная модель”, для поиска используется линейная модель, что в некоторых случаях может увеличить скорость поиска. Если установлен флажок “Показывать результаты итераций”, поиск идет в пошаговом режиме. Флажок “Автоматическое масштабирование” служит для включения автоматической нормализации параметров поиска решения, например, если цель решения - определение прибыли в процентах, а параметры исчисляются в рублях, нужна нормализация. Установив флажок “Значения не отрицательны”, можно ограничить значения влияющих ячеек положительными величинами. Группа переключателей в нижней части диалога служит для настройки таких параметров оптимизации, как экстраполяция значений, а также метода поиска.

Чтобы начать поиск, следует нажать кнопку “Выполнить”. Диалог настройки параметров будет закрыт, и начнется процесс поиска решения. При этом в строке состояния программы Excel будет отображаться вспомогательная информация о ходе поиска: текущий шаг, значения целевой функции и влияющих ячеек. Чтобы досрочно прервать поиск решения, можно нажать клавишу Esc. По окончании поиска появляется диалог с результатами. Установив переключатель в этом диалоге в положение “Сохранить найденное решение”, можно сохранить решение во влияющих ячейках модели. Если переключатель установлен в положение “Восстановить исходные значения”, во влияющих ячейках останутся исходные значения параметров. Программа может показать ход поиска решения. Для этого используются отчеты, формируемые на отдельных листах рабочей книги. В списке “Тип отчета” можно выбрать, какой отчет нужно сформировать.