Автономная некоммерческая образовательная организация высшего образования «Сибирский институт бизнеса и информационных технологий»

Зачетная работа

(семестр №2)
Дисциплина: Учебно-воспитательный семинар
Задание

Анализ данных с помощью подбора параметров Excel

Выполнила: Горева Анастасия Владимировна Группа: ИВз-1122(2) Проверил(а): Ф.И.О ___________________ Дата:_____________________

Омск 2023 г.
Содержание

ВВЕДЕНИЕ ……………………………………………………………….…..3

Суть Функции……………. …………………………………………………..4

Местонахождение функции «Подбор параметра» в Excel…………………5

Решение уравнений методом «Подбора параметров» в Excel……………..6

Примеры «Подбора параметра» в Excel…………………………………….8

Введение

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

Суть функции

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

Местонахождение функции «Подбор параметра» в Excel

Известен результат некой формулы. Имеются также входные данные. Кроме одного. Неизвестное входное значение мы и будем искать. Рассмотрим функцию «Подбора параметров» в Excel на примере.

---

Необходимо подобрать процентную ставку по займу, если известна сумма и срок. Заполняем таблицу входными данными.



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

Когда условия задачи записаны, переходим на вкладку «Данные». «Работа с данными» - «Анализ «Что-Если»» - «Подбор параметра».



В поле «Установить в ячейке» задаем ссылку на ячейку с расчетной формулой (B4). Поле «Значение» предназначено для введения желаемого результата формулы. В нашем примере это сумма ежемесячных платежей. Допустим, -5 000 (чтобы формула работала правильно, ставим знак «минус», ведь эти деньги будут отдаваться). В поле «Изменяя значение ячейки» - абсолютная ссылка на ячейку с искомым параметром ($B$3).



После нажатия ОК на экране появится окно результата.



Чтобы сохранить, нажимаем ОК или ВВОД.



Функция «Подбор параметра» изменяет значение в ячейке В3 до тех пор, пока не получит заданный пользователем результат формулы, записанной в ячейке В4. Команда выдает только одно решение задачи.

Решение уравнений методом «Подбора параметров» в Excel

Функция «Подбор параметра» идеально подходит для решения уравнений с одним неизвестным. Возьмем для примера выражение: 20 * х – 20 / х = 25. Аргумент х – искомый параметр. Пусть функция поможет решить уравнение подбором параметра и отобразит найденное значение в ячейке Е2.

В ячейку Е3 введем формулу: = 20 * Е2 – 20 / Е2.



А в ячейку Е2 поставим любое число, которое находится в области определения функции. Пусть это будет 2.

---

Запускам инструмент и заполняем поля:

«Установить в ячейке» - Е3 (ячейка с формулой);

«Значение» - 25 (результат уравнения);

«Изменяя значение ячейки» - $Е$2 (ячейка, назначенная для аргумента х).



Результат функции:



Найденный аргумент отобразится в зарезервированной для него ячейке.



Решение уравнения: х = 1,80.

Функция «Подбор параметра» возвращает в качестве результата поиска первое найденное значение. Вне зависимости от того, сколько уравнение имеет решений.

Если, например, в ячейку Е2 мы поставим начальное число -2, то решение будет иным.



Примеры «Подбора параметра» в Excel

Функция «Подбор параметра» в Excel применяется тогда, когда известен результат формулы, но начальный параметр для получения результата неизвестен. Чтобы не подбирать входные значения, используется встроенная команда.

Пример 1. Метод подбора начальной суммы инвестиций (вклада).

Известные параметры:

• 
  срок – 10 лет;
  
• 
  доходность – 10%;
  
• 
  коэффициент наращения – расчетная величина;
  
• 
  сумма выплат в конце срока – желаемая цифра (500 000 рублей).
  

Внесем входные данные в таблицу:



Начальные инвестиции – искомая величина. В ячейке В4 (коэффициент наращения) – формула =(1+B3)^B2.

Вызываем окно команды «Подбор параметра». Заполняем поля:



После выполнения команды Excel выдает результат:



Чтобы через 10 лет получить 500 000 рублей при 10% годовых, требуется внести 192 772 рубля.

Пример 2. Рассчитаем возможную прибавку к пенсии по старости за счет участия в государственной программе софинансирования.

Входные данные:

ежемесячные отчисления – 1000 руб.;

период уплаты дополнительных страховых взносов – расчетная величина (пенсионный возраст (в примере – для мужчины) минус возраст участника программы на момент вступления);

---

пенсионные накопления – расчетная величина (накопленная за период участником сумма, увеличенная государством в 2 раза);

ожидаемый период выплаты трудовой пенсии – 228 мес.;

желаемая прибавка к пенсии – 2000 руб.



С какого возраста необходимо уплачивать по 1000 рублей в качестве дополнительных страховых взносов, чтобы получить прибавку к пенсии в 2000 рублей:

Ячейка с формулой расчета прибавки к пенсии активна – вызываем команду «Подбор параметра». Заполняем поля в открывшемся меню.



Нажимаем ОК – получаем результат подбора.



Чтобы получить прибавку в 2000 руб., необходимо ежемесячно переводить на накопительную часть пенсии по 1000 рублей с 41 года.

Функция «Подбор параметра» работает правильно, если:

• 
  значение желаемого результата выражено формулой;
  
• 
  все формулы написаны полностью и без ошибок.
  

---

Смотрите также файлы

• Задания на практические занятия.pdf

• Основная образовательная программа направление подготовки 40. 03. 01 Юриспруденция (уровень бакалавриата).docx

• Требования smart.docx

• Лекция 9 макроэкономическое равновесие в открытой экономике.pptx

• Философия йоги.docx