Для обчислення навмисних циклічних посилань у 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: 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 і жодна з них не буде обчислена. Якщо встановити Iterate у 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
case Book.Recalculate of
lxOk:
// 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);
lxErrorRef:
// 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 записувач додає елемент 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) 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 Excel Component разом із інкрементним перерахунком на основі графа залежностей та збереженням у форматах OOXML та BIFF8. Для фінансових та інженерних моделей, де циклічність є навмисною, це різниця між кодом помилки та правильним результатом.