Синтаксис функции «бз»

Лабораторная работа №10

Сервис Excel: надстройка «Подбор параметра»

Краткие теоретические сведения

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

С помощью этой команды для отыскания нужного решения могут быть произведены необходимые итерационные (повторяющиеся) вычисления, в ходе которых будет меняться значение некоторой изменяемой ячейки, которая содержит значение параметра. Команда начинает работу с вычисления значения целей функции, аргументом которой является начальное значение этой ячейки. Если начальное значение не дает требуемого значения целевой функции, производится изменение значения ячейки с параметром и вычисляется новый результат целевой функции. Так происходит до тех пор, пока не будет найдено нужное значение параметра или не будет выполнено заданной количество повторений изменения параметра, т.е. заданное количество итераций. Говоря математическим языком, мы ищем решение уравнения с одним неизвестным.

Пример 1. Нужно открыть счет в коммерческом банке под 8% годовых и накопить на этом счету за пять лет сумму в 300 тыс. руб., необходимую для обновления компьютерного парка малого предприятия. На это счет будет производиться ежегодное отчисление. Нужно узнать необходимые размеры этого ежегодного отчисления.

Порядок работы

Этап №1. Подготовительный.

è Ввести необходимые данные на рабочий лист. В ячейку В4 ввести начальное значение параметра ежегодной выплаты, например одну пятую часть нужной суммы, т.е.
60 тыс. руб.

Замечание: В финансовых функциях по расчету денежных потоков важно направление потока: «к нам» – со знаком плюс, «от нас» – со знаком минус. Поэтому величина вводится со знаком минус.

è Для расчета Суммы на счете используем функцию БЗ из категории финансовые.

Синтаксис функции «БЗ».

БЗ(<процентная ставка>;<количество периодов>;<выплата>;[<начальное значение>];[<тип>]) – возвращает будущее значение вклада на основе периодических постоянных платежей и постоянной процентной ставки.

<тип> – определяет время начисления процентов: в конце/начале (0/1) периода. Если тип пропущен, он считается равным 0.

è Ввести в ячейку В5функцию БЗ. В открывшееся диалоговое окно мастера функций ввести исходные данные (см.рис.54)

Теперь рабочий лист подготовлен для применения команды: ØСервис ØПодбор параметра… (внешний вид – рис.55).

Этап №2. Применение команды ØСервис ØПодбор параметра...

è Из меню Сервис выбрать команду Подбор параметра…. Откроется окно, представленное на рис.56.

èВ окне Подбор параметра задать исходные данные согласно рис. 56, затем нажать Ok. Внешний вид рабочего листа после итерационных вычислений представлен на рис 57.

Рис.57

Пример 2. Отыскать корень алгебраического уравнения: (2x²+3)*(1- sin(x)) = ln(x) +2.

Замечание: Для того чтобы найти корень уравнения, нужно подобрать такое значение х,прикотором значение выражения (2x²+3)*(1 – sin(x)) – ln(x) – 2равно нулю. При решении таких задач важно, чтобы корень действительно существовал.

Этап №1. Подготовительный.

è Ячейке А1 присвоить имя х, используя команду ØВставка ØИмя ØПрисвоить…

В открывшемся окне «Присвоение имени» в поле ввода ввести хи нажать ОК.

èВ ячейку В1 ввести формулу преобразованного уравнения, которое получено путем переноса всех его элементов в одну часть:

= (2*x^2+3)*(1 – sin(x)) – ln(x) – 2

è В ячейку А1 ввести любое число (оно буден являться начальным значением изменяемого параметра), учитывая область определения функции f(x)= (2x²+3)*(1- sin(x)) – ln(x) +2, например 1.

èВ ячейке В1 автоматически будет вычислено значение функции f(x) при х=1.

Этап №2. Применение команды «Подбор параметра...»

è Выполнить команду Ø Сервис Ø Подбор параметра…В окне Подбор параметра задать исходные данные (см. рис.59). В ячейке А1 в результате итерационных вычислений будет найден корень уравнения.

Пример 3. Имеется многоступенчатое пищевое производство. Относительные нормативные потери на каждом этапе обработки известны из опытных данных и заданы директивно.

• объем продукта на выходе должен быть 800 кг;
• количество этапов обработки – 3;
• коэффициенты нормативных потерь на каждом этапе от поступившего на данный вид обработки объема продукта: на I этапе коэффициент потерь – 0,05; на II этапе – 0,11; на III этапе – 0,02.

Вычислить:

• объем потерь на каждом этапе обработки;
• необходимое количество исходного продукта.

Порядок работы

Рис.60. Исходный вид рабочего листа.

èПодготовить внешний вид рабочего листа с исходными данными согласно рис.60:

è В ячейку Е3 ввести произвольное число исходного объема сырья (это исходное значение изменяемого параметра), чуть большее заданного объема продукта на выходе III этапа, например 900.

è В ячейку С6 ввести формулу объема потерь продукта от исходного объема сырья – ячейки Е3 в соответствии с коэффициентом потерь на I этапе – 0,05: =Е3*В6

è В ячейку D6 ввести формулу для расчета объема продукта на выходе I этапа от исходного объема сырья (ячейки Е3): =Е3–C6

è В ячейку С7 ввести формулу объема потерь продукта от объема продукта на выходе после I этапа обработки (ячейки D6) в соответствии с коэффициентом потерь на II этапе – 0,11: =D6*В7

è Рассуждая подобным образом, в ячейку С8 ввести формулу: =D7*В8

è В ячейку D7 ввести формулу объема продукта на выходе II этапа от объема продукта на выходе I этапа – ячейки D6: =D6–C7

è В ячейку D8 ввести формулу: =D7–C8

Таким образом, таблица подготовлена для применения команды Ø Сервис Ø Подбор параметра…

èВыполнить команду Ø Сервис Ø Подбор параметра… В окне Подбор параметра задать исходные данные согласно рис.61.

Результаты итерационных вычислений представлены на рис.62.