Анализ «что-если»

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

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

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

Необязательно: этот пример можно загрузить для дополнительной практики.

Использование поиска цели

Когда вы создаете формулу или функцию в Excel, вы объединяете разные части для вычисления результата . Goal Seek работает в обратном порядке: он позволяет начать с желаемого результата и вычисляет входное значение , которое даст вам этот результат. Мы будем использовать несколько примеров, чтобы показать, как использовать Goal Seek.

Чтобы использовать поиск цели (пример 1):

Допустим, вы зачислены в класс. В настоящее время у вас есть 65-й класс, и вам нужно как минимум 70 пройти класс. К счастью, у вас есть одно последнее задание, которое может повысить ваш средний уровень. Вы можете использовать Goal Seek, чтобы узнать, какой класс вам нужен для окончательного задания, чтобы пройти класс.

На изображении ниже вы можете видеть, что оценки по первым четырем заданиям – 58 , 70 , 72 и 60 . Несмотря на то, что мы не знаем, что будет пятым классом, мы можем продолжить и написать формулу или функцию, которая рассчитывает окончательный класс. В этом случае каждое присваивание взвешивается одинаково, поэтому все, что нам нужно сделать, это усреднить все пять классов, набрав = СРЕДНИЙ (B2: B6) . Как только мы используем Goal Seek, ячейка B6 покажет нам минимальный класс, который нам нужно будет сделать в окончательном задании.

Скриншот Excel 2010
  1. Выберите ячейку, содержащую значение, которое вы хотите изменить. Когда вы используете Goal Seek, вам нужно выбрать ячейку, которая уже содержит формулу или функцию . В нашем примере мы выберем ячейку B7, потому что она содержит формулу = СРЕДНИЙ (B2: B6) .
    Скриншот Excel 2010
  2. На вкладке « Данные » выберите команду « Что-если проанализировать» , а затем выберите « Поиск цели» в раскрывающемся меню.
    Скриншот Excel 2010
  3. Появится диалоговое окно с тремя полями:
    • Set cell: ячейка, которая будет содержать желаемый результат. В нашем примере ячейка B7 уже выбрана.
    • Значение: Это желаемый результат. В нашем примере мы будем вводить 70, потому что нам нужно заработать, по крайней мере, чтобы пройти класс.
    • Изменяя ячейку: это ячейка, где Goal Seek отправит свой ответ. В нашем примере мы выберем ячейку B6, потому что хотим определить уровень, который нам нужно заработать на окончательном задании.
  4. Когда все будет готово, нажмите « ОК» .
    Скриншот Excel 2010
  5. В диалоговом окне вы узнаете, удалось ли Goal Seek найти решение. Нажмите « ОК» .
    Скриншот Excel 2010
  6. Результат появится в указанной ячейке. В нашем примере Goal Seek подсчитал, что нам нужно будет забить как минимум 90 очков за окончательное задание, чтобы заработать прохождение класса.
    Скриншот Excel 2010

Чтобы использовать поиск цели (пример 2):

Допустим, вам нужен кредит, чтобы купить новый автомобиль. Вы уже знаете, что хотите получить сумму в размере 20 000 долларов США, 60-месячный срок– время, необходимое для погашения кредита;- и оплата не более 400 долларов США в месяц. Однако вы еще не уверены, какова будет процентная ставка .

На изображении ниже вы можете видеть, что процентная ставка остается пустой, а оплата составляет 333,33 доллара США. Это связано с тем, что оплата рассчитывается с помощью специализированной функции, называемой функцией PMT (Payment) , а 333,33 доллара – это то, что ежемесячный платеж будет, если бы не было процентов (20 000 долларов США, деленное на 60 ежемесячных платежей).

Функция расчета ежемесячного платежа

Если бы мы ввели разные значения в пустую ячейку « Процентная ставка» , мы могли бы в конечном итоге найти значение, которое приведет к оплате в размере 400 долларов США, и это будет самая высокая процентная ставка, которую мы можем себе позволить. Однако Goal Seek может делать это автоматически, начиная с результата и работая назад .

Чтобы вставить функцию PMT:

  1. Выберите ячейку, в которой должна быть функция.
  2. На вкладке « Формула » выберите команду « Финансы» .
    Финансовая команда
  3. Появится раскрывающееся меню, отображающее все связанные с финансами функции. Прокрутите вниз и выберите функцию PMT .
    Выбор функции PMT
  4. Появится диалоговое окно.
  5. Введите нужные значения и / или ссылки на ячейки в разные поля. В этом примере мы используем только Rate , Nper (количество платежей) и Pv (сумма кредита).
    Ввод значений в необходимые поля
  6. Нажмите « ОК» . Результат появится в выбранной ячейке. Обратите внимание, что это не наш окончательный результат, потому что мы все еще не знаем, какова будет процентная ставка.
    Ежемесячный платеж, не включая проценты

Чтобы использовать Цель, найдите процентную ставку:

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

  1. На вкладке « Данные » щелкните команду « Что делать» .
  2. Выберите « Поиск цели» .
    Выбор цели
  3. Появится диалоговое окно, содержащее три поля:
    • Set cell: ячейка, которая будет содержать желаемый результат (в данном случае – ежемесячный платеж). В этом примере мы установим его в B5 (неважно, является ли это абсолютной или относительной ссылкой).
    • Значение: Это желаемый результат. Мы установим его на -400 . Поскольку мы производим платеж, который будет вычтен из нашей суммы кредита, мы должны ввести платеж как отрицательное число .
    • Изменяя ячейку: это ячейка, в которой Goal Seek поместит свой ответ (в данном случае, процентную ставку). Мы установим его в B4 .
    Ввод значений в поля поиска цели
  4. Когда все будет готово, нажмите « ОК» . В диалоговом окне вы узнаете, удалось ли Goal Seek найти решение. В этом примере решение составляет 7,42% , и оно помещено в ячейку B4 . Это говорит о том, что процентная ставка 7,42% даст нам платеж в размере 400 долларов США в месяц за кредит в размере 20 000 долларов США, который выплачивается в течение пяти лет или 60 месяцев.
    Решение найдено с помощью Goal Seek

Другие типы анализа if-if

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

  • Сценарии позволяют одновременно подставлять значения для нескольких ячеек (до 32). Вы можете создать столько сценариев, сколько хотите, а затем сравнить их, не изменяя значения вручную. В приведенном ниже примере каждый сценарий содержит термин и процентную ставку . Когда каждый сценарий выбран, он заменит значения в электронной таблице своими собственными значениями, и результат будет пересчитан.
    Выбор сценария

Для получения дополнительной информации о сценариях ознакомьтесь с этой статьей от Microsoft.

  • Таблицы данных позволяют взять одну или две переменные в формуле и заменить их на столько разных значений, сколько хотите , а затем просмотреть результаты в таблице. Этот параметр особенно эффективен, поскольку он показывает несколько результатов одновременно, в отличие от сценариев или поиска цели. В приведенном ниже примере мы можем просмотреть 24 возможных результата для автокредита.
    Использование таблицы данных для сравнения различных условий и процентных ставок

Для получения дополнительной информации о таблицах данных ознакомьтесь с этой статьей от Microsoft.

Задание!

  1. Откройте существующую книгу Excel 2010 . Если вы хотите, вы можете использовать этот пример .
  2. Используйте Цель поиска, чтобы определить неизвестное значение. Если вы используете пример, перейдите на рабочий лист History Class и используйте Goal Seek, чтобы определить, какой уровень вам нужен на Test 3, чтобы получить итоговый средний показатель в 90 баллов .
  3. Вставьте функцию PMT в рабочий лист. Если вы используете пример, перейдите на рабочий лист автомобиля и вставьте функцию в ячейку B5 .
  4. Используйте Goal Seek, чтобы найти процентную ставку , необходимую для ежемесячного платежа в размере 400 долларов США . Какая процентная ставка вам понадобится, если вы можете позволить себе только ежемесячный платеж в размере 380 долларов США ?

Залишити відповідь