HotXLS, la libreria Excel nativa per Delphi e C++Builder, esegue il ricalcolo incrementale delle formule tramite TXLSXWorkbook.Recalculate. La prima chiamata costruisce un grafo delle dipendenze fra formule e valuta ogni cella di formula; ogni chiamata successiva rivaluta solo le celle interessate dalle scritture di valori avvenute dopo l'ultima passata, in ordine topologico, in una sola passata il cui costo è proporzionale al numero di celle sporche anziché alla dimensione della cartella di lavoro
Quella singola decisione di progetto è la differenza fra un modello finanziario che risponde in millisecondi a un'ipotesi modificata e uno che si blocca per secondi. Se generate report in cui una manciata di celle di input alimenta migliaia di formule a valle, il resto di questo articolo spiega cosa fa il grafo, quali funzioni si sottraggono all'incrementalità e come i riferimenti circolari vengono segnalati invece di girare all'infinito
Perché cambiare una cella ricalcola centomila formule?
Un motore di formule ingenuo non ha memoria di chi dipende da chi, quindi la sua unica mossa sicura dopo una qualsiasi modifica è valutare di nuovo tutto. Peggio, la classica strategia ricorsiva — quando la formula A richiama la formula B, valuta B sul posto — rivaluta le celle richiamate senza condizioni, ignorando qualsiasi valore in cache. Una catena di n formule che richiamano ciascuna la precedente costa O(n²) valutazioni per passata completa, e un riferimento circolare manda la ricorsione a sbattere. Ogni sviluppatore di fogli di calcolo che abbia collegato un modello a cascata a un valutatore ricorsivo ha visto accadere entrambe le modalità di guasto
Excel stesso ha risolto la questione decenni fa con la sua catena di calcolo: un ordinamento delle celle di formula mantenuto in modo che una modifica marchi come sporco un piccolo insieme di celle e il motore percorra solo la coda interessata della catena. HotXLS applica la stessa idea come grafo esplicito delle dipendenze, costruito una volta dagli alberi di formula compilati e riutilizzato fra le passate di ricalcolo. Il punto non è l'astuzia; è che il costo del ricalcolo dovrebbe seguire la dimensione della vostra modifica, non la dimensione della vostra cartella di lavoro
Come il grafo delle dipendenze trasforma una modifica in una sola passata
Il grafo delle dipendenze di HotXLS assegna un nodo a ogni cella di formula, con archi che corrono dal precedente al dipendente. Quando il vostro codice scrive il valore di una cella, la cartella di lavoro registra la cella come sporca; quando Recalculate gira, la sporcizia si propaga lungo gli archi verso ogni formula a valle, e il sottografo sporco viene valutato esattamente una volta in ordine topologico usando l'algoritmo di Kahn. Poiché una formula non viene mai visitata prima dei suoi precedenti, ogni nodo richiede una sola valutazione — è questo a rendere la passata O(sporche)
L'ordine topologico risolve anche il problema della ricorsione alla radice. Durante una passata di ricalcolo il motore passa a una modalità dedicata in cui qualsiasi riferimento a un'altra cella di formula legge direttamente il valore in cache di quella cella invece di rivalutarla — l'ordinamento garantisce che la cache sia già fresca. Lo stesso meccanismo fa sì che un ciclo di riferimenti non possa innescare ricorsione illimitata: nulla dentro la passata rientra mai nel valutatore per una cella vicina
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; // ipotesi di crescita
Model.Cells[2, 2].Formula := 'Inputs!B2*1000'; // le formule XLSX non prendono il prefisso '='
Model.Cells[3, 2].Formula := 'B2*(1+Inputs!B2)';
// ... altre migliaia di righe a cascata dalla stessa ipotesi ...
Book.Recalculate; // prima chiamata: costruisce il grafo, valutazione completa
Inputs.Cells[2, 2].Value := 0.07; // una modifica marca una cella come sporca
Book.Recalculate; // seconda chiamata: gira solo la catena a valle
finally
Book.Free;
end;
end;Ogni risultato finisce nel Value in cache della cella, quindi dopo il ritorno di Recalculate leggete gli output esattamente come leggete qualsiasi altra cella. In un ciclo di generazione report lo schema è proprio il codice qui sopra: caricate o costruite il modello una volta, poi alternate la scrittura di poche celle di input e la chiamata a Recalculate, pagando solo per le formule che dipendono davvero da ciò che è cambiato
Quali funzioni di Excel forzano il ricalcolo a ogni passata?
HotXLS tratta NOW, TODAY, RAND, OFFSET e INDIRECT come volatili: qualsiasi formula che ne contenga una viene rivalutata a ogni passata di Recalculate, che qualcosa a monte sia cambiato o no. Le prime tre sono volatili per la stessa ragione per cui lo sono in Excel — il loro risultato dipende dal momento della valutazione, non da altre celle. OFFSET e INDIRECT sono volatili per una ragione più sottile: le celle che leggono vengono calcolate a runtime, quindi il grafo non può sapere staticamente quali archi disegnare per esse
La stessa regola prudente si estende ai riferimenti che il costruttore del grafo non riesce a fissare su un solo rettangolo. Una formula che passa per un nome di intervallo a più aree, o che richiama una cartella di lavoro esterna, viene ugualmente degradata a volatile e rivalutata a ogni passata. La politica è deliberata: una valutazione in più costa un po' di tempo, ma un arco di dipendenza mancante significa un valore silenziosamente obsoleto in un report consegnato, e quello è il guasto di gran lunga peggiore. Se il vostro modello si appoggia a nomi con ambito di cartella di lavoro, l'articolo di accompagnamento su nomi definiti e formule fra fogli spiega come si risolvono i nomi ad area singola — quelli partecipano normalmente al grafo
L'indicazione pratica ne discende direttamente. Tenete i percorsi caldi di un modello grande su semplici riferimenti a celle e intervalli, dove il grafo può fare il suo lavoro, e confinate OFFSET e INDIRECT nei pochi punti che hanno davvero bisogno di indirizzamento dinamico. Un modello con mille formule volatili le rifà girare tutte e mille a ogni passata per quanto piccola fosse la modifica — esattamente il comportamento che gli utenti di Excel conoscono nelle cartelle di lavoro che "ricalcolano a ogni tasto premuto"
Come segnala HotXLS i riferimenti circolari?
TXLSXWorkbook.Recalculate restituisce lxOk su una passata pulita 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 riesce mai a rilasciare — e vengono saltati anziché percorsi in cerchio: i loro valori in cache restano quelli che erano, mentre ogni formula fuori dal ciclo continua a valutarsi normalmente in ordine. Il vostro punto di chiamata riceve un codice di errore preciso invece di un blocco
case Book.Recalculate of
lxOk:
SaveReport(Book);
lxErrorRef:
// esiste un ciclo di riferimenti; i membri del ciclo hanno mantenuto i
// valori in cache precedenti e tutto fuori dal ciclo è aggiornato
LogWarning('Circular reference detected - review model inputs');
end;Scoprire quali celle formino il ciclo è un lavoro di debug, e il tracciatore di valutazione delle formule è lo strumento giusto: tracciate la formula sospetta e la catena di riferimenti che si ripiega su se stessa diventa visibile passo per passo. Nei modelli reali i cicli sono quasi sempre un errore di scrittura — una riga di riepilogo inclusa per sbaglio nel proprio intervallo SUM — quindi un codice di errore ben visibile al momento del ricalcolo è proprio ciò che volete
Formule di matrice, tracciamento della sporcizia e quando il grafo si ricostruisce
Le formule di matrice CSE ottengono un nodo per l'intero rettangolo ancorato, non un nodo per cella. La formula radice si valuta una volta per passata; la matrice risultante viene scritta direttamente in ogni cella membro, e una formula che richiami una qualsiasi cella dentro l'intervallo ancorato — non solo l'ancora in alto a sinistra — prende un arco di dipendenza da quel nodo radice. I risultati scalari si propagano sul rettangolo come prescrive la semantica legacy delle matrici di Excel
Il tracciamento della sporcizia si aggancia ai normali setter delle proprietà, quindi nulla cambia nel vostro codice. Scrivere Value su una cella notifica la cartella di lavoro e marca sporchi i dipendenti; assegnare una nuova Formula è una modifica strutturale, quindi marca obsoleto l'intero grafo, e il Recalculate successivo lo ricostruisce prima di valutare. Anche aggiungere, eliminare o spostare fogli invalida il grafo, dato che l'identità dei nodi codifica l'indice del foglio. Quando nessun grafo è attivo — una cartella di lavoro su cui non chiamate mai Recalculate — i ganci costano un solo controllo su nil per assegnazione, quindi i carichi di semplice lettura e scrittura non ne risentono
Vale la pena dichiarare onestamente un limite: il grafo traccia le dipendenze fra celle, quindi una funzione definita dall'utente registrata tramite OnUserFunction viene rivalutata quando cambiano le celle che alimentano i suoi argomenti, come qualsiasi altra formula. Se state estendendo il motore in quel modo, l'articolo sulle funzioni personalizzate nel motore di formule di HotXLS percorre il contratto della callback e il modo in cui arrivano i valori degli argomenti
Il ricalcolo incrementale fa parte del motore XLSX standard di HotXLS Delphi Excel Component, insieme al calcolatore di formule, ai nomi definiti e alla pipeline di importazione ed esportazione che accelera. Se la vostra applicazione Delphi o C++Builder mantiene modelli vivi — fogli di prezzi, cartelle di consolidamento, cascate di report — Recalculate è la differenza fra ricalcolare una cartella di lavoro e ricalcolare una modifica