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

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

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

В этой статье

Обзор

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

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

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

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

Обзор

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Примечания : 

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

  • Используйте английский вариант этой статьи, который находится здесь, в качестве справочного материала. Вместе с другими участниками сообщества Майкрософт вы можете улучшить эту статью с помощью Community Translation Framework (CTF). Просто наведите указатель мыши на предложение в статье и выберите команду "УЛУЧШИТЬ ПЕРЕВОД" в мини-приложении CTF. Для получения дополнительных сведений о CTF щелкните здесь. Используя CTF, вы соглашаетесь с нашими условиями предоставления услуг.

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

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

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

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

×