Um beabsichtigte Zirkelbezüge in Delphi zu berechnen, stellt HotXLS auf seiner XLSX-Engine die iterative Berechnung bereit: Setzt man TXLSXWorkbook.Iterate auf True, führt TXLSXWorkbook.Recalculate jeden erkannten Referenzzyklus zu einem Fixpunkt — bis zu IterateCount Durchläufe lang oder bis sich jede Zelle um weniger als IterateDelta ändert — statt #REF! zurückzugeben und aufzugeben
Diese Unterscheidung ist wichtiger, als der einzelne Boolean vermuten lässt. Dieselbe Engine, die einen Referenzzyklus erkennt und sich weigert, darauf zu schleifen, wertet diesen Zyklus mit einer umgelegten Eigenschaft bewusst aus, bis er sich einpendelt. Die beiden Verhaltensweisen auseinanderzuhalten — wann ein Zyklus ein zu meldender Defekt ist und wann ein zu lösendes Modell — ist das ganze Thema dieses Artikels
Warum führt ein Zirkelbezug standardmäßig zu einem Fehler?
Standardmäßig behandelt HotXLS jeden Referenzzyklus als Autorenfehler und meldet ihn, statt ihn zu berechnen. TXLSXWorkbook.Recalculate baut einen Formelabhängigkeitsgraphen auf, wertet jede Formelzelle in topologischer Reihenfolge aus und gibt lxErrorRef zurück, sobald es einen Zyklus findet — die Knoten, die während der topologischen Sortierung nie freigegeben werden können. Diese Zyklusmitglieder behalten ihre vorherigen zwischengespeicherten Werte; jede Formel außerhalb des Zyklus wird weiterhin normal ausgewertet. Die Mechanik dieses Graphen, und warum die Zyklusmitglieder übersprungen statt durchlaufen werden, wird im Begleitartikel zur inkrementellen Formelneuberechnung und dem Abhängigkeitsgraphen behandelt
Der Standard ist der sichere, weil die meisten Zyklen Fehler sind: eine Summenzeile, die versehentlich in ihren eigenen SUM-Bereich gezogen wurde, ein Kopieren und Einfügen, das eine Referenz auf sich selbst verschoben hat. Ein lauter Fehlercode zur Neuberechnungszeit ist genau das, was man für solche Fälle will. Aber eine bestimmte und wichtige Klasse von Modellen ist absichtlich zirkulär. Zinseszins-Pläne, zirkuläre Kosten- oder Gemeinkostenumlagen zwischen Abteilungen und saldoabhängige Gebührenberechnungen beschreiben alle einen Wert, der berechtigterweise in seine eigenen Eingaben zurückfließt — und Excel berechnet sie erst, wenn der Nutzer unter Datei → Optionen → Formeln → Iterative Berechnung aktivieren das Häkchen setzt
Wie aktiviert man die iterative Berechnung in HotXLS?
HotXLS spiegelt dieses Excel-Kontrollkästchen mit drei Eigenschaften auf TXLSXWorkbook: Iterate schaltet den Modus ein, und wenn es True ist, wird ein erkannter Zyklus an einen iterativen Löser übergeben, statt lxErrorRef zu erzeugen. Betrachten wir das klassische Zinseszins-Zellenpaar, bei dem der Endsaldo vom Zins abhängt und der Zins vom Saldo
var
Book: TXLSXWorkbook;
Sheet: TXLSXWorksheet;
begin
Book := TXLSXWorkbook.Create;
try
Sheet := Book.Sheets.Add('Model');
Sheet.Cells[1, 2].Value := 1000; // B1: Anfangskapital
Sheet.Cells[2, 2].Value := 0.05; // B2: Periodenzinssatz
Sheet.Cells[3, 2].Formula := 'B1+B4'; // B3: Saldo = Kapital + Zins
Sheet.Cells[4, 2].Formula := 'B3*B2'; // B4: Zins = Saldo * Satz
Book.Iterate := True; // iterative Berechnung einschalten
if Book.Recalculate = lxOk then
// B3 konvergiert gegen 1052.63..., B4 gegen 52.63...
Report(Sheet.Cells[3, 2].Value);
finally
Book.Free;
end;
end;B3 referenziert B4 und B4 referenziert B3, sodass der Abhängigkeitsgraph einen Zyklus aus zwei Knoten meldet. Bleibt Iterate auf seinem Standard False, käme dieses Paar als lxErrorRef zurück, und keine der beiden Zellen würde sich einpendeln. Ist es True, initialisiert Recalculate den Zyklus aus den aktuellen zwischengespeicherten Werten und wertet seine Mitglieder Durchlauf für Durchlauf neu aus, wobei die Ausgaben jedes Durchlaufs als Eingaben des nächsten zurückgespeist werden, bis sich die Zahlen nicht mehr bewegen. Hier lautet die geschlossene Form principal / (1 - rate), sodass sich der Saldo bei 1052.63 und der Zins bei 52.63 einpendelt — dieselben Zahlen, die Excel mit aktivierter Iteration liefert
Was lässt die Iteration anhalten?
Zwei unabhängige Abbruchbedingungen begrenzen den Löser, und beide zu verstehen ist es, was ein Modell davor bewahrt, entweder einen Fehler zu liefern oder endlos zu kreisen. IterateCount ist die harte Obergrenze dafür, wie oft die Zyklusmitglieder neu ausgewertet werden; der Standard ist 100, passend zu Excel. IterateDelta ist die Konvergenzschwelle: Nach jedem Durchlauf misst der Löser die größte numerische Änderung über alle Zykluszellen, und sobald diese maximale Änderung unter IterateDelta fällt — Standard 0.001 — bricht die Durchlaufschleife vorzeitig ab. Die zuerst erfüllte Bedingung beendet die Iteration
Book.Iterate := True;
Book.IterateCount := 1000; // harte Obergrenze: höchstens 1000 Durchläufe über den Zyklus
Book.IterateDelta := 0.0001; // Konvergenz: anhalten, sobald sich jede Zelle um < 0.0001 bewegt
case Book.Recalculate of
lxOk:
// der Zyklus ist konvergiert ODER hat die Obergrenze von 1000 Durchläufen erreicht und
// seine Werte der letzten Iteration behalten -- beide Pfade geben bei Iterate True lxOk zurück
SaveWorkbook(Book);
lxErrorRef:
// nur mit Iterate = False erreichbar: der Zyklus wurde gemeldet, nicht gelöst
LogWarning('Circular reference with iteration disabled');
end;
Eine Konsequenz sollte klar ausgesprochen werden, weil sie die ehrliche Grenze der Funktion ist. Wird die Obergrenze erreicht, ohne dass die Änderung unter IterateDelta fällt, löst SolveCycleIteratively keinen Fehler aus — es gibt lxOk zurück und lässt die Zellen mit ihren Werten aus der letzten Iteration stehen, genau wie Excel die zuletzt berechneten Zahlen schreibt, wenn seine eigene Iterationsgrenze ohne Konvergenz erreicht wird. Ein erfolgreicher Rückgabecode von Recalculate im iterativen Modus bedeutet also „der Löser ist gelaufen", nicht „der Löser ist konvergiert". Ein Modell, dessen Rückkopplungsschleife divergiert oder oszilliert, verbraucht stillschweigend alle IterateCount Durchläufe und liefert Zahlen zurück, die überhaupt kein Fixpunkt sind, und keine Exception markiert den Unterschied
Wie wird die Einstellung in XLSX- und XLS-Dateien gespeichert?
Die Einstellungen zur iterativen Berechnung werden in beiden Tabellenformaten persistiert, sodass sich eine in Excel geöffnete Arbeitsmappe so verhält, wie Ihr Delphi-Code sie konfiguriert hat. Auf der XLSX-Seite gibt der Writer das OOXML-Element <calcPr> nur aus, wenn Iterate True ist, und lässt jedes Attribut weg, das noch auf seinem Standard steht, um die Ausgabe minimal zu halten: Eine Arbeitsmappe mit den Standardwerten schreibt nur <calcPr iterate="1"/>, während iterateCount nur erscheint, wenn es von 100 abweicht, und iterateDelta nur, wenn es von 0.001 abweicht. Beim Öffnen liest TXLSXWorkbook dieselben drei Attribute zurück, sodass der Round-Trip symmetrisch ist
Die ältere BIFF8-Engine (.xls), TXLSWorkbook, führt den entsprechenden Zustand über drei separate Records unter einem anderen Eigenschaftstripel. EnableIteration bildet auf den CalcIter-Record ab ($0011, [MS-XLS] §2.4.33), MaxIterations auf den CalcCount-Record ($000C, [MS-XLS] §2.4.31) und MaxIterationChange auf den CalcDelta-Record ($0010, [MS-XLS] §2.4.32). Die Setter erzwingen die Bereiche der Spezifikation — CalcCount muss in 1..32767 liegen, daher wird MaxIterations begrenzt, und ein negatives MaxIterationChange springt auf den Standard 0.001 zurück. Setzt man sie auf einer geladenen .xls-Arbeitsmappe, werden die drei Calc-Records beim Speichern getreu geschrieben
var
Book: TXLSWorkbook; // BIFF8-Engine (.xls)
begin
Book := TXLSWorkbook.Create;
try
Book.Open('model.xls');
Book.EnableIteration := True; // CalcIter-Record $0011
Book.MaxIterations := 500; // CalcCount-Record $000C (begrenzt auf 1..32767)
Book.MaxIterationChange := 0.0001; // CalcDelta-Record $0010
Book.SaveAs('model.xls'); // die drei Calc-Records überstehen den Round-Trip
finally
Book.Free;
end;
end;Man beachte die bewusste Namensaufteilung: Die XLSX-Engine spricht Iterate / IterateCount / IterateDelta (das OOXML-Vokabular), während die BIFF8-Engine EnableIteration / MaxIterations / MaxIterationChange spricht (angelehnt an die [MS-XLS]-Record-Namen). Beide Tripel beschreiben dieselben drei Regler — einen Ein-/Ausschalter, eine Iterationsobergrenze und ein Konvergenz-Delta — mit denselben Standardwerten aus, 100 und 0.001
Wann ist ein Zirkelbezug ein Fehler und kein Modell?
Die Iteration zu aktivieren ist kein Weg, Zirkelbezugswarnungen verschwinden zu lassen, und sie so zu behandeln ist die Falle. Wer Iterate global einschaltet, verwandelt jeden versehentlichen Zyklus — genau die, die der Standard-Fehlercode abfangen sollte — in eine stillschweigend konvergierte oder stillschweigend nicht konvergierte Zahl. Die Disziplin ist die umgekehrte: Iterate als Normalmodus auf False lassen, damit echte Autorenfehler weiterhin als lxErrorRef auftauchen, und die Iteration nur für Arbeitsmappen aktivieren, deren Zirkularität beabsichtigt und verstanden ist
Taucht ein Zyklus auf und man ist sich nicht sicher, um welche Art es sich handelt, ist der Formelauswertungs-Tracer das Werkzeug, das sie auseinanderhält: Man verfolgt die verdächtige Formel, und die Referenzkette, die auf sich selbst zurückführt, wird Schritt für Schritt sichtbar, sodass man entscheiden kann, ob sie eine echte Rückkopplungsschleife kodiert oder eine verirrte Selbstreferenz. Es hilft auch, sich daran zu erinnern, dass eine Zykluszelle auf dem Weg durch die Schleife jede eingebaute Funktion aufrufen kann — derselbe Rechner, der eine Engineering- oder Formel mit komplexen Zahlen auflöst, wertet die Zyklusmitglieder bei jedem Durchlauf aus — sodass ein divergierendes Modell oft ein Formelproblem innerhalb der Schleife ist und kein Problem der Iterationseinstellungen
Die praktische Checkliste ist kurz. Man bestätigt, dass die Schleife einen echten Fixpunkt hat, bevor man sich auf die Iteration verlässt; man hält IterateDelta eng genug, dass „konvergiert" das bedeutet, was das Modell braucht; und nach einem Recalculate, von dem man Konvergenz erwartet, prüft man eine bekannte Ausgabe auf Plausibilität, statt allein dem lxOk zu vertrauen, da dieser Code Konvergenz nicht von einer erreichten Iterationsobergrenze unterscheiden kann
Die iterative Berechnung von Zirkelbezügen ist Teil der XLSX-Engine in der HotXLS Delphi Excel Component, neben der inkrementellen Abhängigkeitsgraph-Neuberechnung, auf der sie aufbaut, und der OOXML- und BIFF8-Persistenz, die die Einstellung in jede Datei trägt, die Sie schreiben. Für die Finanz- und Engineering-Modelle, die absichtlich zirkulär sind, ist sie der Unterschied zwischen einem Fehlercode und einer Antwort