Если вы знаете результат формулы, но не знаете входное значение, которое будет генерировать результат, Microsoft Excel поможет вам. Просто узнайте, как использовать поиск цели в Excel и выполнять моделирование формул и больших числовых данных, не изменяя фактические данные.
Будь то ваши личные финансы, сберегательные вложения или бизнес-проект, взвешенное принятие решений является ключом к успеху. Для принятия обоснованных решений вам нужны смоделированные данные из моделирования баз данных на основе формулы и вашей цели. Excel Goal Seek — это инструмент для такого моделирования на основе данных.
Если вы новичок в Excel или имеете некоторый опыт его использования, но не являетесь экспертом, читайте дальше, чтобы узнать об основах поиска цели в Excel и о том, как использовать функцию поиска цели в Excel.
Что такое поиск цели в Excel?
Goal Seek — это встроенный в Excel инструмент анализа «что, если». Он показывает, как конечное значение в формуле влияет на входное значение. Проще говоря, он сообщает вам входное значение для формулы, чтобы получить желаемый результат из той же формулы.
Также читайте:Включить/отключить отображение формул в ячейках в Excel
Финансовые инвесторы, финансовые менеджеры, владельцы бизнеса и специалисты по менеджменту широко используют этот инструмент Excel What-If Analysis для прогнозирования входных данных для желаемого результата на основе любой математической модели.
Вы также можете назвать Goal Seek в Excel автоматизированной системой моделирования, которая анализирует вашу формулу, связанную с наборами данных любого размера, и сообщает вам входное значение. Если вы введете входное значение в свою математическую модель, вы получите искомый результат.
Вы можете использовать этот инструмент практически в любом сценарии, но основными являются следующие:
- Сколько единиц продукции вы должны продать, чтобы получить определенный доход с учетом дополнительных скидок и агентских комиссий.
- По какой процентной ставке вы должны брать кредит, чтобы вы могли погасить кредит в течение определенного периода с определенными ежемесячными платежами
- Сколько рабочих часов необходимо для завершения проекта в заранее установленный срок
Где искать цель в Excel?
Excel Goal Seek — это один из трех инструментов анализа «что, если» в Microsoft Excel. Таким образом, вы можете найти инструмент в командном меню анализа «что, если». В меню ленты Excel щелкните вкладку «Данные», а затем перейдите в крайний правый угол ленты. Там вы должны увидеть раздел «Прогноз». Нажмите кнопку «Анализ «что, если»» и «Поиск цели» будет номером два в выпадающий список.
Расположение инструмента такое же, как и в приложении Excel для Mac. На момент написания эта функция была доступна в следующих версиях Excel:
- Excel для Microsoft 365 (Windows и Mac)
- Excel 2021 (Windows и Mac)
- Также доступно для Windows и Mac из Excel 2010 и более поздних версий.
Требования для использования поиска цели в Excel
Excel Goal Seek выполнит все расчеты в бэкэнде. Все, что вам нужно сделать, это определить следующие три параметра из вашей математической модели или таблицы данных:
- Введите ссылку на ячейку в Установить ячейку коробка. Эта ссылочная ячейка должна содержать формулу. Excel Goal Seek использует эту формулу для получения входного значения.
- Вам необходимо ввести результат или цель, которую необходимо достичь в поле Оценивать коробка.
- Сменив ячейку — это ссылка на ячейку, в которой вам нужны входные данные для моделируемой математической модели.
Как использовать поиск цели в Excel
Ниже приведены несколько примеров сценариев, в которых вы можете использовать поиск цели в Excel для прогнозирования входных данных для желаемого результата на основе математической формулы. Шаги одинаковы для устройств с Windows 11 и macOS.
1. Поиск цели для прогнозирования результатов экзамена
Допустим, вам нужно ускорить свои учебные планы в последнем семестре колледжа, чтобы получить средний балл 85% по окончании учебы. Вот как вы можете применить формулу поиска цели, чтобы смоделировать баллы, необходимые в последнем семестре, если вы получили баллы за другие семь семестров:
- Создайте заголовки столбцов с именем Семестр и Счет на листе Excel.
- Тип Семестр I через Семестр VIII под Семестр столбец.
- Введите баллы за все семисеместровые экзамены, кроме Семестр VIII.
- Создать Среднее значение за семестр оценка по следующей формуле:
=СРЕДНЕЕ(B2:B9)
- Настройте ссылки на ячейки в соответствии с вашим собственным листом Excel.
- Теперь позвоните в поиск цели функция.
- Установить ячейку должна быть ячейка формулы, где вы вычислили среднее значение.
- Оценивать ячейка должна быть вашим желаемым средним баллом за семестр, который составляет 85%.
- Введите ссылку на пустую ячейку для Семестр VIII экзаменационный балл в Сменив ячейку коробка.
- Нажмите ХОРОШО чтобы получить Семестр VIII оценка, которую вы должны получить, чтобы получить средний балл 85%.
Вам нужно набрать 98% в последнем семестре, чтобы получить целевой выпускной балл.
Также читайте:Microsoft Excel: как легко управлять листами
2. Предсказать сумму кредита с помощью поиска цели
Предположим, вам нужно занять капитал на 40 лет под фиксированную процентную ставку (8%) с фиксированной ежемесячной выплатой для вашего бизнеса. Теперь вы знаете, что можете платить 1800 долларов в месяц из доходов от бизнеса. Однако вы не знаете, сколько вы должны взять взаймы под вышеуказанные проценты на указанный выше период. Вы можете легко рассчитать сумму займа, выполнив следующие действия:
- Создайте строки для следующих компонентов: Годовая процентная ставка, Годы до окупаемости, Величина займа, и Ежемесячные платежи.
- Введите соответствующие значения для вышеуказанных компонентов в соответствующие ячейки, за исключением Величина займа, который нужно рассчитать с помощью Goal Seek.
- Теперь замените Ежемесячные платежи значение со следующим ПМТ формула в экселе:
=ПЛТ(В1/12,В2*12,В3,0)
- Позвоните поиск цели функция от Данные > Анализ «что, если» > поиск цели.
- Установить ячейку должен ссылаться на ячейку формулы PMT.
- Оценивать должно быть 1500 долларов.
- При изменении ячейки должно быть Величина займа ценить.
- Нажмите ХОРОШО моделировать сумму кредита посредством итерационных вычислений.
Согласно функции поиска цели, сумма кредита должна составлять 215 731 доллар США, если вы хотите взять ее на 40 лет под 8% годовых и ежемесячно выплачивать взнос в размере 1500 долларов США.
3. Найдите цель продаж, используя поиск цели
Если вы являетесь руководителем отдела продаж и маркетинга или руководителем отдела продаж и вам необходимо создать цель продаж для достижения определенной цели по доходам, поиск цели — идеальный инструмент. Например, ваш бизнес в настоящее время продает 1000 единиц продукции.
Рекомендуемая производителем розничная цена продукта составляет 50 долларов США, и вы также предлагаете оптовикам скидку в размере 10%. Однако вы хотите увеличить целевой доход вашей компании от продаж продукции до 75 000 долларов США без изменения рекомендованной розничной цены и ставки дисконтирования. Тогда, сколько продуктов вы должны продать? Давайте узнаем, используя поиск цели ниже:
- Создайте строки для известных компонентов, таких как Проданных единиц, Рекомендуемая производителем розничная цена, и Скидка.
- Рассчитайте выручку по следующей формуле:
=В2*(В3*(1-В4))
- Теперь откройте поиск цели и установите в качестве его параметров следующее:
- Установить ячейку: $B$5
- Оценивать: $75,000
- Сменив ячейку: $B$2
Убедитесь, что вы настроили ссылки на ячейки в соответствии с вашими собственными данными и таблицей Excel. Нажимать ХОРОШО и вы получаете цель продаж продуктов, которая составляет 1667 единиц по той же рекомендованной розничной цене и ставке дисконтирования.
Поиск цели в Excel: часто задаваемые вопросы
Как использовать поиск цели в Excel для нескольких ячеек?
Поиск цели в Excel позволяет имитировать ввод математической формулы для получения заранее определенного результата. К сожалению, он не поддерживает учет переменных или нескольких ячеек. Для нескольких ячеек и анализа «что, если» на основе вариаций вы можете использовать надстройку «Поиск решения» Excel.
Что такое ярлык для поиска цели?
В настоящее время для функции «Поиск цели» в Excel нет специального ярлыка. Вы можете использовать клавишу Alt в Excel, а затем нажать несколько клавиш на клавиатуре, чтобы вызвать инструмент поиска цели без использования мыши. Шаги описаны здесь:
Нажмите Alt в Excel>, не щелкая где-либо в приложении, нажмите A> нажмите W> нажмите G, чтобы открыть поиск цели
Как сделать поиск цели более точным?
Иногда Goal Seek может выводить входное значение в десятичных дробях. Если вам нужна округленная цифра автоматически, вы можете увеличить ее точность. Вот как это сделать:
- Нажмите на Файл вкладку и выберите Параметры на боковой панели.
- Внутри Параметры Excel окно, нажмите на Формулы вариант.
- Отметьте Включить итеративный расчет особенность.
- Увеличить Максимальное количество итераций поле до 150 или более, чтобы Excel мог тестировать более подходящие решения.
- Кроме того, увеличьте Максимальное изменение значение по умолчанию от 0,001 до 0,00001.
Могу ли я выполнять поиск цели на нескольких ячейках?
Вы не можете выполнить поиск цели в Excel для нескольких ячеек.
Каковы преимущества поиска цели?
Вот преимущества поиска цели в Excel:
- Goal Seek — это простейшая симуляция, доступная для анализа «что, если» в Excel.
- Он поставляется из коробки с настольным приложением Excel.
- Goal Seek работает в бэкенде и не требует изменения всей базы данных.
- Полезно для финансового моделирования
Каковы ограничения поиска цели в Excel?
Goal Seek в Excel имеет следующие недостатки:
- Поиск цели не будет работать, если есть циклические ссылки
- Вы не можете вводить какие-либо переменные при выполнении симуляций поиска цели
- Не работает с несколькими ячейками
- Поиск целей Excel недоступен в Excel Web App или Excel для веб-приложения.
Поиск цели в Excel: заключительные слова
Прочитав приведенную выше статью о поиске цели в Excel, вы должны были получить базовое и среднее понимание того, как выполнять анализ «что, если» в Excel. Поиск цели в Excel особенно полезен в математических задачах, где есть четкая формула, входное значение, несколько переменных, связанных с формулой, и одна цель, которую нужно достичь.
Оставьте свой комментарий ниже о своем опыте работы с приведенным выше пошаговым руководством по поиску цели в Excel. Если вы знаете какие-либо другие методы анализа «что, если» в Excel, дайте мне знать.
Следующий, как сравнить текст в экселе.