2.Надстройка «Подбор параметра»

2.1. Введение

На практике часто возникают ситуации, когда желаемый результат одиночной формулы известен, но не известны значения, которые требуется ввести для получения этого результата. В этом случае очень удобно воспользоваться надстройкой Подбор параметра электронной таблицы Microsoft Excel. Воспользоваться средством «Подбор параметра» можно, выбрав команду Подбор параметра в меню Сервис. Говоря математическим языком, мы допускаем, что наша формула (целевая функция) меняется с изменением значения только одного параметра. Подходящее значение этого параметра отыскивается вычислительными методами, производящими итерационные вычисления для нахождения нужного решения (если, конечно, оно существует). Тем самым ищется решение уравнения с одним неизвестным. Подбор параметра является частью блока задач, который иногда называют инструментами анализа "что-если".

Во время подбора параметра в переменную ячейку непрерывно заносятся новые значения, пока не будет найдено решение поставленной задачи. Такой процесс называется итерацией, и продолжается он до тех пор, пока Excel не выполнит 100 попыток или не найдет решение, лежащее в пределах точности 0.001 от точного значения.

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

Давайте теперь рассмотрим примеры практического применения надстройкиПодбор параметра.

2.2. Пример использования надстройки «Подбор параметра»

Возьмем задачу из школьного учебника: Определение времени встречи двух объектов.

Из одного пункта по одной дороге вышли два путника. Первый идет со скоростью 5 км/ч, второй — 10 км/ч. Когда они встретятся, если первый вышел на два часа раньше?

Задачу можно сформулировать и так: при каком значении переменного параметра «время» разница в расстоянии между пешеходами будет равна нулю?

Откроем программу Excel. Введем все исходные данные в рабочий лист (рис. 1). Путь, пройденный первым путником, вычисляется по формуле =B3×B1, а вторым — по формуле =(B3-B6)×B2. Разница путей, в свою очередь, будет определена по формуле =B4-B5.

Рис. 2.1. Введенные данные задачи и вызванное окно надстройки «Подбор параметра»

Выбрав команду «Подбор параметра» в меню Сервис, видим диалоговое окно «Подбор параметра» (рис. 1). В данном окне укажем, в какой ячейке нужно установить заданный результат (в ячейке B7), чему равняется это значение (0) и за счет изменения какого параметра оно достигается ($B$3). Щелкнув мышью на кнопке ОК, получаем результат (рис. 2.2).

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

Рис. 2.2. Результат подбора параметра

2.3. Подбор процентной ставки

Рассмотрим еще одну задачу подбора параметра:

Пусть известна сумма займа 100000 рублей. Необходимо подобрать такую процентную ставку, чтобы ежемесячный платеж стал равен 900 руб. Срок займа – 180 месяцев.

Для решения задачи используем финансовую функцию ПЛТ и метод подбора параметра.

Если вы откроете встроенную помощь по программе MS Excel и наберете в строке поиска ПЛТ, то найдете описание этой функции:

"Функция ПЛТ возвращает сумму периодического платежа для аннуитета на основе постоянства сумм платежей и постоянства процентной ставки.

Синтаксис

ПЛТ(ставка ;кпер; пс; бс ;тип)

Ставка — процентная ставка по ссуде.

Кпер — общее число выплат по ссуде.

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

Бс — требуемое значение будущей стоимости, или остатка средств после последней выплаты. Если аргумент Бс опущен, то он полагается равным нулю, то есть для займа, например, значение Бс равно 0.

Тип — число 0 или 1, обозначающее, когда должна производиться выплата (в начале или конце периода).

Заметки

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

Убедитесь, что вы последовательны в выборе единиц измерения для задания аргументов Ставка и КПер. Если вы делаете ежемесячные выплаты по четырехгодичному займу из расчета 12 процентов годовых, то используйте 12%/12 для задания аргумента Ставка и 4*12 для задания аргумента КПер. Если вы делаете ежегодные платежи по тому же займу, то используйте 12 процентов для задания аргумента «ставка» и 4 для задания аргумента КПер."

Чтобы этот пример проще было понять, скопируйте его на пустой лист, откроем программу Excel и введем необходимые данные на рабочий лист, взяв в качестве начального приближения к ответу 5 %. В ячейку B4 впишем формулу =-ПЛТ(B3/12;B2;B1)

Обратите внимание на поставленный перед функцией знак «минус». Дело в том, что сумма платежа есть сумма займа, и она отрицательна. Чтобы получить положительное число, мы умножили эту сумму на -1 (Рис. 2.3).

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

Рис. 2.3. Введенные данные

Теперь можно воспользоваться командой Подбор параметра для проведения итерационных вычислений и решения поставленной задачи. С этой целью нужно указать, где находится формула, какое значение нас интересует, и где находится изменяемый параметр, являющийся одним из аргументов формулы: в открывшемся окне «Подбор параметра» в поле «Установить в ячейке» укажем $B$3, в поле «Значение» введем 900, а в поле «Изменяя ячейку» обозначим $B$4.

Задавать значения полей ввода Установить в ячейке и Изменяя ячейку удобнее всего следующим образом: активизировать соответствующее поле ввода щелчком мыши, а затем щелкнуть по соответствующей ячейке рабочего листа.

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

Рис. 2.4. Найденное решение и окно-отчет «Результат подбора параметра»

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