3 Использование функции «подбор параметра» ms excel

Один из частных случаев возможности применения MS EXCEL– это решение задачи подбора параметра. Пусть имеется некоторая функция одного аргумента, которую обозначим f(x). Предположим, что значение аргумента x мы можем назначать по своему усмотрению. Задача состоит в том, чтобы установить такое значение аргумента x, при котором функция f(x) примет заданное значение c. Приходим к известной математической задаче решения функционального уравнения f(x) = с.

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

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

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

3 Ввести искомый результат в поле Значение.

4 В поле Изменяя значение ячейки ввести ссылку на ячейку, содержащую подбираемое значение x и щелкнуть на кнопке ОК.

Использование подбора параметров для помещения в формулу

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

Таблица 12 – Входные параметры

	А	В	С
1	Цена квартир	150000
2	Количество
3	Итого продаж	=В1*В3

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

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

2. Появится диалоговое окно, в котором нам надо задать данные (рисунок 2)

Р исунок 2 - Исходные данные

Среди финансовых функций Excel есть функция ППЛАТ (ставка;кпер;нз;бз;тип), которая вычисляет величину постоянных выплат по ссуде на основе постоянной процентной ставки. Аргументами этой функции являются:

- ставка – процентная ставка по ссуде;

- кпер – общее число выплат по ссуде;

- нз – значение взятой ссуды;

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

- тип – равное 0 число или опущено, если платить нужно в конце периода, или равное 1 число, если платить нужно в начале периода.

Например, если Вы взяли ссуду 10000 рублей на условиях ежемесячной в течение года выплаты из расчета процентной ставки 10%, то размер ежемесячной выплаты можно рассчитать с помощью формулы: = ППЛАТ(10%/12;12;-10000). Результат вычислений по этой формуле показан на рисунке 3.

Рисунок 3 - Результаты вычислений

Практические и лабораторные задания

Задание 1

Предприниматель хочет взять ссуду 10000 рублей на 1 год с ежемесячной выплатой и готов по прошествии каждого месяца платить в течение года по 900 рублей. Какой должна быть процентная ставка? Решить задачу подбором параметра.

1 Создайте на пользовательском диске d свою рабочую папку.

2 Запустите Excel.

3 Сохраните файл под своим именем в своей рабочей папке.

4 На листе Лист1 создайте таблицу. В этой таблице ячейка В2 будет играть роль регулируемой ячейки. В этой ячейке Вы получите результат поиска искомого значения процентной ставки. В начале же в эту ячейку запишите начальное значение процентной ставки, например 10%, с которого начнется поиск.

5 С помощью команды меню Формат, Ячейки установите в ячейке B2 процентный формат с одним знаком дробной части.

6 В ячейке В1 запишите формулу = ППЛАТ(B2/12;12;-10000).

7 Выделите ячейку В1 таблицы с формулой.

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

Рисунок 4 - Окно подбора параметра

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

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

Рисунок 5 - Окончание подбора параметра

11 Щелкните на кнопке ОК. Решатель начнет итерационный процесс «Поиска решения». Появиться диалоговое окно Результат подбора параметра.

12 В этом окне текущее значение – это величина ежемесячной выплаты при найденном значении процентной ставки.

13 Щелкните на кнопке ОК. Искомое значение процентной ставки содержится в регулируемой ячейке В2 и равно 14,5% (рисунок 6).

Рисунок 6 - Результаты вычислений

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

Задание 2

Предприятию выдан кредит в размере 20000 руб., процентная ставка 10 % годовых, общее число периодов выплат 8, начисление процентов ежеквартальное, диапазон возможного изменения ставок ссудного процента составляет 8;8,5;9;9,5;10. Используя функцию Подбор параметра, составить график платежей при различных значениях ставки ссудного процента.

Задание 3

Используйте функцию Подбор параметра для решения уравнений:

1 Построить параболу Y=0,1*х²-х-11: задать область определения (х) от –20 до +20. Для этого занести в соседние ячейки (например А5 и А6) –20 и –19, выделить обе ячейки, поставить курсор на черный квадратик в правом нижнем углу, нажать левую клавишу мыши и потащить вниз до появления числа 20; в ячейку рядом с –20 вставить формулу =0,1*А5^2-А5-11, скопировать ее вниз и построить график. Сделать активной ячейку Y вблизи одного из корней, вызвать Подбор параметра (в Меню Сервис), заставить компьютер подобрать Х, чтобы Y обратился в 0: установить в окне Значение 0, в нижнем окне - адрес Х, щелкнув по соответствующей ячейке мышью. Щелкните по клавише ОК. Не забудьте найти второй корень, выбрав исходные Y и X вблизи него.

2 Найти корни, двигая мышью точку графика в 0. Компьютер сам вызовет Подбор параметра.

3 Найти корни уравнения третьего порядка: протабулируйте функцию

Y=х³-4х²-5х+6=0, постройте график, определите, сколько корней и где они примерно находятся, найдите корни через Подбор параметра.