För att beräkna avsiktliga cirkelreferenser i Delphi exponerar HotXLS iterativ beräkning på sin XLSX-motor: ställ in TXLSXWorkbook.Iterate till True så går TXLSXWorkbook.Recalculate igenom varje upptäckt referenscykel mot en fixpunkt — upp till IterateCount passeringar, eller tills varje cell ändras med mindre än IterateDelta — snarare än att returnera #REF! och ge upp
Den skillnaden spelar större roll än vad den enkla booleska variabeln antyder. Samma motor som upptäcker en referenscykel och vägrar att loopa i den, kommer med en ändrad egenskap att utvärdera cykeln avsiktligt tills den stabiliseras. Att hålla de två beteendena isär — när en cykel är ett fel att rapportera och när det är en modell att lösa — är hela ämnet för den här artikeln
Varför ger en cirkelreferens ett felmeddelande som standard?
Som standard behandlar HotXLS alla referenscykler som ett redigeringsfel och rapporterar det istället för att beräkna det. TXLSXWorkbook.Recalculate bygger en beroendegraf för formler, utvärderar varje formelcell i topologisk ordning och returnerar lxErrorRef så fort den hittar en cykel — de noder som aldrig kan frigöras under den topologiska sorteringen. Dessa cykelmedlemmar behåller sina tidigare cachade värden; alla formler utanför cykeln utvärderas fortfarande normalt. Mekaniken bakom den grafen, och varför cykelmedlemmarna hoppas över istället för att loopas, beskrivs i den tillhörande artikeln om inkrementell formelåterberäkning och beroendegrafen
Standardalternativet är det säkra eftersom de flesta cykler är buggar: en sammanfattningsrad som av misstag dras in i sitt eget SUM-intervall, eller en kopiering som flyttar en referens till sig själv. En tydlig felkod vid återberäkning är precis vad du vill ha för dessa. Men en specifik och viktig klass av modeller är cirkulär avsiktligt. Ränte-på-ränta-scheman, cirkulära kostnads- eller omkostnadsfördelningar mellan avdelningar och balansstyrda avgiftsberäkningar beskriver alla ett värde som legitimt matar tillbaka till sina egna indata — och Excel beräknar dem först när användaren kryssar i Arkiv → Alternativ → Formler → Aktivera iterativ beräkning
Hur aktiverar du iterativ beräkning i HotXLS?
HotXLS speglar den kryssrutan i Excel med tre egenskaper på TXLSXWorkbook: Iterate aktiverar läget, och när det är True skickas en upptäckt cykel till en iterativ lösare istället för att generera lxErrorRef. Tänk på det klassiska ränte-på-ränta-cellparet, där det utgående saldot beror på räntan och räntan beror på saldot
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 refererar till B4 och B4 refererar till B3, så beroendegrafen rapporterar en cykel med två noder. Med Iterate kvar på standardvärdet False skulle det paret returneras som lxErrorRef och ingen av cellerna skulle stabiliseras. Med True matar Recalculate cykeln från de aktuella cachade värdena och återutvärderar dess medlemmar pass efter pass, vilket matar varje passerings utdata tillbaka som nästa passeringars indata tills siffrorna slutar röra sig. Här är den stängda formen kapital / (1 - ränta), så saldot stabiliseras på 1052.63 och räntan på 52.63 — samma siffror som Excel producerar med iteration aktiverad
Vad får iterationen att sluta?
Två oberoende stoppvillkor begränsar lösaren, och att förstå båda är vad som hindrar en modell från att antingen felaktigt stanna eller snurra i evighet. IterateCount är det hårda taket för hur många gånger cykelmedlemmarna återutvärderas; det har standardvärdet 100, vilket matchar Excel. IterateDelta är konvergenströskeln: efter varje pass mäter lösaren den största numeriska förändringen över alla cykelceller, och så fort den maximala förändringen faller under IterateDelta — standard 0.001 — avbryts pass-loopen tidigt. Det villkor som uppfylls först avslutar iterationen
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;
En konsekvens är värd att nämna tydligt, eftersom det är funktionens ärliga gräns. När taket nås utan att förändringen faller under IterateDelta genererar SolveCycleIteratively inte ett fel — den returnerar lxOk och lämnar cellerna med sina värden från det senaste passet, exakt som Excel skriver de senast beräknade talen när dess egen iterationsgräns nås utan konvergens. Så en framgångsrik returkod från Recalculate under iterativt läge betyder "lösaren kördes", inte "lösaren konvergerade". En modell vars feedback-loop divergerar eller oscillerar kommer tyst att konsumera alla IterateCount-passeringar och lämna tillbaka siffror som inte alls är en fixpunkt, och inget undantag markerar skillnaden
Hur sparas inställningen till XLSX- och XLS-filer?
Inställningarna för iterativ beräkning sparas i båda kalkylbladsformaten, så att en arbetsbok som öppnas i Excel beter sig som din Delphi-kod konfigurerade den. På XLSX-sidan skriver skrivaren ut OOXML-elementet <calcPr> endast när Iterate är True, och den utelämnar alla attribut som fortfarande har sina standardvärden för att hålla utdata minimala: en arbetsbok som använder standardvärdena skriver bara <calcPr iterate="1"/>, medan iterateCount visas endast när det skiljer sig från 100 och iterateDelta endast när det skiljer sig från 0.001. Vid öppning läser TXLSXWorkbook tillbaka samma tre attribut, så överföringen är symmetrisk
Den äldre BIFF8 (.xls)-motorn, TXLSWorkbook, bär motsvarande tillstånd via tre separata poster under en annan egenskapstrippel. EnableIteration mappar till CalcIter-posten ($0011, [MS-XLS] §2.4.33), MaxIterations till CalcCount-posten ($000C, [MS-XLS] §2.4.31) och MaxIterationChange till CalcDelta-posten ($0010, [MS-XLS] §2.4.32). Setters tvingar fram specifikationsintervallen — CalcCount måste ligga inom 1..32767, så MaxIterations begränsas, och en negativ MaxIterationChange återgår till standardvärdet 0.001. Ställ in dem på en laddad .xls-arbetsbok så skrivs de tre beräkningsposterna troget vid sparning
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;Observera den medvetna uppdelningen i namngivning: XLSX-motorn använder Iterate / IterateCount / IterateDelta (OOXML-vokabulären), medan BIFF8-motorn använder EnableIteration / MaxIterations / MaxIterationChange (anpassat till [MS-XLS] postnamn). Båda tripplarna beskriver samma tre inställningar — en på/av-brytare, ett iterationstak och ett konvergensdelta — med samma standardinställningar för av, 100 och 0.001
När är en cirkelreferens en bugg snarare än en modell?
Att aktivera iteration är inte ett sätt att få cirkelreferensvarningar att försvinna, och att behandla det så är en fälla. Att slå på Iterate globalt omvandlar varje oavsiktlig cykel — de som standardfelkoden var där för att fånga — till ett tyst konvergerat eller tyst icke-konvergerat tal. Disciplinen är den omvända: behåll Iterate som False som normalläge så att verkliga redigeringsfel fortfarande kommer upp som lxErrorRef, och aktivera iteration endast på arbetsböcker vars cirkularitet är designad och förstådd
När en cykel dyker upp och du inte är säker på vilken typ det är, är formelutvärderingsspåraren verktyget som skiljer dem åt: spåra den misstänkta formeln så blir referenskedjan som viker tillbaka på sig själv synlig steg för steg, så att du kan avgöra om den kodar en verklig feedback-loop eller en herrelös självreferens. Det hjälper också att komma ihåg att en cykelcell kan anropa vilken inbyggd funktion som helst på vägen runt loopen — samma kalkylator som löser en ingenjörs- eller komplex-tal-formel utvärderar cykelmedlemmarna vid varje pass — så en divergerande modell är ofta ett formelproblem inuti loopen, inte ett iterationsinställningsproblem
Den praktiska checklistan är kort. Bekräfta att loopen har en verklig fixpunkt innan du litar på iterationen; håll IterateDelta tillräckligt snäv för att "konvergerad" betyder vad din modell behöver; och efter en Recalculate som du förväntar dig ska konvergera, kontrollera ett känt utdata istället för att enbart lita på lxOk, eftersom den koden inte kan skilja konvergens från ett uppnått iterationstak
Iterativ beräkning av cirkelreferenser är en del av XLSX-motorn i HotXLS Delphi Excel Component, tillsammans med den inkrementella återberäkningen av beroendegrafen den bygger på och XLSX- och BIFF8-lagringen som bär inställningen in i varje fil du skriver. För de finansiella och tekniska modeller som är cirkulära avsiktligt är det skillnaden mellan en felkod och ett svar