Для вычисления преднамеренных циклических ссылок в Delphi библиотека HotXLS предоставляет механизм итеративных вычислений в своем движке XLSX: установите свойство TXLSXWorkbook.Iterate в значение True, и TXLSXWorkbook.Recalculate будет последовательно рассчитывать каждый обнаруженный цикл вычислений до тех пор, пока значения не сойдутся — в пределах лимита IterateCount или пока изменения в ячейках не станут меньше IterateDelta — вместо того, чтобы сразу возвращать ошибку #REF!
Это различие гораздо важнее, чем может показаться по простому переключению флага. Тот же самый движок, который обычно обнаруживает цикл зависимостей и отказывается его вычислять, при изменении одного свойства будет целенаправленно рассчитывать этот цикл до стабилизации значений. Разнице между этими двумя типами поведения — когда цикл является ошибкой в формулах, а когда представляет собой корректную математическую модель — и посвящена эта статья
Почему циклическая ссылка по умолчанию возвращает ошибку?
По умолчанию HotXLS рассматривает любую циклическую ссылку как ошибку при составлении формул и сообщает о ней вместо вычисления. Метод TXLSXWorkbook.Recalculate строит граф зависимостей формул, вычисляет ячейки с формулами в топологическом порядке и возвращает код ошибки lxErrorRef, как только обнаруживает цикл — узлы, которые не могут быть обработаны при топологической сортировке. Ячейки, входящие в цикл, сохраняют свои предыдущие кэшированные значения, в то время как все остальные формулы за пределами цикла продолжают вычисляться в обычном режиме. Принципы работы этого графа и причины, по которым циклы пропускаются, а не вычисляются бесконечно, подробно описаны в статье об инкрементном пересчете формул и графе зависимостей
Поведение по умолчанию является безопасным, поскольку большинство циклических ссылок возникают случайно: итоговая строка случайно попадает в собственный диапазон SUM, либо при копировании формула начинает ссылаться на саму себя. В таких случаях явный код ошибки при пересчете — это именно то, что нужно. Однако определенный класс математических моделей требует циклов намеренно. Расчет сложных процентов, взаимное распределение затрат между отделами компании и расчет комиссий на основе итогового баланса — все эти задачи описывают значения, которые возвращаются во входные параметры. Excel вычисляет их только тогда, когда пользователь включает опцию «Включить итеративные вычисления» в меню Файл → Параметры → Формулы
Как включить итеративные вычисления в HotXLS?
HotXLS поддерживает этот функционал с помощью трех свойств в TXLSXWorkbook: свойство Iterate включает режим вычислений, и при его значении True обнаруженный цикл передается итеративному вычислителю вместо генерации ошибки lxErrorRef. Рассмотрим классический пример пары ячеек для расчета процентов, где конечный баланс зависит от начисленных процентов, а проценты — от баланса
var
Book: TXLSXWorkbook;
Sheet: TXLSXWorksheet;
begin
Book := TXLSXWorkbook.Create;
try
Sheet := Book.Sheets.Add('Model');
Sheet.Cells[1, 2].Value := 1000; // B1: opening principal
Sheet.Cells[2, 2].Value := 0.05; // B2: period rate
Sheet.Cells[3, 2].Formula := 'B1+B4'; // B3: balance = principal + interest
Sheet.Cells[4, 2].Formula := 'B3*B2'; // B4: interest = balance * rate
Book.Iterate := True; // opt in to iterative calculation
if Book.Recalculate = lxOk then
// B3 converges to 1052.63..., B4 to 52.63...
Report(Sheet.Cells[3, 2].Value);
finally
Book.Free;
end;
end;Ячейка B3 ссылается на B4, а B4 ссылается на B3, поэтому граф зависимостей фиксирует двухэлементный цикл. Если оставить свойство Iterate в значении False (по умолчанию), эта пара вернет ошибку lxErrorRef. При значении True метод Recalculate инициализирует цикл текущими кэшированными значениями и рассчитывает его шаг за шагом, передавая выходные данные каждого шага в качестве входных для следующего, пока значения не стабилизируются. В данном примере математический предел сходимости равен principal / (1 - rate), поэтому баланс сойдется к 1052.63, а проценты — к 52.63 (абсолютно те же цифры дает Excel с включенными итерациями)
Что останавливает итерации?
Работу вычислителя ограничивают два независимых условия остановки, и понимание обоих важно для того, чтобы модель не зависала и не возвращала неверные результаты. Свойство IterateCount задает жесткий лимит на количество пересчетов цикла (по умолчанию 100, как в Excel). Свойство IterateDelta определяет порог сходимости: после каждого шага вычислитель измеряет максимальное изменение числового значения среди всех ячеек цикла, и как только это изменение падает ниже IterateDelta (по умолчанию 0.001), цикл прерывается досрочно. Итерации завершаются при достижении любого из этих условий
Book.Iterate := True;
Book.IterateCount := 1000; // hard cap: at most 1000 passes over the cycle
Book.IterateDelta := 0.0001; // convergence: stop once every cell moves < 0.0001
ForceRecalcResult := Book.Recalculate;
if ForceRecalcResult = lxOk then
begin
// the cycle converged, OR it hit the 1000-pass cap and kept its
// last-iteration values -- both paths return lxOk when Iterate is True
SaveWorkbook(Book);
end
else if ForceRecalcResult = lxErrorRef then
begin
// only reachable with Iterate = False: the cycle was reported, not solved
LogWarning('Circular reference with iteration disabled');
end;Здесь важно отметить следующее, поскольку это является естественным ограничением функционала. Если лимит шагов достигнут, но изменения не упали ниже порога IterateDelta, метод SolveCycleIteratively не генерирует ошибку — он возвращает lxOk и оставляет ячейки со значениями, полученными на последней итерации. Точно так же поступает Excel, записывая последние вычисленные цифры при превышении лимита без сходимости. Таким образом, успешный код возврата Recalculate в итеративном режиме означает лишь то, что «вычисление было выполнено», но не гарантирует, что «значения сошлись». Расходящаяся или колеблющаяся модель незаметно исчерпает все шаги IterateCount и вернет цифры, которые не являются математической точкой сходимости, и программа не выдаст никаких предупреждений
Как эти настройки сохраняются в файлах XLSX и XLS?
Параметры итеративных вычислений сохраняются в обоих форматах электронных таблиц, поэтому книга, открытая в Excel, будет вести себя точно так же, как вы настроили ее в коде Delphi. В формате XLSX запись XML генерирует элемент <calcPr> только при значении Iterate = True, пропуская атрибуты с настройками по умолчанию ради экономии места: для книги с настройками по умолчанию запишется просто <calcPr iterate="1"/>, в то время как iterateCount запишется только при отличии от 100, а iterateDelta — при отличии от 0.001. При открытии класса TXLSXWorkbook считывает эти атрибуты обратно, обеспечивая полную совместимость
Унаследованный движок BIFF8 (.xls), представленный классом TXLSWorkbook, сохраняет это состояние в трех отдельных записях. Свойство EnableIteration сопоставляется с записью CalcIter ($0011, [MS-XLS] §2.4.33), MaxIterations — с записью CalcCount ($000C, [MS-XLS] §2.4.31), а MaxIterationChange — с записью CalcDelta ($0010, [MS-XLS] §2.4.32). Сеттеры проверяют допустимые спецификацией диапазоны (CalcCount должен быть в пределах 1..32767, поэтому значение MaxIterations ограничивается, а отрицательное значение MaxIterationChange сбрасывается на 0.001). При сохранении файла .xls все три записи записываются корректно
var
Book: TXLSWorkbook; // BIFF8 (.xls) engine
begin
Book := TXLSWorkbook.Create;
try
Book.Open('model.xls');
Book.EnableIteration := True; // CalcIter record $0011
Book.MaxIterations := 500; // CalcCount record $000C (clamped to 1..32767)
Book.MaxIterationChange := 0.0001; // CalcDelta record $0010
Book.SaveAs('model.xls'); // the three calc records round-trip
finally
Book.Free;
end;
end;Обратите внимание на различие в именах: движок XLSX использует имена Iterate / IterateCount / IterateDelta (терминология OOXML), в то время как движок BIFF8 использует EnableIteration / MaxIterations / MaxIterationChange (согласно спецификации записей [MS-XLS]). Обе группы свойств описывают одни и те же настройки — флаг включения, лимит шагов и порог сходимости — со стандартными значениями «выключено», 100 и 0.001
Когда циклическая ссылка является ошибкой, а не расчетной моделью?
Включение итераций — это не способ заставить исчезнуть предупреждения о циклических ссылках, и такое использование является частой ошибкой. Включение свойства Iterate для всех файлов превратит любые случайные ошибки в формулах (для обнаружения которых и создавался код ошибки) в неверно вычисленные числа, о сходимости которых вы ничего не будете знать. Правильный подход прямо противоположен: держите Iterate в значении False для стандартных расчетов, чтобы ошибки проектирования формул сразу возвращали lxErrorRef, и включайте итерации только для тех книг, где циклы спроектированы намеренно и полностью понятны
Если в расчете возникает цикл и вы не уверены в его природе, трассировщик вычисления формул поможет разобраться: отследите подозрительную формулу, и вся цепочка ссылок, возвращающаяся к исходной точке, станет наглядно видна шаг за шагом. Это позволит решить, является ли цикл реальной обратной связью или случайной ошибкой. Также стоит помнить, что ячейка в цикле может вызывать любые встроенные функции (тот же калькулятор, который рассчитывает инженерные формулы или комплексные числа, вычисляет ячейки на каждом шаге итерации), поэтому расхождение значений часто связано с ошибками в самих формулах внутри цикла, а не с параметрами итерации
Контрольный список
Контрольный список для практической работы прост. Убедитесь, что цикл имеет математическую точку сходимости, прежде чем включать итерации. Задавайте IterateDelta достаточно строгим, чтобы сходимость гарантировала требуемую точность. После вызова Recalculate, от которого вы ожидаете сходимости, выборочно проверьте контрольные ячейки вместо слепого доверия коду lxOk, так как этот код не отличает реальную сходимость от простой остановки по лимиту шагов
Итеративный расчет циклических ссылок входит в состав движка XLSX в компоненте HotXLS для Delphi наряду с графом зависимостей и сохранением настроек в файлы OOXML и BIFF8. Для финансовых и инженерных моделей с обратной связью это единственный способ получить правильный ответ вместо ошибки в ячейке