Использование инструмента 'Подбор параметра' (Goal Seek) в Excel для решения задач
Инструмент Goal Seek (Подбор параметра) в Excel — это мощный механизм для автоматического поиска нужного входного значения, которое приведет к желаемому результату в формуле, избавляя вас от ручного подбора чисел.
«Подбор параметра» доступен в настольном Excel для Windows и Mac, включая Microsoft 365 и Excel 2016–2024. Встроенную команду в Excel для Интернета эта инструкция не предполагает. Инструмент ищет равенство заданному значению, а не максимум, минимум или неравенство.
Представьте ситуацию: вы рассчитываете ежемесячный платеж по кредиту и хотите, чтобы он не превышал определенную сумму в вашем бюджете. Вместо того чтобы вручную перебирать различные суммы кредита или процентные ставки, вы можете поручить эту задачу Excel.
Как использовать Goal Seek: пошаговая инструкция
- Создайте проверяемый пример: B1 — сумма 20000, B2 — годовая ставка 6%, B3 — срок 60 месяцев. В B5 введите
=ПЛТ(B2/12;B3;B1)/=PMT(B2/12,B3,B1). Платёж в конце месяца; комиссий и первоначального взноса в этой модели нет. - Выделите ячейку с формулой, результат которой вы хотите изменить (например, ячейку с ежемесячным платежом).
- Перейдите на вкладку Данные.
- Найдите выпадающий список Анализ 'Что-Если' и выберите Подбор параметра….
- В открывшемся диалоговом окне заполните поля:
- Установить в ячейке: Уже будет указана выбранная вами ячейка с формулой.
- Значение: Введите желаемый результат (например,
-425для платежа). - Изменяя значение ячейки: Укажите адрес ячейки с входным параметром, который Excel может менять (например, сумму кредита).
- Нажмите OK и проверьте сообщение о результате и достигнутый платёж. При найденном подходящем решении подтвердите его; иначе отмените изменение. Сходимость и единственность решения не гарантируются.
Для приведённых 6% годовых, 60 месяцев и платежа −425 ожидаемая сумма в B1 — примерно 21 983,36. Отрицательный знак платежа соответствует положительной сумме кредита. Проверяйте знак целевого значения и единицы ставки. Изменяемая ячейка должна содержать число, а целевая формула — зависеть от неё непосредственно или через другие формулы.
Ключевые особенности и советы
- Формула остается неизменной. Goal Seek меняет только значение в указанной вами входной ячейке.
- Экспериментируйте с разными переменными. Вы можете подбирать не только сумму кредита, но и срок или процентную ставку для достижения целевого платежа.
- Инструмент идеально подходит для задач, где есть одна целевая ячейка (зависимая) и одна изменяемая ячейка (независимая).
Заключение
Goal Seek — это невероятно полезный и часто недооцененный инструмент для финансового моделирования, планирования и анализа сценариев в Excel. Он экономит время и обеспечивает точность, автоматизируя поиск входного значения для заданного результата. Для нескольких переменных и ограничений используйте «Поиск решения» (Solver).