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

Дядя Влад

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

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

Функция поиска цели в Excel – это метод проб и ошибок, поэтому, если она не дает желаемого, Excel работает над улучшением этого значения до тех пор, пока это не произойдет.

Что такое формулы поиска целей в Excel?

Поиск цели в Excel по сути делится на три основных компонента:

  1. Установить ячейку : ячейка, которую вы хотите использовать в качестве цели.
  2. Ценность : ценность, которую вы хотите ставить своей целью.
  3. Путем изменения ячейки : ячейка, которую вы хотите изменить, чтобы достичь желаемого значения.

Если эти три параметра установлены, Excel попытается улучшить значение в ячейке, которую вы задали, изменяя ячейку до тех пор, пока ячейка, установленная в поле « Установить ячейку», не достигнет значения, определенного в поле « Значение» .

Если все это звучит запутанно, приведенные ниже примеры дадут вам хорошее представление о том, как работает Goal Seek.

Пример поиска цели 1

Например, предположим, что у вас есть две ячейки (A и B) и третья ячейка, которая вычисляет среднее из этих двух.

Теперь предположим, что у вас есть статическое значение для A, и вы хотите поднять средний уровень, изменив значение для B. Используя Goal Seek, вы можете вычислить, какое значение B даст желаемое среднее значение.

Связанный: Советы по экономии времени в Microsoft Excel

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

  1. В Excel щелкните ячейку C1 .
  2. В строке формул введите следующий код:
     =AVERAGE(A1:B1)

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

  3. В этом примере измените значение ячейки A1 на 16 .
  4. Выберите ячейку C1 , а затем с ленты перейдите на вкладку « Данные ».
  5. На вкладке «Данные» выберите « Анализ возможных вариантов», а затем « Поиск цели» . Это вызовет окно поиска цели.
  6. В окне Goal Seek установите для ячейки Set значение C1 . Это будет ваша ячейка цели. Выделив C1 перед открытием окна Goal Seek, вы автоматически установите его в ячейку Set .
  7. По стоимости, вставить значение цели , которую вы хотите. В этом примере мы хотим, чтобы наше среднее значение было 26, поэтому значение цели также будет 26.
  8. Наконец, в ячейке Изменяя выберите ячейку, которую вы хотите изменить, чтобы достичь цели. В этом примере это будет ячейка B2 .
  9. Нажмите OK, чтобы Goal Seek творит чудеса.

После того, как вы нажмете ОК, появится диалоговое окно, информирующее вас о том, что Goal Seek нашел решение.

Значение ячейки A1 изменится на решение, и это перезапишет предыдущие данные. Рекомендуется запустить Goal Seek на копии таблицы, чтобы не потерять исходные данные.

Связанный: Сумасшедшие формулы Excel, которые делают удивительные вещи

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

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

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

Используя анализ «что, если» и «поиск цели», вы можете рассчитать, сколько денег вам нужно иметь на своем счете, чтобы получать определенную сумму ежемесячных выплат по процентам.

В этом примере предположим, что мы хотим получать 350 долларов в месяц за счет выплаты процентов. Вот как это можно вычислить:

  1. В ячейке A1 введите Баланс .
  2. В ячейке A2 введите Годовая ставка .
  3. В ячейке A4 введите Ежемесячная прибыль .
  4. В ячейку B2 введите 4% .
  5. Выберите ячейку B4 и в строке формул введите формулу ниже:
     =B1*B2/12

    Месячная прибыль будет равна остатку на счете, умноженному на годовую ставку, а затем разделенному на количество месяцев в году, 12 месяцев.

  6. Перейдите на вкладку « Данные », нажмите « Анализ« что, если »», а затем выберите « Поиск цели» .
  7. В окне " Поиск цели" введите B4 в ячейку "Установить" .
  8. Введите 350 в ячейку « Кому» . (Это ежемесячная прибыль, которую вы хотите получать)
  9. Введите B1 в Изменяя ячейку . (Это изменит баланс на значение, которое будет приносить 350 долларов в месяц)
  10. Щелкните ОК . Появится диалоговое окно статуса поиска цели .

В диалоговом окне «Состояние поиска цели» вы увидите, что функция «Поиск цели» нашла решение в ячейке B4. В ячейке B1 вы можете увидеть решение, которое должно быть 105 000.

Требования к поиску цели

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

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

Ограничения поиска цели

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

  1. В ячейке A1 введите 3 .
  2. Выберите ячейку C1 , затем в строке формул введите формулу ниже:
     =1/(A1-5)

    Эта формула разделит единицу на A1-5, и если A1-5 окажется равным 0, результатом будет неопределенное значение.

  3. Перейдите на вкладку « Данные », нажмите « Анализ« что, если »» и затем выберите « Поиск цели» .
  4. В ячейке Set введите C1 .
  5. В поле «Кому» введите 1 .
  6. Наконец, в Путем изменения типа ячейки A1 . (Это изменит значение A1 на 1 в C1)
  7. Щелкните ОК .

Диалоговое окно Goal Seek Status сообщит вам, что, возможно, решение не было найдено, и вы можете дважды проверить, что значение A1 слишком низкое.

Теперь решение этой цели – просто иметь 6 в A1. Это даст 1/1, что равно 1. Но в какой-то момент во время процесса проб и улучшений Excel пробует 5 в A1, что дает 1/0, что не определено, убивая процесс.

Способ обойти эту проблему поиска цели состоит в том, чтобы иметь другое начальное значение, которое может обойти неопределенное в процессе испытаний и улучшений.

Например, если вы измените A1 на любое число больше 5, а затем повторите те же шаги для Goal Seek for 1, вы должны получить правильный результат.

Сделайте расчеты более эффективными с помощью Goal Seek

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