Поиск цели в Excel, как использовать
Обновлено: 05.07.2024
Если вы знаете, какой результат вы хотите получить из формулы, используйте поиск цели в Excel, чтобы найти входное значение, которое дает результат этой формулы.
Пример поиска цели 1
Используйте поиск цели в Excel, чтобы найти оценку за четвертый экзамен, которая дает итоговую оценку 70.
<р>1. Формула в ячейке B7 вычисляет окончательную оценку.
Появится диалоговое окно "Поиск цели".
<р>5. Выберите ячейку B7. <р>6. Нажмите в поле "В значение" и введите 70. <р>7. Нажмите в поле "Изменив ячейку" и выберите ячейку B5.
Результат. Оценка 90 на четвертом экзамене дает итоговую оценку 70.
Пример поиска цели 2
Используйте поиск цели в Excel, чтобы найти сумму кредита, которая обеспечивает ежемесячный платеж в размере 1500 долларов США.
<р>1. Формула в ячейке B5 вычисляет ежемесячный платеж.
Объяснение: функция PMT рассчитывает платеж по кредиту. Если вы никогда раньше не слышали об этой функции, ничего страшного. Чем выше сумма кредита, тем выше ежемесячный платеж. Предположим, вы можете позволить себе только 1500 долларов в месяц. Какова максимальная сумма кредита?
<р>2. Сумма кредита в ячейке B3 является ячейкой ввода.
Появится диалоговое окно "Поиск цели".
<р>5. Выберите ячейку B5. <р>6. Щелкните в поле "В стоимость" и введите -1500 (отрицательное значение, вы платите деньги). <р>7. Нажмите в поле "Изменив ячейку" и выберите ячейку B3.
Результат. Сумма кредита в размере 250 187 долларов США означает ежемесячный платеж в размере 1500 долларов США.
Точность поиска цели
Поиск цели возвращает приблизительные решения. Вы можете изменить параметры итерации в Excel, чтобы найти более точное решение.
<р>1. Формула в ячейке B1 вычисляет квадрат значения в ячейке A1.
Результат. Excel возвращает приблизительное решение.
Подробнее о поиске цели
Существует много проблем, которые не может решить Goal Seek. Для поиска цели требуется одна входная ячейка и одна выходная ячейка (формула). Используйте Решатель в Excel для решения задач с несколькими входными ячейками. Иногда вам нужно начать с другого входного значения, чтобы найти решение.
<р>1. Формула в ячейке B1 ниже дает результат -0,25.
Результат. Excel не может найти решение.
Объяснение: y = 1 / (x - 8) разрывно при x = 8 (деление на 0 невозможно). В этом примере поиск цели не может достичь одной стороны оси x (x>8), если он начинается с другой стороны оси x (x
Функция Excel «Поиск цели» (часто называемая анализом «что, если») – это метод поиска желаемого результата путем изменения допущения, лежащего в его основе. По сути, функция использует метод проб и ошибок для обратного решения проблемы, вставляя предположения, пока не будет получен ответ.
Например, если формула для дохода равна количеству проданных единиц, умноженному на цену продажи, Goal Seek может определить, сколько единиц необходимо продать, чтобы достичь дохода в 1 миллион долларов, если цена продажи известна. Эта функция чрезвычайно полезна для проведения анализа чувствительности. Что такое анализ чувствительности? Анализ чувствительности – это инструмент, используемый в финансовом моделировании для анализа того, как различные значения набора независимых переменных влияют на зависимую переменную в финансовом моделировании.
Простой пример поиска цели
Сначала рассмотрим простое упражнение, чтобы понять, как работает поиск цели. Предположим, у нас есть очень простая модель, которая использует количество проданных единиц, розничную цену и скидку для расчета общего чистого дохода.
Текущая модель содержит следующую информацию:
Теперь предположим, что мы хотим узнать, сколько единиц нужно продать, чтобы доход достиг 20 000 долларов США. Посмотрите на скриншот ниже и выполните перечисленные шаги, чтобы использовать инструмент Excel для поиска цели.
Пошаговые инструкции по использованию Excel для поиска целей:
Ниже приведены результаты анализа. Мы видим, что для достижения дохода в 20 000 долларов необходимо продать 889 единиц. Обратите внимание, что решение отображается непосредственно в ячейке (E4), а не в диалоговом окне.
Нажмите "ОК", чтобы оставить решение в модели (ячейка E4 навсегда изменится на 889 единиц), или нажмите "Отмена", чтобы вернуться к исходному предположению (500 единиц).
Видео пример функции поиска цели в Excel
Вот более продвинутая видеодемонстрация того, как использовать функцию поиска цели в Excel. Этот видеоклип представляет собой отрывок из бесплатного ускоренного курса CFI по Excel. Основы Excel — формулы для финансов Вы ищете ускоренный курс по Excel? Получите бесплатное обучение Excel для карьеры в области корпоративных финансов и инвестиционно-банковской деятельности от Института корпоративных финансов. как записано Тимом Випондом Тим Випонд Тим Випонд — главный исполнительный директор Института корпоративных финансов, ведущего поставщика онлайн-обучения и сертификации в области финансов, генеральный директор Института корпоративных финансов®.
Расшифровка видео
Предположим, вы хотите найти чистую приведенную стоимость, равную 500 миллионам долларов США, исходя из определенной ставки дисконта.Для этого я воспользуюсь функцией Excel SEEK GOAL SEEK. Я собираюсь нажать Alt, A, W и G для поиска цели. Что я хочу сделать, так это установить это значение, значение функции NPV NPV. Функция NPV относится к категории финансовых функций Excel. Он рассчитает чистую текущую стоимость (NPV) для периодических денежных потоков. NPV будет рассчитываться для инвестиции с использованием ставки дисконтирования и ряда будущих денежных потоков. В финансовом моделировании функция NPV полезна для определения стоимости бизнеса, равной 500 миллионам долларов, путем изменения ставки дисконтирования и нажатия кнопки OK. Я вижу здесь, что ставка дисконтирования составляет 25 процентов, чтобы получить чистую приведенную стоимость в размере 500 миллионов долларов. Я собираюсь нажать «Отмена» и ввести этот ответ в эту ячейку. Теперь я просто принял к сведению, какой должна быть ставка дисконтирования, чтобы получить чистую приведенную стоимость в размере 500 млн долларов.
Хорошо, давайте сделаем еще один пример. Предположим, я хочу, чтобы чистая прибыль в 2020 году составила 10 миллионов долларов, и я хочу получить ее, изменив выручку в 2020 году. Выручка от продаж — это доход, полученный компанией от продажи товаров или оказания услуг. В бухгалтерском учете термины продажи и . Сколько выручки нам нужно в 2020 году, чтобы получить 10 миллионов долларов чистой прибыли Чистая прибыль Чистая прибыль является ключевой статьей не только в отчете о прибылях и убытках, но и во всех трех основных финансовых отчетах. В то время как это достигается через ? Я собираюсь нажать Alt, A, W и G для ПОИСКА ЦЕЛИ, поэтому я хочу изменить это число здесь, этот чистый доход в 2020 году, чтобы он составил 10 миллионов долларов, изменив этот целевой доход от дела на тот, который в настоящее время работает в нашей модели, поэтому мне нужно изменить эту ячейку, и я вижу, что целевой доход составляет 122,7 миллиона долларов. Нажмите "Отмена" и введите здесь 122700, чтобы получить доход, необходимый для этого.
Вот как я могу резюмировать важный информационный момент, сделанный здесь с помощью GOAL SEEK.
Небольшое уточнение: я поставлю рамку Alt, H, B, P, чтобы поместить рамку вверху, а здесь я скажу анализ поиска цели в скобках, Что-если, и я просто поставлю рамку над этим разделом здесь, Alt HBP, и я могу выбрать эти ячейки здесь, нажмите Shift + пробел и Alt, D, G, G, чтобы сгруппировать их. Теперь все возвращается в хорошо организованную электронную таблицу.
Дополнительные ресурсы
Спасибо, что прочитали объяснение CFI о функции поиска цели в Excel. Эти дополнительные ресурсы CFI будут полезны для продолжения обучения и развития вашей карьеры:
МАРШАЛЛ ГАННЕЛЛ, ОБНОВЛЕНО 20 АВГУСТА 2021 Г. Чтение: 7 минут
Функция поиска цели в Excel позволяет корректировать входное значение в формуле для определения желаемого результата. Этот инструмент анализа "что, если" идеально подходит для ситуаций, когда вы знаете, какой результат хотите получить, но не уверены в значениях, необходимых для его достижения.
В этом руководстве мы покажем вам, как использовать функцию поиска цели, а также предложим несколько реальных примеров использования.
1. Что такое функция поиска цели в Microsoft Excel?
Microsoft Excel поставляется с тремя встроенными инструментами анализа "что, если": таблицы данных, сценарии и поиск цели. Все три инструмента используются в сценариях "что, если", чтобы понять, как изменение определенных входных значений повлияет на выходное значение или желаемый результат.
Функция поиска цели используется, чтобы понять, какое входное значение необходимо для реального достижения поставленной цели.
Одно предостережение, о котором вы должны знать: функция поиска цели работает, только если есть одно входное значение, которое вы хотите определить. Если вы хотите определить несколько входных значений, вы можете вместо этого использовать надстройку Solver.
Например, если вы хотите взять кредит, и вы знаете выходную стоимость, то есть сумму, которую вы можете позволить себе платить каждый месяц, и вы знаете введите значения (1) сколько денег вам нужно занять и (2) сколько месяцев вам нужно, чтобы погасить кредит, вы можете использовать функцию поиска цели, чтобы определить (3) процентную ставку, необходимую для сохранения ежемесячного платежа в рамках вашего бюджета.
Однако, если вы хотите определить, какая процентная ставка вам нужна и количество времени, необходимое для погашения кредита, чтобы сохранить ежемесячный платеж на желаемой сумме, вам потребуется использовать Солвер.
2. Примеры использования функции поиска цели
Существует множество ситуаций, в которых вы можете захотеть использовать поиск цели. Вот несколько гипотетических ситуаций, в которых может пригодиться функция поиска цели.
Бизнес
Допустим, ваша компания поставила перед собой цель получить в текущем году чистую прибыль в размере 2 000 000 долларов США. Q3 только что закрылся, поэтому у вас есть данные о чистой прибыли за Q1, Q2 и Q3. Ваша текущая чистая прибыль за год составляет 1 518 750 долларов США.
Вы можете использовать поиск цели, чтобы определить, какая чистая прибыль вам нужна для закрытия четвертого квартала, чтобы достичь годовой цели в 2 000 000 долларов США.
Образование
У всех нас в школе был один предмет, который, как бы усердно мы ни учились, просто не очень сообразителен. Возможно, у вас неудовлетворительная оценка, и вы хотите определить, какой балл вам нужен на выпускном экзамене. экзамен, чтобы достичь этого проходного порога. Или, возможно, у вас есть четверка, и вы хотите повысить ее до пятерки.
Вы можете использовать поиск цели, чтобы определить, какой балл вам нужно набрать на выпускном экзамене, чтобы получить желаемый итоговый балл.
Личное
Наиболее часто используемым примером личного использования Goal Seek является пример кредита. Вы знаете сумму, которая вам нужна, вы знаете, сколько месяцев вы хотите выплачивать ее, и вы знаете сумму, которую вы можете себе позволить выплачивать каждый месяц.
Вы можете использовать Goal Seek, чтобы определить процентную ставку, которую вам необходимо обеспечить, чтобы оставаться в рамках вашего месячного бюджета.
3. Как использовать поиск цели в Excel: пошаговое руководство
В этом практическом руководстве мы покажем вам, как использовать функцию поиска цели в Microsoft Excel с нашим ранее упомянутым бизнес-сценарием.
Чтобы изменить сценарий, компания установила цель по чистой прибыли в размере 2 000 000 долларов США, а третий квартал только что завершился. Итак, мы знаем желаемый результат, и у нас есть все данные, кроме одного значения: Q4.
Текущие квартальные данные:
- 1 кв.: 585 000 долл. США
- Второй квартал: 217 500 долларов США
- 3 кв.: 416 250 долл. США
- Q4: ?
Текущая годовая чистая прибыль:
Цель по годовой чистой прибыли:
На основе данных, которые у нас есть в настоящее время, мы можем использовать функцию поиска цели, чтобы определить, какой должна быть чистая прибыль в четвертом квартале для достижения поставленной цели.
Откройте лист Excel, содержащий ваши данные. Вот как выглядит наш в этом примере.
Прежде чем мы пойдем дальше, остановимся на нескольких основных функциях Excel, которые мы используем в этом примере. В D6 мы используем функцию =SUM() для вычисления суммы ячеек D2, D3, D4 и D5. Итак, мы ввели =СУММ(D2:D5) в ячейку D6.
Кроме того, вам нужно автоматизировать значения, полученные в ячейках D2–D5. Например, чтобы автоматически вычислить данные в B2 и C2 и получить окончательный результат в D2, щелкните ячейку D2 и введите: =B2*C2 и нажмите Enter. Сделайте то же самое для рядов 3, 4 и 5.
Теперь, когда эти основные функции Excel готовы, мы можем использовать поиск цели, чтобы определить, чем нам нужно закрыть четвертый квартал, чтобы выполнить нашу годовую цель.
Нажмите ячейку, в которой используется функция =СУММ() (в нашем примере это ячейка D6). Это ячейка, которую мы хотим изменить до желаемой цели. После выбора вокруг ячейки появится рамка, а соответствующий номер строки и буква столбца будут выделены серым цветом.
Затем перейдите на вкладку "Данные", выберите параметр "Анализ "что, если"" в группе "Прогноз", а затем выберите параметр "Поиск цели" в раскрывающемся меню.
Появится диалоговое окно поиска цели. Здесь нам нужно ввести три значения.
- Ячейка набора: ячейка, содержащая значение, в которое вы хотите добавить желаемый результат.
- Ценность: ценность вашей цели.
- Изменив ячейку: ячейка, содержащая входное значение, которое будет определять результат.
Вот цифры, которые нам нужно ввести, чтобы достичь цели, поставленной в нашем примере.
- Установить ячейку: D6
- До значения: 2 000 000
- Изменив ячейку: B5
Введите необходимую информацию и нажмите OK. Excel начнет анализировать данные и выдаст число, необходимое в ячейке B5 для достижения желаемой цели.
Таким образом, в нашем примере анализ показал, что для того, чтобы достичь цели в 2 000 000 долларов США, нам необходимо получить валовую прибыль в размере 3 208 333,33 доллара США, что при 15 % дает нам чистую прибыль в размере 481 250 долларов США.
4.Принятие перспективных решений на основе данных
Использование функции поиска цели необходимо, когда вам нужно определить входное значение, необходимое для достижения вашей цели. Независимо от того, определяете ли вы валовую/чистую прибыль, которая вам нужна в четвертом квартале для достижения годовой цели компании по прибыли, балл, который вам нужен на выпускном экзамене, чтобы получить проходной балл, или процентную ставку, которую вам нужно согласовать, чтобы снизить ежемесячную на номер, который вы можете себе позволить, функция поиска цели быстро предоставит вам данные, необходимые для составления реалистичного плана на основе данных.
Это только верхушка айсберга. Чтобы расширить свои знания в области анализа данных, попробуйте наш бесплатный вводный краткий курс. Если вы нашли этот учебник полезным и хотите изучить больше наших руководств по Excel, ознакомьтесь со следующими статьями:
Что нужно сделать сейчас
Получите практическое введение в аналитику данных и проведите свой первый анализ с помощью нашего бесплатного краткого курса по аналитике данных для самостоятельного изучения.
Примите участие в одном из наших БЕСПЛАТНЫХ онлайн-мероприятий по анализу данных с участием экспертов отрасли.
Поговорите с консультантом программы, чтобы обсудить смену карьеры и то, как вы можете стать квалифицированным аналитиком данных всего за 4–7 месяцев с гарантией работы.
Хотите стать женщиной в сфере технологий в 2022 году? Подайте заявку на получение стипендии «Женщины в технологиях» до конца марта, чтобы обеспечить себе место в нашей программе анализа данных для тех, кто меняет карьеру.
Эта статья является частью:
Аналитика данных
Маршалл — профессиональный писатель с опытом работы в сфере хранения данных. Его путь начался в Synology. Оттуда он продолжил работу в сфере корпоративного хранения данных, а до основания ITEnterpriser был директором по маркетингу и писателем технического персонала в StorageReview. Его работы также можно найти в Интернете на сайтах How-To Geek, Zapier и некоторых других онлайн-изданиях. В настоящее время он является техническим писателем API/программного обеспечения в корпорации LINE в Токио, Япония. Когда он не пишет, он создает ITEnterpriser, создает калькулятор RAID (его первый проект разработки), изучает японский язык и путешествует по красивым горам, разбросанным по Японии.
Статьи по теме об аналитике данных
Что такое описательная аналитика? Полное руководство
Что такое диагностическая аналитика? Полное руководство
Как найти выбросы в данных с помощью Python (и как с ними справиться)
Что такое CareerFoundry?
CareerFoundry — это онлайн-школа для тех, кто хочет начать карьеру в сфере высоких технологий. Выберите программу, обратитесь к опытному наставнику и репетитору и станьте готовым к работе дизайнером, разработчиком или аналитиком с нуля или верните свои деньги.
Успешные маркетологи быстро принимают обоснованные решения, сочетая интуицию и анализ данных. Они знают конечную цель и понимают, что нужно для ее достижения.
Один из способов восполнить эти пробелы — использовать функцию поиска цели в Excel. Эта функция полезна, когда вы знаете желаемый результат, но не знаете, как его достичь.
Возможно, вы хотите повысить коэффициент конверсии, чтобы привлечь больше потенциальных клиентов, но не знаете, сколько людей вам нужно привлечь. Или предположим, что ваша команда по маркетингу поставила перед собой смелую цель по увеличению дохода, и вы хотите знать, сколько клиентов вы должны привлечь с помощью предстоящей кампании. Если вы проводите рекламную акцию, вам, вероятно, нужно выяснить, какую скидку применить, чтобы не оказаться в убытке.
![Загрузить 10 Шаблоны Excel для маркетологов [бесплатный набор]](https://no-cache.hubspot.com/cta/default/53/9ff7a4fe-5293-496c-acca-566bc6e73f42.jpg)
Поиск цели — это ответ. Понимание того, как применять его в маркетинге или продажах, может помочь в разработке стратегии, позволяя рассчитать цифры, необходимые для достижения ваших целей.
В этом посте объясняется, как использовать поиск целей, чтобы вы могли начать планировать свою следующую кампанию или делать прогнозы на предстоящий квартал.
Что такое поиск цели в Excel?
Поиск цели — это мощная функция Excel для анализа возможных вариантов. Также известный как анализ чувствительности, он помогает понять, что может произойти при изменении одной или нескольких переменных. По сути, это способ провести обратный расчет в электронной таблице Excel.
Представьте, что вы разрабатываете маркетинговую стратегию на следующие шесть месяцев. Вы можете использовать функцию поиска цели Excel, чтобы выяснить следующие неизвестные.
- Какой процент месячного роста вам нужен, чтобы удвоить охват к концу года?
- Сколько вы можете потратить на внештатную дизайнерскую работу, не выходя за рамки бюджета на аутсорсинг?
- Какой доход вам нужен, чтобы выйти на безубыточность (и получить прибыль) от предстоящей маркетинговой кампании по электронной почте?
Ответы на эти вопросы могут предотвратить неожиданные результаты и упущенные цели. Вместо того, чтобы задаваться вопросом «что, если» при построении стратегии, вы можете избавиться от неопределенности и составить план действий для достижения успеха.
Прежде чем завершить какие-либо планы, давайте пройдемся по этапам проведения анализа.
Как использовать поиск цели в Excel
Настроить расчет поиска цели просто, если ваши данные систематизированы.
В следующем примере я хочу оценить процент клиентов, приходящих через различные маркетинговые каналы. Цель состоит в том, чтобы к концу года привлечь 50 % клиентов за счет маркетинговых усилий.
Сначала я заполняю таблицу, используя средний рост по месяцам (м/м), чтобы увидеть прогнозы на июнь-декабрь. Я знаю, что на начало декабря у меня запланирована кампания по электронной почте, и я хочу узнать, сколько клиентов мне нужно привлечь, чтобы достичь цели в 50 %.
Шаг 1. Выберите ячейку с выходными данными, которые вы хотите изменить (например, % клиентов из отдела маркетинга).
Шаг 2. На вкладке "Данные" выберите "Анализ возможных вариантов", а затем "Поиск цели".
Источник изображения
Шаг 3. Появится всплывающее окно. Убедитесь, что ячейка из шага 1 отображается в ячейке «Установить».
Шаг 4. Запишите желаемое значение в поле Кому.
Шаг 5. В поле Путем изменения ячейки выберите ячейку, которую вы хотите изменить, чтобы получить желаемый результат.
Источник изображения
Шаг 6. Нажмите "ОК", чтобы увидеть расчет поиска цели. Новый номер появится в ячейке из шага 5, а не во всплывающем окне.
Источник изображения
Шаг 7. Если все выглядит хорошо и вы хотите сохранить расчет, еще раз нажмите "ОК".
Используя Goal Seek, я могу сказать, что если мой рост за месяц останется прежним, мне нужно привлечь как минимум 16 клиентов с помощью моей кампании по электронной почте в декабре. Да, это простой пример. Но вы можете расширить его до гораздо более сложных задач, таких как прогнозирование продаж, необходимых для достижения целей по доходам, или расчет чистой прибыли, которую вы получите от кампании.
Анализ поиска цели в Excel
Давайте рассмотрим еще один пример анализа поиска цели. Я хочу привлечь 130 новых клиентов, но не знаю, сколько посещений мне потребуется, чтобы достичь своей цели. Прежде чем приступить к анализу поиска цели, я упорядочиваю свои данные, чтобы найти средний процент посещений клиентов за месяц.
Шаг 1. Выберите ячейку с результатом, который вы хотите изменить (в данном случае цель клиента).
Шаг 2. На вкладке "Данные" выберите "Анализ возможных вариантов", а затем "Поиск цели".
Шаг 3. Во всплывающем окне убедитесь, что ячейка из шага 1 отображается в ячейке "Установить".
Источник изображения
Шаг 4. Введите нужное число в поле "Кому" (моя цель – 130 клиентов).
Источник изображения
Шаг 5. Выберите ячейку, которую вы хотите изменить, в поле «Изменив ячейку» (у меня для запланированных посещений).
Шаг 6. Нажмите «ОК», чтобы просмотреть анализ поиска цели. (Теперь я знаю, что для того, чтобы получить 130 клиентов, мне нужно привлечь 5055 посещений).
Источник изображения
После того как вы заполните отсутствующую переменную с помощью поиска цели, вы сможете определить другие переменные. Например, я обнаружил, что при 5055 посещениях мне потребуется 910 потенциальных клиентов, чтобы привлечь желаемое количество клиентов. Наличие этих цифр также может помочь мне оценить, соответствуют ли усилия маркетинга и продаж за месяц достижению цели.
Функция поиска цели в Excel
В бизнесе неопределенность может привести к краху даже самой продуманной стратегии. Но вы можете взять под контроль переменные, которые кажутся вам неподвластными, с помощью функции поиска цели.
Проявляя инициативу и оценивая влияние маркетинговой кампании или новых продаж на бизнес, вы не только завоюете уважение в своей компании, но и поможете достичь поставленных целей и даже превзойти их. Вы будете готовы, когда произойдет неожиданное. И вы узнаете, как принимать обоснованные решения или корректировать стратегию с помощью новых навыков анализа возможных вариантов.
Читайте также: