Что делать, если анализ в Excel

Обновлено: 21.11.2024

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

Предположим, вы владеете книжным магазином и храните 100 книг. Вы продаете определенный процент по самой высокой цене в 50 долларов и определенный процент по более низкой цене в 20 долларов.

Если вы продаете 60 % по самой высокой цене, в ячейке D10 рассчитывается общая прибыль: 60 * 50 долл. США + 40 * 20 долл. США = 3 800 долл. США.

Создавайте разные сценарии

Но что, если вы продадите 70 % по самой высокой цене? А что если продать 80% по самой высокой цене? Или 90%, или даже 100%? Каждый другой процент — это другой сценарий. Для создания этих сценариев можно использовать диспетчер сценариев.

Примечание. Вы можете просто ввести другой процент в ячейку C4, чтобы увидеть соответствующий результат сценария в ячейке D10. Однако анализ «что, если» позволяет легко сравнивать результаты различных сценариев. Читайте дальше.

<р>1. На вкладке "Данные" в группе "Прогноз" нажмите "Анализ возможных вариантов".

<р>2. Нажмите Диспетчер сценариев.

Появится диалоговое окно "Диспетчер сценариев".

<р>3. Добавьте сценарий, нажав «Добавить».

<р>4. Введите имя (60 % по максимальной цене), выберите ячейку C4 (% продано по максимальной цене) для изменяющихся ячеек и нажмите OK.

<р>5. Введите соответствующее значение 0,6 и снова нажмите OK.

<р>6. Затем добавьте еще 4 сценария (70 %, 80 %, 90 % и 100 %).

Наконец, ваш диспетчер сценариев должен соответствовать изображению ниже:

Примечание: чтобы увидеть результат сценария, выберите сценарий и нажмите кнопку "Показать". Excel соответствующим образом изменит значение ячейки C4, чтобы вы могли увидеть соответствующий результат на листе.

Сводка сценария

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

<р>1. Нажмите кнопку «Сводка» в диспетчере сценариев.

<р>2. Затем выберите ячейку D10 (общая прибыль) для ячейки результата и нажмите OK.

Вывод: если вы продаете 70 % по самой высокой цене, вы получаете общую прибыль в размере 4 100 долларов США, если вы продаете 80 % по самой высокой цене, вы получаете общую прибыль в размере 4 400 долларов США и т. д. Вот как легко провести анализ возможных вариантов. в Excel можно.

Поиск цели

Что делать, если вы хотите узнать, сколько книг вам нужно продать по самой высокой цене, чтобы получить общую прибыль ровно в 4700 долларов США? Вы можете использовать функцию поиска цели Excel, чтобы найти ответ.

<р>1. На вкладке "Данные" в группе "Прогноз" нажмите "Анализ возможных вариантов".

<р>2. Нажмите "Поиск цели".

Появится диалоговое окно "Поиск цели".

<р>3. Выберите ячейку D10.

<р>4. Нажмите в поле "В значение" и введите 4700.

<р>5. Нажмите в поле "Изменив ячейку" и выберите ячейку C4.

Результат. Вам нужно продать 90 % книг по самой высокой цене, чтобы получить общую прибыль ровно 4700 долларов США.

поиск меню

Урок 21. Использование анализа «что, если»

Введение

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

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

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

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

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

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

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

Использование поиска цели (пример 1):

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

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

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

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

Выбор ячейки B7

Выбор «Поиск цели» в раскрывающемся меню

  • Задать ячейку: это ячейка, которая будет содержать желаемый результат. В нашем примере ячейка B7 уже выделена.
  • В значение: это желаемый результат. В нашем примере мы введем 70, потому что нам нужно заработать как минимум это, чтобы пройти курс.
  • Изменив ячейку. Это ячейка, в которую Goal Seek поместит свой ответ. В нашем примере мы выберем ячейку B6, потому что хотим определить оценку, которую нам нужно получить за итоговое задание.

Введите нужные значения в диалоговое окно и нажмите OK

Нажимаем ОК

Выполненный поиск цели и рассчитанное значение

Использование поиска цели (пример 2):

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

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

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

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

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

  1. Выберите ячейку, в которой должна находиться функция.
  2. На вкладке "Формула" выберите команду "Финансы".

Финансовая команда

Выбор функции PMT

Ввод значений в необходимые поля

Ежемесячный платеж, не включая проценты

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

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

  1. На вкладке "Данные" нажмите команду "Анализ "что, если"".
  2. Выберите Поиск цели.

Выбор поиска цели

  • Установить ячейку: это ячейка, которая будет содержать желаемый результат (в данном случае ежемесячный платеж). В этом примере мы установим для него значение B5 (не имеет значения, абсолютная это ссылка или относительная).
  • В значение: это желаемый результат. Мы установим его на -400. Поскольку мы делаем платеж, который будет вычтен из суммы кредита, мы должны ввести платеж как отрицательное число.
  • Изменив ячейку. Это ячейка, в которую система поиска цели поместит свой ответ (в данном случае процентную ставку). Мы установим его на B4.

Ввод значений в поля поиска цели

Решение найдено поиском цели

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

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

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

Использование диспетчера сценариев для сравнения различных вариантов

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

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

Таблицы данных

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

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

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

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

Большинство анализов типа «что если» — это действительно математические расчеты, и это специализация Excel. Чтобы помочь вам выполнить анализ «что, если», Excel использует команды из группы команд «Прогноз» на вкладке «Данные» для подготовки простых прогнозов или расширенных бизнес-моделей.

Загрузите бесплатный учебный файл Excel

Используйте этот бесплатный файл Excel, чтобы попрактиковаться в анализе "что если" вместе с учебным пособием.

Поиск цели

Самый простой инструмент анализа чувствительности в Excel — поиск цели. Предполагая, что вы знаете единственный результат, которого хотите достичь, функция поиска цели в Excel позволяет достичь этой цели путем математической корректировки одной переменной в уравнении.

Чтобы проиллюстрировать, как это работает, представьте, что банк предлагает процентную ставку 9% годовых по личным кредитам со сроком погашения 24 месяца, и что вы хотели бы занять 40 000 долларов США.

Используя приведенную выше информацию, банк подсчитал, что сумма займа плюс проценты за период кредита составят 47 200 долларов США, как показано в ячейке B5. Сумма, подлежащая выплате каждый месяц, также рассчитывается и отображается в ячейке B6.

С помощью команды "Поиск цели" мы можем указать желаемый результат, а Excel определит корректировку, которую нам нужно внести в одну переменную.

В приведенном выше примере ячейка B5 зависит от переменных в ячейках B1, B2 и B3. Ячейка B6 зависит от ячеек B3 и B5. Поэтому, если мы определим, что указанная сумма ежемесячного погашения превышает желаемую, мы можем использовать поиск цели, чтобы установить ежемесячную сумму в размере 1750 долларов США. Excel может работать в обратном порядке, чтобы изменить ячейку B1, B2 или B3 для достижения этой цели.

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

Нашей первой целью может быть выяснить, сколько мы сможем занять, если будем платить 1750 долларов США в месяц, а все остальные переменные останутся прежними. Excel изменит участника (B1) на основе числа, которое мы вводим в качестве нового значения для ячейки B6.

Предполагая, что сумма процентов (9%) и срок кредита (24 месяца) остаются прежними, новая основная сумма рассчитывается и отображается в ячейке B1, если существует допустимое решение.

  1. Ячейка, выбранная в поле "Установить ячейку", должна быть ячейкой, содержащей формулу.
  2. Ячейка, выбранная в поле «Изменив ячейку», должна быть ячейкой, содержащей константу.
  3. После выбора «ОК» в окне «Статус поиска цели» значения на листе корректируются, и их можно получить, только выбрав команду «Отменить» (сочетание клавиш Ctrl+Z в Windows/сочетание клавиш Cmd+Z в Mac).
    4. ли>

Менеджер сценариев

Еще один инструмент анализа "что если" — диспетчер сценариев. Этот параметр несколько более продвинут, чем поиск цели, поскольку позволяет корректировать несколько переменных одновременно.

Некоторые другие заметные различия между Goal Seek и Scenario Manager перечислены ниже:

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

Если мы продолжим наш пример с банковским кредитом, мы сможем определить чувствительность нашей модели к изменениям, скорректировав некоторые или все значения в ячейках B1, B2 или B3.

Рекомендации

Рекомендуется сохранить исходные данные рабочего листа как сценарий, чтобы вы могли вернуться к ним после завершения всех экспериментов.

Шаг 1. Нажмите "Анализ "что, если"" на вкладке "Данные" и выберите "Диспетчер сценариев".

Шаг 2. Нажмите «Добавить» во всплывающем окне диспетчера сценариев.

Шаг 3. Назовите этот сценарий «Исходный» и введите ссылки на все ячейки с постоянными значениями, которые вы можете изменить в других сценариях (максимум 32 ячейки). Нажмите "ОК".

Шаг 4. Для «Исходного» сценария не изменяйте никакие значения в окне «Значения сценария».

Шаг 5. Нажмите "Добавить", чтобы создать свой первый экспериментальный сценарий.

Создание экспериментальных сценариев

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

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

Как показано выше, наш экспериментальный сценарий получил название "36 месяцев" и относится к ячейкам с B1 по B3 как к меняющимся ячейкам. Дополнительный комментарий указывает, что этот сценарий предназначен для определения эффекта займа 40 000 долларов США в течение 36 месяцев.

В окне «Значения сценария» каждая изменяющаяся ячейка отображается как поле, в котором мы можем манипулировать постоянным значением, чтобы повлиять на результат зависимых ячеек — в нашем случае ячеек B5 и B6. Как описано в названии нашего сценария и комментариях, мы корректируем только ячейку B3, изменяя значение на 36.

Чтобы добавить еще один сценарий, выберите «Добавить». Если нет, нажмите OK.

Настройка нескольких переменных

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

Например, чтобы заставить Excel выполнить анализ «что, если» при заимствовании 50 000 долларов США в течение 36 месяцев в описанной выше ситуации с той же процентной ставкой, мы просто откорректируем поля, ссылающиеся на эти переменные, после создания нового сценария. Диспетчер сценариев Excel может обрабатывать неограниченное количество сценариев, созданных таким же образом.

Список созданных сценариев можно просмотреть, нажав кнопку "ОК" в окне "Значения сценария" или выбрав "Диспетчер сценариев" в раскрывающемся меню "Что, если анализ".

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

Сводка сценария

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

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

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

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

При выборе типа отчета "Сводная таблица сценария" отчет сводной таблицы будет создан на новом листе. Узнайте больше о сводных таблицах в нашей библиотеке ресурсов.

Использование таблиц данных для анализа "что если"

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

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

Таблицы данных с одной переменной

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

Шаг 1. Составьте список всех возможных сумм основного долга. Список может быть по столбцу или по строке. В нашем примере мы введем список столбцов в диапазоне от D9 до D12.

Шаг 2. В соседнем столбце введите формулу, которая использовалась для получения исходного результата. В этом случае мы можем просто ввести =B6 в ячейку E8. Это связывает нашу новую таблицу данных с исходными переменными.

Шаг 3. Выберите весь диапазон таблицы данных, включая список значений переменных, формулу и пустые ячейки.

Шаг 4. В раскрывающемся меню "Что, если анализ" выберите "Таблица данных".

Шаг 5. В поле ячейка ввода столбца (поскольку мы ввели наши переменные в формате столбца) введите ссылку на ячейку, которая использовалась для вычисления результата в исходном наборе данных. В приведенном выше примере это будет ячейка B1, так как это переменная, которую мы скорректировали. В этом примере в поле «Ячейка ввода строки» не вводится значение, так как это таблица данных с одной переменной.

Шаг 6. Нажмите кнопку ОК. Результатом является список результатов, созданный путем корректировки одной переменной в ячейке B1 при условии, что все остальные переменные остаются постоянными.

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

Таблицы данных с двумя переменными

При создании таблицы данных с двумя переменными один набор значений отображается горизонтально, а другой — вертикально. В нашем примере мы добавим период кредита (срок) в качестве второй переменной, отображаемой горизонтально.

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

Как и раньше, выделите весь диапазон таблицы данных и выберите пункт «Таблица данных» в меню «Что, если анализ». Ячейка ввода строки — это ссылка на ячейку (B3), которая соответствует горизонтальным переменным из исходного набора данных, а ячейка ввода столбца (B1) соответствует вертикальным переменным.

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

Обзор

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

После того, как вы освоите их, вам захочется посетить наш ресурсный центр и пройти наш базовый и продвинутый курс Excel, чтобы стать настоящим профессионалом!

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

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

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

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

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

Последипломная программа по бизнес-анализу

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

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

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

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

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

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

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

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

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

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

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

Нажав на сводку, вы увидите новое диалоговое окно. Выберите Сводка сценария и нажмите OK.

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

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

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

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

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

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

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

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

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

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

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

Бесплатный курс: Введение в MS Excel

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

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

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

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

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

  • Электромагнитные помехи
  • Общая сумма
  • Сумма процентов

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

Общая сумма

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

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

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

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

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

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

Теперь выберите OK, и окончательные данные будут выглядеть так, как показано ниже.

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

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

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

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

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

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

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

Ждете карьеры бизнес-аналитика? Ознакомьтесь с сертификационным курсом по бизнес-аналитике с помощью Excel и пройдите сертификацию уже сегодня.

Дальнейшие шаги

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

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

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

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

Найдите наш онлайн-учебный курс для аспирантов по бизнес-анализу в лучших городах:

< tr>
ИмяДатаМесто
Программа последипломного образования в бизнес-анализе Когорта начинается 29 марта 2022 г.,
группа выходного дня		Ваш городПодробнее
Программа последипломного образования в области бизнес-анализа Когорта начинается 12 апреля 2022 г.,
группа выходного дня		Ваш городПодробности< /td>
Программа последипломного образования в области бизнес-анализа Когорта начинается 19 апреля 2022 г.,
Группа выходного дня		Ваш городПодробнее

Об авторе

Шрути М

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

Читайте также: