3 Способ. Подбор параметра

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

Математическая суть задачи состоит в решении уравнения X = а, где функция х описывается заданной формулой, х — искомый параметр, а — требуемый результат формулы.

Для решения этой задачи необходимо выполнить следующие действия:

1. Выделить ячейку, содержащую формулу, для которой нужно найти определенное решение.

2. В меню Сервис выбрать команду Подбор параметра.

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

4. В поле Значение ввести значение, которое нужно получить по заданной формуле.

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

6. Щелкнуть по кнопке ОК.

Пример3.

Дано уравнение

X^2 + ЗХ - 2 = А,

где А — требуемый результат формулы; Х — искомый параметр

Определить такое значение параметра X, при котором А будет равно 20.

1. Занести в ячейку A1 любое значение, например, 1.

2. Ввести в ячейку А2 указанную формулу, которая примет следующий вид: =A1^2+3*A1-2. В формуле указана ссылка на ячейку А1, в которой условно находится параметр X.

3 Задать команду Сервис > Подбор параметра (рис. 22).

4. В поле Установить в ячейке указать А2 (по умолчанию в это поле вводится адрес текущей ячейки).

5. В поле Значение ввести — 20.

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

Рисунок 22. Окно Подбор параметра

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

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

При подборе параметра одна из ячеек обязательно должна содержать формулу.

Надстройка Поиск решения из меню Сервис используется для подбора одновременно нескольких параметров с целью максимизации или минимизации содержимого целевой ячейки и подробно рассматривается в лабораторной работе № 6

Контрольные вопросы

1. Для чего предназначены функции: ПЛТ; БС; ПС; КПЕР и СТАВКА? Поясните синтаксис перечисленных функций.

2. Для чего предназначены функции: AMP, АМГД, ДОБ и ДДОБ? Поясните синтаксис перечисленных функций.

3. Назначение и способы анализа «Что если»?

4. Для чего предназначена «Таблица подстановок», опишите технологию ее применение для функций с одной переменной и для функций с двумя переменными?

5. Что такое сценарий, как его создать, просмотреть, изменить, получить итоговый отчет на отдельном листе?

6. Сущность операции Подбор параметра, как она выполняется?

10