TL;DR. Финансовая модель в таблице разваливается при смене периода расчёта. Я честно старался, был аккуратен как мог, но период в модели хранится в геометрии листа: колонка — это месяц, а формулы адресуют ячейки по абсолютным координатам. На Хабре на эту боль есть два описанных ответа, оба уходят от таблиц: перенести логику в OLAP-куб с семантическим слоем или написать модель на Python. Здесь описан третий: оставить табличный интерфейс, но вынести диапазон и период в параметры модели, а адресацию перевести с координат ячеек на имена строк и колонок. Перевод модели из 10 листов с месяцев на кварталы стоит тогда 2 действия вместо 460–3060 — расчёт по шагам приведён в тексте.
Предыстория. Я делал приложение с функционалом финансовой модели и был удивлен, что готовой методики для удобного описания я сходу не нашел. Пришлось самому изобретать велосипед, а по первым реакциям клиентов стало очевидно, что методикой будет полезно поделиться. Перечисленные далее примеры сделаны в конструкторе Интеграм (я его разработчик), но могут быть реализованы в любом приложении.
Не сомневаюсь, что много кто уже сам дошёл до подобного решения, остальные могут сэкономить время, которое потратил я и другие разработчики на поиск и пробы.
Почему модель ломается структурно, даже с прямыми руками
Типичная модель: строки — статьи (выручка, себестоимость, ФОТ), колонки — месяцы, в ячейках формулы вида =C12*$B$4. Эта модель содержит два принципиально разных вида информации, физически смешанных в одном месте:
Что считаем — «маржа равна выручке минус себестоимость».
За какой период считаем — «в колонке C у нас март 2025».
Первое — это правило, второе — параметр. В листе они склеены: правило записано через координату, а координата и есть период. Отсюда все последствия, которые почти каждый из вас видел не раз.
Добавили месяц в середину — сдвинулись координаты, и часть формул стала указывать не туда, причём молча: значения посчитаются, только не те. Решили смотреть модель по кварталам — надо строить второй лист, потому что «квартал» не задашь как параметр, его можно внедрить только расстановкой колонок. Продлили горизонт с трёх лет до пяти — растянули формулы вправо и получили расхождение там, где кто-то когда-то поставил абсолютную ссылку. Захотели рядом сценарий «пессимистичный» — скопировали файл, и с этого момента у вас две модели, которые расходятся при первой же правке правила.
Это не победить аккуратностью и проверками. Аккуратность здесь помогает до того момента, пока модель не начинают менять — а её меняют всегда, в этом её смысл. Excel не даёт способа сказать «период — это свойство модели», поэтому дисциплинированный финансист изобретает его сам: строка с номерами периодов сверху, именованные диапазоны, СМЕЩ(), отдельная вкладка «параметры». Это работающие костыли, и по ним видно, какой механизм людям на самом деле нужен.
Два ответа, которые уже описаны, и оба избегают плоского табличного хранения
Тема на Хабре живая, но разложена по двум полюсам.
Что считать. Есть свежая и популярная серия — «Excel-ку покажи»: гайд по построению финансовой модели с нуля Михаила Затопляева (26 ноября 2025, 15K прочтений, 73 закладки, +10/−1). Она про содержание модели: P&L, Cash Flow, баланс, модели привлечения клиентов, транзакционная выручка против подписочной. Обещанная в конце вторая часть про расходы за восемь месяцев не вышла — на момент написания статьи её нет.
Куда переехать, когда стало больно. Здесь два стандартных ответа, оба от больших компаний. Первый — унести логику в куб: «Как убрать ручную сборку финансовой отчетности: OLAP-модель, семантический слой и живые отчеты в Excel» от Сбера (6,3K прочтений) и «OLAP-кубы в финансах». Excel в этой схеме остаётся, но только как окно к кубу: «логика отчёта перестаёт жить в отдельных файлах». Второй — унести модель в код: «Финансовое моделирование в Python и Excel: мой путь перехода на код» от Сибура.
Оба ответа правильные и оба дорогие в одном и том же месте: они отбирают модель у её владельца! Финансист, который вчера правил формулу в ячейке, сегодня пишет тикет дата-инженеру или учит pandas. Для холдинга это нормальная цена, для финдира в компании на 200 человек — заградительная.
Разбора третьего варианта, чтобы оставить табличный интерфейс, но починить в нём саму адресацию, я на Хабре не нашел. Про него и пишу далее.
Третий ответ: период — параметр модели, а не координата
Идея в одном предложении: модель хранится не как лист с координатами, а как набор записей в базе, и период расчёта — такой же её параметр, как, например, ставка дисконтирования.
Дальше — механика: сам принцип переносится на реляционную БД и без конструктора — устойчивость к смене периода никуда не денется. Хотя конкретное число действий будет зависеть от интерфейса поверх данных, а не только от схемы хранения.
Модель — это дерево записей, а не прямоугольник ячеек
Модель хранится как таблица со своими подчинёнными таблицами: модель → листы → панели → строки. Панель — это то, что пользователь видит таблицей на листе; строка панели — статья модели со своими свойствами: формула, формат, уровень иерархии, единица измерения. Исходные данные лежат отдельно, в таблице «Значение», а мы даём пользователю привычный теплый вид с листами «как в экселе».
Ключевое отличие от листа при хранении: у строки нет колонок. Колонки не хранятся вообще, они вычисляются.
Конфигурация и данные — два логических хранилища, и их правильное разделение является залогом успеха. Цена ошибки здесь невелика: даже если жестко прописать коэффициент в конфигурации, то поправки сценариев (позитивный/негативный/нейтральный) вы всё равно примените, не перелопачивая каждую ячейку.
Расчётная группа: правило формирования колонок
Колонки задаёт расчётная группа — правило, по которому они разворачиваются. Типов групп восемь на всю систему, и колонка всегда порождается одним из них:
повторяющаяся по периодам
из запроса
одна колонка со значением
одна с единицей измерения
сумма строки
сумма статей
колонка-формула (псевдокод, eval или SQL — на выбор архитектора и безопасника)
пустая группа
Основной тип — повторяющаяся группа (repeating group, RG): она повторяется столько раз, сколько периодов расчёта укладывается в диапазон модели.
Вы можете придумывать свои новые повторяющиеся группы с их логикой, но будьте осторожны! За те несколько проектов, что я сделал с моделями, я десятки раз добавлял новые типы групп, а потом удалял их, потому что они не ложились красиво в будущие трансформации и методики расчета. Тем не менее, список точно неполный, туда что-то редко-редко, но добавляется.
Модель задана диапазоном 01.01.2025 – 31.12.2030 и периодом «Год» — группа развернётся в шесть колонок, по одной на год. Поменяли период на «Квартал» — в тех же строках, с теми же формулами, получите 24 колонки. Ни одна формула при этом не редактируется: она записана один раз на группу, а не по разу на колонку.
Для программиста это знакомо: правило вместо развёрнутого результата. Ближайшая аналогия — секционирование таблицы по ключу: вы описываете, как делить, а не перечисляете секции руками. Для финансиста это неочевидно ровно потому, что в Excel такой сущности нет — и он изобретает её через копирование колонок.
Диапазон и период наследуются вниз и переопределяются на месте
Старт, финиш и период — свойства модели; период выбирается из четырёх — неделя, месяц, квартал, год. Панель может их переопределить: на одном листе уживаются годовая сводка и помесячная расшифровка, при этом «помесячно» — это свойство одной панели, а не второй копии модели.
Тот же принцип действует и мельче. Единица измерения ищется сначала на строке модели, и только если там не задана — в таблице исходных данных. То есть на каждом уровне работает одно правило: значение наследуется сверху, пока его явно не переопределили ниже. Программист узнает здесь каскад и переопределение метода; финансист — то, что он в Excel делает пометкой «здесь в тыс. руб.» в шапке и надеждой, что её заметят.
Адресация: по имени строки, по имени колонки, по смещению
Формула строки ссылается на другие строки по идентификатору или имени в квадратных скобках, а не по координате ячейки. [Выручка] - [Себестоимость] вместо C12-C13.
Способов задать содержимое строки пять, и в одной панели обычно встречаются все: пустые скобки [] — «возьми одноимённое значение из исходных данных»; имя в скобках — «возьми конкретное значение»; арифметика по другим строкам — [113326]+[113330]; просто число, вписанное в строку; и справочное значение, заполненное прямо в ней.
Это то же самое, что именованные диапазоны в Excel, с одной разницей: здесь так устроена адресация по умолчанию, а не как дисциплина, которую можно не соблюдать. Именно поэтому вставка периода или строки не ломает расчёт — ломать нечего, координат в формулах нет.
Второй вид формулы — формула расчётной группы. Она задана на колонке и считается в каждой строке панели, а ссылаться может на соседние колонки той же строки: по имени колонки — [Факт] - [План] — или по смещению. Смещение пишется со знаком: [-1] — соседняя колонка слева, [+1] — соседняя справа, [+2] — через одну. Знак здесь не украшение, а различитель: число без знака во всём языке модели означает идентификатор, и [113326] — это ссылка на строку по id, а не «на сто тринадцать тысяч колонок правее». Типичная колонка-формула выглядит так: Math.round([-1]/[-2]*100) — отношение предыдущей колонки к пред-предыдущей, в процентах.
У двух способов разная область прочности, и это стоит знать заранее: имя переживает любую перестановку, а смещение привязано к раскладке колонок — оно не заметит новых строк, но собьётся, если колонок в группе станет больше. То есть формулу со смещениями после смены периода надо пересобрать, а формулу по именам — нет.
Внутри строки при этом остаётся обычная арифметика с функциями, то есть входной барьер для того, кто пришёл из Excel, — это синтаксис ссылки, а не новый язык.
Произвольная группа: колонки из запроса
Периоды — частный случай. Источником списка колонок может быть произвольный запрос к базе.
Понадобилась межотраслевая матрица: строки — виды продукции, колонки — отрасли. Указываем запрос, возвращающий список отраслей, как источник колонок группы; строки перечисляем как обычно; в таблице исходных значений лежат известные пары «отрасль × продукт». Модель собирает матрицу сама, и при появлении новой отрасли колонка добавится без правки модели — потому что колонки и здесь не хранятся, а вычисляются.
Исходные данные: дата актуальности вместо «колонки марта»
Последняя часть, без которой всё предыдущее не работает. Исходные значения хранятся не «в колонке за март», а записями, у которых первым полем идёт дата актуальности. При расчёте модель сама раскладывает записи по календарным срезам текущего периода и суммирует те, что попали в один срез.
Это и есть то самое разделение, которого нет в листе: значение знает свою дату, а не своё место. Поэтому пересчёт по другому периоду — это не перекладывание данных, а другая группировка тех же записей. И поэтому же план и факт спокойно живут в одной модели: это просто записи с разными метками и датами, а не два файла.
Имя параметра — это имя таблицы
Ещё одно следствие того, что модель живёт в базе: у параметров нет отдельного «конфига». Всё, что модель знает про себя, лежит в таблицах, названных обычными словами — теми же, какими это называет финансист.
Период «Квартал» — не константа в коде, а таблица «Квартал», в которой лежат записи «I кв 2025», «II кв 2025» с датами начала и конца. Выбрали в фильтре листа «Квартал» — рабочее место пошло за границами срезов в таблицу с этим именем. Так же названо и остальное: «Финмодель» — сама модель, «Лист» и «Панель» — её состав, «Строка» — статьи, «Значение» — исходные данные, «Дэшборд» — отчёт, который собирает модель на экран. Даже путь в интерфейсе набран из имён таблиц: Таблицы / Панель / Налоговые ставки / Строка. Справочника соответствий между тем, что видит пользователь, и тем, что лежит в базе, не существует: это одно и то же слово.
Польза здесь не эстетическая. Разбираться в чужой модели можно, не выясняя предварительно схему хранения: имя из разговора и есть адрес данных. Приём общий и к финмоделям не привязан — про то, как из этого получается, есть отдельная статья: программирование на языке бизнеса.
Сколько экономии в действиях
Обычно экономию меряют временем, но здесь время плохой измеритель: профи в Excel сделает полторы сотни правок за десять минут, и по секундомеру выйдет, что проблемы нет. Скрыто окажется главное — что это были за десять минут, сколько стоит час такого специалиста и сколько правок из полутора сотен он сделал неверно, не заметив. Поэтому считаем действия: одну неделимую операцию пользователя — выделение, удаление, вставку, ввод формулы, протяжку, подтверждение.
Задача: перевести модель с месяцев на кварталы. Условия — 10 листов, на листе от 20 до 150 строк бюджета, горизонт шесть лет, то есть 72 месячные колонки превращаются в 24 квартальные.
Как построена модель | 10 листов × 20 строк | 10 листов × 150 строк | От чего растёт |
|---|---|---|---|
Excel, координатная модель (колонка = месяц, формулы по координатам) | ≈ 460 | ≈ 3 060 | от числа строк и листов |
Excel, дисциплинированная (данные списком с датами, свод через | ≈ 70 | ≈ 70 | от числа листов |
Расчётные группы (период — параметр модели) | 2 | 2 | не растёт |
Откуда числа. В координатной модели на каждом листе неизбежны: выделить блок месячных колонок, удалить, вставить 24 новых, ввести заголовок первого квартала, протянуть заголовки — пять действий на шапку. Дальше на каждую строку — переписать формулу в первой квартальной колонке и протянуть вправо: два действия на строку. Плюс одно на поиск поехавших межлистовых ссылок, и это заведомо оптимистично. Итого 6 + 2 × строк на лист.
Дисциплинированный вариант — это тот же приём, воспроизведённый в Excel вручную: значения лежат списком с датами, формулы адресуют не координаты, а границы периода. Тогда сами формулы не трогаются вообще, работа сводится к пересборке строки границ и заголовков — порядка семи действий на лист. Это не то, что вы, скорее всего, видели в реальных моделях. Это перенос дисциплины из мира BI (данные списком, агрегация по диапазону дат) в Excel вручную — работает, но требует знания, которое финансисту неоткуда взять: ни один учебник по построению финмодели этому не учит. Нашел этот логичный способ в среде аналитиков, включил скорее как лучшую достижимую в Excel планку.
Расчетные группы — два действия на всю модель: выбрать период и применить. Правило записано один раз, колонок в хранилище нет. Прибавьте по действию на каждую панель, где период переопределён специально, — обычно это единицы.
Здесь же виден и второй эффект, который временем не измеряется. При трёх тысячах правок вопрос не в том, ошибётся ли человек, а в том, сколько ошибок останется незамеченными: модель после них по-прежнему считает и выдаёт правдоподобные числа. Два действия — это просто негде ошибиться.
Чего это стоит и что ещё дает
Бесплатных архитектурных приёмов не бывает, есть затраты, но и плюшки тоже.
Нужен слой хранения. Модель в базе — это не файл, который можно переслать почтой. Взамен вы получаете то, ради чего всё затевалось: одна версия правил, роли и права, отсутствие «модель v7_final_финал.xlsx».
Формулу видно целиком — вместе с подставленными числами. Правило «ткнуть в ячейку и прочитать формулу» меняется к лучшему: правило живёт на строке и группе, а на экране — результат. А здесь подсказка при наведении разворачивает обе половины сразу — правило по именам строк и те же имена, заменённые числами; в листе ради второй половины надо открывать пошаговое вычисление формулы, и по одной ячейке за раз. Ячейку, которую посчитать не удалось, видно по подсветке, а в подсказке на месте потерянной ссылки стоит (Not found 113326) — не безымянное «#ССЫЛКА!», а тот самый id, которого не хватает. Отсюда же формулу можно поправить: открыть карточку для этой статьи затрат и отредактировать формулу.
Считается на лету. Модель собирается запросами в момент открытия. Пока это её достоинство — данные всегда свежие. На больших объёмах это упирается в стоимость запросов, и тогда нужен либо кэш, либо материализация — ровно та же развилка, что у любого live-отчёта.
Это не замена Excel. Excel останется у всех, и у него нулевое трение — почему это структурно так, я разбирал в предыдущей статье. Приём выше не принижает Excel (наше всё) и не бросает тень; он про то, как перестать хардкодить период, когда вы строите модель в системе, а не в файле.
Что можно забрать, даже если не собираетесь менять инструмент
Приём переносим и работает сам по себе. Четыре правила, которые дают почти весь эффект:
Период и горизонт — параметры, а не колонки. Если в вашей модели нельзя ответить на вопрос «где хранится период расчёта» одной ячейкой, он хранится в геометрии, и модель будет ломаться.
Адресуйте по именам. Именованные диапазоны и структурированные ссылки в таблицах Excel — это доступная версия того же самого. Дорого обойдётся не завести их сразу, а переводить логику потом.
У значения должна быть дата, а не место. Держите исходные данные списком записей с датой актуальности, а не матрицей «статья × месяц». Матрицу всегда можно собрать из списка; список из матрицы — уже нет.
Называйте сущности так, как их называют люди. Лист «Квартал», а не
q_dim; параметр, совпадающий с именем справочника, из которого он берётся. Это ничего не стоит на старте и экономит полчаса-час каждому, кто потом откроет модель и попробует понять, откуда что взялось.
Дальше начинается более продвинутая часть — пользовательские запросы как источник данных модели и сценарии план/факт поверх одного набора записей. Про это будет отдельно, если к статье будет интерес. Обычно у пользователя этого подхода сразу шквал вопросов: как то делать, как это, а я вот так привык и т.д. — ответы есть на всё, а также вы можете развивать эту систему.
Спасибо!
