Что такое подбор параметра
Подбор параметра в Excel и примеры его использования
В упрощенном виде его назначение можно сформулировать так: найти значения, которые нужно ввести в одиночную формулу, чтобы получить желаемый (известный) результат.
Где находится «Подбор параметра» в Excel
Известен результат некой формулы. Имеются также входные данные. Кроме одного. Неизвестное входное значение мы и будем искать. Рассмотрим функцию «Подбора параметров» в Excel на примере.
Необходимо подобрать процентную ставку по займу, если известна сумма и срок. Заполняем таблицу входными данными.
Процентная ставка неизвестна, поэтому ячейка пустая. Для расчета ежемесячных платежей используем функцию ПЛТ.
После нажатия ОК на экране появится окно результата.
Чтобы сохранить, нажимаем ОК или ВВОД.
Функция «Подбор параметра» изменяет значение в ячейке В3 до тех пор, пока не получит заданный пользователем результат формулы, записанной в ячейке В4. Команда выдает только одно решение задачи.
Решение уравнений методом «Подбора параметров» в Excel
Функция «Подбор параметра» идеально подходит для решения уравнений с одним неизвестным. Возьмем для примера выражение: 20 * х – 20 / х = 25. Аргумент х – искомый параметр. Пусть функция поможет решить уравнение подбором параметра и отобразит найденное значение в ячейке Е2.
В ячейку Е3 введем формулу: = 20 * Е2 – 20 / Е2.
А в ячейку Е2 поставим любое число, которое находится в области определения функции. Пусть это будет 2.
Запускам инструмент и заполняем поля:
Найденный аргумент отобразится в зарезервированной для него ячейке.
Решение уравнения: х = 1,80.
Функция «Подбор параметра» возвращает в качестве результата поиска первое найденное значение. Вне зависимости от того, сколько уравнение имеет решений.
Примеры подбора параметра в Excel
Функция «Подбор параметра» в Excel применяется тогда, когда известен результат формулы, но начальный параметр для получения результата неизвестен. Чтобы не подбирать входные значения, используется встроенная команда.
Пример 1. Метод подбора начальной суммы инвестиций (вклада).
Внесем входные данные в таблицу:
Начальные инвестиции – искомая величина. В ячейке В4 (коэффициент наращения) – формула =(1+B3)^B2.
Вызываем окно команды «Подбор параметра». Заполняем поля:
После выполнения команды Excel выдает результат:
Чтобы через 10 лет получить 500 000 рублей при 10% годовых, требуется внести 192 772 рубля.
Пример 2. Рассчитаем возможную прибавку к пенсии по старости за счет участия в государственной программе софинансирования.
С какого возраста необходимо уплачивать по 1000 рублей в качестве дополнительных страховых взносов, чтобы получить прибавку к пенсии в 2000 рублей:
Чтобы получить прибавку в 2000 руб., необходимо ежемесячно переводить на накопительную часть пенсии по 1000 рублей с 41 года.
Функция «Подбор параметра» работает правильно, если:
Подбор параметра в EXCEL
history 18 ноября 2012 г.
Простейший пример
Подготовим исходные данные.
В качестве целевого значения для ячейки B10 укажите 21, изменять будем ячейку B9 (параметр b ).
Инструмент Подбор параметра подобрал значение параметра b равное 5.
Конечно, можно подобрать значение вручную. В данном случае необходимо в ячейку B9 последовательно вводить значения и смотреть, чтобы х текущее совпало с Х целевым. Однако, часто зависимости в формулах достаточно сложны и без Подбора параметра параметр будет подобрать сложно .
Калькуляция, подбираем значение прибыли
Еще пример. Пусть дана структура цены договора: Собственные расходы, Прибыль, НДС.
Известно, что Собственные расходы составляют 150 000 руб., НДС 18%, а Целевая стоимость договора 200 000 руб. (ячейка С13 ). Единственный параметр, который можно менять, это Прибыль. Подберем такое значение Прибыли ( С8 ), при котором Стоимость договора равна Целевой, т.е. значение ячейки Расхождение ( С14 ) равно 0.
В структуре цены в ячейке С9 (Цена продукции) введена формула Собственные расходы + Прибыль ( =С7+С8 ). Стоимость договора (ячейка С11 ) вычисляется как Цена продукции + НДС (= СУММ(С9:C10) ).
Конечно, можно подобрать значение вручную, для чего необходимо уменьшить значение прибыли на величину расхождения без НДС. Однако, как говорилось ранее, зависимости в формулах могут быть достаточно сложны. В этом случае поможет инструмент Подбор параметра .
Теперь, о том когда этот инструмент работает. 1. Изменяемая ячейка не должна содержать формулу, только значение.2. Необходимо найти только 1 значение, изменяя 1 ячейку. Если требуется найти 1 конкретное значение (или оптимальное значение), изменяя значения в НЕСКОЛЬКИХ ячейках, то используйте Поиск решения.3. Уравнение должно иметь решение, в нашем случае уравнением является зависимость стоимости от прибыли. Если целевая стоимость была бы равна 1000, то положительной прибыли бы у нас найти не удалось, т.к. расходы больше 150 тыс. Или например, если решать уравнение x2+4=0, то очевидно, что не удастся подобрать такое х, чтобы x2+4=0
Примечание : В файле примера приведен алгоритм решения Квадратного уравнения с использованием Подбора параметра.
Подбор суммы кредита
В EXCEL существует функция ПЛТ() для расчета ежемесячного платежа в зависимости от суммы кредита, срока и процентной ставки (см. статьи про аннуитет ). Но эта функция нам не подходит, т.к. сумму ежемесячного платежа мы итак знаем, а вот сумму кредита (параметр функции ПЛТ() ) мы как раз и хотим найти. Но, тем не менее, мы будем использовать эту функцию для решения нашей задачи. Без применения инструмента Подбор параметра сумму займа пришлось бы подбирать в ручную с помощью функции ПЛТ() или использовать соответствующую формулу.
Чтобы найти сумму займа соответствующую заданным выплатам 1800 руб./мес., делаем следующее:
Иными словами, инструмент Подбор параметра позволяет сэкономить несколько минут по сравнению с ручным перебором.
3 примера использования подбора параметра в Excel
На данном уроке мы научимся практически применять вычислительный инструмент «Подбор параметр» в Excel. Специально для Вас подготовлено 3 практических примера, которые могут пригодиться Вам уже сегодня.
Подбор параметра для банковских депозитов
На протяжении 10-ти лет мы хотим накопить 20 000$. Свои сбережения будем откладывать на банковский депозит по 5% годовых. Деньги будем вносить на банковский депозитный счет ежегодно и одинаковыми частями взносов. Какой должен быть размер ежегодного взноса, чтобы за 10 лет собрать 20 000$ при 5-т и процентах годовых?
Для решения данной задачи в Excel воспользуемся инструментом «Подбор параметра»:
Результат вычисления получился с отрицательным числом – это правильно в соответствии со стандартом финансовых функций Excel. Регулярные взносы должны отображаться отрицательным значением, так как это категория расходных операций. А по истечению 10 лет мы получим на приход +20 000$.
Полезный совет! Если Вы нужно узнать размер ежемесячных взносов, тогда перед использованием инструмента «Подбор параметра» нужно процентную ставку разделить на 12 (чтобы перевести в ежемесячный процент).
А количество лет нужно перевести в количество месяцев умножив на 12. Таким образом, в ячейке B3 мы получим необходимую сумму ежемесячного взноса для достижения цели.
Поиск решений подбором параметра при ценообразовании
Стратегия для построения производственного плана выпуска продукта:
Какую установить розничную цену, чтобы рентабельность производства сохранялась на уровне 20%?
Рентабельность определяется как соотношение дохода к прибыли (прибыль разделить на доход) и выражается только в процентах!
Снова решим поставленную задачу в Excel с помощью подбора параметра:
Как видно розничную цену (B1) нужно устанавливать в 2 раза выше производственных расходов на 1-ну штуку продукции. Только тогда мы сможем удержать рентабельность производства на уровне 20% при таких расходах на реализацию. В реальности бывает и еще хуже.
Подбор параметра для банковских кредитов
Допустим, Вы хотите приобрести автомобиль в кредит. Максимальная сумма ежемесячного взноса, которую Вы можете себе позволить, составляет 700$. Банк не может выдать Вам кредит сроком более чем на 3 года, с процентной ставкой 5,5% годовых. Можете ли вы себе позволить при таких условиях кредитования приобрести автомобиль стоимостью в 30 000$, а если нет, то на какую сумму можно рассчитывать?
Составьте таблицу условий кредитования в Excel как показано ниже на рисунке. Обратите внимание! Ячейка B4 содержит формулу: =-ПЛТ(B3/12;B2;B1).
Как видно Вы не можете себе позволить такой дорогой автомобиль. Теперь узнаем, какая максимальная стоимость автомобиля соответствует Вашим финансовым возможностям. Для этого перейдите в ячейку B4 и выберите инструмент: «Данные»-«Работа с данными»-«Анализ что если»-«Подбор параметра».
Заполните поля в появившемся диалоговом окне как показано выше на рисунке и нажмите ОК.
Как видно максимальная стоимость автомобиля, на которую можно рассчитывать составляет при таких финансовых возможностях и условиях кредитования составляет – 23 1812$.
Внимание! Если срок кредитования определяется количеством месяцев, а не лет, то годовую процентную ставку нужно перевести в месячную. Поэтому в первом аргументе функции ПЛТ стоит значение B3/12 (5,5% годовых разделено на 12 месяцев).
Используя финансовые функции, следует помнить об их стандартах. Например, сумма займа всегда отображаются как отрицательное число. Поэтому перед функцией ПЛТ мы использовали знак минус.
Уравнения и задачи на подбор параметра в Excel
Часто нам нужно предварительно спрогнозировать, какие будут результаты вычислений при определенных входящих параметрах. Например, если получить кредит на закупку товара в банке с более низкой процентной ставкой, а цену товара немного повысить – существенно ли возрастет прибыль при таких условиях?
При разных поставленных подобных задачах, результаты вычислений могут завесить от одного или нескольких изменяемых условий. В зависимости от типа прогноза в Excel следует использовать соответствующий инструмент для анализа данных.
Подбор параметра и решение уравнений в Excel
Данный инструмент следует применять для анализа данных с одним неизвестным (или изменяемым) условием. Например:
Решим данную задачу встроенными вычислительными инструментами Excel для анализа данных:
В результате мы получили правильное значение 3.
Получили максимально точный результат: 2*3+1=7
Второй пример использования подбора параметра для уравнений
Немного усложним задачу. На этот раз формула выглядит следующим образом:
Обратите внимание! В первом примере мы получили максимально точный результат, а во втором – максимально приближенный.
Это простые примеры быстрого поиска решений формул с помощью Excel. Сегодня каждый школьник знает, как найти значение x. Например:
Excel в своих алгоритмах инструментов анализа данных использует более простой метод – подстановки. Он подставляет вместо x разные значения и анализирует, насколько результат вычислений отклоняется от условий указанных в параметрах инструмента. Как только будет, достигнут результат вычисления с максимальной точностью, процесс подстановки прекращается.
По умолчанию инструмент выполняет 100 повторений (итераций) с точностью 0.001. Если нужно увеличить количество повторений или повысить точность вычисления измените настройки: «Файл»-«Параметры»-«Формулы»-«Параметры вычислений»:
Таким образом, если нас не устраивает результат вычислений, можно:
Используя эти способы настроек можно существенно облегчить и ускорить процесс поиска максимально точного решения.
О подборе нескольких параметров в 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.
В поле Изменяя значение ячейки введите ссылку на ячейку, в которой находится корректируемое значение. В данном примере это ячейка 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.
В поле Изменяя значение ячейки введите ссылку на ячейку, в которой находится корректируемое значение. В данном примере это ячейка B3.
Примечание: Формула в ячейке, указанной в поле Установить в ячейке, должна ссылаться на ячейку, которую изменяет средство подбора параметров.
Выполняется и создается результат, как показано на рисунке ниже.
Напоследок отформатируйте целевую ячейку (B3) так, чтобы результат в ней отображался в процентах. Выполните одно из указанных действий.
In Excel 2016 для Mac: On the Home tab, click Increase Decimal 
or Decrease Decimal 
.
В Excel для Mac 2011: на вкладке Главная в группе Число нажмите кнопку Увеличить десятичность 
или Уменьшить число десятичных 
, чтобы установить количество десятичных десятичных заметок.