Поиск единственного решения

На рис. 1.1 приведен пример простого прогноза объема продаж, расходов и чистой прибыли. Изменяемыми исходными данными являются коэффициенты варьирования объема продаж в строке 16 и относительные затраты в ячейках В18: В20. Коэффициенты варьирования используются для прогноза объема продаж, а величины относительных затрат — для оценки затрат.

Предположим, что необходимо узнать коэффициент варьирования для 1998 года (ячейка D16), который обеспечит в 2000 году чистую прибыль в $3000 (ячейка F12). Воспользуемся для этого средством Подбор параметра.

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

Чтобы воспользоваться средством Подбор параметра:

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

2. Выберите команду Сервис, Подбор параметра. Появится диалоговое окно Подбор параметра (рис. 1.1). В поле Установить в ячейке уже будет находиться ссылка на выделенную на шаге 1 ячейку.

3. В поле Значение введите величину, которую необходимо получить. В данном примере — это 3000.

4. В поле Изменяя значение ячейки введите ссылку на исходную ячейку. Эта ячейка должна влиять на выбранную на шаге 1 формулу. В данном примере исходной ячейкой является D16. Она определяет результаты формул в ячейках строки 5 и тем самым неявно влияет на формулу в ячейке F12.

5. Нажмите кнопку ОК.

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

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

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

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