Mettere =VLOOKUP(A1,B:B,1) in una cella della colonna B ed Excel la calcola senza lamentarsi. Dare la stessa cartella di lavoro a un motore di ricalcolo a grafo delle dipendenze e è probabile ottenere un errore di riferimento circolare, perché la formula dipende da un intervallo che contiene la formula. HotXLS riportava esattamente quello fino alla v2.361.98. La correzione non è un caso speciale per gli intervalli a colonna intera; è una distinzione tra due generi di arco di dipendenza di cui un motore foglio di calcolo ha bisogno e un grafo orientato semplice non dispone
L'argomento lookup-array della famiglia di lookup, LOOKUP, MATCH, HLOOKUP, VLOOKUP, XLOOKUP e XMATCH, è ora marcato come riferimento di scansione. Un riferimento di scansione semina comunque sporchezza, quindi modificare una cella dentro l'intervallo ricalcola la formula, ma non contribuisce mai al rilevamento dei cicli né all'ordinamento di valutazione. I cicli veri si trovano ancora; quelli falsi sono spariti
Perché Excel consente a un intervallo di lookup di contenere la formula?
Perché quell'argomento non viene consumato nel modo in cui lo è un operando aritmetico. La famiglia di lookup scandisce l'intervallo in cerca di valori in cache e restituisce una corrispondenza; non richiede che l'intervallo sia stato valutato fino in fondo prima. Excel tratta un intervallo di lookup auto-sovrapposto come una lettura di ciò che quelle celle tengono attualmente, che è la stessa semantica che applica a qualsiasi cartella di lavoro non iterativa: le celle non ricalcolate in questa passata contribuiscono con il loro ultimo valore calcolato
I riferimenti a colonna intera fanno di questo il caso comune anziché uno esotico. B:B è il modo idiomatico di scrivere "l'intera tabella di lookup" in un foglio dove le righe vengono aggiunte, e qualsiasi formula che vive nella colonna B sta allora dentro il proprio intervallo di lookup. I modelli finanziari, i fogli di riconciliazione e le cartelle di audit lo fanno di continuo, di solito senza che nessuno noti la sovrapposizione
Cosa fa un grafo delle dipendenze con la stessa formula
HotXLS ricalcola in modo incrementale, il che richiede un vero grafo delle dipendenze: nodi per le celle, archi per i riferimenti, un ordine topologico per la valutazione e una passata di componenti fortemente connesse per classificare i cicli. Quella macchina è descritta nell'articolo sul ricalcolo incrementale, ed è esattamente per questo che il falso positivo è comparso
Estrarre le dipendenze da =VLOOKUP(A1,B:B,1) nella cella B7 e il secondo argomento produce un intervallo che contiene B7 stessa. Il grafo ora ha un self-loop. Il in-degree di quel nodo non raggiunge mai zero, così la passata topologica non può mai pianificarlo, e la passata delle componenti lo classifica come ciclo. Il motore ragiona correttamente sul grafo che gli è stato dato. Il grafo è il modello sbagliato, perché codifica un solo tipo di arco dove il foglio di calcolo ne ha due
Due classi di archi, un grafo
La modifica aggiunge un flag al record di riferimento risolto, TXLSDepRange.LookupScan, che l'estrattore di dipendenze imposta quando percorre l'argomento lookup-array di una delle sei funzioni. A valle, gli archi provenienti da quei riferimenti sono conservati separatamente dagli archi ordinari: il nodo del grafo conserva liste ScanDependents e ScanPrecedents accanto alle proprie normali liste di dipendenti e precedenti
La separazione è ciò che rende giusta la semantica. Gli archi di scansione sono percorsi dalla propagazione dello sporco, così una modifica in qualsiasi punto di B:B marca comunque B7 come sporca e B7 ricalcola. Gli archi di scansione non sono mai contati nel in-degree e non entrano mai nel costruttore di componenti, così non possono creare un deadlock topologico né essere classificati come ciclo. Entrambe le implementazioni del grafo nella libreria, il grafo classico per cartella e il grafo di workspace tra cartelle che trasporta l'analisi delle componenti, sono state cambiate insieme; lasciarle divergere produrrebbe una cartella che ricalcola diversamente a seconda che sia aperta da sola o come parte di un workspace
var
Book: TXLSXWorkbook;
Sheet: TXLSXWorksheet;
begin
Book := TXLSXWorkbook.Create;
try
Sheet := Book.Sheets.Add('Ledger');
Sheet.Cells[1, 1].Value := 'ACC-4471';
Sheet.Cells[1, 2].Value := 1200.00;
// L'intervallo di lookup copre la colonna B, e questa formula vive dentro
Sheet.Cells[7, 2].Formula := 'VLOOKUP(A1,B:B,1)';
case Book.Recalculate of
lxOk:
// Prima della v2.361.98 questo ramo era irraggiungibile per questo foglio
SaveReport(Book);
lxErrorRef:
LogWarning('Genuine circular reference - review model inputs');
end;
finally
Book.Free;
end;
end;A cosa si rinuncia escludendo gli archi di scansione dall'ordinamento
Esattamente una cosa, e vale la pena dirlo in modo piano anziché nasconderla. Poiché gli archi di scansione non partecipano all'ordine topologico, una formula di lookup può essere valutata nella stessa passata prima che alcune celle del proprio intervallo di lookup siano state ricalcolate, e leggerà quindi i loro valori precedenti. Il risultato converge al ricalcolo successivo
È accettabile perché è ciò che fa Excel. Per una cartella di lavoro senza calcolo iterativo attivato, la risposta di Excel per un valore non ancora ricalcolato nella passata corrente è l'ultimo valore calcolato, quindi un motore che riproduce questo comportamento sta corrispondendo all'implementazione di riferimento anziché approssimarla. Se serve una risposta davvero convergente su un modello auto-referenziale, il meccanismo per quello è il calcolo iterativo con un limite di iterazione esplicito, trattato nell'articolo sul calcolo iterativo, e si applica ai cicli veri anziché alle sovrapposizioni di scansione
Il rischio di regressione nascosto nella correzione
Aggiungere LookupScan a TXLSDepRange ha introdotto un rischio che non c'entra nulla con i lookup e tutto con il Pascal. TXLSDepRange è un record non gestito, quindi una variabile locale di quel tipo non è inizializzata a zero. Ogni punto della codebase che ne costruisce uno a mano, compresi i blocchi di dipendenza delle tabelle dati e parecchi helper di test, ha dovuto essere aggiornato per impostare esplicitamente il nuovo campo. Se ne salta uno e il byte che per caso si trovava sullo stack decide se quel riferimento è trattato come arco di scansione, il che produce un bug di ricalcolo che appare e scompare con modifiche di codice non correlate
// Un nuovo campo Boolean in un record non gestito rende ogni punto di
// costruzione manuale un bug latente. Due idiomi sicuri:
var
R: TXLSDepRange;
begin
FillChar(R, SizeOf(R), 0); // azzerare tutto, poi riempire
R.Sheet1 := SheetIndex;
R.Sheet2 := SheetIndex;
R.Row1 := Row; R.Col1 := Col;
R.Row2 := Row; R.Col2 := Col;
// oppure impostare ogni campo, compreso il nuovo, in ogni punto
R.LookupScan := False;
end;La regola generale che questo si è guadagnato: aggiungere un campo a un record costruito sullo stack in più di una manciata di punti è una modifica più rischiosa di quanto sembri, e il compilatore non aiuterà a trovare i punti. Se il record è raggiungibile da un percorso caldo, preferire un helper che lo inizializza completamente al fidarsi che ogni call site sia aggiornato
Distinguere un ciclo vero da una sovrapposizione di scansione
Nulla di questa modifica indebolisce il rilevamento dei cicli. =B7+1 in B7 resta un ciclo, una catena di tre formule che si chiude su sé stessa resta un ciclo, ed entrambi sono ancora riportati attraverso il risultato del ricalcolo con i membri del ciclo che conservano i loro precedenti valori in cache mentre tutto ciò che sta fuori dal ciclo resta corrente. Ciò che è cambiato è solo che l'argomento lookup-array non fabbrica più cicli che Excel non vede
Se Lei sta auditando una cartella di lavoro e vuole sapere quali riferimenti il motore abbia effettivamente risolto e in quale ordine, il tracer di valutazione è lo strumento; l'articolo sul tracer di valutazione delle formule copre come leggere il suo output. HotXLS è un componente foglio di calcolo nativo per Delphi e C++Builder che legge e scrive XLS, XLSX, ODS e CSV senza Excel installato, e il motore di ricalcolo è lo stesso su ogni formato; l'attuale copertura di funzioni e motore è elencata nella pagina di prodotto di HotXLS Delphi spreadsheet component