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

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

Выбор параметра

Каждый раз, когда вы используете формулу или функцию в Excel, вы собираете исходные значения вместе, чтобы получить результат. Выбор параметра работает наоборот. Он позволяет по конечному результату рассчитать начальное значение, которое даст такой результат. Ниже мы приведем несколько примеров, демонстрирующих, как это работает. Выбор параметра.

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

Представьте, что вы ходите в определенное учебное заведение. На данный момент вы набрали 65 баллов, а для прохождения отбора вам необходимо набрать минимум 70 баллов. К счастью, есть последнее задание, которое может увеличить ваши очки. В этой ситуации вы можете использовать Выбор параметраузнать, какой балл нужно набрать по последнему заданию, чтобы поступить в учебное заведение.

На изображении ниже вы можете видеть, что ваши баллы за первые два задания (тест и письмо) составляют 58, 70, 72 и 60. Хотя мы не знаем, какой будет ваш балл за последнее задание (тест 3). , мы можем написать формулу, которая вычисляет средний балл сразу по всем заданиям. Все, что нам нужно, это вычислить среднее арифметическое всех пяти оценок. Для этого введите выражение =ЯДРО(B2:B6) в ячейку B7. После того, как вы подадите заявку Выбор параметра Для решения этой задачи в ячейке В6 будет отображен минимальный балл, который необходимо набрать для поступления в учебное заведение.

1. Выберите ячейку, значение которой вы хотите получить. Каждый раз, когда вы используете инструмент Выбор параметра, Вам необходимо выбрать ячейку, которая уже содержит формулу или функцию. В нашем случае мы выберем ячейку B7, поскольку она содержит формулу =ЯДРО(B2:B6).
2. На вкладке Дополнительно Данные выбрать команду Что делать, если анализ, а затем в раскрывающемся меню выберите Выбор параметра.
3. Появится диалоговое окно с тремя полями:
   • Ротобновление в ячейке — это ячейка, содержащая желаемый результат. В нашем случае это ячейка B7 и мы ее уже выбрали.
   • Значение — это желаемый результат, т. е. результат, который должен быть в ячейке B7. В нашем примере мы введем 70, потому что для участия вам нужно набрать минимум 70.
   • Изменение значения ячейки – ячейка, в которой Excel будет отображать результат. В нашем случае мы выберем ячейку B6, потому что хотим знать оценку, которую хотим получить за последнее задание.
4. После выполнения всех действий нажмите кнопку OK.
5. Excel рассчитает результат и в диалоговом окне Результат выбора параметра дайте решение, если есть. Нажмите OK.
6. Результат появится в указанной ячейке. В нашем примере Выбор параметра установили, что за последнее задание нужно набрать минимум 90 баллов, чтобы пройти дальше.

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

Давайте представим, что вы планируете мероприятие и хотите пригласить как можно больше гостей, не выходя за рамки бюджета в 500 долларов. Вы можете использовать Выбор параметрадля расчета количества гостей, которых вы можете пригласить. В следующем примере ячейка B4 содержит формулу =B1+B2*B3, где суммируется общая стоимость аренды номера и стоимость размещения всех гостей (цена за 1 гостя умножается на их количество).

1. Выберите ячейку, значение которой вы хотите изменить. В нашем случае мы выберем ячейку B4.
2. На вкладке Дополнительно Данные выбрать команду Что делать, если анализ, а затем в раскрывающемся меню выберите Выбор параметра.
3. Появится диалоговое окно с тремя полями:
   • Упосадить в клетку — это ячейка, содержащая желаемый результат. В нашем примере ячейка B4 уже выбрана.
   • Значение является желаемым результатом. Мы введем 500, так как можно потратить 500 долларов.
   • измененияя значение ячейки – ячейка, в которой Excel будет отображать результат. Мы выделим ячейку B3, потому что нам нужно подсчитать количество гостей, которых мы можем пригласить, не превысив бюджет в 500 долларов.
4. После выполнения всех действий нажмите кнопку OK.
5. Диалоговое окно Результат выбора параметра сообщит вам, если решение будет найдено. Нажмите OK.
6. Результат появится в указанной ячейке. В нашем случае Выбор параметра подсчитал результат 18,62. Поскольку мы подсчитываем количество гостей, наш окончательный ответ должен быть целым числом. Мы можем округлить результат в большую или меньшую сторону. Округлив количество гостей, мы превысим заданный бюджет, а значит остановимся на 18 гостях.

Как видно из предыдущего примера, существуют ситуации, когда в качестве результата требуется целое число. Если Выбор параметра возвращает десятичное значение, округляя его в большую или меньшую сторону по мере необходимости.

Другие типы анализа «что, если»

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

• Дменеджер сценариев позволяет подставлять значения сразу в нескольких ячейках (до 32). Вы можете создать несколько сценариев, а затем сравнить их, не изменяя значения вручную. В следующем примере мы используем сценарии для сравнения нескольких разных мест проведения мероприятия.
• таблицы данным позволяют взять одну из двух переменных в формуле и заменить ее любым количеством значений, а результаты суммировать в таблице. Этот инструмент имеет самые широкие возможности, так как отображает сразу много результатов, в отличие от Диспетчер скриптов or Выбор параметра. В следующем примере показаны 24 возможных результата ежемесячных платежей по кредиту: