Анализ «что если» в Excel: как меняется прибыль при другой цене и объёме

Модель прибыли, бюджет или кредит редко считают один раз: руководителю нужен пессимистичный и оптимистичный вариант, а аналитику — сетка итогов при разных ценах. Справка Microsoft ограничивает таблицу данных двумя переменными, а один сценарий — 32 значениями^[1]. Анализ «что если» в Excel избавляет от копий листа: модель одна, меняются только входные числа. Копии листа под каждый вариант быстро расходятся между собой, а модель доходов и расходов на отдельном листе удобнее держать в одном экземпляре.

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

Ключевые факты: анализ «что если» в Excel

• Анализ «что если» — процесс изменения значений в ячейках, чтобы увидеть, как меняются результаты формул^[1].
• Один сценарий вмещает не больше 32 значений, а таблица данных — не больше двух переменных^[1].
• Отчёт по сценариям не пересчитывается сам: изменения видны только в новом отчёте^[2].
• Справка Microsoft относит анализ «что если» к Microsoft 365, Excel 2024 и Excel 2021, включая версии для Mac^[1].
• Сценарий в отличие от таблицы данных хранит готовые наборы чисел, а таблица перебирает список значений одной или двух переменных^[1].

Как устроен анализ «что если» в Excel: три инструмента и их пределы

Анализ «что если» в Excel меняет входные значения модели и показывает, как от этого меняется итог формулы. Все три команды открываются на вкладке «Данные» кнопкой «Анализ "что если"»; в английской справке путь записан как Data → What-If Analysis^[2].

Сценарий справка определяет как набор значений, которые Excel сохраняет и может сам подставить в ячейки листа^[1]. Таблица данных устроена иначе: значений у переменной может быть сколько угодно, но самих переменных не больше двух. Когда переменных больше, справка советует сценарии.

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

Диспетчер сценариев по шагам: худший и лучший случай

Диспетчер сценариев хранит наборы значений, подставляет их в изменяемые ячейки модели и собирает отчёт по всем вариантам. Пример из справки Microsoft считает валовую прибыль как выручку минус себестоимость^[2].

1. Откройте вкладку «Данные», нажмите «Анализ "что если"» и выберите «Диспетчер сценариев».
2. Нажмите Add, назовите сценарий «Худший случай» и укажите изменяемые ячейки B2:B3.
3. Введите значения 50000 и 13200 — B4 покажет 36800.
4. Добавьте второй сценарий «Лучший случай» со значениями 150000 и 26000: прибыль станет 124000^[6].
5. Нажмите Summary и выберите сводку, чтобы получить отчёт по сценариям на новом листе.

Отчёт по сценариям собирает все варианты в одну таблицу на отдельном листе, но автоматически не пересчитывается^[1]. Поменяли сценарий — постройте отчёт заново. Если до отчёта дать ячейкам имена, в отчёте будут имена, а не адреса^[2]. Сценарии с других листов и книг собирает кнопка Merge, и структура ячеек у всех источников должна совпадать^[2].

Флажки запрета изменений и скрытия сценария (Prevent Changes и Hidden) действуют только на защищённом листе^[2]. Чтобы снять запрет на правку сценария, снимите защиту листа. Соберите сценарии в уроке тренажёра: урок меняет числа и проверяет, что отчёт сходится.

Однако сценарий хранит лишь несколько вариантов, а для сетки из десятков результатов нужен другой инструмент.

Таблица данных с одной и двумя переменными: чем отличаются

Таблица данных подставляет значения во входную ячейку и пересчитывает одну формулу для списка значений одной или двух переменных. В таблице с одной переменной значения стоят столбцом, а формула — на строку выше и на ячейку правее; в таблице с двумя переменными формула стоит в левом верхнем углу, один список под ней, второй справа^[3].

Модель прибыли: в B1 цена 300, в B2 переменные затраты 180, в B3 постоянные затраты 54000, в B4 объём 600, в B5 формула =B4*(B1-B2)-B3 — она даёт 18000. Сетка ниже — итог расчёта с двумя переменными: в D1 стоит =B5, цены идут по строке, объёмы — по столбцу, Row input cell — B1, Column input cell — B4.

Сетка пересчитывается при каждом пересчёте листа, даже если данные не менялись^[3]. В большой книге это тормозит работу, поэтому в File → Options → Formulas можно пересчитывать лист без таких таблиц. Тогда сетку обновляют вручную: выделите её формулы и нажмите F9^[3]; пересчёт только активного листа — Shift+F9^[4].

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

Когда анализ «что если» не подойдёт

Анализ «что если» не подойдёт, если задача выходит за пределы двух переменных, 32 значений или нужен единственный лучший ответ. Ниже — проверяемые признаки.

• Если у модели три и больше переменных, сетка их не примет: справка ограничивает её двумя^[1].
• Когда в одном варианте меняется больше 32 значений, один набор их не вместит — модель придётся разбить^[1].
• При частой смене исходных данных сводка быстро устареет: она не пересчитывается сама, и её строят заново^[1].
• Если нужен один лучший ответ с условиями, перебор сеткой его не выделит — такую задачу решают надстройкой оптимизации с ограничениями.
• Когда работа идёт в Google Таблицах, справка Google описывает только подбор значения через дополнение Goal Seek с английским интерфейсом^[5].

Анализ «что если» в Excel работает в Microsoft 365, Excel 2024 и Excel 2021, включая версии для Mac, — их перечисляет блок «Применяется к» справки^[1]. Сетка — результат одной формулы, поэтому правьте не отдельные числа в ней, а модель.