Чтобы вычислять намеренные циклические ссылки в Delphi, HotXLS открывает итеративные вычисления в своём движке XLSX: установите TXLSXWorkbook.Iterate в True, и TXLSXWorkbook.Recalculate поведёт каждый обнаруженный цикл ссылок к неподвижной точке — не более чем за IterateCount проходов или пока каждая ячейка не станет меняться меньше чем на IterateDelta — вместо того чтобы вернуть #REF! и сдаться
Это различие значит больше, чем подсказывает единственное логическое значение. Тот же движок, который обнаруживает цикл ссылок и отказывается по нему крутиться, с одним переключённым свойством вычислит этот цикл намеренно, пока тот не устаканится. Не путать эти два поведения — когда цикл является дефектом, о котором надо сообщить, и когда он является моделью, которую надо решить, — и есть весь предмет этой статьи
Почему циклическая ссылка по умолчанию даёт ошибку?
По умолчанию HotXLS считает любой цикл ссылок авторской ошибкой и сообщает о нём, а не вычисляет его. TXLSXWorkbook.Recalculate строит граф зависимостей формул, вычисляет каждую формульную ячейку в топологическом порядке и возвращает lxErrorRef в тот момент, когда находит цикл, — узлы, которые невозможно освободить во время топологической сортировки. Эти участники цикла сохраняют свои прежние кешированные значения; каждая формула вне цикла по-прежнему вычисляется нормально. Механика этого графа и то, почему участники цикла пропускаются, а не зацикливаются, разобраны в сопутствующей статье про инкрементный пересчёт формул и граф зависимостей
Умолчание безопасно, потому что большинство циклов — это ошибки: итоговая строка, случайно втянутая в собственный диапазон SUM, копирование со вставкой, сдвинувшее ссылку на саму себя. Громкий код ошибки во время пересчёта — ровно то, чего вы хотите для таких случаев. Но один конкретный и важный класс моделей цикличен намеренно. Графики процентов на проценты, круговое распределение затрат или накладных расходов между подразделениями и расчёт комиссий от остатка — все они описывают величину, которая законно возвращается в собственные входные данные, и Excel вычисляет их лишь после того, как пользователь поставит галочку Файл → Параметры → Формулы → Включить итеративные вычисления
Как включить итеративные вычисления в HotXLS?
HotXLS отражает этот флажок Excel тремя свойствами на 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: начальная сумма
Sheet.Cells[2, 2].Value := 0.05; // B2: ставка за период
Sheet.Cells[3, 2].Formula := 'B1+B4'; // B3: остаток = сумма + проценты
Sheet.Cells[4, 2].Formula := 'B3*B2'; // B4: проценты = остаток * ставка
Book.Iterate := True; // включаем итеративные вычисления
if Book.Recalculate = lxOk then
// B3 сходится к 1052.63..., B4 к 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; // жёсткий предел: не более 1000 проходов по циклу
Book.IterateDelta := 0.0001; // сходимость: стоп, когда каждая ячейка сдвигается < 0.0001
case Book.Recalculate of
lxOk:
// цикл сошёлся ЛИБО упёрся в предел в 1000 проходов и сохранил значения
// последней итерации -- оба пути возвращают lxOk, когда Iterate равно True
SaveWorkbook(Book);
lxErrorRef:
// достижимо только при Iterate = False: о цикле сообщили, а не решили его
LogWarning('Circular reference with iteration disabled');
end;
Одно следствие стоит назвать прямо, потому что это честная граница возможности. Когда предел достигнут, а изменение так и не упало ниже IterateDelta, SolveCycleIteratively не поднимает ошибку — он возвращает lxOk и оставляет в ячейках значения последней итерации, ровно как Excel записывает последние вычисленные числа, когда его собственный предел итераций исчерпан без сходимости. Так что успешный код возврата из Recalculate в итеративном режиме означает «решатель отработал», а не «решатель сошёлся». Модель, чья петля обратной связи расходится или колеблется, тихо израсходует все IterateCount проходов и вернёт числа, которые вовсе не являются неподвижной точкой, и никакое исключение эту разницу не отметит
Как настройка сохраняется в файлы XLSX и XLS?
Настройки итеративных вычислений сохраняются в обоих табличных форматах, поэтому книга, открытая в Excel, ведёт себя так, как её настроил ваш код на Delphi. На стороне XLSX писатель выпускает элемент OOXML <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)
begin
Book := TXLSWorkbook.Create;
try
Book.Open('model.xls');
Book.EnableIteration := True; // запись CalcIter $0011
Book.MaxIterations := 500; // запись CalcCount $000C (ограничена 1..32767)
Book.MaxIterationChange := 0.0001; // запись CalcDelta $0010
Book.SaveAs('model.xls'); // три записи расчёта переживают круг
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 Excel Component, рядом с инкрементным пересчётом по графу зависимостей, на котором оно построено, и с сохранением в OOXML и BIFF8, которое переносит эту настройку в каждый записываемый вами файл. Для финансовых и инженерных моделей, цикличных по замыслу, это разница между кодом ошибки и ответом