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

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

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

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

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

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

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

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

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

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

Сценарий наихудшего случая
Сценарий наихудшего случая

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

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

Сценарий наилучшего случая
Сценарий наилучшего случая

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

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

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

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

Сводный отчет по сценариям Excel
Сводный отчет по сценариям

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

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

Модель, в которой размер платежа зависит от процентной ставки

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

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

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

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

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

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

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

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

К началу страницы

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

Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.

См. также

Сценарии

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

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

Использование средства "Поиск решения" для создания бюджета

Использование средства "Поиск решения" для финансового планирования

Использование средства "Поиск решения" для определения оптимального ассортимента

Анализ "что если" с помощью средства "Поиск решения"

Определение и решение задачи с помощью средства "Поиск решения"

Надстройка "Пакет анализа"

Обзор формул в Excel

Рекомендации, позволяющие избежать появления неработающих формул

Исправление распространенных ошибок в формулах с помощью функции проверки ошибок

Сочетания клавиш в Excel 2016 для Windows

Сочетания клавиш в Excel 2016 для Mac

Функции Excel (по алфавиту)

Функции Excel (по категориям)

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

Были ли сведения полезными?

Спасибо за ваш отзыв!

Благодарим за отзыв! Возможно, будет полезно связать вас с одним из наших специалистов службы поддержки Office.

×