← Финансовое моделирование в Excel с нуля
Урок
Практикум: шкала времени, абсолютные ссылки и флаг проверки
Собрать строку прогноза на шкале лет с абсолютной ссылкой на допущение, посчитать итог функцией СУММ и поставить флаг проверки через ЕСЛИ.
Строка прогноза на шкале лет
Одна формула на строку: протяжка, $ и флаг проверки
Что считаем: выручку по годам. Годы идут по столбцам, допущение — в отдельной ячейке сверху. Пример на числах урока 1.2: в B1 — рост 5 %, в B3 — выручка первого года 1 000. Выручка следующего года в C3: =B3*(1+$B$1), результат 1 050. Формулу протягивают вправо, и в D3 сама получается =C3*(1+$B$1), результат 1 102,5: ссылка на прошлый год сдвинулась, ссылка на допущение осталась на месте.
Знак $ закрепляет часть ссылки:
• $B$1 — и столбец, и строку;
• $B1 — только столбец, для протяжки вправо этого хватает;
• B$1 — только строку: при протяжке вправо ссылка станет C$1.
Без $ ссылка B1 в D3 превратится в C1 — пустую ячейку, рост станет нулевым, и D3 покажет 1 050 вместо 1 102,5.
Итог за все годы — =СУММ(B3:F3). Флаг проверки — формула, которая сама сообщает, сходятся ли числа: =ЕСЛИ(условие;"OK";"ОШИБКА"). Первый аргумент — проверка, например «сумма кварталов = годовой выручке», второй — что вывести, если она верна, третий — если нет. Текст в формуле пишут в кавычках.
Денежные суммы после деления и степеней могут разойтись на копейки, поэтому их сравнивают с допуском: ABS(F8-B5)<0,01 — функция ABS берёт разницу без знака. Флаг ставят рядом с итогами, чтобы ошибку было видно сразу.
Пример после протяжки вправо
Ссылка на прошлый год сдвигается, закреплённая ссылка на допущение остаётся на B1.
| Ячейка | С $ — верно | Без $ — ошибка |
|---|---|---|
| C3 | =B3*(1+$B$1) → 1 050 | =B3*(1+B1) → 1 050 |
| D3 | =C3*(1+$B$1) → 1 102,5 | =C3*(1+C1) → 1 050C1 пуста — рост 0 |
Конспект урока
Одна формула на строку: протяжка, $ и флаг проверки
Что считаем: выручку по годам. Годы идут по столбцам, допущение — в отдельной ячейке сверху. Пример на числах урока 1.2: в B1 — рост 5 %, в B3 — выручка первого года 1 000. Выручка следующего года в C3: =B3*(1+$B$1), результат 1 050. Формулу протягивают вправо, и в D3 сама получается =C3*(1+$B$1), результат 1 102,5: ссылка на прошлый год сдвинулась, ссылка на допущение осталась на месте.
Знак $ закрепляет часть ссылки:
• $B$1 — и столбец, и строку;
• $B1 — только столбец, для протяжки вправо этого хватает;
• B$1 — только строку: при протяжке вправо ссылка станет C$1.
Без $ ссылка B1 в D3 превратится в C1 — пустую ячейку, рост станет нулевым, и D3 покажет 1 050 вместо 1 102,5.
Итог за все годы — =СУММ(B3:F3). Флаг проверки — формула, которая сама сообщает, сходятся ли числа: =ЕСЛИ(условие;"OK";"ОШИБКА"). Первый аргумент — проверка, например «сумма кварталов = годовой выручке», второй — что вывести, если она верна, третий — если нет. Текст в формуле пишут в кавычках.
Денежные суммы после деления и степеней могут разойтись на копейки, поэтому их сравнивают с допуском: ABS(F8-B5)<0,01 — функция ABS берёт разницу без знака. Флаг ставят рядом с итогами, чтобы ошибку было видно сразу.