Использование средства подбора параметров для получения требуемого результата путем изменения входного значения

Важно :  Данная статья переведена с помощью машинного перевода, см. Отказ от ответственности. Используйте английский вариант этой статьи, который находится здесь, в качестве справочного материала.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  4. Теперь добавьте формулу, для которых у вас есть цели. В данном примере используется функция ПЛТ:

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

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

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

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

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

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

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

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

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

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

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

    Модель, в которой размер платежа зависит от процентной ставки

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  4. Теперь добавьте формулу, для которых у вас есть цели. В данном примере используется функция ПЛТ:

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

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

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

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

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

    В Excel 2016 для Mac: на вкладке данные нажмите кнопку Анализ "что если" и нажмите кнопку Подбор.

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

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

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

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

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

  5. Нажмите ОК.

    Подбор запускается и результат, как показано на приведенном ниже рисунке.

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

    • В Excel 2016 для Mac: на вкладке Главная нажмите кнопку Увеличить разрядность Увеличить разрядность или Уменьшить разрядность Уменьшить разрядность .

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

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

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

Были ли сведения полезными?

Спасибо за ваш отзыв!

Благодарим за отзыв! Возможно, будет полезно связать вас с одним из наших специалистов службы поддержки Office.

×