Если у вас возникли сложности с курсовой, контрольной, дипломной, рефератом, отчетом по практике, научно-исследовательской и любой другой работой - мы готовы помочь.
Предоплата всего
Подписываем
В научной и финансовой деятельности достаточно часто приходится сталкиваться с задачами, в которых результат (значение целевой функции) может быть изменен только с помощью изменения значения одного параметра (при этом остальные мы не хотим или не можем изменять). В этих ситуациях для того, чтобы отыскать подходящее значение этого параметра можно, применяя команду: Сервис Подбор параметра.
С помощью этой команды для отыскания нужного решения могут быть произведены необходимые итерационные (повторяющиеся) вычисления, в ходе которых будет меняться значение некоторой изменяемой ячейки, которая содержит значение параметра. Команда начинает работу с вычисления значения целей функции, аргументом которой является начальное значение этой ячейки. Если начальное значение не дает требуемого значения целевой функции, производится изменение значения ячейки с параметром и вычисляется новый результат целевой функции. Так происходит до тех пор, пока не будет найдено нужное значение параметра или не будет выполнено заданной количество повторений изменения параметра, т.е. заданное количество итераций. Говоря математическим языком, мы ищем решение уравнения с одним неизвестным.
Пример 1. Нужно открыть счет в коммерческом банке под 8% годовых и накопить на этом счету за пять лет сумму в 300 тыс. руб., необходимую для обновления компьютерного парка малого предприятия. На это счет будет производиться ежегодное отчисление. Нужно узнать необходимые размеры этого ежегодного отчисления.
Этап №1. Подготовительный.
Ввести необходимые данные на рабочий лист. В ячейку В4 ввести начальное значение параметра ежегодной выплаты, например одну пятую часть нужной суммы, т.е.
60 тыс. руб.
Замечание: В финансовых функциях по расчету денежных потоков важно направление потока: «к нам» – со знаком плюс, «от нас» – со знаком минус. Поэтому величина вводится со знаком минус.
Для расчета Суммы на счете используем функцию БЗ из категории финансовые.
Синтаксис функции «БЗ».
БЗ(<процентная ставка>;<количество периодов>;<выплата>;[<начальное значение>];[<тип>]) – возвращает будущее значение вклада на основе периодических постоянных платежей и постоянной процентной ставки.
<тип> – определяет время начисления процентов: в конце/начале (0/1) периода. Если тип пропущен, он считается равным 0.
Ввести в ячейку В5 функцию БЗ. В открывшееся диалоговое окно мастера функций ввести исходные данные (см.рис.54)
Теперь рабочий лист подготовлен для применения команды: Сервис Подбор параметра… (внешний вид – рис.55).
Этап №2. Применение команды Сервис Подбор параметра...
Из меню Сервис выбрать команду Подбор параметра…. Откроется окно, представленное на рис.56.
В окне Подбор параметра задать исходные данные согласно рис. 56, затем нажать Ok. Внешний вид рабочего листа после итерационных вычислений представлен на рис 57.
Рис.57
Пример 2. Отыскать корень алгебраического уравнения: (2x2+3)*(1- sin(x)) = ln(x) +2.
Замечание: Для того чтобы найти корень уравнения, нужно подобрать такое значение х, при котором значение выражения (2x2+3)*(1 – sin(x)) – ln(x) – 2 равно нулю. При решении таких задач важно, чтобы корень действительно существовал.
Этап №1. Подготовительный.
Ячейке А1 присвоить имя х, используя команду Вставка Имя Присвоить…
В открывшемся окне «Присвоение имени» в поле ввода ввести х и нажать ОК.
В ячейку В1 ввести формулу преобразованного уравнения, которое получено путем переноса всех его элементов в одну часть:
= (2*x^2+3)*(1 – sin(x)) – ln(x) – 2
В ячейку А1 ввести любое число (оно буден являться начальным значением изменяемого параметра), учитывая область определения функции f(x)= (2x2+3)*(1- sin(x)) – ln(x) +2, например 1.
В ячейке В1 автоматически будет вычислено значение функции f(x) при х=1.
Этап №2. Применение команды «Подбор параметра...»
Выполнить команду Сервис Подбор параметра…В окне Подбор параметра задать исходные данные (см. рис.59). В ячейке А1 в результате итерационных вычислений будет найден корень уравнения.
Пример 3. Имеется многоступенчатое пищевое производство. Относительные нормативные потери на каждом этапе обработки известны из опытных данных и заданы директивно.
Вычислить:
Подготовить внешний вид рабочего листа с исходными данными согласно рис.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.
Индивидуальные задания.
Вариант 1.
Владелец фирмы нанял на работу Петра за 5000руб. «чистыми», Ивана за 2000руб. и Олега за 300руб. Для того, чтобы провести эти работы в бухгалтерских расчётах, следует определить исходную сумму зарплаты до изъятия налогов и других вычетов (страховых отчислений, в пенсионный фонд и т.д.).
Правила расчета:
Сумма обложения = зарплата – 2 мин. зарплаты – кол-во детей *2 мин. зарплаты.
Сумма обложения может получиться отрицательной. Во избежании этого применить функцию МАКС() или функцию ЕСЛИ(), которая обеспечит равенство нулю суммы обложения в случае, если она получиться отрицательной.
Подоходный налог – 13% от суммы обложения.
Медицинскую страховку – 1% от суммы обложения.
Пенсионный фонд – 2% от суммы обложения.
На руки = зарплата – подоходный налог – мед. страховка – пенсионный фонд.
Для вычисления исходной суммы зарплаты до изъятия налогов и других вычетов применить команду Подбор параметра для каждого работника.
81
Рис.54. Исходные данные функции БЗ
Поле ввода
Рис. 53. Исходный вид рабочего листа
Рис.55. Вид листа перед применением команды Подбор параметра.
Рис.56. Исходные данные для команды Подбор параметра.
ВНЕШНИЙ ВИД РАБОЧЕГО ЛИСТА
Рис.58. Окно задания имени ячейке
Рис.59. Исходные данные команды Подбор параметра.
Рис.60. Исходный вид рабочего листа.
Рис.61. Исходные данные
Рис.62.