Как использовать поиск цели в Excel для анализа «что, если»

В этом руководстве объясняется, как использовать поиск цели в Excel 365–2010, чтобы получить нужный результат формулы, изменив входное значение.

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

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

Goal Seek — это встроенный в Excel инструмент анализа «что, если», который показывает, как одно значение в формуле влияет на другое. Точнее, он определяет, какое значение вы должны ввести в ячейку ввода, чтобы получить желаемый результат в ячейке формулы.

Самое лучшее в Excel Goal Seek — это то, что он выполняет все расчеты за кулисами, и вас просят указать только эти три параметра:

• Ячейка формулы
• Целевое/желаемое значение
• Ячейка, которую нужно изменить, чтобы достичь цели

Инструмент Goal Seek особенно полезен для проведения анализа чувствительности в финансовом моделировании и широко используется специалистами в области управления и владельцами бизнеса. Но есть много других применений, которые могут оказаться полезными для вас.

Например, Goal Seek может сказать вам, сколько продаж вы должны сделать за определенный период, чтобы достичь годовой чистой прибыли в размере 100 000 долларов (пример 1). Или какой балл вы должны набрать на последнем экзамене, чтобы получить общий проходной балл 70% (пример 2). Или, сколько голосов нужно набрать, чтобы победить на выборах (пример 3).

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

Примечание. Goal Seek может обрабатывать только одно входное значение за раз. Если вы работаете над расширенной бизнес-моделью с несколькими входными значениями, используйте надстройку «Поиск решения», чтобы найти оптимальное решение.

Как использовать поиск цели в Excel

Цель этого раздела — показать вам, как использовать функцию поиска цели. Итак, мы будем работать с очень простым набором данных:

В приведенной выше таблице показано, что если вы продадите 100 товаров по 5 долларов за штуку за вычетом 10% комиссии, вы заработаете 450 долларов. Вопрос в следующем: сколько товаров вам нужно продать, чтобы заработать 1000 долларов?

Давайте посмотрим, как найти ответ с помощью Goal Seek:

1. Настройте свои данные так, чтобы у вас ячейка формулы и изменение клетка зависит от ячейки формулы.
2. Перейти к Данные вкладка > Прогноз нажмите кнопку «Что, если анализ» и выберите «Поиск цели…».
   
3. в поиск цели диалоговое окно, определите ячейки/значения для проверки и щелкните ХОРОШО:
   • Задать ячейку – ссылка на ячейку, содержащую формулу (B5).
   • В значение – результат формулы, которого вы пытаетесь достичь (1000).
   • Изменив ячейку – ссылка на входную ячейку, которую вы хотите настроить (B3).

   

4. поиск цели Статус появится диалоговое окно и сообщит, найдено ли решение. Если это удалось, значение в «изменяющейся ячейке» будет заменено новым. Нажмите ХОРОШО сохранить новое значение или Отмена чтобы восстановить исходный.

   В этом примере Goal Seek обнаружил, что необходимо продать 223 товара (с округлением до следующего целого числа), чтобы получить доход в 1000 долларов.
   

Если вы не уверены, что сможете продать столько товаров, то, возможно, вы сможете достичь целевого дохода, изменив цену товара? Чтобы протестировать этот сценарий, выполните анализ поиска цели точно так, как описано выше, за исключением того, что вы укажете другой Изменение ячейки (БИ 2):

В результате вы обнаружите, что если вы увеличите цену за единицу до 11 долларов, вы можете достичь дохода в 1000 долларов, продав всего 100 товаров:

Советы и примечания:

• Excel Goal Seek не изменяет формулу, а только изменяет входное значение, которое вы вводите в Сменив ячейку коробка.
• Если Goal Seek не может найти решение, он отображает ближайшее найденное значение.
• Вы можете восстановить исходное входное значение, щелкнув значок Отменить или нажатием клавиши «Отменить» (Ctrl + Z).

Примеры использования поиска цели в Excel

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

Пример 1: Достичь цели по прибыли

Проблема: Это типичная деловая ситуация — у вас есть данные о продажах за первые 3 квартала, и вы хотите знать, сколько продаж вы должны сделать в последнем квартале, чтобы достичь целевой чистой прибыли за год, скажем, 100 000 долларов.

Решение: Организовав исходные данные, как показано на снимке экрана выше, настройте следующие параметры для функции поиска цели:

• Установить ячейку – формула расчета общей чистой прибыли (D6).
• Ценить, оценивать – результат формулы, который вы ищете (100 000 долларов США).
• Сменив ячейку – ячейка, содержащая валовой доход за 4 квартал (B5).

Результат: Анализ поиска цели показывает, что для получения годовой чистой прибыли в размере 100 000 долларов США ваш доход в четвертом квартале должен составить 185 714 долларов США.

Пример 2: определение проходного балла на экзамене

Проблема: В конце курса студент сдает 3 экзамена. Проходной балл составляет 70%. Все экзамены имеют одинаковый вес, поэтому общий балл рассчитывается путем усреднения 3 баллов. Студент уже сдал 2 из 3 экзаменов. Вопрос: какой балл должен набрать студент на третьем экзамене, чтобы сдать весь курс?

Решение: Выполним Goal Seek, чтобы определить минимальный балл на экзамене 3:

• Установить ячейку – формула, усредняющая баллы за 3 экзамена (В5).
• Ценить, оценивать – проходной балл (70%).
• Сменив ячейку – 3-й экзаменационный балл (В4).

Результат: Чтобы получить желаемый общий балл, студент должен набрать минимум 67% на последнем экзамене:

Пример 3: Анализ выборов по принципу «что, если»

Проблема: Вы баллотируетесь на выборную должность, где для победы на выборах требуется большинство в две трети (66,67% голосов). Предполагая, что всего 200 членов с правом голоса, сколько голосов вам нужно получить?

В настоящее время у вас есть 98 голосов, что неплохо, но недостаточно, потому что это составляет всего 49% от общего числа избирателей:

Решение: Используйте поиск цели, чтобы узнать минимальное количество голосов «за», которое вам нужно получить:

• Установить ячейку – формула, рассчитывающая процент текущих голосов «за» (С2).
• Ценить, оценивать – необходимый процент голосов «за» (66,67%).
• Сменив ячейку – количество голосов «за» (B2).

Результат: Анализ «что, если» с помощью Goal Seek показывает, что для достижения отметки в две трети или 66,67% вам нужно 133 голоса «Да»:

Поиск цели в Excel не работает

Иногда Goal Seek не может найти решение просто потому, что его не существует. В таких ситуациях Excel получит ближайшее значение и сообщит вам, что поиск цели, возможно, не нашел решения:

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

1. Дважды проверьте параметры поиска цели

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

2. Настройте параметры итерации

В Excel нажмите Файл > Опции > Формулы и измените эти параметры:

• Максимальное количество итераций – увеличьте это число, если хотите, чтобы Excel тестировал больше возможных решений.
• Максимальное изменение – уменьшите это число, если ваша формула требует большей точности. Например, если вы проверяете формулу с входной ячейкой, равной 0, но поиск цели останавливается на 0,001, установка Максимальное изменение до 0,0001 должно решить проблему.

На приведенном ниже снимке экрана показаны настройки итерации по умолчанию:

3. Никаких циклических ссылок

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

Вот как вы выполняете анализ «что, если» в Excel с помощью инструмента «Поиск цели». Я благодарю вас за чтение и надеюсь увидеть вас в нашем блоге на следующей неделе!

Вас также могут заинтересовать