Toderelt.ru

ПК Журнал ТодерельТ
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Подбор параметра в EXCEL

Подбор параметра в EXCEL

Обычно при создании формулы пользователь задает значения параметров и формула (уравнение) возвращает результат. Например, имеется уравнение 2*a+3*b=x, заданы параметры а=1, b=2, требуется найти x (2*1+3*2=8). Инструмент Подбор параметра позволяет решить обратную задачу: подобрать такое значение параметра, при котором уравнение возвращает желаемый целевой результат X. Например, при a=3, требуется найти такое значение параметра b, при котором X равен 21 (ответ b=5). Подбирать параметр вручную — скучное занятие, поэтому в MS EXCEL имеется инструмент Подбор параметра .

В MS EXCEL 2007-2010 Подбор параметра находится на вкладке Данные, группа Работа с данным .

Суть функции

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

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

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

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

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

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

Данные.

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

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

Поля.

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

Пример2.

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

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

  • ежемесячные отчисления – 1000 руб.;
  • период уплаты дополнительных страховых взносов – расчетная величина (пенсионный возраст (в примере – для мужчины) минус возраст участника программы на момент вступления);
  • пенсионные накопления – расчетная величина (накопленная за период участником сумма, увеличенная государством в 2 раза);
  • ожидаемый период выплаты трудовой пенсии – 228 мес.;
  • желаемая прибавка к пенсии – 2000 руб.

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

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

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

Использование функции

Давайте перейдем к практическому примеру, который позволит наилучшим образом понять, как работает функция.

Итак, у нас есть таблица с перечнем спортивных товаров. Мы знаем только сумму скидки (560 руб. для первой позиции) и ее размер, который для всех наименований одинаковый. Предстоит выяснить полную стоимость товара. При этом важно, чтобы в ячейке, в которой в дальнейшем отразится сумма скидки, была записана формула ее расчета (в нашем случае – умножение полной суммы на размер скидки).

Умножение одной ячейки на другую в Эксель

Итак, алгоритм действий следующий:

Запуск функции Подбор параметра в Эксель

  1. Переходим во вкладку “Данные”, в которой нажимаем на кнопку “Анализ “что если” в группе инструментов “Прогноз”. В раскрывшемся списке выбираем “Подбор параметра” (в ранних версиях кнопка может находиться в группе “Работа с данными”).
  2. На экране появится окно для подбора параметра, которе нужно заполнить:
    • в значении поля “Установить в ячейке” пишем адрес с финальными данными, которые нам известны, т.е. это ячейка с суммой скидки. Вместо ручного ввода координат можно просто щелкнуть по нужной ячейке в самой таблице. При этом курсор должен быть в соответствующем поле для ввода информации.

Транспортная задача

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

Зададим входные данные. Для этого построим таблицы с информацией о затратах на доставку, объеме товаров на каждом из складов и необходимое количество товаров для каждой торговой точки.

Таблицы для поиска

Будем подбирать такие значения поставок, чтобы соблюдались условия:

1) Полные затраты были минимальны.

2) Суммарные поставки товаров в торговые точки удовлетворяли требованиям.

3) Суммарный вывоз продукции со складов не превышал имеющиеся запасы.

4) Количество единиц продукции должно быть целым и неотрицательным.

Параметры поиска решения

Результат поиска решения.

Результаты поиска

Как работает функция «Подбор параметра» в Excel

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

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

Примечание: Подбор параметров поддерживает только одно входное значение переменной. Если вы хотите принять несколько входных значений, Например, надстройка «Надстройка «Надстройка» используется как для суммы займа, так и для ежемесячного платежа по кредиту. Дополнительные сведения см. в теме Определение и решение проблемы с помощью «Решение».

Пошаговый анализ примера

Рассмотрим предыдущий пример шаг за шагом.

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

Подготовка листа

Откройте новый пустой лист.

Прежде всего добавьте в первый столбец эти подписи, чтобы сделать данные на листе понятнее.

В ячейку A1 введите текст Сумма займа.

В ячейку A2 введите текст Срок в месяцах.

В ячейку A3 введите текст Процентная ставка.

В ячейку A4 введите текст Платеж.

Затем добавьте известные вам значения.

В ячейку B1 введите значение 100 000. Это сумма займа.

В ячейку B2 введите значение 180. Это число месяцев, за которое требуется выплатить ссуду.

Примечание: Хотя вам известна необходимая сумма платежа, не вводите ее как значение, поскольку она получается в результате вычисления формулы. Вместо этого добавьте формулу на лист и укажите значение платежа на более позднем этапе при использовании средства подбора параметров.

Теперь добавьте формулу, результат которой вас интересует. Например, используйте функцию ПЛТ.

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

Формула ссылается на ячейки B1 и B2, значения которых вы указали на предыдущих этапах. Она также ссылается на ячейку B3, в которую средство подбора параметров поместит процентную ставку. Формула делит значение из ячейки B3 на 12, поскольку был указан ежемесячный платеж, а функция ПЛТ предусматривает использование годовой процентной ставки.

Поскольку в ячейке B3 нет значения, Excel полагает процентную ставку равной 0 % и в соответствии со значениями из данного примера возвращает сумму платежа 555,56 ₽. Пока вы можете игнорировать это значение.

Использование средства подбора параметров для определения процентной ставки

На вкладке Данные в группе Работа с данными нажмите кнопку Анализ «что если» и выберите команду Подбор параметра.

В поле Установить в ячейке введите ссылку на ячейку, в которой находится нужная формула. В данном примере это ячейка B4.

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

В поле Изменяя значение ячейки введите ссылку на ячейку, в которой находится корректируемое значение. В данном примере это ячейка B3.

Примечание: Формула в ячейке, указанной в поле Установить в ячейке, должна ссылаться на ячейку, которую изменяет средство подбора параметров.

Нажмите кнопку ОК.

Выполняется и создается результат, как показано на рисунке ниже.

Подбор параметров

Ячейки B1, B2 и B3 — это значения для суммы займа, длины срока и процентной ставки.

Ячейка B4 отображает результат формулы =PMT(B3/12;B2;B1).

Напоследок отформатируйте целевую ячейку (B3) так, чтобы результат в ней отображался в процентах.

На вкладке Главная в группе Число нажмите кнопку Процент.

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

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

Примечание: Подбор параметров поддерживает только одно входное значение переменной. Если вы хотите принять несколько входных значений, например сумму займа и сумму ежемесячного платежа по кредиту, воспользуйтесь надстройка «Надстройка «Надстройка». Дополнительные сведения см. в теме Определение и решение проблемы с помощью «Решение».

Пошаговый анализ примера

Рассмотрим предыдущий пример шаг за шагом.

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

Подготовка листа

Откройте новый пустой лист.

Прежде всего добавьте в первый столбец эти подписи, чтобы сделать данные на листе понятнее.

В ячейку A1 введите текст Сумма займа.

В ячейку A2 введите текст Срок в месяцах.

В ячейку A3 введите текст Процентная ставка.

В ячейку A4 введите текст Платеж.

Затем добавьте известные вам значения.

В ячейку B1 введите значение 100 000. Это сумма займа.

В ячейку B2 введите значение 180. Это число месяцев, за которое требуется выплатить ссуду.

Примечание: Хотя вам известна необходимая сумма платежа, не вводите ее как значение, поскольку она получается в результате вычисления формулы. Вместо этого добавьте формулу на лист и укажите значение платежа на более позднем этапе при использовании средства подбора параметров.

Теперь добавьте формулу, результат которой вас интересует. Например, используйте функцию ПЛТ.

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

Формула ссылается на ячейки B1 и B2, значения которых вы указали на предыдущих этапах. Она также ссылается на ячейку B3, в которую средство подбора параметров поместит процентную ставку. Формула делит значение из ячейки B3 на 12, поскольку был указан ежемесячный платеж, а функция ПЛТ предусматривает использование годовой процентной ставки.

Поскольку в ячейке B3 нет значения, Excel полагает процентную ставку равной 0 % и в соответствии со значениями из данного примера возвращает сумму платежа 555,56 ₽. Пока вы можете игнорировать это значение.

Использование средства подбора параметров для определения процентной ставки

Выполните одно из указанных ниже действий.

In Excel 2016 для Mac: On the Data tab, click What-If Analysis, and then click Goal Seek.

В Excel для Mac 2011: на вкладке Данные в группе Инструменты для работы с данными нажмите кнопку Анализ «что если» ивыберите «Поиск окна».

В поле Установить в ячейке введите ссылку на ячейку, в которой находится нужная формула. В данном примере это ячейка B4.

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

В поле Изменяя значение ячейки введите ссылку на ячейку, в которой находится корректируемое значение. В данном примере это ячейка B3.

Примечание: Формула в ячейке, указанной в поле Установить в ячейке, должна ссылаться на ячейку, которую изменяет средство подбора параметров.

Нажмите кнопку ОК.

Выполняется и создается результат, как показано на рисунке ниже.

Напоследок отформатируйте целевую ячейку (B3) так, чтобы результат в ней отображался в процентах. Выполните одно из указанных действий.

In Excel 2016 для Mac: On the Home tab, click Increase Decimal Увеличение числа десятичных замеровor Decrease Decimal Decrease Decimal.

В Excel для Mac 2011: на вкладке Главная в группе Число нажмите кнопку Увеличить десятичность или Уменьшить число десятичных , чтобы установить количество десятичных десятичных заметок.

Решение уравнений

Подбор параметра также используют, если нужно найти какое-либо из значений в заданном уравнении. В качестве примера воспользуемся следующим выражением: 2*а+3*b=x, где x=21, а=3, неизвестная переменная — b.

Заполнение таблицы

Для начала нужно заполнить таблицу.

Параметры а и b следует вводить в ячейки B2 и B3 соответственно. Табличный элемент B4 отведен для формулы =2*B2+3*B3. Переменная x в ячейке B5 указана в качестве примечания.

Выделение ячеек

Необходимо выделить ячейку, в которой вписана формула (B4) и вызвать целевую функцию по определению параметра (инструкция вызова подробно изложена в предыдущем примере).

Ввод данных

Затем вписать во второе поле (значение) результат (21), а в третье адрес ячейки B3, поскольку именно она будет изменяться.

Подтверждение действий

Подтвердить действие кликом по соответствующей кнопке.

Результат выполнения

В результате выполнения команды переменной b подобралось значение 5.

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

Ввод формулы

Для закрепления материала решим еще одно уравнение – 15*x+18*x=46. Для начала нужно записать формулу в ячейку B2. Вместо x необходимо указать ссылку на табличный элемент, где будет отображен результат, в данном случае A2.

Запуск команды

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

Ссылка на ячейку

Во всплывающем окне, в первом верхнем текстовом поле нужно вписать ссылку ячейки, содержащей формулу (B2). Во втором поле — число из уравнения после знака равно, то есть 46. В третьем поле должна быть ссылка на ячейку со значением x, в данном случае это A2.

Подтверждение операции

После того как все поля заполнены, нужно подтвердить операцию. На экране в новом всплывающем окне отобразиться правильное решение уравнения. Значение x будет равно 1,39393939393939.

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

голоса
Рейтинг статьи
Читать еще:  Скрытая запись экрана компьютера — ТОП программ
Ссылка на основную публикацию
ВсеИнструменты
Adblock
detector