Использование средства подбора параметров для получения требуемого результата путем изменения входного значения
Если вы знаете, какой результат вычисления формулы вам нужен, но не можете определить входные значения, позволяющие его получить, используйте средство подбора параметров. Предположим, что вам нужно занять денег. Вы знаете, сколько вам нужно, на какой срок и сколько вы сможете платить каждый месяц. С помощью средства подбора параметров вы можете определить, какая процентная ставка обеспечит ваш долг.
Если вы знаете, какой результат вычисления формулы вам нужен, но не можете определить входные значения, позволяющие его получить, используйте средство подбора параметров. Предположим, что вам нужно занять денег. Вы знаете, сколько вам нужно, на какой срок и сколько вы сможете платить каждый месяц. С помощью средства подбора параметров вы можете определить, какая процентная ставка обеспечит ваш долг.
Примечание: Подбор параметров поддерживает только одно входное значение переменной. Если вы хотите принять несколько входных значений, Например, надстройка "Надстройка "Надстройка" используется как для суммы займа, так и для ежемесячного платежа по кредиту. Дополнительные сведения см. в теме Определение и решение проблемы с помощью "Решение".
Пошаговый анализ примера
Рассмотрим предыдущий пример шаг за шагом.
Так как вы хотите вычислить процентную ставку по кредиту, используйте функцию 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 .
В Excel для Mac 2011: на вкладке Главная в группе Число нажмите кнопку Увеличить десятичность или Уменьшить число десятичных , чтобы установить количество десятичных десятичных заметок.
Функция подбора параметра в программе MS Excel
Функция подбора параметра в программе Excel является одной из самых полезных, так как позволяет автоматически подобрать исходное значение для конечного результата. Это очень удобно, если в таблице у вас заполнены ячейки с результатами, но исходные данные известны не полностью. К сожалению, не все пользователю знают о наличии данного инструмента, а тем более как с ним работать.
Как работает функция подбора параметра в Excel
«Подбор параметра» — это функция, необходимая для вычисления исходных данных для достижения конкретного результата. Во многом похожа на функцию «Поиск решения», но имеет более упрощенный вид и функционал, поэтому с ее помощью можно получить только исходные данные для ячеек, где уже известен ответ. Функция «Подбор параметра» работает только с одним вводным и искомым значением.
Далее рассмотрим, как применить данную функцию на практике.
Пример применения на практике
Чтобы вы лучше понимали особенности «Подбора параметра» в Excel рассмотрим ее применение на примере таблицы с заработной платой и премиями. Мы имеем одного сотрудника, чья премия за рассматриваемый период времени равна 5000 рублей. Премия рассчитывается путем умножения заработной платы на коэффициент, который в данной таблице составляет 0,28.
Нам неизвестна заработная плата. В «обычной жизни» мы бы нашли ее поделив коэффициент на размер премии. В Excel же для этого можно задать формулу через «Подбор параметров», которую можно будет быстро применить к другим столбцам таблицы. Давайте рассмотрим, как это сделать в конкретном случае:
- Выделите ячейку с данными премии. Там можно указать число вручную без использования каких-либо формул.
- В верхнем меню интерфейса таблицы переключитесь в раздел «Данные».
- Там найдите инструмент «Анализ «что если»». В новых версиях Excel он расположен в блоке «Прогноз». В старых обычно находится в блоке «Работа с данными».
- Из контекстного меню выберите пункт «Подбор параметра».
Как видите, работать с функцией «Подбор параметров» очень легко. В некоторых ситуациях она позволяет автоматизировать процесс расчетов, однако в более продвинутых формулах она может не сработать или сработать некорректно.