Um bewusste Zirkelbezüge in Delphi zu berechnen, stellt HotXLS in seiner XLSX-Engine eine iterative Berechnung bereit: Setzen Sie TXLSXWorkbook.Iterate auf True, und TXLSXWorkbook.Recalculate durchläuft jeden erkannten Bezugszyklus bis zu einem Fixpunkt — bis zu IterateCount Durchläufe oder bis sich jede Zelle um weniger als IterateDelta ändert —, anstatt einfach #REF! zurückzugeben und abzubrechen
Dieser Unterschied ist bedeutender, als es das bloße Umlegen eines Booleans vermuten lässt. Dieselbe Engine, die einen Bezugszyklus erkennt und dessen Auswertung verweigert, berechnet diesen Zyklus bei gesetzter Eigenschaft ganz bewusst, bis er sich einpendelt. Diese beiden Verhaltensweisen sauber voneinander zu trennen — wann ein Zyklus ein zu meldender Fehler ist und wann ein zu lösendes Modell —, ist das Thema dieses Artikels
Warum erzeugen Zirkelbezüge standardmäßig einen Fehler?
Standardmäßig behandelt HotXLS jeden Bezugszyklus als Fehler beim Erstellen der Formeln und meldet ihn, statt ihn zu berechnen. TXLSXWorkbook.Recalculate baut einen Abhängigkeitsgraphen der Formeln auf, wertet jede Formelzelle in topologischer Reihenfolge aus und gibt in dem Moment lxErrorRef zurück, in dem es einen Zyklus findet — also jene Knoten, die bei der topologischen Sortierung niemals freigegeben werden können. Diese Zyklusmitglieder behalten ihre bisherigen Cache-Werte. Jede Formel außerhalb des Zyklus wird weiterhin normal ausgewertet. Die Mechanismen dieses Graphen und der Grund, warum die Zyklusmitglieder übersprungen statt in einer Schleife durchlaufen werden, werden im Begleitartikel über das inkrementelle Neuberechnen von Formeln und den Abhängigkeitsgraphen behandelt
Der Standardwert ist der sicherere, da die meisten Zyklen Fehler sind: eine Summenzeile, die versehentlich in ihren eigenen SUM-Bereich einbezogen wurde, oder ein Kopieren-und-Einfügen-Vorgang, der einen Bezug auf sich selbst verschoben hat. Ein klarer Fehlercode zur Berechnungszeit ist für diese Fälle genau das Richtige. Aber eine bestimmte und wichtige Klasse von Modellen ist mit voller Absicht zirkulär aufgebaut. Zinseszins-Berechnungen, zirkuläre Kosten- oder Umlagenzuweisungen zwischen Abteilungen und saldenbasierte Gebührenberechnungen beschreiben alle einen Wert, der legitimerweise wieder in seine eigenen Eingabewerte einfließt. Excel berechnet diese erst, wenn der Benutzer unter Datei → Optionen → Formeln die Option „Iterative Berechnung aktivieren“ auswählt
Wie aktiviert man die iterative Berechnung in HotXLS?
HotXLS spiegelt dieses Excel-Häkchen mit drei Eigenschaften an TXLSXWorkbook wider: Iterate schaltet den Modus ein. Wenn dieser Wert True ist, wird ein gefundener Zyklus an einen iterativen Solver übergeben, statt lxErrorRef zu erzeugen. Betrachten Sie das klassische Zinseszins-Zellenpaar, bei dem der Endbestand vom Zins und der Zins wiederum vom Endbestand abhängt:
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 verweist auf B4 und B4 verweist auf B3, sodass der Abhängigkeitsgraph einen Zyklus aus zwei Knoten meldet. Wenn Iterate auf dem Standardwert False belassen würde, kehrte dieses Paar als lxErrorRef zurück und keine der beiden Zellen würde sich einpendeln. Ist der Wert True, setzt Recalculate den Zyklus mit den aktuellen Cache-Werten an und wertet seine Mitglieder Durchlauf für Durchlauf aus, wobei die Ausgabewerte jedes Durchlaufs als Eingabewerte für den nächsten Durchlauf dienen, bis sich die Zahlen nicht mehr ändern. In diesem Fall lautet die geschlossene Formel principal / (1 - rate), sodass sich der Endbestand bei 1052,63 und die Zinsen bei 52,63 einpendeln — genau die Werte, die auch Excel bei aktivierter Iteration ausgibt
Wann stoppt die Iteration?
Zwei unabhängige Abbruchbedingungen begrenzen den Solver. Das Verständnis beider Bedingungen verhindert, dass ein Modell entweder Fehler erzeugt oder endlos weiterrechnet. IterateCount ist die harte Obergrenze dafür, wie oft die Zyklusmitglieder neu ausgewertet werden. Der Standardwert ist 100, genau wie in Excel. IterateDelta ist die Konvergenzschwelle: Nach jedem Durchlauf misst der Solver die größte numerische Änderung über alle Zykluszellen hinweg. Sobald diese maximale Änderung unter IterateDelta fällt (Standardwert 0,001), wird die Schleife vorzeitig abgebrochen. Die Bedingung, die zuerst erfüllt ist, beendet die Iteration
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
// the cycle converged, OR it hit the 1000-pass cap and kept its
// last-iteration values -- both paths return lxOk when Iterate is True
if Book.Recalculate = lxOk then
SaveWorkbook(Book);Eine Konsequenz sollte offen genannt werden, da sie die Grenze dieser Funktion aufzeigt: Wenn die Obergrenze erreicht wird, ohne dass die Änderung unter IterateDelta sinkt, löst SolveCycleIteratively keinen Fehler aus — die Methode gibt lxOk zurück und belässt die Zellen auf den Werten des letzten Durchlaufs. Dies entspricht exakt dem Verhalten von Excel, das die zuletzt berechneten Zahlen ausgibt, wenn das eigene Iterationslimit ohne Konvergenz erreicht wird. Ein erfolgreicher Rückgabecode von Recalculate im iterativen Modus bedeutet also lediglich „der Solver lief“ und nicht „der Solver konvergierte“. Ein Modell, dessen Rückkopplungsschleife divergiert oder oszilliert, verbraucht unbemerkt alle IterateCount-Durchläufe und liefert Werte zurück, die keineswegs einen Fixpunkt darstellen — und es gibt keine Exception, die auf diesen Unterschied hinweist
Wie werden diese Einstellungen in XLSX- und XLS-Dateien gespeichert?
Die Einstellungen für die iterative Berechnung bleiben in beiden Tabellenformaten erhalten, sodass sich eine in Excel geöffnete Arbeitsmappe genauso verhält, wie Sie es in Ihrem Delphi-Code konfiguriert haben. Auf der XLSX-Seite gibt der Writer das OOXML-Element <calcPr> nur aus, wenn Iterate den Wert True hat, und lässt jedes Attribut weg, das noch auf dem Standardwert steht, um die XML-Ausgabe minimal zu halten: Eine Arbeitsmappe mit Standardeinstellungen schreibt lediglich <calcPr iterate="1"/>, während iterateCount nur geschrieben wird, wenn es von 100 abweicht, und iterateDelta nur bei einer Abweichung von 0,001. Beim Öffnen liest TXLSXWorkbook dieselben drei Attribute wieder ein, sodass der Datenaustausch symmetrisch bleibt
Die klassische BIFF8-Engine (.xls), TXLSWorkbook, speichert den entsprechenden Zustand in drei separaten Datensätzen (Records) unter drei anderen Eigenschaften: EnableIteration entspricht dem Record CalcIter ($0011, [MS-XLS] §2.4.33), MaxIterations dem Record CalcCount ($000C, [MS-XLS] §2.4.31) und MaxIterationChange dem Record CalcDelta ($0010, [MS-XLS] §2.4.32). Die Setter erzwingen die im Standard definierten Bereiche — CalcCount muss im Bereich 1..32767 liegen (daher wird MaxIterations begrenzt) und ein negatives MaxIterationChange springt auf den Standardwert 0,001 zurück. Setzen Sie diese Werte in einer geladenen .xls-Arbeitsmappe, werden die drei Datensätze beim Speichern korrekt geschrieben
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;Beachten Sie die bewusste Namensaufteilung: Die XLSX-Engine nutzt Iterate / IterateCount / IterateDelta (das Vokabular von OOXML), während die BIFF8-Engine EnableIteration / MaxIterations / MaxIterationChange verwendet (ausgerichtet an den Record-Namen von [MS-XLS]). Beide Gruppen steuern dieselben drei Regler — einen Ein/Aus-Schalter, ein Iterationslimit und ein Konvergenzdelta — mit den gleichen Standardwerten: Aus, 100 und 0,001
Wann ist ein Zirkelbezug ein Fehler und wann ein Modell?
Das Aktivieren der Iteration ist kein Weg, um Fehlermeldungen über Zirkelbezüge einfach verschwinden zu lassen. Wenn Sie dies so handhaben, tappen Sie in eine Falle: Das globale Einschalten von Iterate verwandelt jeden versehentlichen Zirkelbezug — genau die Fehler, die der standardmäßige Fehlercode aufdecken sollte — in eine unbemerkt konvergierte oder nicht konvergierte Zahl. Die richtige Vorgehensweise ist umgekehrt: Belassen Sie Iterate standardmäßig auf False, damit echte Fehler beim Erstellen der Formeln weiterhin als lxErrorRef gemeldet werden, und aktivieren Sie die Iteration nur in Arbeitsmappen, deren Zirkularität so entworfen und verstanden wurde
Wenn ein Zirkelbezug auftritt und Sie sich nicht sicher sind, um welche Art es sich handelt, ist der Formelauswertungs-Tracer das Werkzeug der Wahl: Verfolgen Sie die verdächtige Formel und die Kette von Bezügen, die auf sich selbst zurückweist. So können Sie entscheiden, ob es sich um eine echte Rückkopplungsschleife oder einen fehlerhaften Selbstbezug handelt. Hilfreich ist auch die Erinnerung, dass eine Zelle im Zyklus jede integrierte Funktion im Verlauf der Schleife aufrufen kann — derselbe Rechner, der eine technische oder komplexe Zahlenformel auflöst, wertet die Zyklusmitglieder bei jedem Durchlauf aus. Ein divergierendes Modell ist daher oft ein Formelproblem innerhalb der Schleife und kein Problem der Iterationseinstellungen
Die praktische Checkliste ist kurz: Stellen Sie sicher, dass die Schleife einen echten Fixpunkt besitzt, bevor Sie sich auf die Iteration verlassen. Halten Sie IterateDelta so eng, wie es Ihr Modell erfordert. Und prüfen Sie nach einem Recalculate, das konvergieren sollte, einen bekannten Ausgabewert manuell nach, statt sich blind auf den Rückgabecode lxOk zu verlassen, da dieser Code nicht zwischen erfolgreicher Konvergenz und dem Erreichen des Iterationslimits unterscheiden kann
Die iterative Zirkelbezugs-Berechnung ist Teil der XLSX-Engine in der Excel-Komponente HotXLS für Delphi, zusammen mit der inkrementellen Neuberechnung des Abhängigkeitsgraphen, auf der sie aufbaut, und der OOXML- und BIFF8-Persistenz, die diese Einstellungen in jede geschriebene Datei überträgt. Für Finanz- und Technikmodelle, die absichtlich zirkulär aufgebaut sind, ist sie der Unterschied zwischen einem Fehlercode und einem Ergebnis