Goal Seek в Excel — как найти вход по заданному результату

Что такое Goal Seek и когда он полезен

Goal Seek — это инструмент “Что‑если” (What‑If Analysis) в Excel, который автоматически изменяет одно входное значение до тех пор, пока целевая ячейка не покажет требуемый результат. Проще: вы задаёте желаемый результат, Excel подбирает значение, которое даёт этот результат, пробуя и улучшая предположения.

Краткое определение: Goal Seek — итеративный метод обратного подбора одного входного параметра для достижения заданного вывода формулы.

Важно: Goal Seek может менять только одну ячейку за запуск. Если формула зависит от нескольких неизвестных, используйте другие инструменты (например, Solver) или алгебраический подход.

Компоненты Goal Seek

Goal Seek работает с тремя полями:

• Set cell — ячейка, содержащая формулу, результат которой вы хотите зафиксировать.
• To value — требуемое значение результата для ячейки Set cell.
• By changing cell — ячейка, которую Excel будет менять, чтобы достигнуть To value.

Если всё настроено верно, Excel последовательно меняет By changing cell и проверяет Set cell, пока значение не станет близким к To value.

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

Пример 1 — простой случай со средним значением

Сценарий: у вас две ячейки A1 и B1, а в C1 стоит формула среднего. Установим A1 статично и хотим изменить B1, чтобы среднее стало заданным.

1. Выделите ячейку C1 и вставьте формулу:

=AVERAGE(A1:B1)

2. Задайте A1 = 16.
3. Перейдите: вкладка Data (в русской версии — «Данные») → What‑If Analysis → Goal Seek.
4. В окне Goal Seek установите:
   • Set cell: C1
   • To value: 26
   • By changing cell: B1
5. Нажмите OK. Excel выполнит итерации и подберёт значение B1.

Совет: перед тестом работоспособности формулы просто введите числа в A1 и B1 и проверьте, что C1 показывает ожидаемое среднее.

Пример 2 — банковский расчёт ежемесячного дохода

Задача: при годовой ставке 4% узнать, какая сумма на счёте нужна, чтобы ежемесячный процентный доход составлял 350 $.

Шаги:

1. A1 — Balance (Баланс)
2. A2 — Annual Rate (Годовая ставка)
3. A4 — Monthly Profit (Ежемесячный доход)
4. В B2 введите 4% (или 0,04)
5. В B4 введите формулу:

=B1*B2/12

6. Данные → What‑If Analysis → Goal Seek
7. Set cell: B4
8. To value: 350
9. By changing cell: B1
10. OK — Goal Seek подберёт баланс, который даёт 350 в месяц.

В этом примере Goal Seek вернёт число, близкое к 105000 — это баланс, дающий примерно 350 $ в месяц при 4% годовых.

Ограничения и когда Goal Seek может не сработать

Goal Seek — итеративный метод проб и улучшений. Он может столкнуться с проблемами:

• Наличие разрывов или неопределённостей в функции (деление на ноль, логарифм отрицательного числа и др.).
• Наличие нескольких решений — Goal Seek найдёт одно из них, зависящее от начальной точки.
• Много переменных — Goal Seek управляет только одной ячейкой; система с несколькими неизвестными потребует Solver или аналитического решения.
• Негладкая, немонотонная функция — метод может «зациклиться» на локальном решении или не дойти до нужного значения.

Иллюстрация: формула

=1/(A1-5)

Если вы попытаетесь подобрать A1, чтобы значение стало 1, Goal Seek в процессе проб может подставить A1 = 5, что приводит к делению на ноль и прерыванию поиска. Решение — выбрать стартовое значение, которое избегает точки разрыва (например, начать с A1 > 5).

Альтернативы и когда выбирать другой инструмент

• Solver (надстройка Excel) — подходит, если нужно менять несколько переменных, задать ограничения (неотрицательность, целочисленность) или оптимизировать целевую функцию.
• Таблица данных (Data Table) — полезна, если нужно посмотреть результаты при наборе входных значений (массовый анализ), а не одно решение.
• Алгебраический подход — если формула обратима, часто проще выразить требуемую переменную аналитически (вручную или с помощью алгебраического преобразования).

Выбор:

• Одна переменная, простая формула → Goal Seek.
• Несколько переменных/ограничений → Solver.
• Анализ набора вариантов → Data Table.

Ментальная модель (heuristic)

Думайте о Goal Seek как о простом корне‑поисковом алгоритме: вы ищете x такое, что f(x)=target. Goal Seek делает шаги, оценивает f(x) и корректирует x; если функция гладкая и монотонная в окрестности решения — поиск обычно успешен.

Быстрый справочник и шаблоны (cheat sheet)

• Формула среднего:

=AVERAGE(A1:B1)

• Формула месячного дохода от баланса:

=B1*B2/12

• Типичный путь к Goal Seek (англ. Excel): Data → What‑If Analysis → Goal Seek.
• Рекомендация: всегда работайте с копией листа или создавайте резервную колонку перед запуском.

Пошаговый SOP перед запуском Goal Seek

1. Сохраните резервную копию файла или сделайте копию листа.
2. Убедитесь, что Set cell содержит формулу (не константу).
3. Проверьте, не приводит ли промежуточное значение к ошибке (#DIV/0!, #NUM!, #VALUE!).
4. Выберите стартовую точку By changing cell близкой к ожидаемому решению.
5. Запустите Goal Seek и проверьте результат.
6. Если результат неверен или Goal Seek не нашёл решение — попробуйте другой старт, аналитический подход или Solver.

Роль‑ориентированные чек‑листы

• Для аналитика данных:

  • Проверить типы данных и наличие формул.
  • Подготовить диаграмму чувствительности при необходимости.

• Для бухгалтера:

  • Убедиться в корректности процентных ставок и периодов (год/месяц).
  • Зафиксировать исходные данные и итог в отчёте.

• Для разработчика/автоматизатора:

  • Автоматизировать запуск через макрос (VBA) или Power Query, если требуется многократный подбор.

Критерии приёмки (тесты)

• Функциональный тест: при исходных данных Goal Seek должен вернуть значение, при подстановке которого Set cell ≈ To value (с учётом ожидаемой точности).
• Стабильность: несколько запусков с близкими стартовыми значениями должны давать одно и то же решение для монотонной функции.
• Безопасность данных: исходные данные не должны быть перезаписаны без резервной копии.

Сравнение: Goal Seek vs Solver vs Data Table

Decision flowchart (Mermaid)

flowchart TD
  A[Нужен подбор входного значения?] --> B{Сколько переменных нужно менять?}
  B -- 1 --> C[Goal Seek]
  B -- >1 --> D[Solver]
  C --> E{Функция гладкая и нет разрывов?}
  E -- Да --> F[Запустить Goal Seek]
  E -- Нет --> G[Изменить старт/использовать аналитическое решение]
  D --> H[Настроить ограничения и запустить Solver]

Факты и числовая памятка

• Goal Seek меняет только одну ячейку за запуск.
• Goal Seek использует итерации; точность зависит от формулы и начального приближения.
• Для массового анализа входов используйте Таблицу данных.

Риски и меры снижения

• Риск: потеря исходных данных. Митигирование: сохраняйте резервную копию.
• Риск: попадание в точку разрыва (деление на ноль). Митигирование: проверьте область определения и задайте стартовую точку вне опасной зоны.
• Риск: локальное решение при множественных корнях. Митигирование: попробуйте разные стартовые точки; если нужно — аналитика или Solver.

Краткий глоссарий (1 строка)

• Set cell — целевая ячейка с формулой; To value — требуемый результат; By changing cell — изменяемая входная ячейка.

Частые вопросы

Можно ли отменить изменения, которые делает Goal Seek?

Да — перед запуском сохраните файл или сделайте копию листа. Если вы уже применили изменения, используйте Отмена (Ctrl+Z) или восстановите копию.

Можно ли запускать Goal Seek автоматически через макрос?

Да. Goal Seek доступен в VBA через метод Range.GoalSeek. Это удобно для пакетной автоматизации простых подборов.

Что делать, если Goal Seek не находит решение?

Попробуйте другой начальный параметр, проверьте область определения формулы, либо используйте Solver/аналитический метод.

Вывод

Goal Seek — простой и эффективный инструмент для обратного подбора одного параметра в Excel. Он особенно полезен для быстрых расчётов и простых сценариев (финансовые прикидки, планирование, калибровка показателей). Для более сложных задач используйте Solver или аналитические методы.