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

Пакет поиска решения является надстройкой Excel. Это значит, что в стандартной конфигурации Excel по умолчанию данный пакет не устанавливается. Для того чтобы активи­зировать его, необходимо выбрать коман­ду Сервис ► Надстройки. На экран будет выведено окно со списком надстроек, установленных в настоящий момент в Ex­cel. Найдите в списке пункт Поиск реше­ния, установите рядом флажок и щелк­ните на кнопке ОК.

Инструмент будет активизирован, и в меню Сервис появит­ся новая команда Поиск решения. Зачем нужен поиск решения? Если у вас есть некоторый параметр {целевая функция), который зависит от некоторого количества (боль­ше одного) других параметров (переменных), то путем поиска (подбо­ром) решения можно найти такие сочетания переменных, при кото­рых функция принимает заданное значение. При этом можно находить не только заданное постоянное значение, но и минимальное или мак­симальное значение функции с учетом ограничений, наложенных на значения переменных. Это значит, что путем поиска решения вы мо­жете, например:

О найти такое распределение производственных ресурсов, при кото­ром прибыль будет максимальной;

О найти такое распределение инвестиций, при котором риск будет минимальным;

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

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

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

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

О Группа переключателей Равной позволяет выбрать, что именно должно считаться решением:

• максимальному значению — решением является максимально воз­можное значение;

• минимальному значению — решением является минимально воз­можное значение;

• значению— решением является конкретное числовое значение в це­левой ячейке, которое нужно ввести в расположенное рядом поле.

О В поле Изменяя ячейки необходимо ввести диапазон изменяемых ячеек. Кнопка Предположить позволяет Excel автоматически про­смотреть, от каких ячеек зависит конечный результат, и ввести их в поле Изменяя ячейки автоматически.

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

Рис. 9.8. Добавление ограничения

О Кнопки Изменить и Удалить служат, соответственно, для редактиро­вания или удаления уже установленных ограничений.

О Кнопка Параметры выводит на экран окно настройки параметров поиска решения (рис. 9.9).

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

Рис. 9.9. Окно настройки параметров поиска решения

О В поле Максимальное время вводится время в секундах, по истече­нии которого поиск решения будет прекращен даже в том случае, если решение не найдено или не оптимизировано. Допустимые зна­чения — от 1 до 32 767.

О В поле Предельное число итераций вводится количество циклов вы­числений, после которого поиск решения будет прекращен даже в том случае, если решение не найдено или не оптимизировано. До­пустимые значения — от 1 до 32 767.

О В поле Относительная погрешность указывается десятичная дробь в интервале от 0 до 1. Чем она меньше, тем выше точность вычисле­ний.

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

О Если за последние 5 итераций относительное изменение значения в целевой ячейке оказывается меньше числа, указанного в поле Сходимость, решение считается найденным.

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

О Установка флажка Неотрицательные значения ограничивает диапа­зон изменения переменных только положительными значениями.

О Установите флажок Автоматическое масштабирование, если значе­ния входных переменных и целевой функции значительно (на не-

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

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

О Группа переключателей Оценки позволяет выбрать метод экстра­поляции:

• линейная — линейная экстраполяция дает более точные реше­ния при линейных задачах;

• квадратичная — квадратичная экстраполяция лучше работает при нелинейных задачах.

О Группа переключателей Разности позволяет выбрать дифференциа­лы (производные):

• прямые — прямые производные следует использовать, если ана­лизируемая функция гладкая;

• центральные — центральные производные следует использовать, если в анализируемой функции есть точки разрыва.

О Группа переключателей Метод поиска позволяет выбрать метод по­иска решения:

• Ньютона — метод Ньютона обеспечивает высокую скорость вы­числений, но требует больших затрат памяти;

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

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