Om bewuste kringverwijzingen te berekenen in Delphi, biedt HotXLS iteratieve berekening op zijn XLSX-engine: stel TXLSXWorkbook.Iterate in op True en TXLSXWorkbook.Recalculate doorloopt elke gedetecteerde verwijzingscyclus naar een vast punt — tot maximaal IterateCount passages, of totdat elke cel met minder dan IterateDelta verandert — in plaats van #REF! te retourneren en op te geven
Dat onderscheid is belangrijker dan de enkele boolean suggereert. Dezelfde engine die een verwijzingscyclus gedetecteert en weigert erin te lussen, zal met één omgezette eigenschap die cyclus doelbewust evalueren totdat deze zich stabiliseert. Het goed uit elkaar houden van deze twee gedragingen — wanneer een cyclus een te rapporteren defect is en wanneer het een op te lossen model is — is het hele onderwerp van dit artikel
Waarom geeft een kringverwijzing standaard een foutmelding?
Standaard behandelt HotXLS elke verwijzingscyclus als een schrijffout en rapporteert deze in plaats van deze te berekenen. TXLSXWorkbook.Recalculate bouwt een afhankelijkheidsgrafiek van formules op, evalueert elke formulecel in topologische volgorde, en retourneert lxErrorRef op het moment dat het een cyclus vindt — de knooppunten die nooit kunnen worden vrijgegeven tijdens de topologische sortering. Die cyclusleden behouden hun eerdere gecachte waarden; elke formule buiten de cyclus wordt nog steeds normaal geëvalueerd. De mechanica van die grafiek, en waarom de cyclusleden worden overgeslagen in plaats van doorgelust, worden behandeld in het begeleidende artikel over incrementele formuleherberekening en de afhankelijkheidsgrafiek
De standaardinstelling is de veilige, omdat de meeste cycli fouten zijn: een samenvattingsrij die per ongeluk in het eigen SUM-bereik is getrokken, een kopieer-plakactie die een verwijzing naar zichzelf heeft verschoven. Een duidelijke foutcode tijdens de herberekening is precies wat u in die gevallen wilt. Maar een specifieke en belangrijk klasse modellen is bewust circulair. Rente-op-renteschema's, circulaire kosten- of overheadtoewijzingen tussen afdelingen en saldogebaseerde vergoedingenberekeningen beschrijven allemaal een waarde die legitiem terugkoppelt naar de eigen invoer — en Excel berekent deze pas nadat de gebruiker Bestand → Opties → Formules → Iteratieve berekening inschakelen aanvinkt
Hoe schakelt u iteratieve berekening in in HotXLS?
HotXLS weerspiegelt dat Excel-selectievakje met drie eigenschappen op TXLSXWorkbook: Iterate schakelt de modus in, en wanneer deze True is, wordt een gedetecteerde cyclus overgedragen aan een iteratieve oplosser (solver) in plaats van lxErrorRef te produceren. Neem het canonieke rente-op-rente celpaar, waarbij het eindsaldo afhangt van de rente en de rente afhangt van het saldo
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 verwijst naar B4 en B4 verwijst naar B3, dus de afhankelijkheidsgrafiek rapporteert een twee-knooppuntencyclus. Met Iterate op zijn standaardwaarde van False gelaten, zou dat paar terugkomen als lxErrorRef en zou geen van beide cellen zich stabiliseren. Met True parseert Recalculate de cyclus op basis van de huidige gecachte waarden en evalueert het de leden ervan passage na passage, waarbij de uitvoer van elke passage als invoer voor de volgende passage wordt gebruikt, totdat de getallen stoppen met veranderen. Hier is de gesloten vorm principal / (1 - rate), dus het saldo stabiliseert op 1052,63 en de rente op 52,63 — dezelfde getallen die Excel produceert met iteratie ingeschakeld
Wat zorgt ervoor dat de iteratie stopt?
Twee onafhankelijke stopcondities begrenzen de oplosser, en het begrijpen van beide is wat voorkomt dat een model fouten oploopt of blijft spinnen. IterateCount is de harde limiet voor het aantal keren dat de cyclusleden opnieuw worden geëvalueerd; dit staat standaard op 100, net als in Excel. IterateDelta is de convergentiedrempel: na elke passage meet de oplosser de grootste numerieke verandering over alle cycluscellen, en zodra die maximale verandering onder IterateDelta — standaard 0,001 — zakt, breekt de passagelus voortijdig af. Welke voorwaarde het eerst wordt bereikt, beëindigt de iteratie
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;
Eén consequentie moet duidelijk worden vermeld, want dit is de eerlijke grens van de functie. Wanneer de limiet wordt bereikt zonder dat de verandering onder IterateDelta zakt, werpt SolveCycleIteratively geen fout op — het retourneert lxOk en laat de cellen de waarden van hun laatste iteratie behouden, precies zoals Excel de laatst berekende getallen schrijft wanneer zijn eigen iteratielimiet zonder convergentie wordt bereikt. Dus een succesvolle retourcode van Recalculate onder iteratieve modus betekent "de oplosser heeft gedraaid", niet "de oplosser is geconvergeerd". Een model waarvan de terugkoppelingslus divergeert of oscilleert, zal stilletjes alle IterateCount-passages verbruiken en getallen teruggeven die helemaal geen vast punt zijn, en geen uitzondering markeert het verschil
Hoe wordt de instelling opgeslagen in XLSX- en XLS-bestanden?
De instellingen voor iteratieve berekening blijven behouden in beide spreadsheetformaten, zodat een werkmap die in Excel wordt geopend zich gedraagt zoals uw Delphi-code deze heeft geconfigureerd. Aan de XLSX-kant schrijft de schrijver het OOXML-element <calcPr> alleen wanneer Iterate True is, en laat het alle attributen weg die nog op hun standaardwaarde staan om de uitvoer minimaal te houden: een werkmap die de standaardwaarden gebruikt schrijft alleen <calcPr iterate="1"/>, terwijl iterateCount alleen verschijnt wanneer dit afwijkt van 100 en iterateDelta alleen wanneer dit afwijkt van 0,001. Bij het openen leest TXLSXWorkbook dezelfde drie attributen terug, dus de round-trip is symmetrisch
De legacy BIFF8 (.xls) engine, TXLSWorkbook, draagt de equivalente status via drie afzonderlijke records onder een ander eigenschappentrio. EnableIteration verwijst naar het CalcIter-record ($0011, [MS-XLS] §2.4.33), MaxIterations naar het CalcCount-record ($000C, [MS-XLS] §2.4.31), en MaxIterationChange naar het CalcDelta-record ($0010, [MS-XLS] §2.4.32). De setters dwingen de specificatiebereiken af — CalcCount moet in 1..32767 liggen, dus MaxIterations wordt ingeperkt, en een negatieve MaxIterationChange springt terug naar de standaardwaarde van 0,001. Stel ze in op een geladen .xls-werkmap en de drie calc-records worden getrouw weggeschreven bij het opslaan
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;Let op de bewuste splitsing in naamgeving: de XLSX-engine spreekt van Iterate / IterateCount / IterateDelta (het OOXML-vocabulaire), terwijl de BIFF8-engine spreekt van EnableIteration / MaxIterations / MaxIterationChange (afgestemd op de [MS-XLS] recordnamen). Beide trio's beschrijven dezelfde drie knoppen — een aan/uit-schakelaar, een iteratielimiet en een convergentiedelta — met dezelfde standaardwaarden van uit, 100 en 0,001
Wanneer is een kringverwijzing een bug in plaats van een model?
Het inschakelen van iteratie is geen manier om waarschuwingen voor kringverwijzingen te laten verdwijnen, en het op die manier behandelen is de valkuil. Door Iterate wereldwijd in te schakelen, verandert elke onbedoelde cyclus — die de standaardfoutcode juist moest opvangen — in een stil geconvergeerd of stil niet-geconvergeerd getal. De discipline is het omgekeerde: houd Iterate op False als de normale modus, zodat echte schrijffouten nog steeds naar boven komen als lxErrorRef, en schakel iteratie alleen in bij werkmappen waarvan de circulariteit zo is ontworpen en begrepen
Wanneer er een cyclus verschijnt en u weet niet zeker om welke soort het gaat, is de tracer voor formule-evaluatie het hulpmiddel dat ze uit elkaar houdt: traceer de verdachte formule en de verwijzingsketen die in zichzelf terugvouwt wordt stap voor stap zichtbaar, zodat u kunt beslissen of deze een echte feedbacklus of een verdwaalde zelfverwijzing codeert. Het helpt ook om te onthouden dat een cel in de cyclus elke ingebouwde functie kan aanroepen op zijn weg door de lus — dezelfde rekenmachine die een technische of complexe-getallenformule oplost, evalueert de cyclusleden bij elke passage — dus een divergerend model is vaak een formuleprobleem binnen de lus, niet een probleem met de iteratie-instellingen
De praktische checklist is kort. Controleer of de lus een echt vast punt heeft voordat u op iteratie vertrouwt; houd IterateDelta strak genoeg zodat "geconvergeerd" betekent wat uw model nodig heeft; en controleer na een Recalculate waarvan u verwacht dat deze converteert, handmatig een bekende uitvoer in plaats van alleen op lxOk te vertrouwen, aangezien die code geen onderscheid kan maken tussen convergentie en een bereikte iteratielimiet
Iteratieve berekening van kringverwijzingen maakt deel uit van de XLSX-engine in de HotXLS Delphi Excel-component, naast de incrementele herberekening op basis van de afhankelijkheidsgrafiek waarop deze bouwt, en de OOXML- en BIFF8-persistentie die de instelling meeneemt in elk bestand dat u schrijft. Voor de financiële en technische modellen die bewust circulair zijn, is het het verschil tussen een foutcode en een antwoord