Календарь и фазы проекта

Шаг 01 / Переход от каркаса к календарю

Заполняем
временной ряд

Связываем календарь с датами проекта. В новой редакции первые 36 месяцев считаем помесячно, затем переходим к кварталам.

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

До ввода формул выбираем Формулы → Параметры вычислений → Автоматически. Режим вычислений может зависеть от других книг, открытых ранее в этом сеансе Excel; проверяем его перед началом работы.

1. На листе «Исходные данные» вставляем одну целую строку перед строкой 25. Верхний календарный каркас остаётся в строках 1:24.
2. Заголовок палитры должен оказаться в B26, её образцы — в строках 27:82. Группируем строки 27:82, сохранив заголовок видимым.
3. Вырезаем подпись «Итоги» из O24 и вставляем в O4. Очищаем только учебные подписи «Период 1» — «Период 120» в P24:EE24: в следующем уроке этот диапазон займут флаги фактических данных.
4. Все дальнейшие адреса этого урока относятся к состоянию после вставки. Повторно сдвигать их не нужно.

Снимаем закрепление областей и нажимаем Ctrl + Home, чтобы вернуться к A1. При необходимости временно уменьшаем масштаб: строки 1:26 и столбцы A:P должны быть целиком видны на экране. Выбираем P26 и снова закрепляем области: сверху остаются строки 1:25, слева — A:O. После закрепления можно вернуть удобный масштаб.

Дата начала примера — 01.01.2015, срок — 10 лет. Это исторический учебный сценарий, а не прогноз на 2026 год. Название курса обозначает редакцию материала.

Каждый блок формулы ниже предназначен для одной указанной ячейки. Копируем его целиком через F2; печатные переносы не делят формулу на несколько ячеек. Сохраняем различие между "" и " ".

Шаг 02 / Временные параметры

Вводим временные параметры

В B84 вводим «Временные параметры» и применяем стиль «Заголовок1(к)». Заполняем четыре строки.

Для J85:J88 выбираем стиль «Допущение». В H85:H88 вводим «Число» и применяем «ед_изм».

Присваиваем имена ячейкам

Открываем «Формулы → Диспетчер имён → Создать». Для каждого имени указываем область «Книга» и ссылку на соответствующую ячейку листа «Исходные данные».

Например, имя «Число_месяцев_в_году» должно ссылаться на ='Исходные данные'!$J$86. Имена вводятся без пробелов; подчёркивания сохраняем.

365 — параметр, а не длина каждого календарного года. Фактическое количество дней, включая дни високосных лет, ниже рассчитываем по датам.

Имена вводим точно по таблице: они используются в следующих формулах.

Группируем строки 85:88, оставив заголовок B84 видимым: выделяем строки по их номерам и нажимаем Alt + Shift + →.

Шаг 03 / Даты проекта

Задаём границы проекта

В B90 вводим «Даты». В G91:G96 размещаем подписи по таблице. В J91 вводим дату 01.01.2015, в J92 — 10.

=J92*Число_кварталов_в_году

=J92*Число_месяцев_в_году

=J96-J91+1

=КОНМЕСЯЦА(J91;J92*Число_месяцев_в_году-1)

Для B90 используем «Заголовок1(к)»; для H91:H96 — «ед_изм»; J91 и J96 — «Дата»; J92 — «Допущение»; J93:J95 — «Расчеты».

J95 использует J96, поэтому вводим все формулы блока, прежде чем оценивать результат. Расчётный конец исторического примера — 31.12.2024.

Результат / Исходные параметры календаря

Сверяем параметры
и даты проекта

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

Отображение «1 янв 15» означает 01.01.2015, а «31 дек 24» — 31.12.2024. Формат ячейки меняет вид даты, сохраняя её значение.

Шаг 04 / Имена и выбор детализации

Называем даты
и выбираем детализацию

Создаём имена для параметров и результатов предыдущего шага. Они будут использоваться в следующих формулах.

Группируем строки 91:96. В B98 вводим «Количество временных периодов» и применяем «Заголовок1(к)».

Создаём три выпадающих списка

Выделяем нужную ячейку, открываем «Данные → Проверка данных» и выбираем тип «Список». Вводим источник списка:

В G99 вводим «Выбор количества временных периодов в модели», в H99 — «выбор». К H99 применяем стиль «ед_изм», к J99 и J101 — «Расчеты», к J107 — «Обычный». Присваиваем J99 имя:

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

Сроки налоговых платежей проверяем отдельно от шага календаря.

Шаг 05 / Основной временной ряд

Рассчитываем основной ряд

Сначала задаём параметры квартального расчёта на весь срок проекта. Заполняем подписи:

В H102 и H104 вводим «дата», в H103 — «число»; применяем «ед_изм». В J101 уже выбран «квартал».

=Дата_начала_проекта

=ЕСЛИ(J101="месяц";Количество_месяцев_в_проекте;0)+ЕСЛИ(J101=
"квартал";Количество_кварталов_в_проекте;0)+ЕСЛИ(J101="год";
Срок_проекта;0)+ЕСЛИ(J101="полугодие";Количество_месяцев_в_проекте/6;
0)

=ЕСЛИ(J101="месяц";КОНМЕСЯЦА(J102;J103-1);0)+ЕСЛИ(J101="квартал";
КОНМЕСЯЦА(J102;J103*Число_месяцев_в_квартале-1);0)+ЕСЛИ(J101="год";
КОНМЕСЯЦА(J102;J103*Число_месяцев_в_году-1);0)+ЕСЛИ(J101="полугодие";
КОНМЕСЯЦА(J102;J103*(Число_месяцев_в_году/2)-1);0)

J103 содержит количество основных периодов за весь проект, а не число дней в одном квартале. В нашем примере ожидается 40 кварталов.

К J102:J104 применяем стиль «Расчеты». Затем для J102 и J104 отдельно задаём отображение даты через Ctrl + 1 → «Число» → «(все форматы)». В поле «Тип» вводим Д МММ ГГ: день, сокращённое название месяца и две цифры года (например, 1 янв 15 и 31 дек 24). Именованный стиль этих ячеек остаётся «Расчеты». Перед проверкой убеждаемся, что имена диапазонов созданы.

Шаг 06 / Проекция основного ряда

Разворачиваем основной ряд
по столбцам

В O104 создаём техническую дату перед началом проекта. В P101:P104 задаём тип периода, его начало, разницу между концом и началом в днях и дату конца.

=Дата_начала_проекта-1

=ЕСЛИ(И(O104<Дата_конца_проекта;O104>0);$J$101;" ")

=ЕСЛИ(И(O104<Дата_конца_проекта;O104>0);O104+1;" ")

=ЕСЛИ(P101="месяц";КОНМЕСЯЦА(P102;0)-P102;0)+ЕСЛИ(P101="квартал";
КОНМЕСЯЦА(P102;Число_месяцев_в_квартале-1)-P102;0)+ЕСЛИ(P101="год";
КОНМЕСЯЦА(P102;Число_месяцев_в_году-1)-P102;0)+ЕСЛИ(P101="полугодие";
КОНМЕСЯЦА(P102;Число_месяцев_в_году/2-1)-P102;0)

=ЕСЛИ(ЕОШИБКА(P102+P103);"";P102+P103)

Выделяем P101:EE104 через поле имени и нажимаем Ctrl + R. Excel заполнит строки вправо, изменяя относительные ссылки.

К O104, P102:EE102 и P104:EE104 применяем «Дата». К P101:EE101 и P103:EE103 — «Обычный». Выделяем только эти диапазоны, чтобы сохранить оформление подписей и служебных полей слева. В P103 число дней на один меньше полной продолжительности периода. Все календарные дни посчитаем отдельной формулой.

Формулы вводим целиком, без дополнительных строк.

Шаг 07 / Проверки и вспомогательный ряд

Проверяем основной ряд
и задаём месяцы

Контрольные ячейки возвращают 0, если расчёт совпадает с заданными параметрами. Применяем к ним стиль «ТРЕВОГА».

=ЕСЛИ(J103=СЧЁТЕСЛИ(P103:EE103;">0");0;1)

=ЕСЛИ(J104=МАКС(P104:EE104);0;1)

В G107:G110 вводим: «Временной период для модели (вспомогательный)», «Дата начала вспомогательного периода», «Продолжительность периода», «Дата конца вспомогательного периода». В H108 и H110 — «дата», в H109 — «число».

В J107 выбираем «месяц». В J109 вводим 36 и применяем «Допущение». Так задаём помесячный расчёт первых трёх лет. Основной ряд продолжает охватывать все десять лет.

=Дата_начала_проекта

=ЕСЛИ(J107="месяц";КОНМЕСЯЦА(J108;J109-1);0)+ЕСЛИ(J107="квартал";
КОНМЕСЯЦА(J108;J109*Число_месяцев_в_квартале-1);0)+ЕСЛИ(J107="год";
КОНМЕСЯЦА(J108;J109*Число_месяцев_в_году-1);0)+ЕСЛИ(J107="полугодие";
КОНМЕСЯЦА(J108;J109*(Число_месяцев_в_году/2)-1);0)

Присваиваем имена после ввода формул. J108 и J110 отображаем стилем «Дата», единицы в H — «ед_изм».

Шаг 08 / Проекция вспомогательного ряда

Разворачиваем месячный ряд

Создаём техническую дату в O110 и формулы в P107:P110. Месячный ряд работает при выборе двух детализаций в J99.

=Дата_начала_проекта-1

=ЕСЛИ(Выбор_количества_временных_периодов_в_модели=2;
ЕСЛИ(И(O110<Дата_конца_вспомогательного_периода;O110>0);$J$107;" ");
" ")

=ЕСЛИ(Выбор_количества_временных_периодов_в_модели=2;
ЕСЛИ(И(O110<Дата_конца_вспомогательного_периода;O110>0);O110+1;" ");
" ")

=(ЕСЛИ(P107="месяц";КОНМЕСЯЦА(P108;0)-P108;0)+ЕСЛИ(P107="квартал";
КОНМЕСЯЦА(P108;Число_месяцев_в_квартале-1)-P108;0)+ЕСЛИ(P107="год";
КОНМЕСЯЦА(P108;Число_месяцев_в_году-1)-P108;0)+ЕСЛИ(P107="полугодие";
КОНМЕСЯЦА(P108;Число_месяцев_в_году/2-1)-P108;0))

=ЕСЛИ(ЕОШИБКА(P108+P109);"";P108+P109)

Выделяем P107:EE110 и нажимаем Ctrl + R.

К O110, P108:EE108 и P110:EE110 применяем «Дата». К P107:EE107 и P109:EE109 — «Обычный». Не выделяем строки целиком: J109 должен сохранить стиль «Допущение», а единицы в H — «ед_изм». В формулах " " — строка с одним пробелом; сохраняем её при копировании.

Шаг 09 / Связь двух рядов

Проверяем месяцы
и подготавливаем переход

Вводим контрольные формулы в K109 и K110; применяем «ТРЕВОГА».

=ЕСЛИ(Выбор_количества_временных_периодов_в_модели=1;0;ЕСЛИ(J109=
СЧЁТЕСЛИ(P109:EE109;">0");0;1))

=ЕСЛИ(Выбор_количества_временных_периодов_в_модели=1;0;ЕСЛИ(J110=
МАКС(P110:EE110);0;1))

Если в J99 выбрана одна шкала, вспомогательный ряд не используется. Проверки K109 и K110 в этом режиме отключены и возвращают 0; при двух шкалах они проверяют количество вспомогательных периодов и дату их окончания.

В G112 и G113 вводим «Вспомогательная ячейка для построения вспомогательного временного периода», в H112:H113 — «дата». Сами формулы размещаются в рабочей области P, а не в столбце подписей G.

=ПРОСМОТР(Дата_конца_вспомогательного_периода+1;$P$102:$EE$102;P104)

=ЕСЛИ(СТОЛБЕЦ()-СТОЛБЕЦ($P$113)+1<=$J$109;"";СМЕЩ(P112;;-$J$109))

Выделяем P112:EE113, заполняем вправо клавишами Ctrl + R и применяем стиль «Дата».

Исправление новой редакции. В первых 36 столбцах сдвинутая шкала ещё не нужна: используются месячные даты. Условие возвращает пустую строку и не вычисляет преждевременный сдвиг за левую границу листа. Начиная со следующего столбца выполняется исходная формула СМЕЩ.

Проверка условия не скрывает другие ошибки: здесь не используется ЕСЛИОШИБКА. При изменении числа месячных периодов условие ориентируется на J109.

Результат / Параметры двух шкал

Проверяем основной
и месячный ряды

Основной ряд покрывает весь проект, а вспомогательный — первые 36 месяцев. Они перекрываются по времени: количество итоговых периодов нельзя получить простым сложением этих двух рядов.

«Ok» — формат отображения нулевого контрольного значения. При проверке можно выделить ячейку и посмотреть её значение и формулу.

Шаг 10 / Верхний календарь

Объединяем два ряда
в календарь

В верхнем блоке строка 5 показывает дату начала, строка 6 — дату конца каждого расчётного периода.

=Дата_начала_проекта-1

=Дата_начала_проекта

=ЕСЛИ(O6<Дата_конца_проекта;
ЕСЛИ(Выбор_количества_временных_периодов_в_модели=1;P104;ЕСЛИ(P5<=
Дата_конца_вспомогательного_периода;P110;P113));" ")

=ЕСЛИ(P6<Дата_конца_проекта;P6+1;" ")

1. Выделяем P6:EE6 и нажимаем Ctrl + R: формула конца периода скопируется вправо.
2. Выделяем Q5:EE5 и нажимаем Ctrl + R: начало следующего периода определяется концом предыдущего.
3. Для O6 и P5:EE6 применяем «Дата».

=ЕСЛИ(Дата_начала_проекта=МИН(P5:EE5);0;1)

=ЕСЛИ(Дата_конца_проекта=МАКС(P6:EE6);0;1)

К K5 и K6 применяем «ТРЕВОГА». Первая проверка сравнивает начало календаря с началом проекта, вторая — его окончание.

Шаг 11 / Признаки периода

Добавляем годы и кварталы

Раскрываем группы верхнего блока. Оставляем O11:O14 пустыми и применяем «Пустая_ячейка»: некоторые формулы используют соседние значения для расчёта порядковых номеров.

=ЕСЛИ(P5=" ";" ";ГОД(P5))

=ЕСЛИ(P5=" ";0;P9-$P$9+1)

=ЕСЛИ(P5=" ";0;ЦЕЛОЕ((МЕСЯЦ(P5)+2)/3))

=ЕСЛИ(P5=" ";0;ЕСЛИ(P11=O11;O12;O12+1))

Строки 9 и 11 показывают календарный год и квартал. Строки 10 и 12 — их порядковые номера от начала проекта.

Выделяем P9:EE12 и заполняем вправо Ctrl + R. Используем «Обычный», чтобы результат отображался числом.

=ЕСЛИ(Срок_проекта=МАКС(P10:EE10);0;1)

=ЕСЛИ(Количество_кварталов_в_проекте=МАКС(P12:EE12);0;1)

К контрольным ячейкам K10 и K12 применяем «ТРЕВОГА».

Шаг 12 / Месяцы и ограничения проверок

Добавляем месяцы
и проверяем нумерацию

=ЕСЛИ(P5=" ";0;МЕСЯЦ(P5))

=ЕСЛИ(P5=" ";0;ЕСЛИ(P13=O13;O14;O14+1))*ЕСЛИ(ИЛИ(P107="месяц";P101=
"месяц");1;0)

Строка 13 показывает номер календарного месяца. Строка 14 рассчитывает порядковый номер месячного периода при выбранной месячной детализации.

Выделяем P13:EE14 и заполняем вправо Ctrl + R. Используем стиль «Обычный».

=ЕСЛИ(МАКС(P14:EE14)=ЕСЛИ($J$101="месяц";Количество_месяцев_в_проекте;
ЕСЛИ(И(Выбор_количества_временных_периодов_в_модели=2;$J$107="месяц");
$J$109;0));0;1)

Для K14 выбираем «ТРЕВОГА».

Проверка K14 использует число расчётных периодов с месячным шагом. Оно не всегда равно 120 календарным месяцам проекта. При совпадении сама K14 возвращает 0.

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

Проверка нумерации не заменяет проверку непрерывности дат. Между соседними активными периодами не должно быть пропусков или наложений.

Шаг 13 / Длительность в днях

Рассчитываем количество дней

В B115 вводим «Количество дней в периодах» и применяем «Заголовок1(к)». В G116:G118 — подписи «Количество дней в периоде», «Количество дней в году», «Количество дней в квартале». В H116:H118 вводим «число» и применяем «ед_изм».

=ЕСЛИ(P5=" ";0;P6-P5+1)

=ЕСЛИ(P5=" ";0;РАЗНДАТ(ДАТА(P9;1;1);ДАТА(P9;12;31);"d")+1)

=ЕСЛИ(P11=4;РАЗНДАТ(ДАТА(P9;10;1);ДАТА(P9;12;31);"d")+1;0)+ЕСЛИ(P11=3;
РАЗНДАТ(ДАТА(P9;7;1);ДАТА(P9;9;30);"d")+1;0)+ЕСЛИ(P11=2;
РАЗНДАТ(ДАТА(P9;4;1);ДАТА(P9;6;30);"d")+1;0)+ЕСЛИ(P11=1;
РАЗНДАТ(ДАТА(P9;1;1);ДАТА(P9;3;31);"d")+1;0)

Выделяем P116:EE118, нажимаем Ctrl + R и применяем «Обычный».

В P116 добавляем единицу, чтобы учесть и начальную, и конечную дату. P117 определяет фактическое количество дней в календарном году; P118 — календарного квартала, к которому относится начало периода.

Группируем строки 99:113 и 116:118, оставив заголовки B98 и B115 видимыми.

Проверка / Календарь

Проверяем календарь
перед фазами проекта

Проверяем расчётные ориентиры:

Сверяем рабочую книгу с эталоном. После ввода формул проверяем ориентиры из таблицы и контрольные ячейки. Если значения отличаются, возвращаемся к параметрам и формулам соответствующего блока.

Оформляем подписи и проверяем группы

По очереди выделяем G4:G24 и G83:G118. Для каждого диапазона включаем «Главная → Перенос текста», затем выполняем «Главная → Формат → Автоподбор высоты строки».

Проверяем пять уже созданных групп: 27:82, 85:88, 91:96, 99:113, 116:118. Строка 25 должна оставаться вне группировки. Повторно создавать существующие группы не нужно.

Что проверить при воспроизведении

• Имена ссылаются на текущий лист и окончательные адреса.
• Каждый активный период начинается на следующий день после предыдущего; последний заканчивается 31.12.2024.
• Контрольные ячейки K5, K6, K10, K12, K14, K103, K104, K109, K110 возвращают 0.
• В рабочем календаре и используемых расчётах нет ошибок ссылок и типов данных.
• Сумма дней по строке 116 равна 3653, а резервные столбцы после активных периодов не создают дополнительных дней.

После проверки календаря можно переходить к уроку «Разбиваем проект на фазы».

Результат / Переход и окончание календаря

Сверяем переход к кварталам
и конец проекта

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

Файл для сверки результата: М02_После_урока_01_v3.xlsx. Следующий урок выполняем в своей рабочей книге.

Шаг 01 / Структура фаз

Разбиваем проект
на фазы

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

Продолжаем работу в книге после урока «Заполняем временной ряд». Строка 25 уже вставлена, все адреса ниже окончательные. Не вставляем её повторно.

Одинаковое оформление пяти блоков

Подписи в G — «Обычный», единицы в H — «ед_изм». Даты начала и конца в J — «Дата», длительности в годах — «Допущение», вычисленное число кварталов, месяцев и дней — «Расчеты».

Имена создаём через «Формулы → Диспетчер имён», с областью «Книга» и ссылкой на текущий лист «Исходные данные». Имена и ссылки задаём по таблицам ниже.

Шаг 02 / Строительство

Задаём срок строительства

Строительство начинается одновременно с проектом. Заполняем подписи и единицы; в J123 вводим 3 — продолжительность в годах.

=Дата_начала_проекта

=J123*Число_кварталов_в_году

=J123*Число_месяцев_в_году

=J127-J122+1

=КОНМЕСЯЦА(J122;J123*Число_месяцев_в_году-1)

Ожидаемая граница исторического сценария: 01.01.2015–31.12.2017. Ячейка J126 использует дату J127, поэтому проверяем результат после ввода всего блока.

Шаг 03 / Эксплуатация

Продолжаем проект
эксплуатацией

Эксплуатация начинается на следующий день после строительства. Её длительность — оставшаяся часть общего срока проекта.

=Дата_конца_строительства+1

=Срок_проекта-Срок_строительства

=J131*Число_кварталов_в_году

=J131*Число_месяцев_в_году

=J135-J130+1

=КОНМЕСЯЦА(J130;J131*Число_месяцев_в_году-1)

J131 содержит формулу. Сохраняем исходный стиль «Допущение», но число 7 вручную вместо формулы не вводим. При изменении сроков проекта или строительства результат должен пересчитываться.

Для текущих параметров ожидается эксплуатация с 01.01.2018 по 31.12.2024.

Результат / Две фазы проекта

Сверяем границу строительства
и эксплуатации

После ввода обеих фаз проверяем даты и продолжительность. Эксплуатация начинается на следующий день после окончания строительства.

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

Шаг 04 / Акционерное финансирование

Описываем вложение акционеров

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

=Дата_начала_проекта

=J139*Число_кварталов_в_году

=J139*Число_месяцев_в_году

=J143-J138+1

=КОНМЕСЯЦА(J138;J139*Число_месяцев_в_году-1)

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

Ожидаемый период — 01.01.2015–31.12.2015.

Шаг 05 / Заёмное финансирование

Задаём окно
заёмного финансирования

Заёмное финансирование начинается 31.12.2015, в последний день периода акционерного финансирования. Пересечение намеренное: в этом периоде модели используются оба источника. В J147 вводим 7 лет.

=Дата_конца_акционерного_финансирования

=J147*Число_кварталов_в_году

=J147*Число_месяцев_в_году

=J151-J146+1

=КОНМЕСЯЦА(J146;J147*Число_месяцев_в_году-1)

Срок долга в этом примере — 7 лет.

Исходная формула J151 даёт конец 30.11.2022. Параметр J147=7 сохраняется, однако полученный по датам период не равен ровно семи годам. График выборки, процентов и погашения долга будет рассчитан в последующих разделах.

Шаг 06 / Фактические данные

Отмечаем месяц
фактических данных

Для примера отмечаем первый месяц проекта. Продолжительность хранится в годах: в J155 используем формулу =1/12, не округлённое десятичное число.

=Дата_начала_проекта

=1/12

=J155*Число_кварталов_в_году

=J155*Число_месяцев_в_году

=J159-J154+1

=КОНМЕСЯЦА(J154;J155*Число_месяцев_в_году-1)

J155 оформляется как «Допущение» и содержит формулу. Для текущего примера ожидаются один месяц, 31 день и окно 01.01.2015–31.01.2015.

Это границы периода фактических данных в учебном примере. Флаг помечает период; фактические доходы и расходы он сам не загружает.

Результат / Финансирование и факт

Сверяем сроки финансирования
и факта

Проверяем границу между акционерным и заёмным финансированием, затем длительность периода фактических данных.

В дальнейшем зададим структуру финансирования: 30% — капитал, 70% — долг. За первый год инвестируем треть общей суммы; 30% капитала её не покрывают. Поэтому в конце года используем оба источника.

В J155 вводится =1/12. Видимое «0,08» округлено форматом; не заменяем им точную формулу.

Шаг 07 / Флаги фаз

Показываем фазы
в каждом периоде

Раскрываем верхний блок. Каждая формула возвращает 1, если расчётный период пересекается с заданным окном, и 0 в противном случае.

=ЕСЛИ(И(Дата_начала_строительства<=P$6;Дата_конца_строительства>=P$5);
1;0)

=ЕСЛИ(И(Дата_начала_эксплуатации<=P$6;Дата_конца_эксплуатации>=P$5);
1;0)

=ЕСЛИ(И(Дата_начала_акционерного_финансирования<=P$6;
Дата_конца_акционерного_финансирования>=P$5);1;0)

=ЕСЛИ(И(Дата_начала_заемного_финансирования<=P$6;
Дата_конца_заемного_финансирования>=P$5);1;0)

=ЕСЛИ(И(Дата_начала_фактических_данных<=P$6;
Дата_конца_фактических_данных>=P$5);1;0)

По очереди выделяем P17:EE17, P18:EE18, P21:EE21, P22:EE22 и P24:EE24. Для каждого диапазона выполняем Ctrl + R и применяем стиль «Флаг».

Флаг показывает пересечение. Единица не означает, что фаза занимает весь расчётный период. Точные границы задаются датами в параметрах; это особенно важно при переходе к более крупным периодам.

Шаг 08 / Условное форматирование

Окрашиваем строки флагов

Сначала применяем стиль «Флаг» к пяти диапазонам из таблицы ниже. Затем настраиваем условное оформление: оно должно появляться только в ячейках со значением 1.

1. Выделяем P17:EE17. Открываем «Главная → Условное форматирование → Создать правило».
2. Выбираем «Форматировать только ячейки, которые содержат»: значение ячейки равно 1.
3. Нажимаем «Формат». На вкладке «Шрифт» задаём цвет RGB (0,0,0). На вкладке «Заливка» задаём фон, цвет и вид узора по таблице.
4. Повторяем для остальных четырёх диапазонов. Значения цветов ниже указаны как RGB: красный, зелёный, синий.

Восходящие линии идут снизу слева вверх направо. Выбираем соответствующий образец в списке узоров; для строительства используется более редкая штриховка, для остальных строк — более плотная.

Для активных флагов задаём чёрный шрифт. Нулевые значения сохраняют базовый стиль.

Шаг 09 / Условное форматирование дат

Окрашиваем даты по фазам

Для каждого диапазона таблицы создаём отдельное правило: «Главная → Условное форматирование → Создать правило → Использовать формулу для определения форматируемых ячеек».

Буква столбца в формуле остаётся относительной, номер строки закреплён знаком $. В «Формат» задаём сплошную заливку и указанную тонкую границу; другие границы не добавляем. Цвета приведены в RGB.

Открываем «Условное форматирование → Управление правилами», показываем правила текущего листа и располагаем эти восемь правил сверху вниз в порядке таблицы. Оформление фактических данных имеет приоритет над оформлением эксплуатации и строительства. Флажок «Остановить, если истина» оставляем снятым.

Для P5 сохраняем отдельное правило. Проверяем поле «Применяется к» и формулу: у диапазонов с Q формула также начинается с Q.

Результат / Флаги на границе фаз

Проверяем смену фазы
в календаре

В одном фрагменте видны и переход к кварталам, и начало эксплуатации. Сопоставляем даты с флагами в строках 17 и 18.

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

Проверка / Завершение модуля

Проверяем границы
и завершаем каркас

Проверяем: в O4 находится «Итоги», а O24 пуста. Перенос выполнен в начале модуля — повторно вырезать содержимое O24 не нужно.

Группируем по отдельности строки 122:127, 130:135, 138:143, 146:151 и 154:159. Для каждой группы выделяем строки по их номерам и нажимаем Alt + Shift + →. Заголовки пяти блоков останутся видимыми. Строка 25 должна оставаться вне групп; повторно вставлять её не нужно. Проверяем закрепление относительно P26.

Выделяем G120:G159, включаем «Главная → Перенос текста» и затем выполняем «Главная → Формат → Автоподбор высоты строки».

Проверка пересечения источников

Столбец P соответствует январю 2015. Сверяем флаги на границе года:

Проверка связей

• После завершения строительства эксплуатация начинается на следующий день.
• Капитал и долг пересекаются 31.12.2015; в декабре 2015 активны оба флага.
• Все 28 имён модуля ссылаются на текущую учебную книгу.
• При J109=36 ожидаются 64 активных периода и 3653 календарных дня.
• Условное форматирование следует флагам, группы раскрываются, палитра сохранена.

Проверка периода долга. Сверяем даты с J146 и J151. Параметр J147=7 не заменяет проверку полученного конца 30.11.2022.

Файл для сверки: М02_Контроль_v3.xlsx. Сохраняем собственную рабочую книгу для следующего модуля.

Следующий модуль посвящён экономическим допущениям: макроэкономике, доходам и расходам.