Поиск решения – ЧАСТЬ 1

Поиск решения — надстройка,  входящая  в комплект  поставки  Excel. Ее основным назначением является решение линейных  и нелинейных задач оптимизации. Для линейных задач используется симплекс-метод, для задач целочисленного программирования — метод ветвей, границ и для нелинейных задач — метод приведенного градиента.

Надстройка Поиск решения является частью блока задач, который иногда называют анализом  «что–если».

Если надстройка  Поиск решения подключена, на вкладке Данные появляется группа

Анализ и в ней кнопка Поиск решения (рис. 2.49).

Рис. 2.49. Применение надстройки Поиск решения

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

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

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

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

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

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

Если найдено оптимальное решение, то в диалоговом окне Результаты поиска решения выводятся два ключевых  сообщения: Решение  найдено и Все ограничения  и условия оптимальности выполнены. Отсутствие хотя бы одного из этих сообщений свидетель ствует о том, что программе  не удалось  найти оптимальное решение.  Причины неудачи могут быть различны, необходимо проверить установленные ограничения, начальные значения изменяемых ячеек и постановку задачи в целом. Если решение не было найдено, сообщаются иные причины  окончания вычислений.

В окне Результаты поиска решения можно принять найденный вариант или восстановить  прежний, а также сохранить сценарий расчетов с определенным именем. В этом окне есть список Тип отчета. Если выделить один или несколько вариантов, то при закрытии окна создаются дополнительные листы рабочей книги (Отчет по результатам 1, Отчет по пределам 1, Отчет по устойчивости 1) с запрошенной дополнительной информацией. При повторном запуске могут создаваться дополнительные листы с новыми номерами отчетов, таким образом, информация о различных вариантах расчетов не будет утеряна.

Для работы с сохраненными сценариями можно использовать Диспетчер сценариев, вызываемый нажатием кнопки Анализ «что–если» и выбором инструмента Диспетчер сценариев… в группе Работа с данными вкладки Данные. Здесь можно добавить, изменить, удалить сценарии, а также объединить их и вывести отчет на дополнительный лист. В этом окне приводится список существующих  в данной  книге сценариев, для запуска одного из которых нужно выбрать конкретное  имя сценария и нажать кнопку Вывести. При этом происходит перерасчет результатов, и в целевой ячейке появляется новое значение.

С помощью надстройки  Поиск решения можно решать следующие задачи.

✓                    Поиск оптимума при наличии ограничений (условная оптимизация) — наиболее

общий тип задачи оптимизации.

В этом случае заданы ограничения и адрес ячейки целевой функции, которую необходимо  максимизировать или минимизировать. Если  целевая  функция и все ограничения линейны,  то имеет место задача линейной  оптимизации. В этом случае в диалоговом  окне Параметры поиска решения можно установить  флажок  Линейная модель. При  этом решение  задачи будет найдено  быстрее, надежнее,  и будет выдана  более  подробная  дополнительная информация. В другом случае надстройка  Поиск  решения использует  метод приведенного градиента.  Если  целевая  функция имеет несколько  оптимумов,  удовлетво ряющих  ограничениям, то надстройка  Поиск решения найдет один из них (то есть локальный оптимум).  Найденный оптимум  может не быть глобальным (то есть наилучшим из всех оптимумов). При наличии нескольких локальных  оптимумов на результат поиска решения может повлиять изменение начальных значений  изменяемых ячеек.

✓                    Поиск безусловного оптимума — задача нахождения максимума или минимума

целевой функции при отсутствии ограничений.

Подобная  постановка  задачи  имеет смысл только  в том случае, если целевая функция является нелинейной. При поиске оптимума линейной целевой функции без задания  ограничений будет выводиться  сообщение о неограниченном решении. Как и в предыдущем случае, если целевая функция имеет несколько максимумов или минимумов, то надстройка Поиск решения находит один из них (локальный оптимум), который может не совпадать с глобальным оптимумом.  Начальные значения  изменяемых ячеек могут существенно  влиять  на поиск оптимума.

✓                    Поиск допустимого решения.

Если не задана целевая ячейка (поле Установить целевую ячейку в диалоговом окне Поиск решения), то надстройка  Поиск решения прекратит работу, найдя допустимое решение  задачи, то есть набор значений  для изменяемых ячеек, которые удовлетворяют всем ограничениям. В случае линейности функций ограничений можно установить  флажок Линейная модель в диалоговом окне Параметры поиска решения. Данная настройка параметров задачи может ускорить поиск допустимого решения.

✓                    Решение систем уравнений.

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

✓                    Подбор параметров.

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

Пример подбора параметров, в качестве одного из вариантов применения надстрой ки Поиск решения, приводится далее.

На рис. 2.51 показано окно Поиск решения с настройками для подбора параметров.

Рис. 2.51. Задание значения целевой ячейки для подбора параметров

На рис. 2.52 показан результат подбора параметров.

Рис. 2.52. Результаты подбора параметров

Далее приводится более подробное описание параметров надстройки Поиск решения, которые задаются в отдельных диалоговых окнах.

Диалоговое окно Поиск решения

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

Для установки целевой ячейки в поле Установить целевую ячейку вводится адрес ячейки, содержащей значение целевой функции. Адрес ячейки можно ввести вручную или щелкнув мышью на нужной ячейке (в этом случае адрес вводится автоматически).

В области Равной диалогового окна Поиск решения задается тип оптимизации. Можно указать, требуется ли достичь максимального или минимального значения целевой ячейки или некоторого конкретного значения, которое вводится в соответствующем поле данного диалогового окна.

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

Список  Ограничения  содержит  перечень  всех ограничений,  установленных для данной  задачи. При  нажатии  на кнопку Добавить открывается диалоговое  окно Добавление ограничения. По умолчанию предполагается, что ограничение имеет вид неравенства <=. Вместо данного неравенства из списка могут быть выбраны другие варианты, а также ограничения вида цел и двоич, которые указывают, что значения  изменяемых ячеек могут принимать только целые значения или только двоичные значения  (0 и 1).

В Excel 2007 максимальное количество изменяемых ячеек, с которыми одновремен но может работать надстройка Поиск решения, равно 200. При решении нелинейных задач на каждую  из изменяющихся ячеек можно наложить  до 100 ограничений (в дополнение к целочисленным ограничениям на переменные).

Источник: Трусов А. Ф. Excel 2007 для менеджеров и экономистов: логистические,  производственные и оптимизационные расчеты (+CD). — СПб.: Питер, 2009. — 256 с.: ил.

Похожие посты:

Вы можете оставить комментарий, или ссылку на Ваш сайт.

Оставить комментарий