Технология решения задач оптимизации
В электронных таблицах Excel с помощью надстройки «Поиск решения» можно решать задачи оптимизации. Первоначально необходимо включить надстройку. Последовательность подключения надстройки показана на рис. 1-4.
Перед тем как запустить поиск решения, необходимо четко сформулировать в виде электронной таблицы решаемую задачу. При этом необходимо в одну из ячеек записать формулу вычисления целевой функции, для каждой переменной можно задать ограничения, например, верхнюю или нижнюю границу.
Рис. 1. Кнопка “Office” - Параметры Excel
Рис. 2. Надстройки – Перейти
Рис. 3. Выбор надстройки «Поиск решения»
Рис. 4. Доступ к надстройке – вкладка «Данные»
Отправной точкой при поиске оптимального решения является модель вычисления, созданная в рабочем листе в виде таблицы. Надстройке «Поиск решения» необходимы следующие данные:
1. Изменяемые ячейки – это ячейки, которые будут содержать искомые значения параметров. При этих значениях целевая функция получит экстремальное (минимальное или максимальное) значение.
2. Целевая ячейка - это ячейка таблицы, значение в которой должно быть максимизировано или минимизировано. Она должна содержать формулу, которая прямо или косвенно ссылается на изменяемые ячейки. Эта формула представляет собой целевую функцию.
3. Значения в изменяемых ячейках будут последовательно изменяться до тех пор, пока не будет получено нужное значение в целевой ячейке. Эти ячейки, следовательно, прямо или косвенно должны влиять на значение целевой ячейки.
4. Вы можете задать ограничения и граничные условия. Можно задать также ограничения для других ячеек, прямо или косвенно присутствующих в модели.
Надстройка предоставляет возможность задать параметры, определяющие процесс поиска решения. После задания всех необходимых параметров можно запустить поиск решения.
Порядок решения задачи с помощью надстройки «Поиск решения»
1. Создайте оптимизационную модель, которую следует представить в виде электронной таблицы.
2. Выделите в модели целевую ячейку. Соответствующая ссылка затем будет автоматически отображена в окне диалога «Поиск решения».
3. Выполните переход Данные/Поиск решения. В открывшемся окне диалога в поле Целевая ячейка будет представлена ссылка на активную ячейку.
4. В группе «Равной» установите переключатель в нужное положение, в зависимости от того, должно ли в результате оптимизации значение в целевой ячейке быть максимальным, минимальным или же равняться определенному заданному значению.
5. В поле «Изменяя ячейки» укажите ссылки на изменяемые ячейки. Поместите курсор в это поле и выделите соответствующие ячейки.
6. При необходимости задайте нужные ограничения (рис. 5), используя описанную ниже процедуру определения ограничений.
Рис. 5. Окно «Поиск решения»
7. Установите параметры решения задачи (кнопка «Параметры»).
8. Нажмите кнопку Выполнить. Надстройка определит решение, выполняя последовательные вычислении и изменяя значения в соответствующих ячейках. Если программа нашла решение, то это будет отображено в окне диалога Результаты поиска решения (рис. 6) и в модели.
9. Определите, следует ли сохранить в модели найденное решение или нет.
Рис. 6. Окно «Результаты поиска»
Если поиск решения не привел к нахождению оптимального результата, то в окне диалога будет отображено соответствующее сообщение.
Определение ограничений
Ограничения – это условия, которые должны быть выполнены при поиске решения при решении задачи оптимизации. Ограничения определяют допустимые значения переменных, которые варьируются в процессе поиска решения.
1. В окне диалога «Поиск решения» в поле «Ограничения» нажмите кнопку «Добавить». На экране будет отображено окно диалога для определения ограничения (рис. 7).
Рис. 7. Окно «Добавление ограничений»
2. В поле «Ссылка на ячейку» укажите ссылку на ячейку или диапазан ячеек, для которой(ых) должно действовать ограничение.
3. Выберите из списка нужный режим сравнения: =, <=, >=. Дополнительно в распоряжении имеется режим «Цел» (для переменной допустимы только целые значения) и «Двоич. » - допустимы только значения 0 или 1.
4. В поле «Ограничение» укажите верхнюю или нижнюю границу. Вы можете указать как число, так и ссылку на ячейку, диапазон ячеек, а также формулу.
5. Нажмите кнопку «Добавить». При этом окно не будет закрыто.
6. Аналогичным способом задайте и другие ограничения.
7. Завершите определение последнего ограничения нажатием кнопки ОК.