За изчисляване на умишлени циклични препратки (circular references) в Delphi, HotXLS предлага итеративно изчисляване във XLSX ядрото си: настройте TXLSXWorkbook.Iterate на True и тогава методът TXLSXWorkbook.Recalculate обхожда всеки открит цикъл от препратки до достигане на фиксирана точка – до максимум IterateCount преминавания или докато всяка клетка се променя с по-малко от IterateDelta – вместо да спре и да върне грешка #REF!
Тази разлика е по-съществена, отколкото едно обикновено булево свойство предполага. Същото изчислително ядро, което открива циклична препратка и отказва да влезе в безкраен цикъл, при промяна на една опция ще изчисли цикъла съзнателно, докато стойностите му се стабилизират. Точното разбиране на двете поведения – кога цикълът е дефект, който трябва да се докладва, и кога е модел, който трябва да се реши – е основната тема на тази статия
Защо цикличната препратка дава грешка по подразбиране?
По подразбиране HotXLS третира всеки цикъл от препратки как грешка при създаването на документа и я съобщава, вместо да го изчислява. TXLSXWorkbook.Recalculate изгражда граф на зависимостите на формулите, изчислява всяка клетка с формула в топологичен ред и връща стойност lxErrorRef в момента, в който открие цикъл – възлите, които никога не могат да бъдат обработени при топологичното сортиране. Клетките в този цикъл запазват предходните си кеширани стойности, а всяка формула извън цикъла се изчислява нормално. Механиката на този граф и причините клетките в цикъла да се пропускат, а не да се въртят в цикъл, са описани в съпътстващата статия за инкрементно преизчисляване на формули и графа на зависимостите
Поведението по подразбиране е по-безопасното, тъй като повечето цикли са грешки: сумарен ред, случайно включен в собствения си SUM диапазон, или копиране, което пренасочва препратка към самата себе си. Ясният код за грешка при преизчисляването е точно това, което ви е нужно в тези случаи. Но съществува специфичен и важен клас от модели, които са циклични умишлено. Таблици за изчисляване на сложна лихва, циклично разпределение на разходи или режийни между отдели, както и изчисляване на такси на базата на баланса – всички те описват стойност, която съвсем легитимно се връща като входни данни към самата себе си. Excel ги изчислява само след като потребителят избере File → Options → Formulas → Enable iterative calculation
Как се активира итеративното изчисляване в 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;Какво спира итерациите?
Две независими условия за спиране управляват алгоритъма и разбирането им е важно, за да се предпази моделът от грешки или безкрайно въртене. IterateCount е лимитът за това колко пъти се преизчисляват клетките в цикъла; стойността му по подразбиране е 100, което съответства на Excel. IterateDelta е прагът на сходимост: след всяко преминаване алгоритъмът измерва най-голямата числова промяна във всички клетки от цикъла и когато максималната промяна падне под IterateDelta – по подразбиране 0.001 – итерационният цикъл прекъсва преждевременно. Итерациите приключват при изпълнението на което и да е от двете условия първо
Едно от следствията трябва да се отбележи ясно, тъй като това е реалната граница на тази функция. Когато лимитът на стъпките бъде достигнат, без промяната да е паднала под IterateDelta, методът SolveCycleIteratively не генерира грешка – той връща lxOk и оставя в клетките стойностите от последната итерация, точно както Excel записва последно изчислените числа, когато собственият му лимит на итерации е достигнат без сходимост. Поради това успешният код от Recalculate при итеративен режим означава „алгоритъмът се изпълни“, а не „стойностите са сходими“. Модел, чиято обратна връзка се разсейва или вибрира, просто ще изразходва всички IterateCount итерации и ще върне числа, които изобщо не са фиксирана точка, без да бъде сигнализирана грешка
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;
Как се запива тази настройка в 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 структурата за съхранение, записваща настройките във всеки генериран файл. За финансовите и инженерните модели, които са умишлено циклични, това е разликата между код за грешка и реален отговор