For å beregne tilsiktede sirkelreferanser i Delphi, tilbyr HotXLS iterativ beregning på sin XLSX-motor: sett TXLSXWorkbook.Iterate til True, og TXLSXWorkbook.Recalculate går gjennom hver oppdagede referansesyklus mot et fast punkt — opptil IterateCount iterasjoner, eller til hver celle endrer seg med mindre enn IterateDelta — i stedet for å returnere #REF! og gi opp
Dette skillet betyr mer enn den enkle boolske verdien antyder. Den samme motoren som oppdager en referansesyklus og nekter å gå i løkke på den, vil, med én egenskap endret, evaluere den syklusen bevisst til den stabiliserer seg. Å holde styr på de to oppførslene — når en syklus er en feil som skal rapporteres, og når den er en modell som skal løses — er hele temaet for denne artikkelen
Hvorfor gir en sirkelreferanse feil som standard?
Som standard behandler HotXLS enhver referansesyklus som en opprettelsesfeil (authoring mistake) og rapporterer den i stedet for å beregne den. TXLSXWorkbook.Recalculate bygger en avhengighetsgraf for formler, evaluerer hver formelcelle i topologisk rekkefølge, og returnerer lxErrorRef i det øyeblikket den finner en syklus — nodene som aldri kan frigjøres under den topologiske sorteringen. Disse syklusmedlemmene beholder sine tidligere hurtigbufrede verdier; alle formler utenfor syklusen evalueres fortsatt som normalt. Mekanismene for denne grafen, og hvorfor syklusmedlemmene hoppes over i stedet for å gå i løkke, dekkes i den tilhørende artikkelen om inkrementell formelnyberegning og avhengighetsgrafen
Standardverdien er den sikre, fordi de fleste sykluser er feil: en oppsummeringsrad som ved et uhell trekkes inn i sitt eget SUM-område, eller en kopier-og-lim inn-operasjon som flyttet en referanse inn på seg selv. En tydelig feilkode ved ny beregning er akkurat det du ønsker for slike tilfeller. But en spesifikk og viktig klasse av modeller er sirkulære med vilje. Rente-på-rente-tabeller, sirkulære kostnads- eller felleskostnadsfordelinger mellom avdelinger, og saldobaserte gebyrberegninger beskriver alle en verdi som legitimt mates tilbake til sine egne inndata — og Excel beregner dem bare når brukeren huker av for Fil → Alternativer → Formler → Aktiver iterativ beregning
Hvordan aktiverer du iterativ beregning i HotXLS?
HotXLS speiler den avhukingsboksen i Excel med tre egenskaper på TXLSXWorkbook: Iterate slår på modusen, og når den er satt til True, overleveres en oppdaget syklus til en iterativ løser i stedet for å produsere lxErrorRef. Tenk på det klassiske (canonical) rente-på-rente-celleparet, der utgående saldo avhenger av renten, og renten avhenger av 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;Hva får iterasjonen til å stoppe?
To uavhengige stoppvilkår begrenser løseren, og å forstå begge er det som forhindrer at en modell enten feiler eller går i uendelig løkke (spinning). IterateCount is den absolutte grensen for hvor mange ganger syklusmedlemmene re-evalueres; den har en standardverdi på 100, tilsvarende Excel. IterateDelta er konvergens-terskelen: etter hver runde måler løseren den største numeriske endringen på tvers av alle syklusceller, og når den maksimale endringen faller under IterateDelta — standardverdi 0,001 — avbrytes rundeløkken tidlig. Det vilkåret som oppfylles først, avslutter iterasjonen
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;
Én konsekvens er verdt å si tydelig, fordi det er den ærlige grensen for denne funksjonen. Når grensen nås uten at endringen faller under IterateDelta, vil ikke SolveCycleIteratively utløse en feil — den returnerer lxOk og lar cellene beholde verdiene fra den siste iterasjonen, akkurat slik Excel skriver de sist beregnede tallene når dens egen iterasjonsgrense nås uten konvergens. Så en vellykket returkode fra Recalculate under iterativ modus betyr at "løseren kjørte", ikke at "løseren konvergerte". En modell der tilbakemeldingssløyfen divergerer eller svinger (oscillates) vil i det stille forbruke alle IterateCount-iterasjonene og gi tilbake tall som ikke er et fast punkt i det hele tatt, og ingen unntak markerer forskjellen
Hvordan lagres innstillingen i XLSX- og XLS-filer?
Innstillingene for iterativ beregning lagres i begge regnearkformatene, slik at en arbeidsbok åpnet i Excel oppfører seg slik Delphi-koden din konfigurerte den. På XLSX-siden skriver skrivemodulen ut OOXML-elementet <calcPr> kun når Iterate er satt til True, og den utelater alle attributter som fortsatt har sine standardverdier for å holde utdataene minimale: en arbeidsbok som bruker standardverdiene skriver bare <calcPr iterate="1"/>, mens iterateCount vises kun når det avviker fra 100 og iterateDelta bare når det avviker fra 0,001. Ved åpning leser TXLSXWorkbook de samme tre attributtene tilbake, slik at overføringen frem og tilbake er symmetrisk
Den eldre BIFF8-motoren (.xls), TXLSWorkbook, bærer den tilsvarende tilstanden gjennom tre separate oppføringer (records) under en annen trippel av egenskaper. EnableIteration kartlegges til CalcIter-oppføringen ($0011, [MS-XLS] §2.4.33), MaxIterations til CalcCount-oppføringen ($000C, [MS-XLS] §2.4.31), og MaxIterationChange til CalcDelta-oppføringen ($0010, [MS-XLS] §2.4.32). Setterne håndhever spesifikasjonens områder — CalcCount må ligge i 1..32767, så MaxIterations blir avskåret (clamped), og en negativ MaxIterationChange spretter tilbake til standardverdien 0,001. Sett dem på en innlastet .xls-arbeidsbok, og de tre beregningsoppføringene skrives trofast ved lagring
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;Merk den delvise navnedelingen: XLSX-motoren bruker Iterate / IterateCount / IterateDelta (OOXML-vokabularet), mens BIFF8-motoren bruker EnableIteration / MaxIterations / MaxIterationChange (tilpasset record-navnene i [MS-XLS]). Begge triplene beskriver de samme tre innstillingene — en på/av-bryter, en iterasjonsgrense, og en konvergens-terskel — med de samme standardverdiene: av, 100, og 0,001
Når er en sirkelreferanse en feil fremfor en modell?
Å aktivere iterasjon er ikke en måte å få sirkelreferanseadvarsler til å forsvinne på, og å behandle det slik er en felle. Å slå på Iterate globalt konverterer hver utilsiktet syklus — de som den standard feilkoden var der for å fange opp — til et lydløst konvergert eller lydløst ikke-konvergert tall. Disiplinen er den motsatte: hold Iterate som False i normal modus, slik at ekte opprettelsesfeil fortsatt kommer til overflaten som lxErrorRef, og aktiver iterasjon kun på arbeidsbøker der sirkulariteten er designet med vilje og forstått
Når en syklus dukker opp og du ikke er sikker på hvilken type det es, er formelevaluering-sporeren verktøyet som skiller dem fra hverandre: spor den mistenkelige formelen, og referansekjeden som brettes tilbake på seg selv blir synlig trinn for trinn, slik at du kan avgjøre om den koder en ekte tilbakemeldingssløyfe (feedback loop) eller en tilfeldig selvreferanse. Det hjelper også å huske at en sykluscelle kan kalle en hvilken som helst innebygd funksjon på veien rundt sløyfen — den samme kalkulatoren som løser en ingeniør- eller komplekse-tall-formel evaluerer syklusmedlemmene ved hver runde — så en divergerende modell er ofte et formelproblem inne i sløyfen, ikke et problem med iterasjonsinnstillingene
Den praktiske sjekklisten er kort. Bekreft at sløyfen har et reelt fast punkt før du stoler på iterasjon; hold IterateDelta stram nok til at "konvergert" betyr det modellen din trenger at det skal bety; og etter en Recalculate som du forventer skal konvergere, bør du foreta en rimelighetskontroll (sanity-check) av en kjent utdata i stedet for å stole på lxOk alene, siden den koden ikke kan skille konvergens fra en nådd iterasjonsgrense
Iterativ sirkelreferanseberegning er en del av XLSX-motoren i HotXLS Delphi Excel Component, sammen med den inkrementelle avhengighetsgraf-nyberegningen den bygger på, og OOXML- og BIFF8-lagringen som overfører innstillingen til hver fil du skriver. For de økonomiske og ingeniørmessige modellene som er sirkulære med vilje, er det forskjellen mellom en feilkode og et svar