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

1. Что такое поиск цели в Excel?
2. Как использовать Goal Seek в Excel
3. Примеры использования Goal Seek в Excel
4. Пример 1: достижение цели прибыли
5. Пример 2: Определить сдачу экзамена
6. Пример 3: что-если анализ выборов
7. Excel Goal Seek не работает
8. 1. Дважды проверьте параметры поиска цели
9. 2. Настройте параметры итерации
10. 3. Нет циркулярных ссылок

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

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

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

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

Лучшее в Excel 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 долларов.

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

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

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

:

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

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

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

Пример 1: достижение цели прибыли

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

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

• Задать ячейку - формула, которая рассчитывает общую чистую прибыль (D6).
• Чтобы оценить - результат формулы, который вы ищете ($ 100 000).
• Путем изменения ячейки - ячейка для хранения валового дохода за 4 квартал (B5).

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

Пример 2: Определить сдачу экзамена

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

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

• Задать ячейку - формула, которая усредняет баллы за 3 экзамена (B5).
• К значению - проходной балл (70%).
• Меняя ячейку - 3-й экзаменационный балл (B4).

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

Пример 3: что-если анализ выборов

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

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

Решение . Используйте функцию «Поиск цели», чтобы определить минимальное количество голосов «Да», которое необходимо получить:

• Задать ячейку - формула, которая вычисляет процент текущих голосов «Да» (C2).
• Для оценки - требуемый процент голосов «Да» (66,67%).
• При смене ячейки - количество голосов «Да» (B2).

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

Excel Goal Seek не работает

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

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

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

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

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

В Excel выберите « Файл» > « Параметры» > « Формулы» и измените следующие параметры:

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

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

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

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

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

Вы также можете быть заинтересованы в: