Вы когда-нибудь подбирали входные значения в вашем расчёте Excel, чтобы получить на выходе нужный результат? В такие моменты чувствуешь себя матёрым артиллеристом, правда? Всего-то пара десятков итераций «недолёт — перелёт», и вот оно, долгожданное «попадание»!
Microsoft Excel сможет сделать такую подгонку за вас, причём быстрее и точнее. Для этого нажмите на вкладке «Вставка» кнопку «Анализ „что если“» и выберите команду «Подбор параметра» (Insert — What If Analysis — Goal Seek). В появившемся окне задайте ячейку, где хотите подобрать нужное значение, желаемый результат и входную ячейку, которая должна измениться. После нажатия на «ОК» Excel выполнит до 100 «выстрелов», чтобы подобрать требуемый вами итог с точностью до 0,001.
Эта функция позволяет подобрать такое значение ячейки, при котором формула, ссылающаяся на эту ячейку, будет принимать нужное значение.
Например, если есть формула в ячейке А1 вида: =A2+B2*C2-D2^3, то с помощью подбора параметра можно задать в ячейке C2 такое значение, при котором А1 будет возвращать 100.
Ячейка А1 обязательно должна содержать формулу, ссылающуюся на С2
ПОДБОР ПАРАМЕТРА В 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 года.
Функция «Подбор параметра» работает правильно, если:
- значение желаемого результата выражено формулой;
- все формулы написаны полностью и без ошибок.
Пошаговый анализ примера
Рассмотрим предыдущий пример шаг за шагом.
Так как вы хотите вычислить ставку процентных ценностей, необходимую для достижения цели, вы можете использовать функцию ПЛТ. Функция ПЛТ вычисляет сумму ежемесячного платежа. В данном примере эту сумму и требуется определить.
Подготовка листа
- Откройте новый пустой лист.
- Прежде всего добавьте в первый столбец эти подписи, чтобы сделать данные на листе понятнее.
- В ячейку 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.
Примечание: Формула в ячейке, указанной в поле Установить в ячейке, должна ссылаться на ячейку, которую изменяет средство подбора параметров.
- Напоследок отформатируйте целевую ячейку (B3) так, чтобы результат в ней отображался в процентах.
- На вкладке Главная в группе Число нажмите кнопку Процент.
- Чтобы задать количество десятичных разрядов, нажмите кнопку Увеличить разрядность или Уменьшить разрядность.