Окно диалога Подбор параметра

В поле Значение нужно ввести значение, которое должно быть достигнуто в ячейке, указанной в поле Установить в ячейке.

В поле Изменяя значение ячейки необходимо ввести ссылку на ячейку, значение которой нужно подобрать таким образом, чтобы в ячейке из поля Установить в ячейке было получено значение, указанное в поле Значение. То есть в этом поле нужно указать ссылку на ячейку, значение которой является неизвестным.

Пример 1.

Вы хотите положить деньги в банк под 10% и получить ровно 100000 руб. по истечении года. Необходимо определить сумму вклада.

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

Если в банк положить х руб. под 10% годовых, то в конце года банк выплатит (1+0,1) * х = 1,1* х. Поскольку в конце года по условию задачи нужно получить сумму 100000 руб., то для решения задачи требуется решить уравнение 1,1 * х = 100000.

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

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

1. Сделать активной ячейку ВЗ.

2. Вызвать средство Подбор параметра.

3. Проверить, что в поле Установить в ячейке диалогового окна Подбор параметра указана ссылка на ячейку ВЗ.

4. В поле Значение ввести значение 100000.

5. В поле Изменяя значение ячейки указать ссылку на ячейку В2.

6. Нажать кнопку ОК.

Средство Подбор параметра найдет решение, равное 90909,09, и поместит это значение в ячейку В2.

Пример 2.

Решить уравнение 5 x ³–6 x ²–3 x +2 = 0.

Кубическое уравнение имеет не более трех корней. Решение задачи включает два этапа.

1. Локализация корней. Этот этап представляет собой нахождение отрезков, на которых находится один корень.

Сделать это можно следующим образом. Сначала нужно протабулировать функцию на некотором отрезке [ x ₀; х_п ]. Возможно, что выбранный отрезок не будет включать корни уравнения. Поэтому нужно построить график этой функции. При необходимости в соответствии с характером изменения функции поменять интервал исследования функции, а также шаг изменения аргумента. После этого найти либо на графике функции, либо в таблице табулирования функции отрезки, на которых функция меняет знак. За первое приближение корня можно взять середину найденного отрезка.

На рисунке видно, что найдено три отрезка [-0.8, -0.4], [0.4; 0.8] и [1.2; 1.6]. В ячейки D6, D7 и D8 помещены начальные приближения корней на соответствующих отрезках.

2. Уточнение корней. Этот этап позволит найти точное значение корней в найденных на первом этапе отрезках.

Нужно вычислить значение функции в значениях, которые приняты за первое приближение корня. На рисунке в ячейках E6, E7 и E8 размещаются формулы, вычисляющие значения функции в начальных приближениях корней.

Далее для каждого отрезка нужно применить средство Подбор параметра для уточнения корня на этом отрезке. Выделить ячейку E6, воспользоваться командой Сервис|Подбор параметра. В поле Установить в ячейке должна быть указана ссылка на ячейку E6. В поле Значение ввести значение 0. В поле Изменяя значение ячейки указать ссылку на ячейку D6. Excel найдет решение и поместит его в ячейку D6. То же самое нужно проделать для второго и третьего приближений чтобы найти остальные корни.