С помощью средств анализа What-If в классическом приложении Excel можно использовать несколько наборов значений в одной или нескольких формулах для изучения всех различных результатов.
Например, можно выполнить анализ "что если" для формирования двух бюджетов с разными предполагаемыми уровнями дохода. Или можно указать нужный результат формулы, а затем определить, какие наборы значений позволят его получить. В Excel предлагается несколько средств для выполнения разных типов анализа.
Обратите внимание на то, что в этой статье приведен только обзор инструментов. Подробные сведения о каждом из них можно найти по ссылкам ниже.
Обзор
Анализ "что если" — это процесс изменения значений в ячейках, который позволяет увидеть, как эти изменения влияют на результаты формул на листе.
В состав Excel входят три вида средств анализа What-If: сценарии, поиск цели и таблицы данных. В сценариях и таблицах данных берутся наборы входных значений и определяются возможные результаты. Таблицы данных работают только с одной или двумя переменными, но могут принимать множество различных значений для них. Сценарий может содержать несколько переменных, но допускает не более 32 значений. Подбор параметров отличается от сценариев и таблиц данных: при его использовании берется результат и определяются возможные входные значения для его получения.
В дополнение к этим трем инструментам вы можете установить надстройки, помогающие выполнять What-If Анализ, например надстройку "Поиск решения". Надстройка "Поиск решения" похожа на "Поиск цели", но может вместить больше переменных. Вы также можете создавать прогнозы, используя маркер заполнения и различные команды, встроенные в Excel.
Для более сложных моделей можно использовать надстройку Analysis ToolPak.
Использование сценариев для учета множества разных переменных
Сценарий — это набор значений, которые Excel сохраняет и может автоматически подставлять в ячейки на листе. Вы можете создавать и сохранять различные группы значений на листе, а затем переключиться на любой из этих новых сценариев, чтобы просмотреть другие результаты.
Предположим, у вас есть два сценария бюджета: для худшего и лучшего случаев. Вы можете с помощью диспетчера сценариев создать оба сценария на одном листе, а затем переключаться между ними. Для каждого сценария вы указываете изменяемые ячейки и значения, которые нужно использовать. При переключении между сценариями результат в ячейках изменяется, отражая различные значения изменяемых ячеек.
1. Изменение ячеек
2. Ячейка результата
1. Изменение ячеек
2. Ячейка результата
Если у нескольких человек есть конкретные данные в отдельных книгах, которые вы хотите использовать в сценариях, вы можете собрать эти книги и объединить их сценарии.
После создания или сбора всех нужных сценариев вы можете создать сводный отчет по сценариям, в который включаются данные из этих сценариев. В отчете по сценариям все данные отображаются в одной таблице на новом листе.
Примечание
В отчетах по сценариям автоматический пересчет не выполняется. Изменения значений в сценарии не будут отражается в уже существующем сводном отчете. Вам потребуется создать новый сводный отчет.
Использование подбора параметров для получения нужного результата
Если вы знаете, какой результат требуется от формулы, но не уверены, какое входное значение требуется для получения этого результата, вы можете использовать функцию "Поиск параметров ". Предположим, что вам нужно занять денег. Вы знаете, сколько вам нужно, на какой срок и сколько вы сможете выплачивать каждый месяц. С помощью средства подбора параметров вы можете определить, какая процентная ставка вам подойдет.
Ячейки B1, B2 и B3 являются значениями суммы кредита, срока его действия и процентной ставки.
Ячейка B4 содержит результат формулы =ПЛТ(B3/12;B2;B1).
Примечание
Подбор параметров работает только с одним входным значением переменной. Если необходимо определить несколько входных значений, например сумму кредита и сумму ежемесячного платежа по займу, вместо этого следует использовать надстройку "Поиск решения". Дополнительные сведения о надстройке "Поиск решения" см. в разделе Подготовка прогнозов и расширенных бизнес-моделей, а также перейдите по ссылкам в разделе См. также.
Использование таблиц данных для просмотра влияния переменных в формуле
Если есть формула с одной или двумя переменными или несколько формул с одной общей переменной, вы можете использовать таблицу данных для просмотра всех результатов в одном месте. С помощью таблиц данных можно легко и быстро проверить несколько возможностей. Поскольку используются всего одна или две переменные, результат можно без труда прочитать или опубликовать в табличной форме. Если для книги включен автоматический пересчет, данные в таблицах данных сразу же пересчитываются, и вы всегда видите свежие данные.
Ячейка B3 содержит входное значение.
Ячейки C3, C4 и C5 являются значениями, которые Excel подставляет на основе значения, введенного в ячейку B3.
В таблицу данных нельзя помещать больше двух переменных. Для анализа большего количества переменных используйте сценарии. Несмотря на то что переменных не может быть больше двух, можно использовать сколько угодно различных значений переменных. В сценарии можно использовать не более 32 различных значений, зато вы можете создать сколько угодно сценариев.
Подготовка прогнозов и расширенных бизнес-моделей
При подготовке прогнозов вы можете использовать Excel для автоматической генерации будущих значений на базе существующих данных или для автоматического вычисления экстраполированных значений на основе арифметической или геометрической прогрессии.
Можно заполнить ряд значений, соответствующий простому линейному или экспоненциальному тренду роста, с помощью маркера заполнения или команды "Ряд ". Для расширения сложных и нелинейных данных можно использовать функции листа или инструмент регрессионного анализа в надстройке Analysis ToolPak.
Несмотря на то что функция "Поиск параметра" может вместить только одну переменную, с помощью надстройки "Поиск решения" можно проецировать изображение в обратном направлении для получения дополнительных переменных. Надстройка "Поиск решения" помогает найти оптимальное значение для формулы в одной ячейке листа, которая называется целевой.
Надстройка "Поиск решения" работает с группой ячеек, связанных с формулой в целевой ячейке. Надстройка "Поиск решения" изменяет значения в указанных вами изменяющихся ячейках (называемых регулируемыми ячейками), чтобы получить результат, указанный из формулы целевой ячейки. Ограничения могут применяться для ограничения значений, которые надстройка "Поиск решения" может использовать в модели, а также ссылаться на другие ячейки, влияющие на формулу целевой ячейки.
Дополнительные сведения
Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.
См. также
Использование надстройки "Поиск решения" для бюджетирования капитальных вложений
Постановка и решение задачи с помощью надстройки "Поиск решения"
Полные сведения о формулах в Excel
Рекомендации, позволяющие избежать появления неработающих формул