Анализ данных и автоматизация
Подбор параметра в Excel: ставка, план продаж и целевой итог
Подбор параметра в Excel — это команда обратного расчёта для тех, кто знает нужный итог формулы, но не знает входное число. По справке Microsoft инструмент работает только с одним входным значением переменной[2]. Так находят процент под посильный платёж, объём продаж под плановую прибыль или оценку под целевой средний балл.
Формула считает итог по входным числам, а в работе часто известен как раз итог: банк называет посильный платёж, руководитель — плановую прибыль. Справка Microsoft советует эту команду, когда нужный итог одной формулы известен, а входное значение — нет[5]. Перебирать числа вручную долго, а подбор параметра делает этот перебор за вас и останавливается на нужном итоге.
Путь короткий: найти команду на вкладке «Данные», выделить ячейку с формулой, выбрать «Подбор параметра» и заполнить три поля, проверить ответ обратным расчётом, при промахе отменить подстановку и запустить заново, а потом повторить приём на своих данных. Схема одна и та же для кредита, цены и плана — меняются только ячейки. Общий порядок работы с листом собран на главной странице тренажёра Excel с уроками.
Ключевые факты: подбор параметра в Excel
- Подбор параметра меняет входное значение, пока формула в целевой ячейке не выдаст заданный итог[1].
- В примере справки кредит 100 000 на 180 месяцев ищет ставку под платёж −900, а при пустой ставке ПЛТ показывает 555,56[1].
- Команда меняет только одну ячейку, и эта ячейка должна участвовать в формуле целевой[1].
- Справка Microsoft перечисляет для команды Microsoft 365, Excel 2024, Excel 2021, Excel 2019 и Excel 2016[1].
- Google Таблицы в отличие от Excel дают подбор только через дополнение Goal Seek на английском языке[3].
Как работает подбор параметра в Excel: целевая ячейка и одна изменяемая
Подбор параметра в Excel подбирает число в изменяемой ячейке под заданный итог формулы. Команда находится на вкладке «Данные»: нажмите «Анализ "что если"» и выберите «Подбор параметра»; в английской справке путь записан как Data → What-If Analysis → Goal Seek[1]. Русскую версию этой страницы справки сверить не удалось, поэтому поля окна ниже названы по английской версии.
| Поле окна | Что указать | В примере справки |
|---|---|---|
| Set cell — целевая ячейка | ячейку с формулой, итог которой нужен | B4 с формулой ПЛТ |
| To value — значение | нужный итог числом | −900 |
| By changing cell — изменяемая ячейка | входное значение, которое команда будет менять | B3 со ставкой |
Изменяемая ячейка обязана участвовать в формуле целевой ячейки, иначе команде нечего менять[1]. В поле значения вводят число, а не адрес: окно спрашивает именно итог. Команда подставляет найденное число в изменяемую ячейку и на этом заканчивает работу.
Место подбора параметра в анализе «что если»
Справка Microsoft перечисляет три инструмента анализа «что если», и подбор параметра среди них единственный, кто идёт от итога к входу[2]. В русском переводе той же страницы команда названа то «поиском цели», то «подбором параметров» — речь об одном инструменте.
Разобравшись с полями, переходим к примеру из справки — он показывает, зачем в поле значения нужен минус.
Пошаговый пример: ставка кредита под нужный платёж
Подбор параметра в Excel находит ставку кредита, при которой формула ПЛТ выдаёт посильный платёж. Данные взяты из примера справки Microsoft: сумма 100 000, срок 180 месяцев, ставка пока пустая[1].
| A | B | |
|---|---|---|
| 1 | Сумма кредита | 100000 |
| 2 | Срок, мес. | 180 |
| 3 | Ставка, годовых | |
| 4 | Платёж | =ПЛТ(B3/12;B2;B1) |
- Введите в B4 формулу
=ПЛТ(B3/12;B2;B1): пока B3 пуста, ставка считается нулевой, и ячейка показывает −555,56. - Выделите B4, на вкладке «Данные» выберите «Анализ "что если"» → «Подбор параметра».
- В первом поле оставьте B4, во втором введите
-900, в третьем укажите B3. - Нажмите OK: команда подставит в B3 годовую ставку около 0,0702.
- Задайте B3 процентный формат — получится 7,02%[6].
Минус в поле значения обязателен: ПЛТ возвращает выплату отрицательным числом, и справка объясняет знак именно тем, что это платёж[1]. Проверьте ответ обратным расчётом: =ПЛТ(7,02%/12;180;100000) вернёт −899,95, а разница с −900 возникла из-за округления ставки до сотых процента. Повторите подбор в уроке тренажёра на фудтраке: там нужно подобрать число порций, цену и срок кредита под заданную сумму.
| Что видно | Причина | Что сделать |
|---|---|---|
| команда не срабатывает, ответ не находится | изменяемая ячейка не входит в формулу целевой | исправить ссылки в формуле |
| в ячейке длинная дробь | Excel считает с точностью 15 значащих цифр[5] | задать формат или округлить |
| платёж вышел с другим знаком | в поле значения нет минуса | ввести итог как выплату со знаком минус |
Однако найденное число ведёт себя иначе, чем формула, и в этом главное отличие команды от обратного расчёта формулой.
Подбор параметра или обратная формула: чем отличается ответ
Подбор параметра подставляет в ячейку готовое число, а обратная формула пересчитывает ответ при каждом изменении данных. Разницу видно на плане продаж: цена, затраты и прибыль связаны одной формулой.
| A | B | |
|---|---|---|
| 1 | Цена, ₽ | 450 |
| 2 | Переменные затраты на единицу, ₽ | 270 |
| 3 | Постоянные затраты, ₽ | 81000 |
| 4 | Продажи, шт. | 500 |
| 5 | Прибыль, ₽ | =B4*(B1-B2)-B3 |
При 500 штуках формула в B5 показывает 9000. Команда с целевой ячейкой B5, значением 45000 и изменяемой ячейкой B4 подставит около 700 штук. Та же задача решается формулой =(45000+B3)/(B1-B2): она вернёт 700 и сама пересчитается, если изменится цена.
| Признак | Подбор параметра | Обратная формула |
|---|---|---|
| что остаётся в ячейке | найденное число | формула |
| новая цена или затраты | команду запускают заново | ответ обновляется сам |
| сложная зависимость, ставка внутри ПЛТ | находит ответ перебором | нужна готовая функция, например СТАВКА |
| сколько входов меняется | одно входное значение[1] | любое число ссылок в формуле |
Для кредита обратная функция уже есть: =СТАВКА(180;-900;100000)*12 вернёт ту же 0,0702 без запуска команды — подробнее о ней на странице о функциях ПЛТ, СТАВКА и КПЕР.
Остаётся понять, в каких задачах команда не поможет вовсе.
Когда подбор параметра не подойдёт
Подбор параметра в Excel не подойдёт, если в задаче больше одного неизвестного, есть условия на ответ или ответ должен обновляться сам. Ниже — признаки, по которым это видно заранее.
- Когда менять нужно две ячейки сразу, например цену и объём, команда второй адрес не примет: справка ограничивает её одним входным значением[1].
- Если ответ обязан быть целым или не больше заданного предела, команда этого не учтёт: в окне всего три поля, и места для условий там нет.
- При исходных данных, которые меняются каждую неделю, найденное число быстро устареет — для такого отчёта надёжнее обратная формула или функция СТАВКА.
- Когда работа идёт в Google Таблицах, нужно сначала установить дополнение Goal Seek, а его интерфейс доступен только на английском[3].
- Если изменяемая ячейка не участвует в формуле целевой, менять нечего — сначала свяжите ячейки ссылкой.
На Mac команда тоже есть: справка Microsoft относит команду к Microsoft 365, Excel 2024, Excel 2021, Excel 2019 и Excel 2016, а для трёх новейших — и к версиям для Mac[1]. Если подстановка оказалась ошибочной, верните прежнее значение сочетанием Ctrl+Z — по справке это отмена последнего действия[4]. После этого исправьте ссылки и запустите подбор параметра ещё раз.
Частые вопросы
Где находится подбор параметра в Excel?
Подбор параметра в Excel находится на вкладке «Данные»: нажмите «Анализ "что если"» и выберите «Подбор параметра»[1]. В английской справке тот же путь записан как Data → What-If Analysis → Goal Seek.
Может ли подбор параметра менять два значения сразу?
Нет: подбор параметра работает только с одним входным значением переменной[2]. Для двух и больше изменяемых ячеек справка Microsoft предлагает отдельную надстройку с несколькими переменными[1].
Почему подбор параметра не находит решение?
Решение не находится, когда изменяемая ячейка не входит в формулу целевой: менять оказывается нечего[1]. Проверьте и знак значения: для платежа справка вводит −900, потому что это выплата.
Что ставить в поле значения подбора: 900 или −900?
Значение для платежа по ПЛТ вводят со знаком минус, то есть −900. Функция возвращает выплату отрицательным числом, и справка Microsoft объясняет минус именно этим[1].
Есть ли подбор параметра в Google Таблицах?
Встроенной команды в Google Таблицах нет: справка Google советует установить дополнение Goal Seek[3]. Поля в нём называются Set cell, To value и By changing cell, а интерфейс доступен только на английском.
Источники
- [1] Microsoft, справка «Use Goal Seek to find the result you want by adjusting an input value» (английская версия), 2026
- [2] Microsoft, справка «Введение в анализ "что если"», 2026
- [3] Google, справка «Find an unknown value with Goal Seek» (английская версия), 2026
- [4] Microsoft, справка «Сочетания клавиш в Excel», 2026
- [5] Microsoft, справка «Change formula recalculation, iteration, or precision in Excel» (английская версия), 2026
- [6] Редакция «Excel-тренажёра», проверка «подбор ставки кредита и плана продаж» в движке тренажёра, 2026