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

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

Например, предположим, что вы набрали в общей сложности 75 баллов по предмету, и вам нужно как минимум 90, чтобы получить оценку S по этому предмету. К счастью, у вас есть последний тест, который может помочь вам повысить свой средний балл. Вы можете использовать Goal Seek, чтобы выяснить, сколько очков вам нужно на последнем тесте, чтобы получить оценку S.

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

Компоненты функции поиска цели

Функция поиска цели состоит из трех параметров, а именно:

  • Установить ячейку - Это ячейка, в которую вводится формула. Он указывает ячейку, в которой вы хотите получить желаемый результат.
  • Оценивать - Это целевое / желаемое значение, которое вы хотите получить в результате операции поиска цели.
  • Изменяя ячейку - Указывает ячейку, значение которой необходимо отрегулировать, чтобы получить желаемый результат.

Как использовать поиск цели в Excel: пример 1

Чтобы продемонстрировать, как это работает на простом примере, представьте, что вы управляете фруктовым киоском. На приведенном ниже снимке экрана в ячейке B11 (ниже) отображается, сколько вам стоит покупка фруктов для вашего киоска, а в ячейке B12 отображается ваш общий доход от продажи этих фруктов. А ячейка B13 показывает процент вашей прибыли (20%).

Если вы хотите увеличить свою прибыль до «30%», но не можете увеличить свои вложения, вы должны увеличить свой доход, чтобы получить прибыль. Но насколько? Поиск цели поможет вам ее найти.

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

= (B12-B11) / B12

Теперь вы хотите рассчитать размер прибыли, чтобы ваш процент прибыли составлял 30%. Вы можете сделать это с помощью Goal Seek в Excel.

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

Затем перейдите на вкладку «Данные», нажмите кнопку «Что, если анализ» в группе «Прогноз» и выберите «Поиск цели».

Появится диалоговое окно Goal Seek с 3 полями:

  • Установить ячейку - Введите ссылку на ячейку, содержащую формулу (B13). Это ячейка, которая будет иметь желаемый результат.
  • Оценивать - Введите желаемый результат, которого вы пытаетесь достичь (30%).
  • Изменяя ячейку - Вставьте ссылку на ячейку для входного значения, которое вы хотите изменить (B12), чтобы получить желаемый результат. Просто щелкните ячейку или введите ссылку на ячейку вручную. Когда вы выбираете ячейку, Excel добавит знак «$» перед буквой столбца и номером строки, чтобы сделать ячейку абсолютной.

Когда вы закончите, нажмите «ОК», чтобы проверить это.

Затем появится диалоговое окно «Статус поиска цели», в котором сообщается, было ли найдено какое-либо решение, как показано ниже. Если решение было найдено, входное значение в «изменяющейся ячейке (B12)» будет скорректировано на новое значение. Это то, что мы хотим. Итак, в этом примере анализ показал, что для достижения цели 30% прибыли вам необходимо получить доход в размере 1635,5 долларов США (B12).

Нажмите «ОК», и Excel изменит значения ячеек, или нажмите «Отмена», чтобы отменить решение и восстановить исходное значение.

Входное значение (1635,5) в ячейке B12 - это то, что мы обнаружили с помощью Goal Seek для достижения нашей цели (30%).

Что следует помнить при использовании функции поиска цели

  • Set Cell всегда должен содержать формулу, зависящую от ячейки «By Changing Cell».
  • Вы можете использовать Goal Seek только для входного значения одной ячейки за раз
  • Поле «Путем изменения ячейки» должно содержать значение, а не формулу.
  • Если Goal Seek не может найти правильное решение, он показывает самое близкое значение, которое он может дать, и сообщает вам, что сообщение «Goal-Seeking, возможно, не нашло решения».

Что делать, если Excel Goal Seek не работает

Однако, если вы уверены, что решение существует, вы можете попробовать решить эту проблему несколькими способами.

Проверьте параметры и значения Goal Seek

Вы должны проверить две вещи: во-первых, проверьте, относится ли параметр «Установить ячейку» к ячейке формулы. Во-вторых, убедитесь, что ячейка формулы (Set cell) прямо или косвенно зависит от изменяющейся ячейки.

Настройка параметров итерации

В настройках Excel вы можете изменить количество возможных попыток, которые Excel может предпринять, чтобы найти правильное решение, а также его точность.

Чтобы изменить настройки расчета итераций, нажмите «Файл» в списке вкладок. Затем нажмите «Параметры» внизу.

В окне параметров Excel нажмите «Формулы» на левой панели.

В разделе «Параметры расчета» измените следующие настройки:

  • Максимальное количество итераций - указывает количество возможных решений, которые Excel рассчитает; чем выше число, тем больше итераций он может выполнить. Например, если вы установите для параметра «Максимальное количество итераций» значение 150, Excel протестирует 150 возможных решений.
  • Максимальное изменение - указывает на точность результатов; меньшее число дает более высокую точность. Например, если значение вашей входной ячейки равно «0», но Goal Seek перестает вычисляться при «0,001», изменение этого значения на «0,0001» должно решить проблему.

Увеличьте значение «Максимальное количество итераций», если вы хотите, чтобы Excel протестировал больше возможных решений, и уменьшите значение «Максимальное изменение», если вы хотите получить более точный результат.

На снимке экрана ниже показано значение по умолчанию:

Нет циркулярных ссылок

Когда формула ссылается на собственную ячейку, это называется циклической ссылкой. Если вы хотите, чтобы программа Excel Goal Seek работала правильно, в формулах не следует использовать циклическую ссылку.

Пример 2 поиска цели в Excel

Допустим, вы одалживаете у кого-то деньги в размере "25000 долларов". Они ссужают вам деньги под процентную ставку 7% в месяц сроком на 20 месяцев, что составляет «1327 долларов США» в месяц. Но вы можете позволить себе платить только 1000 долларов в месяц в течение 20 месяцев под 7% годовых. Goal Seek может помочь вам найти сумму кредита, при которой ежемесячный платеж (EMI) составляет «1000 долларов США».

Введите три входные переменные, которые вам понадобятся для расчета вашего PMT, то есть процентная ставка 7%, срок 20 месяцев и основная сумма 25000 долларов США.

Введите следующую формулу PMT в ячейку B5, которая даст вам сумму EMI в размере 1327,97 долларов США.

Синтаксис функции PMT:

= PMT (процентная ставка / 12, срок, основная сумма)

Формула:

= PMT (B3 / 12; B4; -B2)

Выделите ячейку B5 (ячейка формулы) и перейдите в Data -> What If Analysis -> Goal Seek.

Используйте эти параметры в окне «Поиск цели»:

  • Установить ячейку - B5 (ячейка, содержащая формулу для расчета EMI)
  • Оценивать - 1000 (результат формулы / цель, которую вы ищете)
  • Изменяя ячейку - B2 (это сумма кредита, которую вы хотите изменить для достижения цели)

Затем нажмите «ОК», чтобы сохранить найденное решение, или «Отмена», чтобы отменить его.

Анализ показывает, что максимальная сумма денег, которую вы можете занять, составляет 18825 долларов, если вы хотите превысить свой бюджет.

Вот как вы используете Goal Seek в Excel.