Команда СЕРВИС/ПОДБОР ПАРАМЕТРА позволяет определить неизвестное значение (параметр), которое будет давать желаемый результат. Технология использования команды следующая:
- решить нужную задачу с каким-либо начальным значением параметра;
- выбрать команду СЕРВИС/ПОДБОР ПАРАМЕТРА;
- в окне диалога ПОДБОР ПАРАМЕТРА в поле УСТАНОВИТЬ В ЯЧЕЙКЕ задать абсолютную ссылку на ячейку, содержащую расчетную формулу, а в поле ЗНАЧЕНИЕ – то значение, которое следует получить в качестве результата формулы;
- в поле ИЗМЕНЯЯ ЗНАЧЕНИЕ ЯЧЕЙКИ ввести ссылку на ячейку с параметром;
- нажать кнопку ОК или клавишу Enter, на экране появится окно диалога РЕЗУЛЬТАТ ПОДБОРА ПАРАМЕТРА;
- для сохранения найденного значения нажат кнопку ОК. Для восстановления значения, которое было в ячейке с параметром до использования команды ПОДБОР ПАРАМЕТРА нажать кнопку ОТМЕНА.
При подборе параметра Excel использует итерационный процесс. Он проверяет для изменяемой ячейки одно значение за другим, пока не получит нужное решение. Если задача подбора параметра занимает много времени, можно нажать кнопку ПАУЗА в окне диалога РЕЗУЛЬТАТ ПОДБОРА ПАРАМЕТРА и прервать вычисление, а затем нажать кнопку ШАГ, чтобы просмотреть результаты последовательных итераций.
По умолчанию команда ПОДБОР ПАРАМЕТРА прекращает вычисления, когда выполняется 100 итераций или при получении результата, который находится в пределах 0,001 от заданного целевого значения. Если нужна большая точность, можно изменить используемые по умолчанию параметры командой СЕРВИС/ПАРАМЕТРЫ – вкладка ВЫЧИСЛЕНИЯ.
Команда ПОДБОР ПАРАМЕТРА находит только одно решение, даже если задача имеет несколько решений.
Этот инструмент может применяться для решения задач, которые включают много изменяемых ячеек, и помогает найти комбинацию переменных, которые максимизируют или минимизируют значение в целевой ячейке. Он также позволяет задать одно или несколько ограничений условий, которые должны выполняться при поиске решений. Для запуска этого инструмента следует выполнить команду СЕРВИС/ПОИСК РЕШЕНИЯ.
В диалоговом окне ПОИСК РЕШЕНИЯ в поле УСТАНОВИТЬ ЦЕЛЕВУЮ ЯЧЕЙКУ задается цель, которую должен достичь поиск решения. Например, <имя ячейки> Равной Минимальному значению. Целевая ячейка в поле УСТАНОВИТЬ ЦЕЛЕВУЮ ЯЧЕЙКУ может быть задана ссылкой или именем. Поиск решения может находить конкретное значение целевой функции, тогда это значение нужно задать в поле УСТАНОВИТЬ ЦЕЛЕВУЮ ЯЧЕЙКУ, установив переключатель РАВНОЙ в положение ЗНАЧЕНИЮ. В этом случае, задав только изменяемую ячейку без указания ограничений, можно использовать ПОИСК РЕШЕНИЯ вместо команды ПОДБОР ПАРАМЕТРА.
Цель поиска решений может не задаваться. Тогда поле УСТАНОВИТЬ ЦЕЛЕВУЮ ЯЧЕЙКУ следует оставить пустым, нажать кнопку ПАРАМЕТРЫ и установить флажок ПОКАЗЫВАТЬ РЕЗУЛЬТАТЫ ИТЕРАЦИЙ. ПОИСК РЕШЕНИЯ будет перебирать комбинации изменяемых ячеек, которые удовлетворяют заданным ограничениям. Пользователь может выбрать нужное решение, но оно необязательно будет оптимальным.
В поле ИЗМЕНЯЯ ЯЧЕЙКИ следует задать ячейки с переменными. Можно указать ссылки на ячейки или их имена. Если ячейки находятся в несмежных диапазонах. Их следует разделять точкой с запятой. Место ввода ячеек можно нажать кнопку ПРЕДПОЛОЖИТЬ, и ПОИСК РЕШЕНИЯ сам предложит изменяемые ячейки, исходя из заданной целевой функции. Поле ИЗМЕНЯЯ ЯЧЕЙКИ нельзя оставить пустым и указанные в нем ячейки обязательно должны влиять на значение целевой ячейки.
Последний шаг определения поиска решений – задание ограничений. Он не является обязательным. Чтобы задать ограничения, следует в окне ПОИСК РЕШЕНИЯ нажать кнопку ДОБАВИТЬ и заполнить окно диалога ДОБАВЛЕНИЕ ОГРАНИЧЕНИЙ. Ограничение состоит из трех компонентов: ссылки на ячейку, оператора сравнения и значения ограничения. В левой части от оператора сравнения кроме ссылки на ячейку может также задаваться ссылка на диапазон. В правой части может задаваться диапазон (той же размерности, что и в левой части), ссылка на ячейку или константное значение.
После заполнения диалогового окна ПОИСК РЕШЕНИЯ следует нажать кнопку ВЫПОЛНИТЬ. При нахождении оптимального решения на экран выводится диалоговое окно РЕЗУЛЬТАТЫ ПОИСКА РЕШЕНИЯ. Значения, отображаемые в рабочем листе, представляют собой оптимальное решение задачи. Можно либо оставить эти значения на листе, если установить переключатель СОХРАНИТЬ НАЙДЕННОЕ РЕШЕНИЕ и нажать кнопку ОК, либо восстановить исходные значения, если нажать кнопку ОТМЕНА или установить переключатель ВОССТАНОВИТЬ ИСХОДНЫЕ ЗНАЧЕНИЯ и нажать кнопку ОК. Можно также сохранить найденные значения в качестве сценария.
Найденные результаты решения задачи могут быть нецелыми. Их можно округлить или добавить новые ограничения, при которых результаты должны быть целыми числами. В последнем случае в качестве операции сравнения в окне ДОБАВЛЕНИЕ ОГРАНИЧЕНИЙ следует выбрать ЦЕЛ. Использование целочисленных ограничений в задаче поиска решений может увеличить сложность задачи в геометрической прогрессии., что приводит к существенным временным затратам. Некоторые задачи могут быть решены только при использовании целочисленных ограничений. В частности, целочисленные решения являются обязательными для задач, в которых переменные могут принимать только два значения, например 0 и 1. В этом случае в окне диалога ДОБАВЛЕНИЕ ОГРАНИЧЕНИЯ можно использовать оператор сравнения ДВОИЧ.
При сохранении книги после использования поиска решения все значения, введенные в окнах диалога поиска решений, сохраняются вместе с данными рабочего листа. С каждым рабочим листом в книге можно сохранить один набор значений параметров поиска. Однако пользуясь кнопкой СОХРАНИТЬ МОДЕЛЬ в окне диалога ПАРАМЕТРЫ ПОИСКА РЕШЕНИЙ, можно сохранить несколько таких наборов. Для этого необходимо выполнить следующие действия:
1). Выполнить команду СЕРВИС/ПОИСК РЕШЕНИЯ.
2). Нажать кнопку ПАРАМЕТРЫ, а затем в окне диалога ПАРАМЕТРЫ ПОИСКА РЕШЕНИЯ нажать кнопку СОХРАНИТЬ МОДЕЛЬ. Excel попросит указать ячейку или диапазон рабочего листа, в котором нужно сохранить параметры поиска решения.
3). Задать пустую ячейку, щелкнув на ней или введя ссылку, затем нажать кнопку ОК. Если задана одна ячейка, ПОИСК РЕШЕНИЯ вставит сохраняемые параметры поиска в лист, начиная с заданной ячейки. Если задан диапазон, ПОИСК РЕШЕНИЯ заполнит параметрами модели только заданные ячейки. Если диапазон слишком мал, некоторые из параметров не будут сохранены.
4). Чтобы снова использовать сохраненные параметры, следует нажать кнопку ПАРАМЕТРЫ в окне диалога ПОИСК РЕШЕНИЯ, затем нажать кнопку ЗАГРУЗИТЬ МОДЕЛЬ и задать диапазон, в котором сохранена модель поиска решений.
Еще один способ сохранения параметров поиска решений – сохранение их в виде именованных сценариев. Окно диалога РЕЗУЛЬТАТЫ ПОИСКА РЕШЕНИЙ содержит кнопку СОХРАНИТЬ СЦЕНАРИЙ. При нажатии этой кнопки активизируется диспетчер сценариев, который позволяет назначить имя сценария текущим значениям изменяемых ячеек.
Окно диалога ПАРАМЕТРЫ ПОИСКА РЕШЕНИЙ дает возможность задать еще ряд показателей, управляющих процессом поиска:
1). С помощью полей МАКСИМАЛЬНОЕ ВРЕМЯ и ПРЕДЕЛЬНОЕ ЧИСЛО ИТЕРАЦИЙ можно указать ПОИСКУ РЕШЕНИЙ, сколько усилий он должен приложить для решения задачи. Если поиск решений достигает предела по времени или числу итераций до нахождения нужного результата, Excel спрашивает, нужно ли продолжать поиск решения. Используемые по умолчанию величины обычно достаточны для решения большинства задач, но если решения получить не удается, можно попробовать их подобрать.
2). ОТНОСИТЕЛЬНАЯ ПОГРЕШНОСТЬ используется ПОИСКОМ РЕШЕНИЯ для определения точности выполнения ограничений. Чем ближе это значение к 1, тем ниже точность. Задание относительной погрешности, меньше установленной по умолчанию (0,000 001) приводит к росту времени поиска решения.
3). ДОПУСТИМОЕ ОТКЛОНЕНИЕ служит для задания допуск на отклонение от оптимального решения и применяется только в задачах с целочисленными ограничениями.
4). Переключатели ОЦЕНКИ, РАЗНОСТИ и МЕТОД ПОИСКА позволяют уточнить применяемые методы оптимизации, обычно используются значения. Установленные по умолчанию.
5).Флажок ЛИНЕЙНАЯ МОДЕЛЬ может быть активизирован только для моделей, в которых отсутствуют нелинейные зависимости, т.е. график зависимости целевой функции от каждого ограничения может быть представлен прямой линией. Если этот флажок установлен, решение линейной задачи будет найдено быстрее.
6). Флажок ПОКАЗЫВАТЬ РЕЗУЛЬТАТЫ ИТЕРАЦИЙ позволяет исследовать различные комбинации значений изменяемых ячеек, а не только комбинацию, дающую оптимальный результат. После каждой итерации будет открываться окно диалога ТЕКУЩЕЕ СОСТОЯНИЕ ПОИСКА РЕШЕНИЙ, которое позволяет сохранить сценарий, прекратить поиск или продолжить его со следующей итерации. Следует иметь ввиду, что промежуточные результаты могут не удовлетворять всем заданным ограничениям.
ПОИСК РЕШЕНИЙ может представлять свои результаты в виде трех отчетов: РЕЗУЛЬТАТЫ, УСТОЙЧИВОСТЬ и ПРЕДЕЛЫ. Для генерации одного или нескольких отчетов следует выбрать их названия в окне диалога РЕЗУЛЬТАТЫ ПОИСКА РЕШЕНИЙ. Каждый отчет сохраняется на отдельном листе текущей книги, а имена отчетов отображаются на ярлычках.
Отчет по устойчивости содержит информацию о том, насколько целевая ячейка чувствительна к изменениям ограничений и переменных. В отчете показывается, как целевая функция реагирует на увеличение значений изменяемых ячеек на единицу и на увеличение значения ограничения на единицу
Отчет по результатам содержит целевую ячейку, список изменяемых ячеек и ограничений. В этом отчете для каждого ограничения выводятся «статус» и «разница». Разница – это разность между значением, выводимым в ячейке ограничения при получении решения, и числом, заданным в правой части формулы ограничения. Статус может принимать три состояния: «Связанное» (ограничение, для которого значение разницы равно 0), «Не связанное» (ограничение, которое было выполнено с ненулевым значением разницы) и «Не выполненное».
Отчет по пределам сообщает о том, в каких пределах значения изменяемых ячеек могут быть увеличены или уменьшены без нарушения ограничений задачи. Для каждой изменяемой ячейки этот отчет содержит оптимальное значение, а также наименьшее и наибольшее значения, которые ячейка может принимать без нарушения ограничений.
ПОИСК РЕШЕНИЙ может решить не каждую предложенную задачу. Если оптимальное решение не найдено, в окне диалога РЕЗУЛЬТАТЫ ПОИСКА РЕШЕНИЙ выводится сообщение о неуспешном завершении. Наиболее типичными сообщениями о неуспешном завершении являются следующие:
1). ПОИСК РЕШЕНИЯ НЕ МОЖЕТ НАЙТИ ПОДХОДЯЩЕГО РЕШЕНИЯ. Это означает, что не найдено решение, удовлетворяющее всем ограничениям. Это может произойти из-за того, что ограничения логически противоречивы или не все ограничения могут быть выполнены. В некоторых случаях ПОИСК РЕШЕНИЙ выводит это сообщение, если начальные значения изменяемых ячеек находятся слишком далеко от их оптимальных значений. Если пользователь считает, что заданные ограничения логически правильны и задача имеет решение, можно попробовать изменить начальные значения и повторить поиск решения.
2). ПОИСК ОСТАНОВЛЕН (ДОСТИГНУТО МАКСИМАЛЬНОЕ ЧИСЛО ИТЕРАЦИЙ). Можно возобновить поиск, либо прекратить его. Можно также сохранить текущее значение в виде именного сценария. Установленное по умолчанию значение максимального числа итераций можно изменить в окне ПАРАМЕТРЫ ПОИСКА РЕШЕНИЙ.
3). ПОИСК ОСТАНОВЛЕН (ИСТЕКЛО ЗАДАННОЕ НА ПОИСК ВРЕМЯ). Это сообщение аналогично предыдущему. Используемое по умолчанию значение также можно изменить в окне диалога ПАРАМЕТРЫ ПОИСКА РЕШЕНИЙ.
