HotXLS, la libreria Excel nativa per Delphi e C++Builder, esegue il ricalcolo incrementale delle formule tramite TXLSXWorkbook.Recalculate. La prima chiamata crea un grafo di dipendenza delle formule e valuta ogni cella contenente formule; ogni chiamata successiva rivaluta solo le celle interessate dalle scritture di valori effettuate dopo l'ultimo passaggio, in ordine topologico, in un'unica scansione il cui costo è proporzionale al numero di celle dirty anziché alle dimensioni della cartella di lavoro
Questa singola scelta di progettazione fa la differenza tra un modello finanziario che risponde in pochi millisecondi alla modifica di un'ipotesi e uno che si blocca per secondi. Se si generano report in cui poche celle di input alimentano migliaia di formule a valle, il resto di questo articolo spiega il funzionamento del grafo, quali funzioni non beneficiano dell'incremento e come vengono segnalati i riferimenti circolari per evitare loop infiniti
Perché la modifica di una singola cella ricalcola centomila formule?
Un motore di formule elementare non ha memoria di chi dipenda da chi, quindi la sua unica mossa sicura dopo qualsiasi modifica è valutare di nuovo tutto. Peggio ancora, la classica strategia ricorsiva — in cui la formula A fa riferimento alla formula B e B viene valutata sul posto — rivaluta incondizionatamente le celle interessate, ignorando qualsiasi valore memorizzato nella cache. Una catena di n formule, ciascuna delle quali fa riferimento alla precedente, ha un costo di O(n²) valutazioni per passaggio completo, e un riferimento circolare genera una ricorsione infinita. Qualsiasi sviluppatore di fogli di calcolo che abbia inserito un modello a cascata in un valutatore ricorsivo ha riscontrato entrambe le modalità di errore
Excel stesso ha risolto questo problema decenni fa con la sua catena di calcolo: un ordinamento delle celle con formule configurato in modo che una modifica contrassegni come dirty un piccolo set di celle e il motore analizzi solo la coda interessata della catena. HotXLS applica la stessa idea sotto forma di un grafo di dipendenza esplicito, costruito una volta a partire dagli alberi delle formule compilati e riutilizzato nei passaggi di ricalcolo. L'obiettivo non è l'ingegnosità, ma far sì che il costo del ricalcolo rifletta l'entità della modifica, non la dimensione della cartella di lavoro
In che modo il grafo di dipendenza trasforma una modifica in un unico passaggio
Il grafo di dipendenza di HotXLS associa un nodo a ciascuna cella con formula, con archi che collegano i precedenti ai dipendenti. Quando il codice scrive un valore in una cella, la cartella di lavoro registra la cella como dirty; all'avvio di Recalculate, lo stato dirty si propaga lungo gli archi a ogni formula a valle, e il sottografo dirty viene valutato esattamente una volta in ordine topologico utilizzando l'algoritmo di Kahn. Poiché una formula non viene mai esaminata prima dei suoi precedenti, ciascun nodo richiede una sola valutazione — il che rende il passaggio O(dirty)
L'ordinamento topologico risolve anche il problema della ricorsione alla radice. Durante un passaggio di ricalcolo, il motore passa a una modalità dedicata in cui qualsiasi riferimento a un'altra cella con formula legge direttamente il valore memorizzato nella cache di quella cella anziché rivalutarlo — l'ordinamento garantisce che la cache sia già aggiornata. Lo stesso meccanismo impedisce che un ciclo di riferimenti avvii una ricorsione illimitata: niente all'interno del passaggio richiama nuovamente il valutatore per una cella adiacente
var
Book: TXLSXWorkbook;
Inputs, Model: TXLSXWorksheet;
begin
Book := TXLSXWorkbook.Create;
try
Inputs := Book.Sheets.Add('Inputs');
Model := Book.Sheets.Add('Model');
Inputs.Cells[2, 2].Value := 0.05; // growth assumption
Model.Cells[2, 2].Formula := 'Inputs!B2*1000'; // XLSX formulas take no leading '='
Model.Cells[3, 2].Formula := 'B2*(1+Inputs!B2)';
// ... thousands more rows cascading off the same assumption ...
Book.Recalculate; // first call: builds the graph, full evaluation
Inputs.Cells[2, 2].Value := 0.07; // one edit marks one cell dirty
Book.Recalculate; // second call: only the downstream chain runs
finally
Book.Free;
end;
end;Ogni risultato viene memorizzato nella cache Value della cella, quindi al termine di Recalculate si leggono gli output nello stesso modo in cui si legge qualsiasi altra cella. In un ciclo di generazione di report il pattern segue esattamente il codice sopra indicato: si carica o si crea il modello una volta, quindi si alterna la scrittura di alcune celle di input alla chiamata di Recalculate, pagando solo per le formule che dipendono effettivamente da ciò che è cambiato
Quali funzioni di Excel forzano il ricalcolo a ogni passaggio?
HotXLS considera volatili NOW, TODAY, RAND, OFFSET, e INDIRECT: qualsiasi formula contenente una di esse viene rivalutata a ogni passaggio di Recalculate, indipendentemente dal fatto che sia cambiato o meno qualcosa a monte. Le prime tre sono volatili per lo stesso motivo di Excel — il loro risultato dipende dal momento della valutazione, non da altre celle. OFFSET e INDIRECT sono volatili per un motivo più sottile: le celle che leggono vengono calcolate in fase di esecuzione, quindi il grafo non può conoscere staticamente quali archi tracciare per esse
La stessa regola conservativa si estende ai riferimenti che il generatore di grafi non può limitare a un singolo rettangolo. Una formula che attraversa un intervallo denominato ad aree multiple, o una che fa riferimento a una cartella di lavoro esterna, viene degradata a volatile e valutata a ogni passaggio. Questa politica è intenzionale: una valutazione in più richiede poco tempo, ma una dipendenza mancante significa inviare un report con un valore obsoleto, il che costituisce un fallimento ben peggiore. Se il modello si basa su nomi con ambito cartella di lavoro, l'articolo complementare sui nomi definiti e formule tra fogli descrive come si risolvono i nomi ad area singola — che partecipano al grafo normalmente
La guida pratica deriva direttamente da questo. Mantieni i percorsi critici di un modello di grandi dimensioni su riferimenti semplici a celle e intervalli in cui il grafo può svolgere il suo lavoro, e limita OFFSET e INDIRECT ai pochi punti che richiedono realmente un indirizzamento dinamico. Un modello con mille formule volatili esegue nuovamente quelle mille valutazioni a ogni passaggio, indipendentemente dall'entità della modifica — esattamente il comportamento riscontrato dagli utenti di Excel con le cartelle di lavoro che "si ricalcolano a ogni digitazione"
Come segnala HotXLS i riferimenti circolari?
TXLSXWorkbook.Recalculate restituisce lxOk in caso di passaggio corretto e lxErrorRef quando rileva un ciclo di riferimenti. I membri del ciclo vengono identificati durante l'ordinamento topologico — sono i nodi che l'algoritmo di Kahn non può mai sbloccare — e vengono saltati invece di generare un loop: i loro valori in cache rimangono inalterati, mentre ogni formula al di fuori del ciclo viene comunque valutata in ordine. Il chiamante riceve un codice di errore definito anziché un blocco
case Book.Recalculate of
lxOk:
SaveReport(Book);
lxErrorRef:
// a reference cycle exists; cycle members kept their previous
// cached values and everything outside the cycle is up to date
LogWarning('Circular reference detected - review model inputs');
end;Individuare quali celle formano il ciclo è un'operazione di debug, e lo strumento di tracciamento della valutazione delle formule è lo strumento ideale a tale scopo: tracciando la formula sospetta, la catena di riferimenti che si ripiega su se stessa diventa visibile passo dopo passo. I cicli nei modelli reali costituiscono quasi sempre un errore di stesura — una riga di riepilogo inserita accidentalmente nel proprio intervallo SUM — per cui un codice di errore chiaro al momento del ricalcolo è esattamente ciò che si desidera
Formule matriciali, tracciamento dirty e tempi di ricostruzione del grafo
Le formule matriciali CSE ottengono un nodo per l'intero rettangolo ancorato, non un nodo per cella. La formula radice viene valutata una volta per passaggio; la matrice risultante viene scritta direttamente in ciascuna cella membro, e una formula che fa riferimento a qualsiasi cella all'interno dell'intervallo ancorato — non solo all'ancora in alto a sinistra — riceve un arco di dipendenza da quel nodo radice. I risultati scalari vengono trasmessi in tutto il rettangolo secondo quanto prescritto dalla semantica degli array legacy di Excel
Il tracciamento dello stato dirty intercetta i normali setter delle proprietà, quindi il codice non subisce modifiche. Scrivere Value in una cella notifica la cartella di lavoro e contrassegna i dipendenti come dirty; assegnare una nuova Formula rappresenta una modifica strutturale, quindi contrassegna l'intero grafo come obsoleto, e la chiamata successiva a Recalculate lo ricostruisce prima della valutazione. Anche l'aggiunta, l'eliminazione o lo spostamento di fogli invalida il grafo, poiché l'identità del nodo codifica l'indice del foglio. Quando nessun grafo è attivo — ovvero in una cartella di lavoro in cui non si chiama mai Recalculate — gli hook costano un singolo controllo nil per assegnazione, mantenendo inalterate le normali operazioni di lettura-scrittura
Un limite da esporre chiaramente: il grafo tiene traccia delle dipendenze tra le celle, quindi una funzione definita dall'utente registrata tramite OnUserFunction viene rivalutata quando le celle che alimentano i suoi argomenti cambiano, come qualsiasi altra formula. Se stai estendendo il motore in questo modo, l'articolo sulle funzioni personalizzate nel motore di formule HotXLS illustra il contratto di callback e le modalità di arrivo dei valori degli argomenti
Il ricalcolo incrementale fa parte del motore XLSX standard in HotXLS Delphi Excel Component, insieme al calcolatore di formule, ai nomi definiti e alla pipeline di importazione/esportazione che accelera. Se la tua applicazione Delphi o C++Builder gestisce modelli attivi — fogli di calcolo prezzi, cartelle di lavoro di consolidamento, report a cascata — Recalculate fa la differenza tra il ricalcolo di un'intera cartella di lavoro e il ricalcolo di una modifica