Una colonna di 10.000 formule, ognuna delle quali incrementa il valore nella cella sopra di lei, è più o meno il modello più economico che un foglio di calcolo possa contenere. In un valutatore che calcola una formula referenziata sul posto ogni volta che la incontra, i totali escono comunque giusti, ma il conto aritmetico no: l'ultima cella trascina 9.999 antenati attraverso la valutazione, quella sopra di lei 9.998, e una singola passata completa somma fino a circa 50 milioni di valutazioni per 10.000 moltiplicazioni. Cambiate un tasso di crescita in cima e quel valutatore deve pagare di nuovo l'intero conto, perché non tiene traccia di quale cella legga quale
Perché una cella modificata può costare una passata sull'intera cartella
Due scorciatoie producono quel comportamento, e la maggior parte dei motori di formule fatti in casa ne prende una. La scorciatoia ricorsiva valuta di fresco ogni formula referenziata e ignora qualsiasi valore sia già in cache, il che trasforma una catena in lavoro quadratico e un riferimento circolare in uno stack overflow. La scorciatoia a passata completa è più sicura: valuta ogni formula in un qualche ordine fisso, quante volte serve perché nulla cambi più. È corretta, e il suo costo segue la dimensione della cartella di lavoro anziché quella della modifica
Excel evita entrambe con la sua catena di calcolo, un ordinamento delle celle di formula che permette a una modifica di marcare un piccolo insieme di celle e percorrere solo la coda interessata. Il motore XLSX di HotXLS usa la stessa idea nella forma di un grafo delle dipendenze esplicito dietro a TXLSXWorkbook.Recalculate. La prima chiamata costruisce il grafo da ogni formula su ogni foglio e valuta tutte. Ogni chiamata successiva valuta solo le formule a valle delle celle i cui valori sono cambiati dalla passata precedente, più una breve lista di formule che devono girare ogni volta
Come Recalculate trasforma una modifica in una passata ordinata
Ogni cella di formula diventa un nodo, e un arco corre da ogni cella di formula che una formula legge alla formula che la legge. Un riferimento come SUM(Model!B2:B500) produce un arco da ogni cella di formula dentro quel rettangolo, e un riferimento 3D su una sequenza di fogli viene confrontato con ciascun foglio della sequenza. Le costanti semplici non sono affatto nodi; contano solo come punto dove una modifica inizia
Scrivere Value su una cella non fa quasi nulla al momento della scrittura. La cartella di lavoro accoda l'indirizzo della cella, e questo è tutto. Quando Recalculate parte, ogni formula i cui riferimenti coprono un indirizzo accodato viene marcata sporca, e il marchio scorre lungo gli archi verso tutti i dipendenti di quella formula, poi verso i loro. Una cartella di lavoro su cui Recalculate non è mai stato chiamato non tiene alcun grafo, e le sue scritture costano un solo controllo nil, quindi il codice che legge e scrive soltanto valori non paga nulla per la funzionalità
La parte sporca del grafo viene poi ordinata con l'algoritmo di Kahn. Il dettaglio che rende economica la passata è come vengono contati gli in-degree: contano solo i precedenti sporchi. Un precedente pulito ha già un valore in cache valido, quindi una formula non aspetta mai su di lui. Durante la passata, una formula che referenzia un'altra cella di formula legge il valore in cache di quella cella invece di rivalutarla; l'ordinamento garantisce che la cache sia già fresca. Ogni formula sporca viene valutata esattamente una volta
Il codice qui sotto costruisce la catena del paragrafo iniziale e modifica il suo unico input. Gli indici dei fogli in HotXLS partono da 1, quindi Inputs è il foglio 1 e Model è il foglio 2; righe e colonne in Cells sono a base 1 anch'esse
uses
SysUtils, lxCalc, lxHandleX; // lxOk è dichiarato in lxCalc
var
Book: TXLSXWorkbook;
Inputs, Model: TXLSXWorksheet;
Row: Integer;
Last: Variant;
begin
Book := TXLSXWorkbook.Create;
try
Inputs := Book.Sheets.Add('Inputs'); // Sheets[1]
Model := Book.Sheets.Add('Model'); // Sheets[2]
Inputs.Cells[2, 2].Value := 0.05; // ipotesi di crescita in Inputs!B2
Model.Cells[2, 2].Formula := 'Inputs!B2*1000';
for Row := 3 to 10000 do
Model.Cells[Row, 2].Formula :=
'B' + IntToStr(Row - 1) + '*(1+Inputs!$B$2)';
if Book.Recalculate <> lxOk then // costruisce il grafo, valuta 9.999 formule
Exit;
Inputs.Cells[2, 2].Value := 0.07; // accoda un indirizzo
if Book.Recalculate = lxOk then // le formule nutrite da Inputs!B2 girano di nuovo (qui: tutte e 9.999)
Last := Model.Cells[10000, 2].Value; // risultato in cache, si legge come qualsiasi valore
// Lettura una tantum: RecalcCell esegue Recalculate, poi restituisce la cella
// (foglio 2 = Model, riga 10000, colonna 2)
Book.RecalcCell(2, 10000, 2, Last);
finally
Book.Free;
end;
end;
In questo particolare modello ogni formula legge Inputs!$B$2, quindi la seconda passata tocca comunque tutte e 9.999; il grafo non può rimpicciolire una modifica che davvero interessa tutto. Il risparmio si vede nei modelli con molti input, dove un ciclo di scenari scrive tre o quattro celle e il resto della cartella di lavoro conserva i suoi valori in cache
Le scritture di valori sono economiche, le scritture di formule ricostruiscono il grafo
Solo le modifiche ai valori restano incrementali. Qualsiasi cosa possa cambiare l'insieme dei nodi o degli archi marca l'intero grafo come obsoleto, e il prossimo Recalculate lo ricostruisce da zero e valuta ogni formula. Copre assegnare Formula, azzerare una cella di formula scrivendoci Null o un valore vuoto, aggiungere, spostare o eliminare fogli, inserire o eliminare righe e colonne, modificare i nomi definiti, e cambiare le tabelle dati what-if. Registrare o deregistrare una funzione utente, o assegnare OnUserFunction, conserva il grafo ma marca ogni nodo sporco
La conseguenza pratica per i generatori di report è una forma di ciclo. Disponete le formule una volta, chiamate Recalculate, e da lì in poi toccate solo valori. Un ciclo che riscrive un template di formula per ogni cliente e poi ricalcola ottiene una ricostruzione completa a ogni iterazione, che è esattamente il costo che il grafo esiste per evitare. Quando un cambio di template serve davvero, radunate tutte le modifiche alle formule prima di un solo Recalculate, così la ricostruzione avviene una volta. Il ciclo di scenari poi scrive solo valori
// Modello costruito una volta; Recalculate già chiamato
for I := 0 to High(Scenarios) do
begin
Inputs.Cells[2, 2].Value := Scenarios[I].Growth; // solo scritture di valori
Inputs.Cells[3, 2].Value := Scenarios[I].Discount;
if Book.RecalcNeeded then // True quando ci sono celle accodate o sporche
if Book.Recalculate <> lxOk then
Break;
Results[I] := Model.Cells[10000, 2].Value;
end;
// Lasciate che SaveAs ricalcoli solo se qualcosa è ancora in sospeso
Book.RecalcBeforeSave := xrmIfDirty;
Book.SaveAs('scenarios.xlsx');
RecalcBeforeSave è predefinito a xrmManual, che non ricalcola mai durante un salvataggio. xrmIfDirty è l'impostazione per i file generati: ricalcola solo quando il grafo è obsoleto o ha formule sporche, quindi una cartella di lavoro già ricalcolata nel ciclo viene scritta così com'è, e una con una modifica dimenticata non viene salvata con valori in cache stantii
Quali funzioni di Excel forzano il ricalcolo a ogni passata?
Le formule che chiamano NOW, TODAY, RAND, RANDBETWEEN, RANDARRAY, OFFSET, INDIRECT, CELL, INFO o GETPIVOTDATA sono volatili in HotXLS: vengono marcate sporche all'inizio di ogni passata, che qualcosa a monte sia cambiato o no, e lo sono anche tutti i loro dipendenti. Le funzioni di orologio e casuali sono volatili per la ragione ovvia. OFFSET e INDIRECT sono volatili perché le celle che leggono vengono calcolate dai valori degli argomenti a runtime, e nessuna passeggiata statica sulla formula può dire quali archi disegnare
La stessa regola vale per i riferimenti che il costruttore del grafo non riesce a confinare in un rettangolo concreto, per un nome definito che si riferisce a sé stesso attraverso altri nomi, per i riferimenti spill delle matrici dinamiche, e per le tabelle dati what-if. I nomi definiti in generale non sono un problema: il costruttore espande un nome agli intervalli a cui si riferisce, quindi le formule che passano per nomi con ambito cartella o foglio ottengono archi veri. L'articolo sui nomi definiti e le formule tra fogli copre come si risolvono quei nomi
La direzione prudente è qui quella giusta. Una valutazione superflua costa un po' di tempo; un arco mancante significa un numero stantio in un report che sembra finito. Tenete OFFSET e INDIRECT fuori dai percorsi caldi dei modelli grandi e confinateli nelle poche celle che hanno davvero bisogno di indirizzi calcolati, perché una cella volatile vicino alla cima di una catena rende volatile in pratica l'intera catena
Le funzioni fornite tramite OnUserFunction o RegisterUserFunction stanno all'altro capo. Non sono volatili, quindi un handler viene richiamato di nuovo solo quando cambiano le celle che nutrono i suoi argomenti, come qualsiasi funzione incorporata. Un handler che legge un tasso di cambio da un database quindi continua a restituire la sua ultima risposta attraverso le passate. Scrivete invece il tasso in una cella di input e passate quella cella come argomento; il grafo allora vede la dipendenza, e la formula riesegue precisamente quando il tasso cambia. L'articolo sulle funzioni personalizzate nel motore di formule di HotXLS spiega come arrivano gli argomenti dell'handler
Riferimenti circolari: lxErrorRef o calcolo iterativo
Con il predefinito Iterate = False, TXLSXWorkbook.Recalculate restituisce lxErrorRef quando l'ordinamento trova un ciclo. I membri del ciclo sono i nodi sporchi che l'algoritmo di Kahn non riesce mai a rilasciare, e qualsiasi formula sporca a valle di loro è bloccata per la stessa ragione. Quelle formule conservano i loro precedenti valori in cache, mentre tutto il resto dell'insieme sporco viene valutato in ordine. Una passata fallita marca anche il grafo come obsoleto, quindi la chiamata dopo di lei ricostruisce il grafo e valuta ogni formula una volta di più
case Book.Recalculate of
lxOk:
Book.SaveAs(OutputFile);
lxErrorRef:
// il ciclo e le sue formule a valle conservano ancora le loro vecchie cache;
// LogWarning è una routine vostra
LogWarning('Circular reference in model, report not written');
else
LogWarning('Recalculation did not complete');
end;
Impostare Iterate := True accende lo stesso calcolo iterativo che offre Excel. Il motore valuta il ciclo e i suoi dipendenti bloccati ripetutamente, fino a IterateCount passate (predefinito 100), finché nessun valore si muove di IterateDelta (predefinito 0,001) o più. I membri vuoti del ciclo partono da 0. Raggiunto il limite di passate, gli ultimi risultati restano e la chiamata restituisce comunque lxOk, ed è per questo che l'iterazione dovrebbe restare spenta per i report generati: i cicli nei modelli di report reali sono quasi sempre errori, come una riga di totale presa dentro il proprio intervallo SUM, e da loro volete un codice di errore netto. Accendetelo solo per modelli costruiti attorno a un ciclo voluto, come un interesse addebitato su un saldo medio che include l'interesse
Un caso che sembra circolare non viene riportato come ciclo. L'argomento lookup-array di VLOOKUP, HLOOKUP, LOOKUP, MATCH, XLOOKUP e XMATCH rende comunque sporca la formula quando una cella in quell'intervallo cambia, ma non conta per il rilevamento dei cicli, quindi VLOOKUP(A1,B:B,1) scritta nella colonna B valuta normalmente. Per i cicli veri, il tracciatore di valutazione delle formule mostra la catena di riferimenti che si ripiega su se stessa, un passo alla volta
Formule di matrice e passate multi-thread
Una formula di matrice CSE legacy è un nodo per l'intero rettangolo ancorato. La formula radice viene valutata una volta per passata, la sua matrice viene scritta in ogni cella membro (un risultato scalare viene diffuso sul rettangolo), e una formula che referenzia qualsiasi cella del rettangolo, non solo l'ancora in alto a sinistra, ottiene un arco dalla radice. Le formule a matrice dinamica sono gestite con più cautela: girano a ogni passata, e quando uno spill cambia dimensione, Recalculate ricostruisce il grafo e ripete la passata finché le forme degli spill non si assestano. Se non si assestano mai entro 32 round, la chiamata restituisce lxErrorCalc
RecalcThreads impostato sopra 1 valuta ogni livello topologico di formule indipendenti attraverso thread worker, con un calcolatore separato per thread. Le tabelle dati girano comunque in serie, e una cartella di lavoro che contiene matrici dinamiche resta sul percorso seriale. Qualsiasi handler di funzione utente deve essere thread-safe prima che alziate questo valore; per un handler che tocca stato condiviso, tenere RecalcThreads al suo predefinito di 0 è la scelta più sicura
Il grafo delle dipendenze, la gestione dei volatili e il calcolo iterativo fanno parte del motore XLSX di HotXLS Delphi Excel Component, che copre anche il calcolatore di formule, i nomi definiti e la pipeline di lettura e scrittura attorno a loro. Per applicazioni Delphi e C++Builder che tengono aperti fogli di prezzo o cartelle di consolidamento e le nutrono di nuovi input tutto il giorno, il lavoro per modifica segue allora le celle che la modifica raggiunge davvero