Per calcolare i riferimenti circolari intenzionali in Delphi, HotXLS espone il calcolo iterativo sul suo motore XLSX: impostando TXLSXWorkbook.Iterate su True, il metodo TXLSXWorkbook.Recalculate percorre ciascun ciclo di riferimento rilevato verso un punto fisso — fino a un massimo di IterateCount passaggi, o fino a quando ogni cella cambia per meno di IterateDelta —, anziché restituire #REF! e interrompersi
Questa distinzione è più importante di quanto suggerisca il singolo valore booleano. Lo stesso motore che rileva un ciclo di riferimento e si rifiuta di eseguirvi un ciclo, con una proprietà attivata valuterà deliberatamente quel ciclo fino a quando non si stabilizza. Tenere ben distinti i due comportamenti — quando un ciclo è un difetto da segnalare e quando è un modello da risolvere — è l'argomento centrale di questo articolo
Perché un riferimento circolare genera un errore per impostazione predefinita?
Per impostazione predefinita, HotXLS tratta qualsiasi ciclo di riferimento come un errore di stesura e lo segnala anziché calcolarlo. TXLSXWorkbook.Recalculate costruisce un grafo delle dipendenze delle formule, valuta ogni cella con formula in ordine topologico e restituisce lxErrorRef nel momento in cui trova un ciclo — i nodi che non possono mai essere rilasciati durante l'ordinamento topologico. I membri di quel ciclo mantengono i loro precedenti valori memorizzati nella cache; ogni formula al di fuori del ciclo viene comunque valutata normalmente. I meccanismi di questo grafo, e il motivo per cui i membri del ciclo vengono saltati anziché eseguiti in ciclo, sono trattati nell'articolo di accompagnamento sul ricalcolo incrementale delle formule e grafo delle dipendenze
L'impostazione predefinita è quella sicura perché la maggior parte dei cicli rappresenta un bug: una riga di riepilogo inserita accidentalmente nel proprio intervallo SUM, o un copia-incolla che ha spostato un riferimento su se stesso. Per questi casi, un codice di errore evidente al momento del ricalcolo è esattamente ciò che si desidera. Tuttavia, una classe specifica e importante di modelli è circolare intenzionalmente. Piani di ammortamento con interessi composti, allocazioni circolari di costi o spese generali tra reparti e calcoli di commissioni basati sul saldo descrivono tutti un valore che legittimamente ritorna nei propri input — ed Excel li calcola solo dopo che l'utente seleziona File → Opzioni → Formule → Abilita calcolo iterativo
Come si abilita il calcolo iterativo in HotXLS?
HotXLS rispecchia la casella di controllo di Excel con tre proprietà su TXLSXWorkbook: Iterate attiva la modalità e, quando è impostato su True, un ciclo rilevato viene passato a un risolutore iterativo anziché produrre lxErrorRef. Consideriamo la coppia di celle canonica degli interessi composti, in cui il saldo di chiusura dipende dagli interessi e gli interessi dipendono dal saldo
var
Book: TXLSXWorkbook;
Sheet: TXLSXWorksheet;
begin
Book := TXLSXWorkbook.Create;
try
Sheet := Book.Sheets.Add('Model');
Sheet.Cells[1, 2].Value := 1000; // B1: capitale iniziale
Sheet.Cells[2, 2].Value := 0.05; // B2: tasso del periodo
Sheet.Cells[3, 2].Formula := 'B1+B4'; // B3: saldo = capitale + interessi
Sheet.Cells[4, 2].Formula := 'B3*B2'; // B4: interessi = saldo * tasso
Book.Iterate := True; // abilita il calcolo iterativo
if Book.Recalculate = lxOk then
// B3 converge a 1052.63..., B4 a 52.63...
Report(Sheet.Cells[3, 2].Value);
finally
Book.Free;
end;
end;B3 fa riferimento a B4 e B4 fa riferimento a B3, quindi il grafo delle dipendenze segnala un ciclo a due nodi. Con Iterate impostato al valore predefinito False, questa coppia verrebbe restituita come lxErrorRef e nessuna cella verrebbe calcolata. Con esso impostato su True, Recalculate avvia il ciclo partendo dai valori correnti memorizzati nella cache e valuta i membri passaggio dopo passaggio, reinserendo l'output di ogni passaggio come input per quello successivo, fino a quando i valori non smettono di variare. In questo caso la formula chiusa è capitale / (1 - tasso), quindi il saldo si stabilizza a 1052.63 e gli interessi a 52.63 — le stesse cifre prodotte da Excel con l'iterazione abilitata
Cosa fa arrestare l'iterazione?
Due condizioni di arresto indipendenti vincolano il risolutore, e comprenderle entrambe è ciò che impedisce a un modello di generare errori o di andare in loop infinito. IterateCount è il limite massimo per quante volte i membri del ciclo vengono rivalutati; l'impostazione predefinita è 100, in linea con Excel. IterateDelta è la soglia di convergenza: dopo ogni passaggio, il risolutore misura la variazione numerica maggiore tra tutte le celle del ciclo, e una volta che tale variazione massima scende al di sotto di IterateDelta — valore predefinito 0,001 — il ciclo dei passaggi si interrompe in anticipo. La condizione che si verifica per prima pone fine all'iterazione
Book.Iterate := True;
Book.IterateCount := 1000; // limite massimo: al massimo 1000 passaggi sul ciclo
Book.IterateDelta := 0.0001; // convergenza: si ferma quando ogni cella si sposta di < 0.0001
case Book.Recalculate of
lxOk:
// il ciclo è converso, OPPURE ha raggiunto il limite di 1000 passaggi mantenendo i suoi
// valori dell'ultima iterazione -- entrambi i percorsi restituiscono lxOk quando Iterate è True
SaveWorkbook(Book);
lxErrorRef:
// raggiungibile solo con Iterate = False: il ciclo è stato segnalato, non risolto
LogWarning('Circular reference with iteration disabled');
end;
Una conseguenza vale la pena essere dichiarata chiaramente, poiché rappresenta il limite intrinseco di questa funzionalità. Quando il limite viene raggiunto senza che la variazione scenda al di sotto di IterateDelta, SolveCycleIteratively non genera un errore — restituisce lxOk e lascia le celle con i valori dell'ultima iterazione, esattamente come Excel scrive gli ultimi numeri calcolati quando viene raggiunto il proprio limite di iterazione senza convergenza. Di conseguenza, un codice di ritorno positivo da Recalculate in modalità iterativa significa "il risolutore è stato eseguito", non "il risolutore è converso". Un modello il cui loop di feedback diverge o oscilla consumerà silenziosamente tutti i passaggi di IterateCount restituendo numeri che non rappresentano affatto un punto fisso, e nessuna eccezione segnalerà la differenza
In che modo l'impostazione viene salvata nei file XLSX e XLS?
Le impostazioni del calcolo iterativo rimangono in entrambi i formati di foglio di calcolo, quindi una cartella di lavoro aperta in Excel si comporta esattamente come configurato nel codice Delphi. Sul lato XLSX, il writer emette l'elemento OOXML <calcPr> solo quando Iterate è impostato su True, e omette qualsiasi attributo che sia ancora al suo valore predefinito per mantenere l'output ridotto: una cartella di lavoro che utilizza le impostazioni predefinite scrive semplicemente <calcPr iterate="1"/>, mentre iterateCount compare solo quando differisce da 100 e iterateDelta solo quando differisce da 0.001. All'apertura, TXLSXWorkbook rilegge i tre attributi, rendendo il ciclo simmetrico
Il motore legacy BIFF8 (.xls), TXLSWorkbook, gestisce lo stato equivalente tramite tre record separati sotto una diversa terna di proprietà. EnableIteration mappa sul record CalcIter ($0011, [MS-XLS] §2.4.33), MaxIterations sul record CalcCount ($000C, [MS-XLS] §2.4.31) e MaxIterationChange sul record CalcDelta ($0010, [MS-XLS] §2.4.32). I setter applicano gli intervalli delle specifiche — CalcCount deve essere compreso tra 1..32767, quindi MaxIterations viene bloccato, e un valore negativo di MaxIterationChange ritorna al valore predefinito di 0.001. Configura questi valori su una cartella di lavoro .xls caricata e i tre record calc verranno scritti fedelmente al salvataggio
var
Book: TXLSWorkbook; // motore BIFF8 (.xls)
begin
Book := TXLSWorkbook.Create;
try
Book.Open('model.xls');
Book.EnableIteration := True; // record CalcIter $0011
Book.MaxIterations := 500; // record CalcCount $000C (bloccato su 1..32767)
Book.MaxIterationChange := 0.0001; // record CalcDelta $0010
Book.SaveAs('model.xls'); // i tre record calc completano il ciclo
finally
Book.Free;
end;
end;Nota la suddivisione intenzionale dei nomi: il motore XLSX utilizza Iterate / IterateCount / IterateDelta (il vocabolario OOXML), mentre il motore BIFF8 utilizza EnableIteration / MaxIterations / MaxIterationChange (allineato ai nomi dei record [MS-XLS]). Entrambe le terne descrivono le stesse tre manopole — un interruttore di attivazione/disattivazione, un limite di iterazione e una variazione di convergenza — con gli stessi valori predefiniti di disattivato, 100 e 0.001
Quando un riferimento circolare è un bug anziché un modello?
Abilitare l'iterazione non è un modo per far scomparire gli avvisi di riferimento circolare, e trattarlo in questo modo rappresenta una trappola. Attivare Iterate a livello globale converte ogni ciclo accidentale — quelli che il codice di errore predefinito doveva rilevare — in un numero silente converso o silente non converso. La disciplina corretta è il contrario: mantenere Iterate su False come modalità normale, in modo che i reali errori di stesura si manifestino come lxErrorRef, e abilitare l'iterazione solo sulle cartelle di lavoro in cui la circolarità è voluta e compresa
Quando si manifesta un ciclo e non sei sicuro del tipo, lo strumento di tracciamento della valutazione delle formule è lo strumento ideale per distinguerli: traccia la formula sospetta e la catena di riferimenti che si ripiega su se stessa diventerà visibile passaggio dopo passaggio, in modo da poter decidere se codifica un ciclo di feedback reale o un riferimento a se stesso errato. Aiuta anche ricordare che una cella di ciclo può chiamare qualsiasi funzione integrata lungo il percorso del loop — lo stesso calcolatore che risolve una formula ingegneristica o di numeri complessi valuta i membri del ciclo a ogni passaggio —, quindi un modello divergente è spesso un problema di formula all'interno del loop, non un problema di impostazioni di iterazione
La checklist pratica è breve. Conferma che il loop abbia un reale punto fisso prima di fare affidamento sull'iterazione; mantieni IterateDelta sufficientemente stretto in modo che il termine "converso" esprima ciò di cui il modello ha bisogno; e dopo un ricalcolo Recalculate che ti aspetti converga, verifica un output noto anziché fidarti del solo codice lxOk, poiché tale codice non distingue la convergenza dal raggiungimento del limite di iterazione
Il calcolo iterativo dei riferimenti circolari fa parte del motore XLSX in HotXLS Delphi Excel Component, insieme al ricalcolo incrementale del grafo delle dipendenze su cui si basa e alla persistenza OOXML e BIFF8 che trasporta l'impostazione in ogni file che scrivi. Per i modelli finanziari e ingegneristici circolari intenzionalmente, rappresenta la differenza tra un codice di errore e una risposta