Использование надстройки Поиск решенияMSExcel

Для решения оптимизационных задач используется надстройка Поиск Решения. Все действия по настройке и использованию данной надстройки приводятся для Excel 2007 и выше. Сначала необходимо убедиться, что эта надстройка присутствует на вкладке Данные в группе Анализ. Если команда Поиск решения или группа Анализ отсутствует, необходимо загрузить эту надстройку (Надстройка. Вспомогательная программа, служащая для добавления в MicrosoftOffice специальных команд или возможностей.). Для этого щелкните пиктограммуMicrosoftOffice (в верхнем левом углу окна), далее щелкните кнопку Параметры Excel. В появившемся окне Параметры Excel выберите слева поле Надстройки.В правой частиокна должно быть установлено значения поля Управление равным Надстройки Excel, нажмите кнопку Перейти, которая находится рядом с этим полем. В окнеНадстройки установите флажок рядом с пунктом Поиск решения и нажмите кнопку ОК. Далее можно работать с установленной надстройкойПоиск Решения.

До вызова Поиск Решения необходимо подготовить данные для решения задачи (из математической модели):

1) Определить ячейки, в которые будет помещен результат решения (изменяемые ячейки).

2) Ввести данные системы ограничений (коэффициенты при неизвестных и правую часть).

3) Ввести зависимости от изменяемых ячеек для левых частей системы ограничений.

4) Ввести зависимость от изменяемых ячеек для целевой функции.

Ниже перечислены основные поля и кнопки диалогового окна Поиск решенияи их назначение.

· Поле Установить целевую ячейку служит для указания ячейки, содержащей целевую функцию, значение которой необходимо максимизировать, минимизировать или установить равным заданному числу. (Эта ячейка должна содержать формулу).

· Переключатель Равной служит для выбора варианта оптимизации значения целевой ячейки (максимизация, минимизация или подбор заданного значения). Чтобы установить заданное значение, введите его в поле, расположенное справа от слова «Значение».

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

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

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

· Кнопка Добавить служит для добавления ограничений. При нажатии этой кнопки открывается диалоговое окно Добавить ограничение, с помощью которого вводятся ограничения.

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

· Кнопка Удалить служит для удаления выбранного ограничения.

· Кнопка Выполнить решает поставленную задачу.

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

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

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

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

Ниже перечислены основные поля и органы управления названных диалоговых окон.

· Поле «Ссылка на ячейку» служит для указания ячейки или диапазона, на значения которых необходимо наложить ограничение.

· Раскрывающийся список предназначен для выбора условия (<=, >=, =) определяющего ограничение.

· Поле «Ограничение» служит для задания числа, формулы, ссылки на ячейку или диапазон, определяющие ограничение.

· Кнопка «Добавить» используется для того, чтобы, не возвращаясь в окно диалога «Параметры поиска решения», наложить новое условие на поиск решения задачи.

Управление алгоритмом поиска и определение его параметров (скорости сходимости, точности и т.п.) выполняется с помощью диалогового окна «Параметры поиска решения», представленного на рисунке:

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

Ниже описаны значения полей, переключателей и флагов этого диалогового окна:

· Поле «Максимальное время» служит для ограничения времени, отпускаемого на поиск решения задачи. В поле можно ввести время (в секундах), не превышающее 32767; значение 100, используемое по умолчанию, подходит для решения большинства простых задач.

· Поле «Предельное число итераций» служит для управления временем решения задачи путем ограничения числа промежуточных вычислений. В поле можно ввести количество итераций, не превышающее 32767; значение 100, используемое по умолчанию, подходит для решения большинства простых задач.

· Поле «Относительная погрешность» служит для задания точности, с которой определяется соответствие ячейки целевому значению или приближение к указанным границам. Поле должно содержать число из интервала от 0 до 1. Относительная погрешность по умолчанию 0,000001.

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

· Поле «Сходимость». Когда относительное изменение значения в целевой ячейке за последние пять итераций становится меньше числа, указанного в поле «Сходимость», поиск прекращается. Сходимость применяется только к нелинейным задачам, условием служит дробь из интервала от 0 до 1.

· Флаг «Линейная модель» служит для ускорения поиска решения линейной задачи оптимизации или линейной аппроксимации нелинейной задачи.

· Флаг «Показывать результаты итераций» служит для приостановки поиска решения для просмотра результатов отдельных итераций.

· Флаг «Автоматическое масштабирование» служит для включения автоматической нормализации входных и выходных значений, качественно различающихся по величине, например, максимизация прибыли в процентах по отношению к вложениям, исчисляемым в миллионах рублей.

· Флаг «Значения не отрицательны» позволяет установить нулевую нижнюю границу для тех влияющих ячеек, для которых она не была указана в поле "Ограничение" диалогового окна «Добавить ограничение».

· Переключатель «Оценки» служит для указания метода экстраполяции (линейная или квадратичная), используемого для получения исходных оценок значений переменных в каждом одномерном поиске.

· Значение переключателя «Линейная» служит для использования линейной экстраполяции вдоль касательного вектора.

· Значение переключателя «Квадратичная» служит для использования квадратичной экстраполяции, которая дает лучшие результаты при решении нелинейных задач и не играет роли для линейных задач.

· Переключатель «Производные» служит для указания метода численного дифференцирования и для линейных задач его значение не играет роли.

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

· Кнопка «Загрузить модель» служит для отображения на экране диалогового окна «Загрузить модель», в котором можно задать ссылку на область ячеек, содержащих загружаемую модель.

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