С помощью средств анализа "что если" в Excel вы можете экспериментировать с различными наборами значений в одной или нескольких формулах, чтобы изучить все возможные результаты.
Например, можно выполнить анализ "что если" для формирования двух бюджетов с разными предполагаемыми уровнями дохода. Или можно указать нужный результат формулы, а затем определить, какие наборы значений позволят его получить. В Excel предлагается несколько средств для выполнения разных типов анализа.
Обратите внимание на то, что в этой статье приведен только обзор инструментов. Подробные сведения о каждом из них можно найти по ссылкам ниже.
Обзор
Анализ "что если" — это процесс изменения значений в ячейках, который позволяет увидеть, как эти изменения влияют на результаты формул на листе.
В Excel поставляются три вида средств анализа What-If: сценарии, поиск целей и таблицы данных. В сценариях и таблицах данных берутся наборы входных значений и определяются возможные результаты. Таблицы данных работают только с одной или двумя переменными, но могут принимать множество различных значений для них. Сценарий может содержать несколько переменных, но допускает не более 32 значений. Подбор параметров отличается от сценариев и таблиц данных: при его использовании берется результат и определяются возможные входные значения для его получения.
В дополнение к этим трем средствам можно установить надстройки, которые помогают выполнять анализ What-If, например надстройку Решателя. Надстройка Solver похожа на Goal Seek, но она может вместить больше переменных. Вы также можете создавать прогнозы, используя маркер заполнения и различные команды, встроенные в Excel.
Для более сложных моделей можно использовать надстройку Analysis ToolPak.
Использование сценариев для учета множества разных переменных
Сценарий — это набор значений, которые 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 или получить поддержку в сообществах.
См. также
Использование решателя для бюджетирования капитала
Использование решателя для определения оптимального набора продуктов
Постановка и решение задачи с помощью надстройки "Поиск решения"
Полные сведения о формулах в Excel
Рекомендации, позволяющие избежать появления неработающих формул