В этом уроке объясняется, как использовать поиск цели в Excel. Это включает объяснение этой особенности с несколькими практическими примерами.
Что такое поиск цели?

Goal Seek - это встроенная утилита Excel, которая решает уравнение и помогает вам ответить на вопрос «Каково будет входное значение для получения желаемого результата».
Он просит нас ввести три параметра:

1. Формула в клетке
2. Целевое / желаемое значение
3. Какую ячейку изменить

Давайте объясним это на примере:

Пример из практики I

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

Подготовка данных для поиска цели

Ниже приведены шаги для подготовки данных перед запуском Goal Seek.

1. Введите текст № Не пропущенных значений » в ячейке B3 и введите любое фиктивное значение (скажем, 117), соответствующее ему в ячейке C3. Мы рассчитаем точное значение в следующих шагах.
2. Введите текст «Нет. недостающих значений в ячейке B4 и фактическая цифра (скажем, 50) в клетке C4.
3. Введите «% от отсутствующих значений» в ячейку B5 и введите следующую формулу в ячейку C5.
С3 / (С3 + С4)
4. Перейдите на вкладку «Данные» и нажмите кнопку «Что-если анализ» и выберите Поиск цели под ним. Смотрите и следуйте инструкциям, показанным на изображении выше.
5. Когда вы нажмете кнопку « Поиск цели» , откроется окно, в котором необходимо указать ссылку на ячейку и целевое значение. Объяснение приводится ниже -

В. Каково будет количество пропущенных значений, если мы хотим получить 60% непропущенных значений?

Задать ячейку:

ссылка на ячейку, в которую введена формула. В этом случае дайте ссылку на ячейку C5 .

Значение: целевое / желаемое значение. В этом случае это 60% . Убедитесь, что вы вводите% после 60. Вы также можете ввести 0,6 вместо 60%

Изменяя ячейку: ссылка на ячейку, значение которой вы хотите изменить. В этом примере это ссылка на ячейку C3 .

После того, как вы заполнили 3 вышеупомянутых ввода, нажмите кнопку «ОК» , и тогда Excel найдет решение для вас и покажет вам результат в ячейке C3.

Решение: нам нужно, чтобы 75 непропущенных значений имели 60% непропущенных процентов.

Как мы можем сделать это без поиска цели?
Мы можем рассчитать это путем построения математических уравнений -
Шаг I: C3 / (C3 + C4) = 0,6
Шаг II: (С3 * 0,6) + (С4 * 0,6) = С3
Стадия III: (1 - 0,6) * C3 = C4 * 0,6
Этап IV: C3 = (C4 * 0,6) / (1 - 0,6)

Если вы введете уравнение шага IV в формулу = (C4 * 0,6) / (1-0,6) , будет возвращено требуемое значение.

Является ли вышеуказанное решение правильным?

Ответ НЕТ . Это потому, что количество пропущенных значений зависит от не пропущенных значений. Оба составляют полный набор данных. Например, у вас есть набор данных, который состоит из 150 записей. Если вы заполните 15 пропущенных записей до какого-то значения, это увеличит число не пропущенных записей на 15, а количество пропущенных записей соответственно уменьшится на 15 записей. Чтобы обойти эту проблему, нам нужно включить общее количество записей в качестве ограничения. Смотрите решение ниже.

Добавить ограничение в поиск цели

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

1. Введите формулу = 150-C3 в ячейку C4 (как показано на рисунке выше)
2. Запустите анализ поиска цели еще раз (следуйте инструкциям ниже)

Важные моменты

1. Ссылка на ячейку в поле Set cell: должна содержать формулу. В противном случае он вернет ошибку.
2. Вы не можете дать ссылку на ячейку в поле «To value:». Вам необходимо ввести значение.
3. Ссылка на ячейку в «Изменяя ячейку:» не может содержать формулу. Это должно быть типизированное значение.

Пример II

Предположим, вы хотите купить роскошный автомобиль стоимостью 150 000 долларов. Для его финансирования вы берете автокредит в банке на сумму 150,00 долл. США с процентной ставкой 14%. Вы можете платить максимум 4000 долларов в месяц в рассрочку. Вам нужно выяснить, сколько периодов вам нужно платить ежемесячный платеж?
Вы можете использовать функцию PMT () для расчета платежа по кредиту.

Рассрочка кредита = PMT (процентная ставка, количество платежей, сумма кредита)

В этом случае нам нужно рассчитать Платежей », который является параметром в самой функции.

Подготовка данных

1. Введите значения суммы кредита, количества платежей (ежемесячно) и годовой процентной ставки из ячейки C3 - C5. Поместите любую фиктивную стоимость против количества платежей. Мы вычислим правильное значение позже.
2. Введите формулу = PMT (C5 / 12, C4, C3) в ячейку C6 (см. Изображение выше). Поскольку C5 - это годовая процентная ставка, нам нужно разделить ее на 12
3. Перейдите на вкладку «Данные», затем нажмите « Что-если анализ» и выберите « Поиск цели».
4. В поле Set cell: введите ссылку на ячейку C6.
5. В поле Значение: введите -4000. Убедитесь, что он отрицательный, так как это платеж.
6. В поле Изменение ячейки: укажите ссылку на ячейку C4 (см. Рисунок 2).

Goal Seek нашла решение, и необходимо платить 50 ежемесячных платежей, когда 4000 долларов выплачиваются ежемесячно в рассрочку.

Сноски
Поиск цели очень помогает быстро решить любую финансовую и статистическую формулу для одного неизвестного значения. Это экономит много времени, когда трудно получить формулу для вычисления неизвестного значения. Как показано во втором тематическом исследовании, требуется время для преобразования уравнения платежа по кредиту и расчета условий без использования поиска цели. Если у вас есть сложная задача, которая содержит несколько уравнений и несколько ограничений, и вас просят найти оптимальное решение, вы должны искать надстройку Solver в Excel, а не поиск цели. Excel Solver - это мощный метод решения проблемы оптимизации.
Любите этот пост? Распространить слово!
Комментируйте и делитесь, чтобы мотивировать нас писать больше! Об авторе:

Дипаншу основал ListenData с простой целью - сделать аналитику легкой для понимания и отслеживания. У него более 8 лет опыта в науке о данных. За время своей работы он работал с глобальными клиентами в различных областях, таких как банковское дело, страхование, телекоммуникации и управление персоналом.

Давайте на связи: Эл. адрес | LinkedIn

Похожие сообщения:

Похожие

Комментарии