Использование надстройки «Поиск решения»
Добавление надстройки Поиск решения
Если вы впервые хотите воспользоваться надстройкой Поиск решения ее необходимо загрузить. Для этого:
Во вкладке Файл выберете команду Параметры, а затем – категорию Надстройки;
В поле Управление выберете значение Надстройки Excel и нажмите кнопку Перейти;
В окне диалога Надстройки в поле Доступные надстройки установите флажок рядом с пунктом Поиск решения и нажмите кнопку ОК;
Во вкладке Данные будет добавлена новая группа Анализ с командой
.
Постановка задачи и оптимизация модели с помощью процедуры поиска решения
Выберите команду Поиск решения.
Если группа Анализ отсутствует во вкладке Данные, установите соответствующую надстройку.
В поле Оптимизировать целевую функцию введите адрес или имя ячейки, в которой находится формула оптимизируемой модели.
Чтобы максимизировать значение целевой ячейки путем изменения значений влияющих ячеек, установите переключатель в положение Максимум.
Чтобы минимизировать значение целевой ячейки путем изменения значений влияющих ячеек, установите переключатель в положение Минимум.
Чтобы установить значение в целевой ячейке равным некоторому числу путем изменения значений влияющих ячеек, установите переключатель в положение Значения и введите в соответствующее поле требуемое число.
5. В поле Изменяя ячейки переменных введите имена или адреса изменяемых ячеек, разделяя их запятыми. Изменяемые ячейки должны быть прямо или косвенно связаны с целевой ячейкой. Допускается установка до 200 изменяемых ячеек.
6. В поле В соответствии с ограничениями введите все ограничения, накладываемые на поиск решения.
7. Выберите метод решения из раскрывающегося списка.
8. Нажмите кнопку Найти решение.
9. Чтобы сохранить найденное решение, установите переключатель в диалоговом окне Результаты поиска решения в положение Сохранить найденное решение.
10. Чтобы восстановить исходные данные, установите переключатель в положение Восстановить исходные значения.
Совет. Чтобы прервать поиск решения, нажмите клавишу ESC. Microsoft Excel пересчитает лист с учетом найденных значений для ячеек переменных решения.
Вызов надстройки “Поиск решения”
Чтобы запустить поиск решения выберите вкладку Данные, в группе Анализ - команду Поиск решения.
!!! Вызовите диалог «Поиск решения» и ознакомьтесь с его составляющими |
Диалоговое окно «Параметры поиска решения»
Оптимизировать целевую функцию |
Служит для указания целевой ячейки, значение которой необходимо максимизировать, минимизировать или установить равным заданному числу. Эта ячейка должна содержать формулу. |
До |
Служит для выбора варианта оптимизации значения целевой ячейки (максимизация, минимизация или подбор заданного числа). Чтобы установить число, введите его в поле. |
Изменяя ячейки переменных |
Служит для указания ячеек, значения которых изменяются в процессе поиска решения до тех пор, пока не будут выполнены наложенные ограничения и условие оптимизации значения ячейки, указанной в поле «Оптимизировать целевую функцию». |
В соответствии с ограничениями |
Служит для отображения списка граничных условий поставленной задачи. |
Сделать переменные без ограничений неотрицательными |
Ограничения неотрицательности значений переменных можно задать с помощью установки флажка для данного поля. |
Выберите метод решения |
Служит для выбора метода решения. Для гладких нелинейных задач – поиск решения нелинейных задач методом ОПГ. Для линейных задач – поиск решения линейных задач симплекс-методом. Для негладких задач – эволюционный поиск решения. |
Добавить |
Служит для отображения диалогового окна «Добавление ограничения». |
Изменить |
Служит для отображения диалоговое окна «Изменение ограничения». |
Удалить |
Служит для снятия указанного ограничения. |
Сбросить |
Служит для очистки полей окна диалога и восстановления значений параметров поиска решения, используемых по умолчанию. |
Загрузить/сохранить |
1.Загрузить Служит для отображения на экране диалогового окна Загрузить или Сохранить модель, в котором можно задать ссылку на область ячеек, содержащих загружаемую модель. |
2. Сохранить Служит для отображения на экране диалогового окна Загрузить или Сохранить модель, в котором можно задать ссылку на область ячеек, предназначенную для хранения модели оптимизации. Данный вариант предусмотрен для хранения на листе более одной модели оптимизации - первая модель сохраняется автоматически. |
|
Найти решение |
Служит для запуска поиска решения поставленной задачи. |
Параметры |
Служит для отображения диалогового окна «Параметры», в котором можно указать предусмотренные варианты поиска решения. |
Закрыть |
Служит для выхода из окна диалога без запуска поиска решения поставленной задачи. При этом сохраняются установки сделанные в окнах диалога, появлявшихся после нажатий на кнопки Параметры, Добавить, Изменить или Удалить. |
Рассмотрим примеры задач, при решении которых используется надстройка Поиск решения.
Диалоговое окно “Параметры”
В Microsoft Excel можно изменять условия и варианты поиска решения для линейных и нелинейных задач. Значения и состояния элементов управления, используемые по умолчанию, подходят для решения большинства задач.
!!! Вызовите диалоговое окно Параметры поиска решений
Вкладка «Все методы» |
|
Точность ограничения |
Служит для задания точности, с которой определяется соответствие ячейки целевому значению или приближение к указанным границам. Поле должно содержать число из интервала от 0 (нуля) до 1. Низкая точность соответствует введенному числу, содержащему меньшее количество десятичных знаков, чем число, используемое по умолчанию - например, 0,000001. Высокая точность увеличит время, которое требуется для того, чтобы сошелся процесс оптимизации. |
Использовать автоматическое масштабирование |
Служит для включения автоматической нормализации входных и выходных значений, качественно различающихся по величине - например, максимизация прибыли в процентах по отношению к вложениям, исчисляемым в миллионах рублей. |
Показывать результаты итераций |
Служит для приостановки поиска решения для просмотра результатов отдельных итераций. |
Игнорировать целочисленные ограничения |
Используется для задач, где возможно получение дробных значений параметров. |
Целочисленная оптимальность |
Служит для задания допуска на отклонение от оптимального решения, если множество значений влияющей ячейки ограничено множеством целых чисел. При указании большего допуска поиск решения заканчивается быстрее. |
Максимальное время |
Служит для ограничения времени, отпускаемого на поиск решения задачи. В поле можно ввести время (в секундах) не превышающее 32767; значение 100, используемое по умолчанию, подходит для решения большинства простых задач. |
Число итераций |
Служит для управления временем решения задачи, путем ограничения числа промежуточных вычислений. В поле можно ввести время (в секундах) не превышающее 32767; значение 100, используемое по умолчанию, подходит для решения большинства простых задач. |
!!!
Самостоятельно определите назначение
полей «Максимальное число подзадач»
и «Максимальное число допустимых
решений»
Вкладка «Поиск решения нелинейных задач методом ОПГ» |
|
Сходимость |
Когда относительное изменение значения в целевой ячейке за последние пять итераций становится меньше числа, указанного в поле Сходимость, поиск прекращается. Сходимость применяется только к нелинейным задачам, условием служит дробь из интервала от 0 (нуля) до 1. Лучшую сходимость характеризует большее количество десятичных знаков ѕ например, 0,0001 - это меньшее относительное изменение, чем 0,01. Лучшая сходимость требует больше времени на поиск оптимального решения. |
Производные |
Служит для указания метода численного дифференцирования - правые или центральные производные - который используется для вычисления частных производных целевых и ограничивающих функций. |
Правые |
Используется для гладких непрерывных функций. |
Центральные |
Используется для функций, имеющих разрывную производную. Не смотря на то, что данный способ требует больше вычислений, он может помочь при получении итогового сообщения о том, что процедура поиска решения не может улучшить текущий набор влияющих ячеек. |
!!!
Самостоятельно определите назначение
области Несколько начальных
точек:
- Использовать несколько
начальных точек; -
Размер совокупности; -
Случайное значение; -
Обязательные границы для переменных.
Вкладка «Эволюционный поиск решения» |
|
Сходимость |
Когда относительное изменение значения в целевой ячейке за последние пять итераций становится меньше числа, указанного в поле Сходимость, поиск прекращается. Сходимость применяется только к нелинейным задачам, условием служит дробь из интервала от 0 (нуля) до 1. Лучшую сходимость характеризует большее количество десятичных знаков ѕ например, 0,0001 - это меньшее относительное изменение, чем 0,01. Лучшая сходимость требует больше времени на поиск оптимального решения. |
Г
!!! Самостоятельно определите назначение полей:
- Скорость изменения;
- Размер совокупности;
- Случайное значение;
- Максимальное время без улучшения;
- Обязательные границы для переменных.
раничные условия
Ограничения на значения изменяемых ячеек, конечных ячеек или других ячеек, прямо или косвенно связанных друг с другом. Для линейных задач ограничений на количество граничных условий не налагается. Для нелинейных задач, каждая изменяемая ячейка может иметь следующие граничные условия: логическое ограничение; целочисленное ограничение плюс верхний, нижний или оба предела; кроме того, верхний или нижний предел может быть определен для 100 ячеек.
При задании граничных условий можно использовать следующие операторы.
Оператор Значение
<= меньше или равно
>= больше или равно
= равно
цел целое число (применимо только к изменяемым ячейкам)
бин двоичное (применимо только к изменяемым ячейкам)
раз все разные (применимо только к изменяемым ячейкам)
Наложение ограничений на поиск решения
1. Во вкладке Данные, в группе Анализ, выберите команду Поиск решения.
2.Нажмите кнопку Добавить.
3.В поле Ссылка на ячейки введите адрес или имя ячейки, на значение которой накладываются ограничения.
4.Выберите из раскрывающегося списка условный оператор, который необходимо разместить между ссылкой и ограничением.
5.В поле Ограничения введите число, формулу или имя ячейки.
6.Чтобы наложить условие на поиск решения задачи, и приступить к набору нового условия, нажмите кнопку Добавить.
7.Чтобы наложить условие и вернуться в диалоговое окно Поиск Решения, нажмите кнопку OK.
Добавление ограничения
Нажмите на кнопку Добавить, чтобы, не возвращаясь в окно Параметры поиска решения, наложить новое условие на поиск решения задачи.
Ссылка на ячейку
Служит для указания ячейки или диапазона, на значения которых необходимо наложить ограничение.
Ограничение
Служит для задания условия, которое накладывается на значения ячейки или диапазона, указанного в поле Ссылка на ячейку. Выберите необходимый условный оператор и введите ограничение – число, формулу, ссылку на ячейку или диапазон ѕ в поле справа от раскрывающегося списка.
Примечания
Условные операторы: целое, бинарное, все разные можно применять только при наложении ограничений на изменяемые ячейки;
!!! Добавьте несколько новых ограничений в текущую задачу |
Изменение и удаление ограничения на поиск решения
Во вкладке Данные, в группе Анализ, выберите команду Поиск решения;
В списке В соответствии с ограничениями укажите строку, которую следует изменить или удалить;
Чтобы удалить ограничение, нажмите кнопку Удалить;
Чтобы изменить ограничение, нажмите кнопку Изменить, а затем внесите нужные изменения.