Goal Seek, часть набора инструментов Excel для анализа «что, если», представляет собой мощную функцию, используемую для обратных вычислений. По сути, если вы знаете желаемый результат формулы, но не уверены, какое входное значение приведет к этому результату, вы можете использовать поиск цели, чтобы найти его. Независимо от того, являетесь ли вы финансовым аналитиком, студентом или владельцем бизнеса, понимание того, как использовать Goal Seek, может значительно упростить процесс решения проблем. В этом руководстве мы рассмотрим, что такое Goal Seek, как получить к нему доступ, и продемонстрируем его практическое применение на различных примерах: от корректировки планов погашения кредита до расчета минимальных баллов на экзаменах и многого другого.

• Пример 1: Корректировка плана погашения кредита
• Пример 2: Определение минимального балла для сдачи экзамена
• Пример 3: Расчет необходимого количества голосов для победы на выборах
• Пример 4: Достижение целевой прибыли в бизнесе

Что такое поиск цели в Excel?

Поиск цели является неотъемлемой функцией Microsoft Excel, частью набора инструментов анализа «что, если». Он позволяет пользователям выполнять обратные вычисления — вместо вычисления результата заданных входных данных вы указываете желаемый результат, а Excel корректирует входное значение для достижения этой цели. Эта функция особенно полезна в сценариях, где вам нужно найти входное значение, которое приведет к определенному результату.

• Регулировка одной переменной:
  Поиск цели может одновременно изменять только одно значение входной ячейки.
• Требуется формула:
  При применении функции поиска цели целевая ячейка должна содержать формулу.
• Сохраняйте целостность формулы:
  Поиск цели не изменяет формулу в целевой ячейке; он изменяет значение во входной ячейке, от которой зависит формула.

Доступ к поиску цели в Excel

Чтобы получить доступ к функции поиска цели, перейдите в Данные вкладку нажмите Что-Анализ > Поиск цели. Смотрите скриншот:

Тогда Поиск цели появится диалоговое окно. Вот объяснение трех опций в диалоговом окне «Поиск цели» Excel:

• Установить ячейку: целевая ячейка, содержащая формулу, которую вы хотите получить. Эта ячейка должна содержать формулу, и поиск цели скорректирует значения в другой связанной ячейке, чтобы значение этой ячейки достигло установленной цели.
• Оценивать: Целевое значение, которое вы хотите "Установить ячейку" достигать.
• Путем изменения ячейки: ячейка ввода, которую вы хотите настроить при поиске цели. Значение в этой ячейке будет изменено, чтобы обеспечить значение в "Установить ячейку"встречается"Оценивать" цель.
  
  

В следующем разделе мы рассмотрим, как использовать функцию поиска цели Excel, на подробных примерах.

Использование поиска цели на примерах

В этом разделе мы рассмотрим четыре распространенных примера, демонстрирующих полезность поиска цели. Эти примеры будут варьироваться от корректировок личных финансов до стратегических бизнес-решений, предлагая понимание того, как этот инструмент может применяться в различных реальных сценариях. Каждый пример предназначен для того, чтобы дать вам более четкое представление о том, как эффективно использовать поиск цели для решения различных типов проблем.

Пример 1: Корректировка плана погашения кредита

Сценарий: Человек планирует взять кредит в размере 20,000 5 долларов США и намерен погасить его в течение 5 лет с годовой процентной ставкой 377.42%. Требуемый ежемесячный платеж составляет $500. Позже он понимает, что может увеличить ежемесячный платеж на XNUMX долларов. Учитывая, что общая сумма кредита и годовая процентная ставка остаются прежними, сколько лет теперь потребуется для погашения кредита?

Как показано в следующей таблице:

• B1 содержит сумму кредита 20000 долларов США.
• B2 содержит срок кредита 5 (лет).
• B3 содержит годовую процентную ставку 5%.
• B5 содержит ежемесячный платеж, который рассчитывается по формуле =PMT(B3/12,B2*12,B1).
  
  

Здесь я покажу вам, как использовать функцию поиска цели для выполнения этой задачи.

Шаг 1. Активируйте функцию поиска цели.

Перейдите в Данные вкладку нажмите Что-Анализ > Поиск цели.

Шаг 2. Настройте параметры поиска цели

1. В Установить ячейку выберите формулу B5.
2. В Ценить, оценивать Введите ежемесячную запланированную сумму погашения -500 (отрицательное число соответствует платежу).
3. В Изменяя ячейку выберите ячейку B2, которую хотите изменить.
4. Нажмите OK.
   
   

Результат

Ассоциация Статус поиска цели Появится диалоговое окно, показывающее, что решение найдено. При этом значение в ячейке B2, указанное вами в поле «Изменив ячейку», будет заменено новым значением, а ячейка формулы получит целевое значение на основе изменения переменной. Нажмите OK применить изменения

Теперь на погашение кредита ему понадобится около 3.7 лет.

Пример 2: Определение минимального балла для сдачи экзамена

Сценарий: Студенту необходимо сдать 5 экзаменов, каждый с одинаковым весом. Чтобы сдать экзамен, студент должен набрать средний балл 70% по всем экзаменам. Сдав первые 4 экзамена и зная свои баллы, студенту теперь необходимо рассчитать минимальный балл, необходимый для пятого экзамена, чтобы общий средний балл достиг или превысил 70%.

Как показано на скриншоте ниже:

• B1 содержит оценку первого экзамена.
• B2 содержит оценку второго экзамена.
• B3 содержит оценку третьего экзамена.
• B4 содержит оценку четвертого экзамена.
• B5 будет содержать оценку пятого экзамена.
• B7 – проходной средний балл, который рассчитывается по формуле =(B1+B2+B3+B4+B5)/5.
  
  

Для достижения этой цели вы можете применить функцию поиска цели следующим образом:

Шаг 1. Активируйте функцию поиска цели.

Перейдите в Данные вкладку нажмите Что-Анализ > Поиск цели.

Шаг 2. Настройте параметры поиска цели

1. В Установить ячейку выберите ячейку формулы B7.
2. В Ценить, оценивать поле введите проходной средний балл 70%.
3. В Изменяя ячейку выберите ячейку (B5), которую хотите изменить.
4. Нажмите OK.
   
   

Результат

Ассоциация Статус поиска цели Появится диалоговое окно, показывающее, что решение найдено. При этом в ячейку B7, которую вы указали в поле «Изменив ячейку», будет автоматически введено соответствующее значение, а ячейка формулы получит целевое значение на основе изменения переменной. Нажмите OK отказаться от решения.

Итак, чтобы достичь необходимого общего среднего и сдать все экзамены, студенту необходимо набрать не менее 71% на последнем экзамене.

Пример 3: Расчет необходимого количества голосов для победы на выборах

Сценарий: На выборах кандидату необходимо подсчитать минимальное количество голосов, необходимое для получения большинства. Если известно общее количество поданных голосов, какое минимальное количество голосов должен получить кандидат, чтобы набрать более 50% и победить?

Как показано на скриншоте ниже:

• Ячейка B2 содержит общее количество голосов, поданных на выборах.
• Ячейка B3 показывает текущее количество голосов, полученных кандидатом.
• Ячейка B4 содержит желаемое большинство (%) голосов, которое кандидат стремится получить, которое рассчитывается по формуле =B2/B1.
  
  

Для достижения этой цели вы можете применить функцию поиска цели следующим образом:

Шаг 1. Активируйте функцию поиска цели.

Перейдите в Данные вкладку нажмите Что-Анализ > Поиск цели.

Шаг 2. Настройте параметры поиска цели

1. В Установить ячейку выберите ячейку формулы B4.
2. В Ценить, оценивать введите желаемый процент большинства голосов. В этом случае, чтобы победить на выборах, кандидат должен получить более половины (т. е. более 50%) от общего числа действительных голосов, поэтому я ввожу 51%.
3. В Изменяя ячейку выберите ячейку (B2), которую хотите изменить.
4. Нажмите OK.
   
   

Результат

Ассоциация Статус поиска цели Появится диалоговое окно, показывающее, что решение найдено. При этом ячейка B2, которую вы указали в поле «Изменив ячейку», будет автоматически заменена новым значением, а ячейка формулы получит целевое значение на основе изменения переменной. Нажмите OK для внесения изменений.

Итак, для победы на выборах кандидату необходимо не менее 5100 голосов.

Пример 4: Достижение целевой прибыли в бизнесе

Сценарий: Компания стремится достичь конкретной цели по прибыли в размере 150,000 XNUMX долларов США в текущем году. Учитывая как постоянные, так и переменные затраты, необходимо определить необходимый доход от продаж. Сколько единиц продаж необходимо для достижения этой цели по прибыли?

Как показано на скриншоте ниже:

• B1 содержит постоянные затраты.
• B2 содержит переменные затраты на единицу продукции.
• B3 содержит цену за единицу.
• B4 содержит проданные единицы.
• B6 — общий доход, который рассчитывается по формуле =B3*B4.
• B7 – общая стоимость, которая рассчитывается по формуле =B1+(B2*B4).
• В9 – текущая прибыль от продаж, которая рассчитывается по формуле =В6-В7.
  
  

Чтобы получить общее количество единиц продаж на основе целевой прибыли, вы можете применить функцию поиска цели следующим образом.

Шаг 1. Активируйте функцию поиска цели.

Перейдите в Данные вкладку нажмите Что-Анализ > Поиск цели.

Шаг 2. Настройте параметры поиска цели

1. В Установить ячейку выберите ячейку формулы, которая возвращает прибыль. Вот ячейка B9.
2. В Ценить, оценивать поле введите целевую прибыль 150000.
3. В Изменяя ячейку выберите ячейку (B4), которую хотите изменить.
4. Нажмите OK.
   
   

Результат

После этого появится диалоговое окно «Статус поиска цели», показывающее, что решение найдено. При этом ячейка B4, которую вы указали в поле «Изменив ячейку», будет автоматически заменена новым значением, а ячейка формулы получит целевое значение на основе изменения переменной. Нажмите OK принять изменения.

В результате компании необходимо продать не менее 6666 единиц, чтобы получить целевую прибыль в размере 150,000 XNUMX долларов.

Распространенные проблемы и решения для поиска цели

В этом разделе перечислены некоторые распространенные проблемы и соответствующие решения для поиска цели.

1. Поиск цели не может найти решение

• Причина: Обычно это происходит, когда целевое значение недостижимо с любым возможным входным значением или если первоначальное предположение слишком далеко от любого потенциального решения.
• Решения: Отрегулируйте целевое значение, чтобы убедиться, что оно находится в допустимом диапазоне. Кроме того, попробуйте разные первоначальные предположения, более близкие к ожидаемому решению. Убедитесь, что формула в «Задать ячейку» верна и может логически создать желаемое «Значение».

2. Медленная скорость вычислений.

• Причина: поиск цели может быть медленным при использовании больших наборов данных, сложных формул или когда первоначальное предположение далеко от решения, что требует большего количества итераций.
• Решения: Упростите формулы, где это возможно, и уменьшите размер данных. Убедитесь, что первоначальное предположение как можно ближе к ожидаемому решению, чтобы уменьшить количество необходимых итераций.

3. Проблемы с точностью

• Причина: При использовании поиска цели вы можете заметить, что иногда Excel сообщает, что нашел решение, но это не совсем то, что вам нужно — близко, но недостаточно близко. Поиск цели не всегда может дать очень точный результат из-за точности вычислений и округления Excel.
  
  Например, чтобы вычислить конкретное возведение числа в степень и использовать поиск цели, чтобы найти основание, обеспечивающее желаемый результат. Здесь я изменю входное значение в ячейке A1 (база) так, чтобы ячейка A3 (результат возведения в степень) соответствовала целевому результату 25.
  
  Как вы можете видеть на скриншоте ниже, Goal Seek предоставляет приблизительное значение, а не точный корень.
  
• Решения: увеличьте количество десятичных знаков, отображаемых в параметрах вычислений Excel, для большей точности, а затем повторно запустите функцию поиска цели. Пожалуйста, сделайте следующее.
  
  1. Нажмите Файл > Опции для открытия Параметры Excel окно.
  2. Выберите Формулы на левой панели и в Варианты расчета разделе увеличьте количество десятичных знаков в Максимальное изменение коробка. Здесь я меняю 0.001 в 0.000000001 и нажмите OK.
     
  3. Перезапустите Goal Seek, и вы получите точный корень, как показано на скриншоте ниже.
     

4. Ограничения и альтернативные методы

• Причина: Поиск цели может корректировать только одно входное значение и может не подходить для уравнений, требующих одновременной корректировки нескольких переменных.
• Решения: для сценариев, требующих корректировки нескольких переменных, используйте Solver Excel, который является более мощным и позволяет устанавливать ограничения.

Goal Seek — это мощный инструмент для выполнения обратных вычислений, избавляющий вас от утомительной задачи ручного тестирования различных значений для приближения к вашей цели. Вооружившись знаниями из этой статьи, вы теперь можете с уверенностью применять Goal Seek, поднимая анализ данных и эффективность на новую высоту. Для тех, кто хочет глубже изучить возможности Excel, наш веб-сайт может похвастаться множеством учебных пособий. Дополнительные советы и рекомендации по работе с Excel можно найти здесь..