Подбор параметра для получения требуемого результата

Решение нелинейных уравнений

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

В общем случае решение нелинейного уравнения проводится численно в два этапа (здесь речь идёт лишь о вещественных корнях уравнения). На первом этапе производится поиск интервалов, в которых содержится только по одному корню. Второй этап решения связан с уточнением корня в выбранном интервале (определением значения корня с заданной точностью). Известно, что корень уравнения (уравнение записано в виде f(x)=0) – это такое значение аргумента, при котором значение функции равно нулю. В графическом представлении – это может быть точка пересечения или касания графика функции с осью абсцисс.

При решении уравнения не надейтесь никогда найти точное значение корня и добиться обращения функции в ноль при использовании компьютера, где сами числа представлены ограниченным числом знаков. Здесь критерием может служить приемлемая абсолютная или относительная погрешность корня. Если, например, относительная погрешность равна 0,000001 (ɛ =0,000001), то искомый результат будет иметь 6 верных (значащих) цифр после запятой (n=lg(1/ ɛ)).

В настоящей работе решение уравнений сводится к выполнению второго этапа, то есть к уточнению корня на заданном отрезке. Решение нелинейных уравнений в таблицах MS Excel выполнить следующими способами:

1) Подбором параметра;

2) C помощью Поиска решения.

Подбор параметра для получения требуемого результата

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

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

Пошаговый анализ примера

Рассмотрим предыдущий пример шаг за шагом

Поскольку для достижения цели необходимо рассчитать ссудный процент, потребуется функция ПЛТ. Эта функция рассчитывает сумму ежемесячного платежа. В данном примере эту сумму и требуется определить.

Подготовка листа

1. Откройте новый пустой лист.

2. Сначала добавьте в первый столбец несколько названий, что упростит прочтение данных на листе.

- В ячейку A1 введите текст Сумма ссуды.

- В ячейку A2 введите текст Срок в месяцах.

- В ячейку A3 введите текст Процентная ставка.

- В ячейку A4 введите текст Платеж.

- 3. Затем добавьте известные значения.

- В ячейку B1 введите значение 100000. Это требуемая сумма ссуды.

- В ячейку B2 введите значение 180. Это число месяцев, за которое требуется выплатить ссуду.

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

4. Затем введите формулу, для которой требуется подобрать параметры. Например, воспользуйтесь функцией ПЛТ:

В ячейку B4 введите функцию ПЛТ(B3/12;B2;B1). Эта формула вычисляет сумму платежа. В данном примере каждый месяц требуется выплачивать 900 р. Это значение здесь не вводится, поскольку необходимо определить процентную ставку с помощью средства подбора параметров, а использование этого средства начинается с формулы.

Формула ссылается на ячейки B1 и B2, в которых находятся значения, указанные на предыдущих этапах. Формула также ссылается на ячейку B3, в которую средство подбора параметров поместит процентную ставку. Формула делит значение из ячейки B3 на 12, поскольку был указан ежемесячный платеж, а функция ПЛТ предусматривает использование годовой процентной ставки.

Поскольку в ячейке B3 отсутствует значение, Microsoft Excel полагает процентную ставку равной 0% и с использованием значений из примера возвращает значение платежа 555,56 р. В данный момент это значение можно проигнорировать.

5. Наконец, отформатируйте целевую ячейку (B3) так, чтобы результат в ней отображался в процентном формате.

- На вкладке Главная в группе Число нажмите кнопку Процентный формат.

- Чтобы задать количество дробных разрядов, нажмите кнопку Увеличить разрядность или Уменьшить разрядность.