Что такое анализ «что если» в Excel и его 3 типа

Анализ «что если» в Excel — это процедура, применяемая к листам Excel с формулами для просмотра табличных данных, когда любые изменения применяются к исходным значениям без необходимости повторного создания нового листа. У нас есть три типа анализа «что если», как показано ниже.

Менеджер сценариев анализа «что если» в Excel

Менеджер сценариев в анализе «Что если» Excel используется для использования исходных данных и математических формул, реализованных на основе данных, для воссоздания другого сценария, который наследует сходства из предыдущей таблицы и генерирует новую таблицу.

Для лучшего усвоения материала давайте представим, что вы управляете ИТ-компанией и у вас есть сотрудники из трех разных групп, а именно:

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

Станьте специалистом по обработке данных с помощью практического обучения!Программа магистратуры для специалистов по обработке данныхИзучить программу

Давайте рассмотрим пример. На следующем изображении показаны данные сотрудников группы B C. Ячейки B8, B16 и B18 содержат математическую формулу, которая вычисляет сумму других ячеек над ними и определяет их как «Компенсация от компании», «Счет за техническое обслуживание на одного сотрудника» и «Общие расходы компании» соответственно.

Теперь давайте воспользуемся этой таблицей и менеджером сценариев из анализа «Что если» в Excel, чтобы воссоздать ту же таблицу для сотрудников группы B и группы A.

Выберите ячейки, содержащие числовые данные, и проигнорируйте ячейки с математическими формулами, как показано ниже.

Перейдите к параметру «Данные» на панели инструментов и выберите параметр «Анализ Excel «Что если»» на ленте «Прогноз», как показано ниже.

На экране появится диалоговое окно с пустыми сценариями. Чтобы добавить сценарий, выберите опцию добавления, как показано ниже.

Затем на экране появится новое диалоговое окно с адресами ячеек. Введите имя для сценария и нажмите OK. Здесь мы выбрали Band C в качестве имени для этого сценария.

На следующем экране отобразится небольшое диалоговое окно со всеми готовыми значениями для Band C, как показано ниже.

Аналогично, вы можете добавить еще два сценария: Band B и Band A в этом примере. Вы можете выбрать опцию добавления, и у вас появится новое диалоговое окно с редактируемыми параметрами данных. Теперь обновите значения для Band B и нажмите OK, как показано ниже.

И последний шаг — создание сценария для Band A. Чтобы обновить значения, выполните ту же процедуру, что и раньше: нажмите «Добавить» и отредактируйте значения в новом диалоговом окне, а затем нажмите «ОК», как показано ниже.

Теперь, когда вы добавили все значения, у вас есть три различных сценария, а именно:

Группа А

Группа Б

Группа С

Если вы нажмете на опцию «Сводка», вы также сможете создать полную сводку.

После нажатия на сводку вы увидите новое диалоговое окно. Выберите Сводку сценария и нажмите ОК.

Затем на новом листе вы получите полную подробную сводку всех трех таблиц. Первый столбец полностью редактируемый, а адреса ячеек можно называть именами строк в исходных данных.

На следующем этапе этой статьи по анализу «что если» в Excel мы узнаем о поиске цели в Excel.

Станьте специалистом по обработке данных с помощью практического обучения!Программа магистратуры для специалистов по обработке данныхИзучить программу

Анализ «Что если» в Excel. Поиск цели в Excel

На вкладке Excel What-if Analysis у нас есть опция Goal Seek in Excel. Эта опция помогает пользователю запустить все возможные перестановки и комбинации для генерации или достижения цели в результирующей ячейке.

Чтобы понять это более подробно, давайте рассмотрим следующий пример. Здесь у нас есть набор студентов, список их оценок и их итоговый совокупный балл.

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

Мы можем использовать Excel What-if Analysis и использовать Goal Seek в методе Excel, чтобы предсказать требуемые баллы для достижения 60% или выше в качестве целевого агрегата. Чтобы реализовать поиск цели в Excel, вам нужно выбрать ячейку из столбца агрегата, как показано ниже.

Теперь нажмите на опцию данных на панели инструментов и перейдите на ленту прогнозов и нажмите на кнопку анализа Excel what-if. Появится небольшое окно, и вы должны выбрать вторую опцию, которая называется Goal Seek, как показано ниже.

На экране появится небольшое окно, и здесь вам нужно указать местоположение целевой ячейки, целевое значение и ячейку, которую вы хотите изменить. Здесь ваша целевая ячейка — M4, целевое значение — 60, и поскольку вы хотите написать экзамен по улучшению по предмету Engg 4, адрес ячейки будет I4.

После ввода значений нажмите OK, и Excel автоматически выполнит все перестановки и комбинации и предоставит результат. В этом случае результат или предоставленный результат — это целевой балл, который вам необходимо получить по предмету «Engg 4», чтобы набрать 60% совокупно.

На следующем этапе анализа «что если» в Excel мы изучим таблицу данных.

Станьте специалистом по обработке данных с помощью практического обучения!Программа магистратуры для специалистов по обработке данныхИзучить программу

Таблица данных анализа «Что если» в Excel

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

Давайте предположим, что вы берете в банке автокредит. Сумма вашего кредита составляет два лакха рупий, а процентная ставка составляет 10% при сроке владения 25 месяцев.

Теперь вы хотите рассчитать и увидеть различные ставки EMI для различных процентных ставок.

Итак, давайте посчитаем следующее.

• ЭМИ
• Общая сумма
• Сумма процентов

Для расчета вышеупомянутых полей нам потребуются следующие соответствующие формулы, как показано ниже.

ЭМИ

Общая сумма

Сумма процентов

Итоговая таблица будет выглядеть следующим образом.

Основная постановка задачи — определить EMI для различных процентных ставок.

Теперь создайте новый столбец со всеми процентными ставками, которые вы хотите найти. После этого напишите адрес ячейки EMI в вашей таблице в следующем столбце. В данном случае это B7. Таблица должна выглядеть примерно так, как показано ниже.

Теперь выделите всю таблицу, нажмите «Анализ Excel «Что если»» и выберите опцию таблицы данных.

На экране появится новое окно. В диалоговом окне укажите скорость адреса ячейки интереса. В данном случае адрес ячейки — B3. Изображение будет выглядеть следующим образом.

Теперь нажмите «ОК», и окончательные данные появятся, как показано ниже.

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

Станьте специалистом по обработке данных с помощью практического обучения!Программа магистратуры для специалистов по обработке данныхИзучить программу

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

Посмотрите на первую ячейку. В этом случае G11 хранит данные рупий 8895,42; это значение заимствовано из ячейки B7 l=, как и в предыдущем примере.

Теперь выберите весь набор строк и столбцов, как показано выше, затем выберите анализ «что если» в Excel и щелкните таблицу данных.

Появится следующее диалоговое окно. Введите входные данные. В этом случае вам необходимо указать адрес ячейки процентной ставки и суммы кредита. Таким образом, ваши адреса ячеек — B3 и B7.

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

На этом мы подходим к концу статьи. Надеемся, статья оказалась для вас информативной и полезной.

Следующие шаги

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

Хотите ли вы изучить и получить больше знаний о бизнес-аналитике с помощью Microsoft Excel, а также пройти онлайн-обучение и сертификацию?

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

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