Анализ что если в excel
Перейти к содержимому

Анализ что если в excel

  • автор:

Введение в анализ «что если»

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

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

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

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

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

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

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

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

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

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

1. Изменяемые ячейки

2. Ячейка результата

1. Изменяемые ячейки

2. Ячейка результата

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

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

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

Использование подбора параметров для получения нужного результата

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

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

Ячейки B1, B2 и B3 — это значения суммы кредита, продолжительности срока и процентной ставки.

Ячейка B4 отображает результат формулы =PMT(B3/12;B2;B1).

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

Использование таблиц данных для просмотра влияния переменных в формуле

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

Анализ ипотечного кредита

Ячейка B3 содержит входное значение.
Ячейки C3, C4 и C5 — это значения, заменяемые Excel на основе значения, введенного в B3.

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

Подготовка прогнозов и расширенных бизнес-моделей

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

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

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

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

Дополнительные сведения

Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Анализ “что если” в 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). Вы можете создать несколько сценариев, а затем сравнить их, не меняя значения вручную. В следующем примере мы используем сценарии для сравнения различных мест проведения мероприятия.
  • Таблицы данных позволяют вам взять одну из двух переменных в формуле, заменить ее любым количеством значений и свести в таблицу результаты. Этот инструмент имеет широчайшие возможности, так как выводит сразу много результатов, в отличие от диспетчера сценариев или выбора параметров.

Анализ данных в Excel с помощью условного форматирования

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

Как это сделать? В Excel имеется набор инструментов, который позволит это выполнить быстро – буквально за пару-тройку щелчков мыши. Самый простой из таких инструментов – условное форматирование. Давайте разберем ситуации, в которых пригодятся условные форматы.

1. Выделение ячеек с определенными значениями

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

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *