Библиотека HotXLS для Delphi и C++Builder выполняет инкрементный пересчет формул с помощью метода TXLSXWorkbook.Recalculate. При первом вызове строится граф зависимостей формул и вычисляется каждая ячейка с формулой. Все последующие вызовы пересчитывают в топологическом порядке только те ячейки, на которые повлияли изменения значений с момента предыдущего прохода. Вся процедура выполняется за один проход, а её стоимость пропорциональна количеству измененных (dirty) ячеек, а не размеру всей книги
Это единственное архитектурное решение отличает финансовую модель, которая реагирует на изменение исходных данных за миллисекунды, от модели, зависающей на секунды. Если вы генерируете отчеты, где несколько входных ячеек питают тысячи зависимых формул, оставшаяся часть статьи объяснит работу графа зависимостей, укажет, какие функции исключаются из инкрементного расчета, и покажет, как возвращаются циклические ссылки вместо бесконечного зацикливания
Почему изменение одной ячейки приводит к пересчету ста тысяч формул?
Простой формульный движок не хранит информацию о зависимостях между ячейками, поэтому при любом изменении единственный безопасный шаг — вычислить всё заново. Хуже того, классический рекурсивный подход (когда формула А ссылается на формулу Б, и Б вычисляется на месте) выполняет пересчет зависимых ячеек безусловно, игнорируя кэшированные значения. Цепочка из n формул, каждая из которых ссылается на предыдущую, требует O(n²) вычислений за один полный проход, а циклическая ссылка приводит к бесконечной рекурсии и сбою. Каждый разработчик таблиц, связывавший каскадную модель с рекурсивным вычислителем, сталкивался с обеими этими проблемами
Microsoft Excel решил эту проблему десятки лет назад с помощью цепочки вычислений: определенного порядка ячеек с формулами, при котором изменение помечает небольшую группу ячеек как dirty (измененные), и движок проходит только по зависимой части цепочки. HotXLS применяется эту же идею в виде явного графа зависимостей, который строится один раз на основе скомпилированных деревьев формул и повторно используется при пересчете. Смысл заключается в том, что затраты на пересчет должны определяться объемом изменений, а не общим размером книги
Как граф зависимостей превращает изменение в один проход
Граф зависимостей HotXLS представляет каждую ячейку с формулой как узел, а связи идут от влияющих ячеек к зависимым. Когда ваш код записывает значение в ячейку, книга помечает её как dirty. При запуске Recalculate этот статус распространяется по связям на все зависимые формулы, и измененный подграф вычисляется ровно один раз в топологическом порядке с использованием алгоритма Кана. Поскольку формула никогда не обрабатывается раньше влияющих на неё ячеек, каждый узел вычисляется лишь однажды, что делает сложность прохода равной O(dirty)
Топологический порядок также решает проблему рекурсии в её корне. Во время прохода пересчета движок переходит в специальный режим, в котором любое обращение к другой ячейке с формулой считывает её кэшированное значение напрямую, вместо повторного вычисления. Заданный порядок гарантирует, что кэш уже обновлен. Этот же механизм означает, что циклическая ссылка не может вызвать бесконечную рекурсию: ничто внутри прохода не приводит к повторному вызову оценщика для соседней ячейки
var
Book: TXLSXWorkbook;
Inputs, Model: TXLSXWorksheet;
begin
Book := TXLSXWorkbook.Create;
try
Inputs := Book.Sheets.Add('Inputs');
Model := Book.Sheets.Add('Model');
Inputs.Cells[2, 2].Value := 0.05; // допущение роста
Model.Cells[2, 2].Formula := 'Inputs!B2*1000'; // формулы XLSX не требуют ведущего символа '='
Model.Cells[3, 2].Formula := 'B2*(1+Inputs!B2)';
// ... еще тысячи строк, зависящих от того же допущения ...
Book.Recalculate; // первый вызов: строится граф, полное вычисление
Inputs.Cells[2, 2].Value := 0.07; // одно изменение помечает одну ячейку как dirty
Book.Recalculate; // второй вызов: выполняется только цепочка зависимых ячеек
finally
Book.Free;
end;
end;Каждый результат записывается в кэш Value ячейки, поэтому после завершения Recalculate вы считываете выходные данные так же, как и для любой другой ячейки. В цикле генерации отчетов используется шаблон из примера выше: один раз загружается или строится модель, а затем чередуются запись нескольких входных ячеек и вызов Recalculate с затратами ресурсов только на те формулы, которые действительно зависят от внесенных изменений
Какие функции Excel требуют пересчета при каждом проходе?
HotXLS обрабатывает функции NOW, TODAY, RAND, OFFSET и INDIRECT как волатильные: любая формула с их участием пересчитывается при каждом вызове Recalculate независимо от изменений во влияющих ячейках. Первые три функции волатильны по той же причине, что и в Excel — их результат зависит от времени вычисления, а не от других ячеек. Функции OFFSET и INDIRECT волатильны по более сложной причине: считываемые ими ячейки определяются динамически во время выполнения, поэтому граф не может статически построить для них связи зависимостей
Это же консервативное правило распространяется на ссылки, которые построитель графа не может сопоставить с одной прямоугольной областью. Формула, использующая именованный диапазон из нескольких областей или ссылающаяся на внешнюю книгу, также помечается как волатильная и пересчитывается при каждом проходе. Эта политика осознанна: лишнее вычисление требует немного времени, но пропущенная связь зависимости приведет к неактуальному значению в итоговом отчете, что является критической ошибкой. Если ваша модель опирается на имена в масштабе книги, сопутствующая статья об именованных диапазонах и межстраничных формулах описывает правила разрешения имен для одной области — они участвуют в графе в стандартном режиме
Отсюда следует практическое правило. В критичных путях выполнения большой модели используйте простые ссылки на ячейки и диапазоны, где граф может эффективно выполнять свою работу, и ограничьте использование OFFSET и INDIRECT лишь теми областями, где динамическая адресация действительно необходима. Модель с тысячей волатильных формул будет пересчитывать всю тысячу при каждом проходе, независимо от объема изменений, что аналогично поведению книг Excel, пересчитывающихся при каждом нажатии клавиши
Как HotXLS сообщает о циклических ссылках?
Метод TXLSXWorkbook.Recalculate возвращает lxOk при успешном проходе и lxErrorRef при обнаружении циклической ссылки. Участники цикла определяются во время топологической сортировки — это узлы, которые алгоритм Кана не может исключить. Они просто пропускаются, что предотвращает зацикливание: их кэшированные значения остаются без изменений, а все формулы за пределами цикла рассчитываются в обычном порядке. Вызывающий код получает понятный код ошибки вместо зависания процесса
case Book.Recalculate of
lxOk:
SaveReport(Book);
lxErrorRef:
// обнаружена циклическая ссылка; участники цикла сохранили предыдущие
// кэшированные значения, а всё остальное за пределами цикла обновлено
LogWarning('Circular reference detected - review model inputs');
end;Поиск ячеек, образующих цикл — задача отладки, для которой отлично подходит трассировщик вычисления формул: с его помощью можно шаг за шагом проследить цепочку вычислений, замыкающуюся саму на себя. Циклы в реальных моделях почти всегда являются ошибкой автора (например, строка итогов случайно включается в собственный диапазон SUM), поэтому явный вывод кода ошибки при пересчете — именно то, что нужно
Формулы массивов, отслеживание dirty-состояний и перестройка графа
Формулы массивов CSE получают один узел на весь закрепленный прямоугольник, а не по узлу на каждую ячейку. Корневая формула вычисляется один раз за проход; результирующая матрица записывается непосредственно в каждую ячейку диапазона, а формула, ссылающаяся на любую ячейку внутри закрепленного диапазона (а не только на левый верхний угол), получает связь зависимости от этого корневого узла. Скалярные результаты распределяются по прямоугольнику в соответствии с традиционными правилами обработки массивов в Excel
Отслеживание dirty-состояний встроено в стандартные сеттеры свойств, поэтому в вашем коде ничего не меняется. Запись Value в ячейку уведомляет книгу и помечает зависимые ячейки как dirty; назначение новой Formula является изменением структуры, поэтому помечает весь граф как неактуальный, и при следующем вызове Recalculate граф перестраивается заново перед вычислением. Добавление, удаление или перемещение листов также аннулирует граф, так как идентификатор узла кодирует индекс листа. Если граф не используется (для книги никогда не вызывается Recalculate), эти перехватчики требуют лишь одной проверки на nil при каждом присваивании, не влияя на обычные операции чтения-записи
Одно ограничение следует упомянуть открыто: граф отслеживает зависимости между ячейками, поэтому пользовательская функция, зарегистрированная через OnUserFunction, пересчитывается при изменении ячеек, передающих значения в её аргументы, как и любая другая формула. Если вы расширяете движок таким образом, статья о пользовательских функциях в формульном движке HotXLS подробно описывает контракт обратного вызова и правила передачи аргументов
Инкрементный пересчет является частью стандартного движка XLSX в составе компонента HotXLS Delphi Excel Component наряду с вычислителем формул, именованными диапазонами и конвейером импорта-экспорта, работу которого он ускоряет. Если ваше приложение Delphi или C++Builder работает с динамическими моделями (прайс-листами, сводными таблицами, каскадами отчетов), метод Recalculate — это именно то, что отделяет пересчет всей книги от пересчета единичного изменения