For at beregne tilsigtede cirkulære referencer i Delphi eksponerer HotXLS iterativ beregning på sin XLSX-motor: Indstil TXLSXWorkbook.Iterate til True, og TXLSXWorkbook.Recalculate gennemgår hver opdagede referencecyklus mod et fast punkt — op til IterateCount gennemløb, eller indtil hver celle ændrer sig med mindre end IterateDelta — i stedet for at returnere #REF! og give op
Denne forskel betyder mere, end den enkelte boolske værdi antyder. Den samme motor, som opdager en referencecyklus og nægter at køre i løkke på den, vil, med én egenskab slået til, evaluere denne cyklus bevidst, indtil den falder til ro. At holde styr på de to adfærdsmønstre — hvornår en cyklus er en defekt, der skal rapporteres, og hvornår det er en model, der skal løses — er hele emnet for denne artikel
Hvorfor fejler en cirkulær reference som standard?
Som standard behandler HotXLS enhver referencecyklus som en redigeringsfejl og rapporterer den i stedet for at beregne den. TXLSXWorkbook.Recalculate opbygger en formelafhængighedsgraf, evaluerer hver formelcelle i topologisk rækkefølge og returnerer lxErrorRef i det øjeblik, den finder en cyklus — de noder, der aldrig kan frigives under den topologiske sortering. Disse cyklusmedlemmer beholder deres tidligere cachede værdier; enhver formel uden for cyklussen evalueres stadig normalt. Mekanikken i denne graf, og hvorfor cyklusmedlemmerne springes over frem for at blive kørt i løkke, er dækket i den ledsagende artikel om trinvis formelgenberegning og afhængighedsgrafen
Standarden er den sikre, fordi de fleste cyklusser er fejl: En opsummeringsrække, der ved et uheld er trukket ind i sit eget SUM-område, eller en kopiering og indsættelse, der har flyttet en reference over på sig selv. En tydelig fejlkode på genberegningstidspunktet er præcis, hvad du ønsker for disse. Men en specifik og vigtig klasse af modeller er cirkulære med vilje. Rente-på-rente-planer, cirkulær fordeling af omkostninger eller faste omkostninger mellem afdelinger og saldostyrede gebyrberegninger beskriver alle en værdi, der legitimt føres tilbage til sine egne input — og Excel beregner dem kun, når brugeren markerer Filer → Indstillinger → Formler → Aktivér iterativ beregning
Hvordan aktiverer du iterativ beregning i HotXLS?
HotXLS afspejler dette afkrydsningsfelt i Excel med tre egenskaber på TXLSXWorkbook: Iterate slår tilstanden til, og når den er True, overgives en opdaget cyklus til en iterativ løser i stedet for at producere lxErrorRef. Overvej det kanoniske rente-på-rente-cellepar, hvor den endelige saldo afhænger af renten, og renten afhænger af saldoen
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 refererer til B4, og B4 refererer til B3, så afhængighedsgrafen rapporterer en cyklus med to noder. Med Iterate efterladt på standarden False, ville dette par komme tilbage som lxErrorRef, og ingen af cellerne ville falde til ro. Med den sat til True, sår Recalculate cyklussen ud fra de aktuelle cachede værdier og genevaluerer dens medlemmer gennemløb efter gennemløb, hvor hvert gennemløbs output fødes tilbage som det næste gennemløbs input, indtil tallene holder op med at bevæge sig. Her er den lukkede form principal / (1 - rate), så saldoen falder til ro på 1052.63 og renten på 52.63 — de samme tal, som Excel producerer med iteration aktiveret
Hvad får iterationen til at stoppe?
To uafhængige stopbetingelser begrænser løseren, og at forstå begge er det, der forhindrer en model i enten at fejle eller køre i ring. IterateCount er det hårde loft for, hvor mange gange cyklussens medlemmer genevalueres; det er standard 100, hvilket matcher Excel. IterateDelta er konvergensgrænsen: Efter hvert gennemløb måler løseren den største numeriske ændring på tværs af alle cyklusceller, og når denne maksimale ændring falder til under IterateDelta — standard 0.001 — afbrydes gennemløbsløkken tidligt. Den betingelse, der opfyldes først, afslutter 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 er værd at sige tydeligt, fordi det er funktionens ærlige grænse
Når loftet nås, uden at ændringen falder til under IterateDelta, SolveCycleIteratively ikke rejser en fejl — den returnerer lxOk og efterlader cellerne med deres værdier fra sidste iteration, præcis som Excel skriver de senest beregnede tal, når dets egen iterationsgrænse nås uden konvergens. Så en vellykket returkode fra Recalculate under iterativ tilstand betyder "løseren kørte", ikke "løseren konvergerede". En model, hvis feedback-løkke divergerer eller svinger, vil stille forbruge alle IterateCount-gennemløb og aflevere tal, der slet ikke er et fast punkt, og ingen undtagelse markerer forskellen
Hvordan gemmes indstillingen i XLSX- og XLS-filer?
Indstillingerne for iterativ beregning bevares i begge regnearksformater, så en projektmappe, der åbnes i Excel, opfører sig på den måde, som din Delphi kode konfigurerede den til. På XLSX-siden udsender skriveren kun OOXML-<calcPr>-elementet, når Iterate er True, og den udelader enhver attribut, der stadig er på sin standard, for at holde outputtet minimalt: En projektmappe, der bruger standarderne, skriver blot <calcPr iterate="1"/>, mens iterateCount kun vises, når det afviger fra 100, og iterateDelta kun, når det afviger fra 0.001. Ved åbning læser TXLSXWorkbook de samme tre attributter tilbage, så turen frem og tilbage er symmetrisk
Den ældre BIFF8 (.xls)-motor, TXLSWorkbook, bærer den tilsvarende tilstand via tre separate poster under en anden egenskabstriple
EnableIteration kortlægges til CalcIter-posten ($0011, [MS-XLS] §2.4.33), MaxIterations til CalcCount-posten ($000C, [MS-XLS] §2.4.31), og MaxIterationChange til CalcDelta-posten ($0010, [MS-XLS] §2.4.32). Setterne håndhæver specifikationsområderne — CalcCount skal ligge i 1..32767, så MaxIterations fastklemmes, og en negativ MaxIterationChange springer tilbage til standarden 0.001. Indstil dem på en indlæst .xls-projektmappe, og de tre calc-poster skrives trofast ved gemning
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;Bemærk den bevidste navneopdeling: XLSX-motoren taler om Iterate / IterateCount / IterateDelta (OOXML-ordforrådet), mens BIFF8-motoren taler om EnableIteration / MaxIterations / MaxIterationChange (tilpasset postnavnene i [MS-XLS]). Begge tripler beskriver de samme tre knapper — en tænd/sluk-knap, et iterationsloft og en konvergensgrænse — med de samme standarder: slukket, 100 og 0.001
Hvornår er en cirkulær reference en fejl frem for en model?
Aktivering af iteration er ikke en måde at få advarsler om cirkulære referencer til at forsvinde, og at behandle det på den måde er en fælde. At slå Iterate til globalt konverterer enhver utilsigtet cyklus — dem, som standardfejlkoden var der for at fange — til et lydløst konvergeret eller lydløst ikke-konvergeret tal. Videnskaben er den omvendte: Hold Iterate på False som den normale tilstand, så reelle redigeringsfejl stadig fremstår som lxErrorRef, og aktiver kun iteration på projektmapper, hvis cirkularitet er tilsigtet og forstået
Når en cyklus dukker op, og du ikke er sikker på, hvilken slags det er, er værktøjet til formelevalueringstracing det, der skelner dem fra hinanden: Spor den mistænkelige formel, og referencekæden, der folder sig tilbage på sig selv, bliver synlig trin for trin, så du kan afgøre, om den koder en reel feedback-løkke eller en vildfaren selvreference. Det hjælper også at huske, at en cykluscelle kan kalde enhver indbygget funktion undervejs i løkken — den samme beregner, der løser en ingeniør- eller kompleks-nummer-formel, evaluerer cyklusmedlemmerne ved hvert gennemløb — så en divergerende model er ofte et formelproblem inde i løkken, ikke et problem med iterationsindstillingerne
Den praktiske tjekliste er kort. Bekræft, at løkken har et reelt fast punkt, før du stoler på iteration; hold IterateDelta stram nok til, at "konvergeret" betyder det, din model har brug for; og efter en Recalculate, som du forventer konvergerer, skal du foretage et kvalitetstjek af et kendt output frem for blot at stole på lxOk, da denne kode ikke kan skelne konvergens fra et nået iterationsloft
Iterativ cirkulær referenceberegning er en del af XLSX-motoren i HotXLS Delphi Excel Component, sammen med den trinvise afhængighedsgraf-genberegning, den bygger på, og OOXML- og BIFF8-persistensen, der bærer indstillingen ind i enhver fil, du skriver. For de finansielle modeller og ingeniørmodeller, der er cirkulære med vilje, er det forskellen på en fejlkode og et svar