Анализ «что, если» в Excel: Подбор параметра, Диспетчер сценариев и Таблицы данных

Ему нужны три параметра:

• Установить в ячейке: ячейка с вашей формулой (выход)
• Значение: целевой результат
• Изменяя ячейку: входная ячейка, которую Excel может корректировать

Посмотрим на практике. 

Вы управляете небольшим бизнесом по продаже блокнотов. Текущая выручка — $25,000, цель — $50,000. Цена фиксирована на уровне $25, значит, нужно узнать, сколько единиц нужно продать. 

Делайте так:

1. Откройте Данные > Анализ "что, если" > Подбор параметра

2. В поле Установить в ячейке щёлкните по ячейке B4 (ячейка с формулой выручки)

3. В поле Значение введите 50000

4. В поле Изменяя ячейку выберите B2 (Количество проданных единиц)

5. Нажмите ОК

Подбор параметра изменит B2 с 1 000 до 2 000 единиц — именно столько нужно продать, чтобы получить $50 000 выручки при $25 за единицу.

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

Как использовать Диспетчер сценариев в Excel

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

Вы можете создать столько, сколько нужно, и мгновенно переключаться между ними для сравнения результатов. 

Это полезно, когда вы хотите спланировать оптимистичный, пессимистичный и базовый варианты без трёх отдельных файлов. 

Ему нужны три вещи:

• Название сценария: метка для каждого набора допущений
• Изменяемые ячейки: входные ячейки, отличающиеся в разных сценариях
• Значения: конкретные числа для каждого сценария

Создание сценария

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

Предположим, текущая прибыль — $15,000. Вы хотите смоделировать три сценария: лучший, ожидаемый и худший. 

Как создать сценарий:

1. Откройте Данные > Анализ-"что, если" > Диспетчер сценариев

2. В окне Диспетчер сценариев нажмите Добавить

3. В поле Название сценария введите Best Case.

4. В поле Изменяемые ячейки выберите B2:B4.

5. Появится диалоговое окно Значения сценария. Введите следующие значения:

• B2 = 2000, 

• B3 = $30

• B4= $10

6. Нажмите ОК. 

Повторите шаги для двух оставшихся сценариев, используя значения ниже:

Создайте сценарий с тремя вариантами. Изображение автора.

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

Нажмите «Показать», чтобы увидеть значения каждого сценария. Изображение автора.

Редактирование сценариев

Выберите сценарий в списке и нажмите Изменить. Можно переименовать его, изменить диапазон ячеек или обновить значения. Ничего на листе не меняется, пока вы не нажмёте Показать

Создание отчётов по сценариям

Диспетчер сценариев также может создать отчёт, который сравнит все сохранённые сценарии рядом. 

Чтобы создать отчёт по сценариям:

1. В окне Диспетчер сценариев нажмите Итоги

2. В окне Сводка по сценарию выберите Сводка по сценарию

3. В поле Итоговые ячейки выберите B7 (ячейка с прибылью)

4. Нажмите ОК

Excel создаст сводную таблицу на новом листе с тремя сценариями, показанными рядом

Создание сводного отчёта по сценариям. Изображение автора.

Как использовать Таблицы данных в Excel

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

Им нужны три параметра:

• Диапазон входных значений: какие значения вы хотите протестировать
• Входная ячейка строки/столбца: ячейка в формуле, которую эти значения будут замещать
• Ячейка с формулой: выход, вокруг которого строится таблица

Одномерные таблицы данных

Одномерная таблица данных проверяет, как изменение одного входа влияет на результат формулы.

Например, у вас есть формула платежа по кредиту, и вы хотите увидеть, как меняется ежемесячный платёж при ставках от 5% до 10%.

Чтобы создать одномерную таблицу данных:

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

Создайте одномерную таблицу данных. Изображение автора.

2. Затем выделите весь диапазон D1:E9 (включая E1)

3. Откройте Данные > Анализ "что, если" > Таблица данных

4. Поле Входные данные по строке оставьте пустым

5. В поле Входные данные по столбцу выберите B4 (процентная ставка)

6. Нажмите ОК.

Excel заполнит таблицу рассчитанными результатами для каждого входного значения. Обратите внимание, что платёж растёт неравномерно. Переход с 3% на 4% даёт +$49, а с 9% на 10% — уже +$61. Чем выше ставка, тем сильнее эффект её изменения.

Рассчитанные результаты для каждого входного значения. Изображение автора.

Двумерные таблицы данных

Двумерная таблица данных проверяет, как одновременное изменение двух входов влияет на результат формулы.

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

Сначала подготовьте набор данных. 

Посмотрите, какой набор данных используется:

• В ячейке D1 введите =B5. Это ссылка на формулу, она должна быть в левом верхнем углу сетки, а не рядом с меткой

• По строке 1, начиная с E1, перечислите планируемые объёмы продаж: 500, 1000, 1500, 2000

• В столбце D, начиная с D2, перечислите ценовые уровни: $20, $25, $30, $35

Подготовьте набор данных для двумерной таблицы данных. Изображение автора.

Чтобы создать двумерную таблицу данных:

1. Выделите диапазон D1:H5.

2. Откройте Данные > Анализ «что, если» > Таблица данных

3. В поле Входные данные по строке выберите B3 (продано единиц)

4. В поле Входные данные по столбцу выберите B2 (цена за единицу)

5. Нажмите ОК.

Excel заполнит прибыль для каждой комбинации цены и проданных единиц

Рассчитанные результаты для входных значений. Изображение автора.

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

Подбор параметра vs. Диспетчер сценариев vs. Таблицы данных

Подбор параметра, Диспетчер сценариев и Таблицы данных входят в набор инструментов анализа «что, если» Excel, но решают разные задачи. 

Вот краткое сравнение между ними 

Анализ «что, если» и Поиск решения

Анализ «что, если» и Поиск решения помогают исследовать разные исходы в Excel, но решают разные типы задач.

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

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

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

Лучшие практики анализа «что, если»

Следуйте этим рекомендациям при работе с анализом «что, если». 

1. Отделяйте допущения от формул

Храните входные значения в отдельных ячейках и ссылайтесь на них из формул. Избегайте «зашивания» чисел прямо в расчёты. 

Например, эту формулу неудобно обновлять:

=(50*1000)-10000

А этот вариант намного проще сопровождать:

=(B2*B3)-B4

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

2. Чётко называйте сценарии

Названия сценариев должны сразу говорить, что они представляют. «Сценарий 1», «Сценарий 2», «Сценарий 3» через полгода ни о чём не скажут. Называйте по сути, например «Лучший случай: рост 15%». 

3. Проверяйте входные данные

Надёжность анализа «что, если» зависит от корректности тестируемых входов. Перед запуском проверьте, что:

• Проценты введены корректно
• Даты в едином формате
• Единицы измерения согласованы
• Входные значения лежат в реалистичных пределах

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

4. Документируйте допущения

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

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

Распространённые проблемы с анализом «что, если»

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

1. Формулы не обновляются 

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

Как исправить: откройте Формулы > Параметры вычислений и убедитесь, что стоит Автоматически. Если уже так, нажмите F9 для принудительного пересчёта.

2. Неверные ссылки на ячейки

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

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

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

3. Непонимание ограничений Подбора параметра

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

Например, он определит объём продаж, необходимый для прибыли $15 000. Но он не сможет одновременно скорректировать объём, цену и затраты, чтобы достичь этой цели.

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

4. Чрезмерно сложные модели сценариев

Технически Диспетчер сценариев допускает до 32 изменяемых ячеек на сценарий, но это не значит, что так стоит делать. 

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

Итоги 

В любой таблице есть допущения. Это может быть темп роста, цена или ставка.

Анализ «что, если» не устраняет эти допущения. Он показывает, что произойдёт, если они окажутся неверными, — а это и есть основа качественного принятия решений.

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