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

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

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

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

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

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

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

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

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

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

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

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

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

где используется моя электронная почта

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

В Excel щелкните ячейку C1 .
В строке формул введите следующий код: =AVERAGE(A1:B1) Эта формула позволит Excel вычислить среднее значение в ячейках. A1 а также B1 и выведите это в ячейку C1 . Вы можете убедиться, что ваша формула работает, введя числа в A1 и B1. C1 должен показать свой средний результат.
В этом примере измените значение A1 ячейка в 16 .
Выберите ячейку C1 , а затем с ленты перейдите к Данные таб.
Под Вкладка данных , Выбрать Что-если анализ а потом Поиск цели . Откроется окно поиска цели.
В окне Goal Seek установите Установить ячейку к C1 . Это будет ваша ячейка цели. Выделив C1 перед открытием окна Goal Seek, вы автоматически установите его в Установить ячейку .
В Ценить , введите желаемую ценность цели. В этом примере мы хотим, чтобы наше среднее значение было 26, поэтому значение цели также будет 26.
Наконец, в Изменяя ячейку , выберите ячейку, которую вы хотите изменить, чтобы достичь цели. В этом примере это будет ячейка Би 2 .
Нажмите Ok чтобы Goal Seek творил чудеса.

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

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

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

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

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

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

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

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

что происходит с данными на твердотельном накопителе при отключении питания?

В камере A1 тип Остаток средств .
В камере A2 тип Годовая ставка .
В камере A4 тип Ежемесячная прибыль .
В камере Би 2 тип 4% .
Выберите ячейку B4 и в строке формул введите формулу ниже: =B1*B2/12 Месячная прибыль будет равна остатку на счете, умноженному на годовую ставку, а затем разделенному на количество месяцев в году, 12 месяцев.
Перейти к Данные вкладку, нажмите на Что-если анализ, а затем выберите Поиск цели .
в Поиск цели окно, тип B4 в Установить ячейку .
Введите 350 в Ценить клетка. (Это ежемесячная прибыль, которую вы хотите получать)
Тип B1 в Изменяя ячейку . (Это изменит баланс на значение, которое будет приносить 350 долларов в месяц)
Нажмите Ok . В Статус поиска цели появится диалог.

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

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

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

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

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

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

В камере A1 , тип 3 .
Выбрать ячейку C1 , затем в строке формул введите следующую формулу: =1/(A1-5) Эта формула разделит единицу на A1-5, и если A1-5 окажется равным 0, результатом будет неопределенное значение.
Перейти к Данные вкладку, щелкните Что-если анализ а затем выберите Поиск цели .
В Установить ячейку , тип C1 .
В Ценить , тип 1 .
Наконец, в Изменяя ячейку тип A1 . (Это изменит значение A1 на 1 в C1)
Нажмите Ok .

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

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

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

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

передача песни с ipod на компьютер

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

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

Делиться Делиться Твитнуть Эл. адрес 15 формул Excel, которые помогут вам решить реальные жизненные проблемы

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

Читать далее Похожие темы

Продуктивность
Таблица
Майкрософт Эксель
Советы по Microsoft Office
Анализ данных

Об авторе Амир М. Интеллект(Опубликовано 39 статей)

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

Ещё от Amir M. Bohlooli

Подписывайтесь на нашу новостную рассылку

Подпишитесь на нашу рассылку технических советов, обзоров, бесплатных электронных книг и эксклюзивных предложений!

Нажмите здесь, чтобы подписаться