Анализ “что если” в Excel

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

Подбор параметра

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

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

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

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

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

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

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

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

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

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

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

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