Iterativ beregning af cirkulære referencer i Delphi

For at beregne bevidste cirkulære referencer i Delphi eksponerer HotXLS iterativ beregning i sin XLSX-motor: sæt TXLSXWorkbook.Iterate til True, og TXLSXWorkbook.Recalculate fører hver detekteret referencecyklus mod et fikspunkt — op til IterateCount gennemløb, eller indtil hver celle ændrer sig mindre end IterateDelta — i stedet for at returnere #REF! og give op

Den skelnen betyder mere, end den enkelte boolean antyder. Den samme motor, der detekterer en referencecyklus og nægter at løbe rundt i den, vil med én egenskab vendt evaluere den cyklus bevidst, indtil den falder til ro. At holde de to adfærd adskilt — hvornår en cyklus er en defekt at rapportere, og hvornår den er en model at løse — er hele emnet for denne artikel

Hvorfor giver en cirkulær reference en fejl som standard?

Som standard behandler HotXLS enhver referencecyklus som en forfatterfejl og rapporterer den i stedet for at beregne den. TXLSXWorkbook.Recalculate bygger 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. De cyklusmedlemmer beholder deres tidligere cachede værdier; hver formel uden for cyklussen evalueres stadig normalt. Mekanikken i den graf, og hvorfor cyklusmedlemmerne springes over frem for at blive gennemløbet, er dækket i ledsageartiklen om inkrementel formelgenberegning og afhængighedsgrafen

Standarden er den sikre, fordi de fleste cyklusser er fejl: en sumrække, der ved et uheld er trukket ind i sit eget SUM-område, en kopiér-indsæt, der forskød en reference over på sig selv. En højlydt fejlkode ved genberegning er præcis det, du ønsker for dem. Men en specifik og vigtig klasse af modeller er cirkulær med vilje. Renters rente-planer, cirkulære omkostnings- eller overheadfordelinger mellem afdelinger og saldodrevne gebyrberegninger beskriver alle en værdi, der legitimt føres tilbage i sine egne input — og Excel beregner dem først, når brugeren sætter flueben ved Filer → Indstillinger → Formler → Aktivér iterativ beregning

Hvordan aktiverer man iterativ beregning i HotXLS?

HotXLS afspejler det Excel-afkrydsningsfelt med tre egenskaber på TXLSXWorkbook: Iterate slår tilstanden til, og når den er True, overdrages en detekteret cyklus til en iterativ løser i stedet for at producere lxErrorRef. Tag det kanoniske renters rente-cellepar, hvor slutsaldoen 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: hovedstol ved start
    Sheet.Cells[2, 2].Value   := 0.05;     // B2: periodens rente
    Sheet.Cells[3, 2].Formula := 'B1+B4';  // B3: saldo = hovedstol + rente
    Sheet.Cells[4, 2].Formula := 'B3*B2';  // B4: rente = saldo * rentesats

    Book.Iterate := True;                   // tilvælg iterativ beregning
    if Book.Recalculate = lxOk then
      // B3 konvergerer mod 1052.63..., B4 mod 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 på to noder. Med Iterate efterladt på sin standardværdi False ville det par komme tilbage som lxErrorRef, og ingen af cellerne ville falde til ro. Med den True seeder Recalculate cyklussen fra de aktuelle cachede værdier og evaluerer dens medlemmer igen gennemløb efter gennemløb, hvor hvert gennemløbs output føres 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 lander på 1052.63 og renten på 52.63 — de samme tal, 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 cyklusmedlemmerne evalueres igen; standardværdien er 100, hvilket matcher Excel. IterateDelta er konvergenstærsklen: efter hvert gennemløb måler løseren den største numeriske ændring på tværs af alle cyklusceller, og når den maksimale ændring falder under IterateDelta — standard 0.001 — afbrydes gennemløbsløkken tidligt. Den betingelse, der opfyldes først, afslutter iterationen

Book.Iterate := True;
Book.IterateCount := 1000;    // hårdt loft: højst 1000 gennemløb af cyklussen
Book.IterateDelta := 0.0001;  // konvergens: stop, når hver celle flytter sig < 0.0001

case Book.Recalculate of
  lxOk:
    // cyklussen konvergerede, ELLER den ramte loftet på 1000 gennemløb og beholdt
    // værdierne fra sidste iteration -- begge stier returnerer lxOk, når Iterate er True
    SaveWorkbook(Book);
  lxErrorRef:
    // kan kun nås med Iterate = False: cyklussen blev rapporteret, ikke løst
    LogWarning('Circular reference with iteration disabled');
end;

Én konsekvens er værd at sige ligeud, for den er funktionens ærlige grænse. Når loftet nås, uden at ændringen er faldet under IterateDelta, rejser SolveCycleIteratively ikke en fejl — den returnerer lxOk og lader cellerne beholde deres værdier fra sidste iteration, præcis som Excel skriver de sidst beregnede tal, når dens egen iterationsgrænse rammes uden konvergens. Så en vellykket returkode fra Recalculate i iterativ tilstand betyder "løseren kørte", ikke "løseren konvergerede". En model, hvis feedbackløkke divergerer eller svinger, vil stille bruge alle IterateCount gennemløb og aflevere tal, der slet ikke er et fikspunkt, og ingen undtagelse markerer forskellen

Hvordan gemmes indstillingen i XLSX- og XLS-filer?

Indstillingerne for iterativ beregning bevares i begge regnearksformater, så en projektmappe åbnet i Excel opfører sig, som din Delphi-kode konfigurerede den. På XLSX-siden udsender writeren OOXML-elementet <calcPr> kun, når Iterate er True, og den udelader enhver attribut, der stadig står på sin standardværdi, for at holde outputtet minimalt: en projektmappe, der bruger standardværdierne, skriver blot <calcPr iterate="1"/>, mens iterateCount kun optræder, når den afviger fra 100, og iterateDelta kun, når den afviger fra 0.001. Ved åbning læser TXLSXWorkbook de samme tre attributter tilbage, så rundturen er symmetrisk

Den ældre BIFF8-motor (.xls), TXLSWorkbook, bærer den tilsvarende tilstand gennem tre separate records under en anden egenskabstriple. EnableIteration mapper til CalcIter-recorden ($0011, [MS-XLS] §2.4.33), MaxIterations til CalcCount-recorden ($000C, [MS-XLS] §2.4.31) og MaxIterationChange til CalcDelta-recorden ($0010, [MS-XLS] §2.4.32). Setterne håndhæver specifikationens intervaller — CalcCount skal ligge i 1..32767, så MaxIterations klemmes fast, og en negativ MaxIterationChange springer tilbage til standardværdien 0.001. Sæt dem på en indlæst .xls-projektmappe, og de tre calc-records skrives trofast ved lagring

var
  Book: TXLSWorkbook;   // BIFF8-motor (.xls)
begin
  Book := TXLSWorkbook.Create;
  try
    Book.Open('model.xls');
    Book.EnableIteration    := True;    // CalcIter-record $0011
    Book.MaxIterations      := 500;     // CalcCount-record $000C (klemt til 1..32767)
    Book.MaxIterationChange := 0.0001;  // CalcDelta-record $0010
    Book.SaveAs('model.xls');           // de tre calc-records overlever rundturen
  finally
    Book.Free;
  end;
end;

Bemærk den bevidste navneopdeling: XLSX-motoren taler Iterate / IterateCount / IterateDelta (OOXML-ordforrådet), mens BIFF8-motoren taler EnableIteration / MaxIterations / MaxIterationChange (på linje med [MS-XLS]-recordnavnene). Begge tripler beskriver de samme tre knapper — en tænd/sluk-kontakt, et iterationsloft og et konvergensdelta — med de samme standardværdier fra, 100 og 0.001

Hvornår er en cirkulær reference en fejl snarere end en model?

At aktivere iteration er ikke en måde at få advarsler om cirkulære referencer til at forsvinde på, og at behandle det sådan er fælden. At slå Iterate til globalt forvandler hver utilsigtet cyklus — dem, standardfejlkoden var der for at fange — til et stille konvergeret eller stille ikke-konvergeret tal. Disciplinen er den omvendte: hold Iterate på False som normaltilstand, så ægte forfatterfejl stadig dukker op som lxErrorRef, og aktivér 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 formelevalueringstraceren det værktøj, der skelner dem fra hinanden: sporer du den mistænkte formel, bliver den referencekæde, der folder tilbage på sig selv, synlig trin for trin, så du kan afgøre, om den koder en reel feedbackløkke eller en vildfaren selvreference. Det hjælper også at huske, at en cyklus-celle kan kalde enhver indbygget funktion på vejen rundt i løkken — den samme beregner, der opløser en ingeniør- eller komplekstalsformel, 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 fikspunkt, før du forlader dig på iteration; hold IterateDelta stram nok til, at "konvergeret" betyder det, din model har brug for, at det betyder; og efter en Recalculate, som du forventer konvergerer, så sanity-tjek et kendt output i stedet for at stole på lxOk alene, da den kode ikke kan skelne konvergens fra et ramt iterationsloft

Iterativ beregning af cirkulære referencer er en del af XLSX-motoren i HotXLS Delphi Excel Component, sammen med den inkrementelle afhængighedsgraf-genberegning, den bygger på, og den OOXML- og BIFF8-persistens, der bærer indstillingen ind i hver fil, du skriver. For de finansielle og tekniske modeller, der er cirkulære med vilje, er det forskellen mellem en fejlkode og et svar