Как пользоваться инструментом «Подбор параметра» в Excel: пошаговое руководство

В статье разберём, для каких задач подходит инструмент, как его найти в русской и английской версии Excel, как заполнить три поля диалога на примере кредитного расчёта, и чем он отличается от надстройки «Поиск решения».

Что такое «Подбор параметра» в Excel и для чего он нужен

Подбор параметра в Excel — инструмент обратного расчёта, не путайте его с функцией (=ПЛТ) или формулой (=A1+B1): он живёт не в строке формул, а в меню вкладки «Данные».

Типичные сценарии применения:

• подобрать процентную ставку при фиксированном ежемесячном платеже по кредиту;
• найти корень уравнения, записанного в ячейку;
• рассчитать объём продаж для точки безубыточности;
• определить цену товара, при которой прибыль достигнет нужного уровня.

Механизм работы: Excel итерационно перебирает значения изменяемой ячейки, пока формула в целевой ячейке не даст заданный результат с точностью 0,001. Одна переменная — принципиальное ограничение инструмента.

Где находится «Подбор параметра» в Excel: путь в меню

Вкладка Данные → группа «Прогноз» → Анализ «Что если» → Подбор параметра. В английской версии Excel: Data → What-If Analysis → Goal Seek. Функциональность обеих версий идентична.

Группа «Анализ «Что если»» содержит три инструмента: Подбор параметра, Таблица данных и Диспетчер сценариев. «Поиск решения» (Solver) — отдельная надстройка, требует ручной активации через Файл → Параметры → Надстройки.

Если хотите освоить анализ данных в Excel системно — в рамках нацпроекта «Кадры» доступна программа Excel. Системный аналитик: моделирование данных в табличных процессорах (72 ч, бесплатно для участников).

Пошаговая инструкция: как сделать подбор параметра в Excel

Инструмент работает в четыре шага. Главное требование: в целевой ячейке должна стоять формула, которая прямо или косвенно ссылается на изменяемую ячейку.

Шаг 1. Создайте таблицу с исходными данными и формулой

Введите известные данные: B1 = 100 000 ₽ (сумма займа), B2 = 180 (срок в месяцах). Ячейку B3 оставьте пустой — это изменяемая ячейка для ставки.

В целевую ячейку B4 введите функцию ПЛТ (PMT): =ПЛТ(B3/12;B2;B1). Пока B3 пустая, Excel принимает ставку за 0 и вернёт ≈ −556 ₽. Это временный результат: формула ссылается на B3, а значит, у инструмента есть точка опоры для итерационного подбора.

Шаг 2. Откройте диалоговое окно инструмента

Данные → Анализ «Что если» → Подбор параметра. Откроется диалоговое окно с тремя полями. Кнопка расположена в группе «Прогноз» на вкладке «Данные».

Шаг 3. Заполните три поля и нажмите ОК

• «Установить в ячейке»: B4 — ячейка с формулой ПЛТ (целевая ячейка).
• «Значение»: −900 — желаемый ежемесячный платёж (отрицательное число, потому что платёж — это отток средств).
• «Изменяя значение ячейки»: B3 — пустая ячейка ставки.

Нажмите ОК. Excel запустит итерационные вычисления и запишет найденное значение в ячейку B3.

Шаг 4. Оцените результат и настройте точность

В ячейке B3 появится число около 0,109. Чтобы отобразить ставку в привычном виде, отформатируйте ячейку: Главная → Число → Процент. Результат — ≈10,9% годовых.

Точность вычислений по умолчанию — 0,001. Для задач, где нужна большая точность, уменьшите погрешность: Файл → Параметры → Формулы → Относительная погрешность. При нелинейных уравнениях итоговый результат зависит от начального приближения в изменяемой ячейке: разные стартовые значения могут давать разные корни.

Подбор параметра или Поиск решения: что выбрать

Оба инструмента ищут входное значение по известному результату, но возможности принципиально разные.

Подбор параметра — быстрое решение для одного неизвестного без условий. Если переменных несколько или нужны ограничения — «Поиск решения» (Solver).

Часто задаваемые вопросы

Подбор параметра — это функция или формула Excel?

Ни то ни другое. Это инструмент из меню: Данные → Анализ «Что если». Он опирается на готовую формулу в ячейке, но сам формулой не является.

Как найти инструмент в русской и английской версии Excel?

Русская: Данные → Анализ «Что если» → Подбор параметра. Английская: Data → What-If Analysis → Goal Seek. Функциональность одинакова.

Можно ли подобрать значение сразу для нескольких переменных?

Нет, строго одна изменяемая ячейка. Для двух и более параметров одновременно используйте надстройку «Поиск решения».

Как использовать подбор параметра для решения уравнений?

Введите уравнение (пример: =A1^3−6*A1^2+11*A1−6) в ячейку B1. Установите B1 = 0, изменяя A1. Чтобы найти другой корень, задайте иное начальное значение в A1 и повторите подбор.

Почему подбор параметра не сходится?

Три причины: формула в целевой ячейке не ссылается на изменяемую; неверное начальное приближение; не включены итерационные вычисления (Файл → Параметры → Формулы).

Как повысить точность вычислений инструмента?

Файл → Параметры → Формулы → «Включить итеративные вычисления» → уменьшите «Относительная погрешность» с 0,001 до нужного значения.

Как активировать надстройку «Поиск решения»?

Файл → Параметры → Надстройки → Управление: Надстройки Excel → Перейти → отметьте «Поиск решения» → ОК. Кнопка появится на вкладке «Данные».

Что входит в группу «Анализ «Что если»» в Excel?

Три инструмента: Подбор параметра (1 переменная), Таблица данных (анализ сценариев), Диспетчер сценариев (сохранение вариантов). «Поиск решения» — смежная, но отдельная надстройка.