Технологии анализа данных. Режим подбора параметра.

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

Процедура подбора параметра применяется, если известен желаемый результат, но неизвестны значения, которые требуется ввести для получения этого результата. Методом перебора воспользоваться можно, но это очень утомительно. При подборе параметра программа автоматически изменяет значение в ячейке до тех пор, пока формула, зависимая от этой ячейки, не возвращает нужный результат. Примером подбора параметров может служить такая задача. Какова должна быть прибыль, чтобы налог получился равным заданной величине. Чтобы выполнить задачу подбора параметров, нужно выбрать команду “Сервис”->“Подбор параметра”. В диалоговом окне в поле “Установить в ячейке” указывается адрес ячейки, в которой находится формула, параметры которой нужно подбирать. Можно ввести адрес с помощью клавиатуры, а можно свернуть диалог и выбрать ячейку с помощью мыши. Поле “Значение” предназначено для указания числового результата, который нужно получить, подбирая параметры в формуле. Поле “Изменяя значение ячейки” задает адрес ячейки, изменяя значение которой, можно получить нужный результат. Это поле также содержит кнопку для свертывания диалога, и можно указать ячейку не только с помощью клавиатуры, но и выбрав ее мышью. После завершения ввода параметров следует нажать кнопку “ОК”, чтобы закрыть диалог и начать подбор. Подбор сопровождается появлением соответствующего диалога, в котором отображается текущее значение параметра, а также количество шагов подбора, которое уже сделано. В Excel реализован только подбор по одному параметру, и если формула содержит несколько параметров, то нужно производить подбор по каждому из них.

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

Процедура поиска решения позволяет найти оптимальное значение формулы, содержащейся в ячейке, которая называется целевой. При этом чтобы получить нужный результат, происходят изменения в группе ячеек, прямо или косвенно связанных с формулой в целевой ячейке. Такие ячейки называются влияющими ячейками. Процедура одновременного изменения значений в нескольких влияющих ячейках достаточно трудоемкая, и, чтобы сузить множество значений, используемых при поиске решения, накладываются ограничения. Эти ограничения могут ссылаться на другие влияющие ячейки. Чтобы использовать процедуру поиска решения, программа должна быть соответствующим образом настроена. Для настройки нужно выбрать команду “Сервис”->“Надстройки”. В диалоговом окне следует установить флажок у элемента “Поиск решения”. Чтобы начать поиск решения, необходимо выбрать команду “Сервис”->“Поиск решения”. Будет открыт диалог настройки параметров поиска (Рис. 10.2).

Поле ввода “Установить целевую ячейку” используется для указания целевой ячейки, решение в которой нужно найти. Переключатель “Равной” определяет цель поиска: максимальное, минимальное или конкретное значение. Поле ввода “Изменяя ячейки” служит для указания ячеек, значения которых изменяются в процессе поиска решения до тех пор, пока не будут выполнены наложенные ограничения и условие оптимизации значения ячейки, указанной в поле “Установить целевую ячейку”. Можно ввести ячейки с помощью клавиатуры или выбрать ячейки с помощью мыши. Также можно нажать кнопку “Предположить”, и программа сама попробует найти ячейки, влияющие на формулу. Список “Ограничения” служит для отображения граничных условий поставленной задачи.

Рис. 8.6. Поиск решения. Рис8.7. Параметры поиска решения.

Если нужно уточнить параметры поиска решения, следует нажать кнопку “Параметры”. Будет открыт диалог дополнительной настройки (рис. 10.3). Поле ввода “Максимальное время” используется для ограничения времени (в секундах), отпускаемого на поиск решения задачи. Если при поиске решения это время превышено, поиск будет остановлен. Поле ввода “Предельное число итераций” служит для ограничения числа промежуточных вычислений. Поле ввода “Относительная погрешность” определяет точность, с которой определяется соответствие ячейки целевому значению (от нуля до единицы). Поле ввода “Допустимое отклонение” используется для задания допуска на отклонение от оптимального решения (в процентах). При указании большего допуска поиск решения заканчивается быстрее. Поле ввода “Сходимость” используется для определения условия окончания поиска. Если относительное изменение значения в целевой ячейке за последние пять итераций становится меньше числа, указанного в этом поле, поиск прекращается. Если установлен флажок “Линейная модель”, для поиска используется линейная модель, что в некоторых случаях может увеличить скорость поиска. Если установлен флажок “Показывать результаты итераций”, поиск идет в пошаговом режиме. Флажок “Автоматическое масштабирование” служит для включения автоматической нормализации параметров поиска решения, например, если цель решения - определение прибыли в процентах, а параметры исчисляются в рублях, нужна нормализация. Установив флажок “Значения не отрицательны”, можно ограничить значения влияющих ячеек положительными величинами. Группа переключателей в нижней части диалога служит для настройки таких параметров оптимизации, как экстраполяция значений, а также метода поиска.

Чтобы начать поиск, следует нажать кнопку “Выполнить”. Диалог настройки параметров будет закрыт, и начнется процесс поиска решения. При этом в строке состояния программы Excel будет отображаться вспомогательная информация о ходе поиска: текущий шаг, значения целевой функции и влияющих ячеек. Чтобы досрочно прервать поиск решения, можно нажать клавишу Esc. По окончании поиска появляется диалог с результатами. Установив переключатель в этом диалоге в положение “Сохранить найденное решение”, можно сохранить решение во влияющих ячейках модели. Если переключатель установлен в положение “Восстановить исходные значения”, во влияющих ячейках останутся исходные значения параметров. Программа может показать ход поиска решения. Для этого используются отчеты, формируемые на отдельных листах рабочей книги. В списке “Тип отчета” можно выбрать, какой отчет нужно сформировать.