Переключать сценарии одной ячейкой: выбирать активные допущения функциями ВЫБОР и ИНДЕКС по номеру сценария и защищать номер флагом проверки.
Переключатель сценариев: номер в одной ячейке
Переключатель сценариев: номер в одной ячейке
Переключатель сценариев — одна ячейка с номером сценария, от которой зависят допущения модели. У «Зерна» номер стоит в B1: 1 — база, 2 — оптимистичный, 3 — пессимистичный. Допущения всех сценариев лежат строками рядом (B4:D5), а столбец «Активный» (E) берёт из каждой строки одно значение — дальше модель считает только по нему.
Разобранный пример — активная маржа в E5: =ВЫБОР($B$1;B5;C5;D5). ВЫБОР(номер; вариант1; вариант2; …) возвращает вариант с этим номером: при B1 = 1 — B5, то есть 20 %; при B1 = 3 — D5, то есть 16 %. Номер закреплён знаком $, как допущение в практикуме 1.3: формулу копируют на соседние строки, и ссылка на B1 не должна съехать.
Ту же работу делает ИНДЕКС(диапазон; номер) из урока 3.4: он берёт из строки значение с этим номером. Расчётные строки о сценариях не знают: выручка 2027 = выручка 2026 × (1 + активный рост), EBIT = выручка × активная маржа. Сменили число в B1 — пересчиталась вся модель.
Номер вне списка ломает модель, поэтому рядом с B1 ставят флаг. И(B1>=1; B1<=3) истинна, только когда верны оба условия; вложенная в ЕСЛИ, она выбирает «OK» или «ОШИБКА».
Допущения сценариев «Зерна» (строки 4–5 листа)
Номер активного сценария — в B1, активные значения — в столбце E.
| Допущение | База (1) | Оптимистичный (2) | Пессимистичный (3) |
|---|---|---|---|
| Рост выручки | 10 % | 15 % | 3 % |
| Маржа EBIT | 20 % | 22 % | 16 % |
Цепочка переключателя
- 1B1
Номер сценария: 1, 2 или 3
- 2E4, E5
Активные рост и маржа: ВЫБОР и ИНДЕКС по $B$1
- 3B8
Выручка 2027 от активного роста
- 4B9
EBIT 2027 от активной маржи
- 5D1
Флаг: номер от 1 до 3 — «OK», иначе «ОШИБКА»
Конспект урока
Переключатель сценариев: номер в одной ячейке
Переключатель сценариев — одна ячейка с номером сценария, от которой зависят допущения модели. У «Зерна» номер стоит в B1: 1 — база, 2 — оптимистичный, 3 — пессимистичный. Допущения всех сценариев лежат строками рядом (B4:D5), а столбец «Активный» (E) берёт из каждой строки одно значение — дальше модель считает только по нему.
Разобранный пример — активная маржа в E5: =ВЫБОР($B$1;B5;C5;D5). ВЫБОР(номер; вариант1; вариант2; …) возвращает вариант с этим номером: при B1 = 1 — B5, то есть 20 %; при B1 = 3 — D5, то есть 16 %. Номер закреплён знаком $, как допущение в практикуме 1.3: формулу копируют на соседние строки, и ссылка на B1 не должна съехать.
Ту же работу делает ИНДЕКС(диапазон; номер) из урока 3.4: он берёт из строки значение с этим номером. Расчётные строки о сценариях не знают: выручка 2027 = выручка 2026 × (1 + активный рост), EBIT = выручка × активная маржа. Сменили число в B1 — пересчиталась вся модель.
Номер вне списка ломает модель, поэтому рядом с B1 ставят флаг. И(B1>=1; B1<=3) истинна, только когда верны оба условия; вложенная в ЕСЛИ, она выбирает «OK» или «ОШИБКА».