Excel 2019 (часть 28): анализ «что если» (анализ «если-то»)

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

Поиск цели

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

Пример 1, как использовать поиск цели

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

На изображении ниже вы можете видеть, что баллы за первые 4 упражнения составляют 58, 70, 72 и 60. Хотя неизвестно, какой будет балл за 5-е упражнение, вы можете написать формулу или функцию для расчета. нужен окончательный балл. В этом случае каждое задание имеет одинаковый вес, поэтому все, что нам нужно сделать, это усреднить все пять баллов, введя =СРЗНАЧ(B2:B6). При использовании поиска цели в ячейке B6 будет отображаться минимальный балл, необходимый для этого окончательного задания.

1. Выберите ячейку, значение которой вы хотите изменить. Всякий раз, когда вы используете поиск цели, вам нужно будет выбрать ячейку, которая уже содержит формулу или функцию. Например, мы выберем ячейку B7, поскольку она содержит формулу = СРЗНАЧ (B2: B6).

2. На вкладке «Данные» нажмите команду «Анализ «что если»» и выберите « Поиск цели» в раскрывающемся меню.

3. Появится диалоговое окно с тремя полями. Первое поле Set cell: будет содержать желаемый результат. В примере выбрана ячейка B7.

Второе поле To value: — это желаемый результат. Например, мы бы ввели 70 , потому что нам нужно набрать хотя бы это число, чтобы пройти курс.

Третье поле « Изменив ячейку: » — это ячейка, в которую Goal Seek поместит свой ответ. Например, мы выберем ячейку B6 , потому что хотим определить балл, который нам нужно набрать в последнем упражнении.

4. Когда закончите, нажмите ОК.

5. В диалоговом окне будет сообщено, удалось ли Goal Seek найти ответ или нет. Нажмите ОК.

6. Результаты появятся в указанной ячейке. В этом примере Goal Seek подсчитал, что для прохождения финального задания необходимо набрать не менее 90 баллов.

Пример 2 использования поиска цели

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

1. Выберите ячейку, значение которой вы хотите изменить. Например, мы выберем ячейку B5.

2. На вкладке «Данные» нажмите команду «Анализ «что если»» и выберите « Поиск цели» в раскрывающемся меню.

3. Появится диалоговое окно с тремя полями. Первое поле Set cell: будет содержать желаемый результат. В примере выбрана ячейка B5.

Второе поле To value: — это желаемый результат. Например, я бы ввел 500, потому что хочу потратить только 500 долларов.

Третье поле « Изменив ячейку: » — это ячейка, в которую Goal Seek поместит свой ответ. Например, я бы выбрал ячейку B4, потому что хочу знать, сколько гостей я могу пригласить, не потратив более 500 долларов.

4. Когда закончите, нажмите ОК.

5. В диалоговом окне будет сообщено, удалось ли Goal Seek найти ответ или нет. Нажмите ОК.

6. Результаты появятся в указанной ячейке. В этом примере Goal Seek рассчитал ответ примерно 18,62. В этом случае окончательный ответ должен быть целым числом, поэтому его необходимо округлить в большую или меньшую сторону. Поскольку округление в большую сторону приведет к превышению бюджета, в примере будет округлено до 18 гостей.

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

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

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

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

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