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

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

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

Модуль Поиск решения представляет собой надстройку и по умолчанию не включен в состав MS Excel. Если команда Поиск решения в группе Анализ на вкладке Данные отсутствует, следует открыть окно Параметры Excel (вкладка Файл – Параметры), в категории Надстройки выбрать элемент управления: Надстройки Excel и нажать кнопку Перейти. В открывшемся окне Надстройки в списке доступных надстроек установить флажок напротив компоненты Поиск решения и нажать ОК.

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

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

1. На вкладке Данные в группе Анализ щелкните Поиск решения. Если команда Поиск решения или группа Анализ отсутствует, необходимо загрузить надстройку (Надстройка. Вспомогательная программа, служащая для добавления в Microsoft Office специальных команд или возможностей.) "Поиск решения".

2. В поле Оптимизировать целевую функцию введите ссылку на ячейку (Ссылка на ячейку. Координаты, определяющие расположение ячейки на листе. Например, B3 представляет ссылку на ячейку, находящуюся на пересечении столбца B и строки 3.) или имя (Имя. Слово или строка знаков, представляющие ячейку, диапазон ячеек, формулу или константу. Понятные имена, такие как «Продукты», используют для ссылок на диапазоны, названия которых трудно запомнить, например, Продажи!C20:C30.) целевой ячейки. Целевая ячейка должна содержать формулу.

3. Выполните одно из указанных ниже действий.

§ Чтобы значение целевой ячейки было максимальным из возможных, установите переключатель в положение максимальному значению.

§ Чтобы значение целевой ячейки было минимальным из возможных, установите переключатель в положение минимальному значению.

§ Чтобы задать для целевой ячейки конкретное значение, установите переключатель в положение значению и введите в поле нужное число.

4. В поле Изменяя ячейки переменных введите имена диапазонов ячеек переменных решения или ссылки на них. Несмежные ссылки разделяйте запятыми. Ячейки переменных должны быть прямо или косвенно связаны с целевой ячейкой. Можно задать до 200 ячеек переменных.

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

1) В диалоговом окне Параметры поиска решения нажмите кнопку Добавить.

2) В поле Ссылка на ячейку введите ссылку на ячейку или имя диапазона ячеек, на значения которых налагаются ограничения.

3) Выберите в раскрывающемся списке отношение (<=, =, >=, цел, бин или раз), которое нужно использовать между ссылкой и ограничением.

- Если выбрать вариант цел, в поле Ограничение появится значение целое число.

- Если выбрать вариант бин, в поле Ограничение появится значение двоичное число.

- Если выбрать вариант раз, в поле Ограничение появится значение все разные.

4) Если в поле Ограничение было выбрано отношение <=, = или >=, введите число, ссылку на ячейку (или имя ячейки) или формулу.

5) Выполните одно из указанных ниже действий.

§ Чтобы принять данное ограничение и добавить другое, нажмите кнопку Добавить.

§ Чтобы принять ограничение и вернуться в диалоговое окно Параметры поиска решения, нажмите кнопку ОК.

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

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

2) Нажмите кнопку Изменить и внесите изменения либо нажмите кнопку Удалить.

6. Нажмите кнопку Найти решение.

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

Чтобы прервать поиск решения, нажмите клавишу Esc. Лист Microsoft Excel будет пересчитан с учетом последних найденных значений влияющих ячеек.

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

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

1. С помощью полей Максимальное время и Ч исло итераций можно указать поиску решений, сколько усилий он должен приложить для решения задачи. Если поиск решений достигает предела по времени или числу итераций до нахождения нужного результата, Excel спрашивает, нужно ли продолжать поиск решения. Используемые по умолчанию величины обычно достаточны для решения большинства задач, но если решения получить не удается, можно попробовать их подобрать.

2. Точность ограничения используется поиском решения для определения относительной погрешности вычислений. Чем ближе это значение к 1, тем ниже точность. Задание относительной погрешности, меньше установленной по умолчанию (0,000001) приводит к росту времени поиска решения.

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

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

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

Отчет по результатам содержит целевую ячейку, список изменяемых ячеек и ограничений. В этом отчете для каждого ограничения выводятся "статус" и "разница". Разница – это разность между значением, выводимым в ячейке ограничения при получении решения, и числом, заданным в правой части формулы ограничения. Статус может принимать три состояния: " Связанное " (ограничение, для которого значение разницы равно 0), " Не связанное " (ограничение, которое было выполнено с ненулевым значением разницы) и " Не выполненное ".

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

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

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

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

Дата добавления: 2014-01-11