HotXLS, biblioteca nativă Excel pentru Delphi și C++Builder, realizează recalcularea incrementală a formulelor prin intermediul TXLSXWorkbook.Recalculate. Primul apel construiește un grafic de dependență a formulelor și evaluează fiecare celulă cu formulă; fiecare apel ulterior reevaluează doar celulele afectate de scrierile de valori de la ultima etapă, în ordine topologică, într-o singură trecere al cărei cost este proporțional cu numărul de celule modificate (dirty), mai degrabă decât cu dimensiunea registrului de lucru
Această singură decizie de proiectare face diferența între un model financiar care răspunde în milisecunde la o ipoteză editată și unul care se blochează timp de câteva secunde. Dacă generați rapoarte în care câteva celule de intrare alimentează mii de formule din aval, restul acestui articol explică ce face graficul, care funcții renunță la incrementalitate și cum sunt raportate referințele circulare în loc să ruleze la nesfârșit
De ce modificarea unei singure celule recalculează o sută de mii de formule?
Un motor de formule simplu nu reține cine de cine depinde, așa că singura sa decizie sigură după orice editare este să evalueze toutul din nou. Mai rău, strategia recursivă clasică — când formula A face referire la formula B, evaluează B pe loc — reevaluează necondiționat celulele menționate, ignorând orice valoare stocată în cache. Un lanț de n formule, fiecare făcând referire la cea anterioară, costă O(n²) evaluări per etapă completă, iar o referință circulară trimite recursivitatea într-un blocaj. Fiecare dezvoltator de foi de calcul care a integrat un model în cascadă într-un evaluator recursiv a asistat la apariția ambelor moduri de eșec
Cum dovedește un PDF semnat că nu a fost modificat neautorizat?
Excel a rezolvat această problemă cu zeci de ani în urmă prin lanțul său de calcul: o ordonare a celulelor cu formule menținută astfel încât o editare marchează un set mic de celule ca fiind modificate (dirty), iar motorul parcurge doar partea afectată din lanț. HotXLS aplică aceeași idee sub forma unui grafic de dependență explicit, construit o singură dată din arborii de formule compilați și reutilizat în etapele de recalculare. Scopul nu este ingeniozitatea; scopul este ca costul recalculării să urmărească amploarea editării dvs., nu dimensiunea registrului de lucru
Cum transformă graficul de dependență o editare într-o singură etapă
Graficul de dependență HotXLS oferă fiecărei celule cu formulă un nod, cu conexiuni care merg de la precedent la dependent. Când codul dvs. scrie o valoare de celulă, registrul de lucru înregistrează celula ca fiind modificată (dirty); când rulează Recalculate, starea de modificare se propagă de-a lungul conexiunilor la fiecare formulă din aval, iar subgraficul modificat este evaluat exact o singură dată în ordine topologică folosind algoritmul lui Kahn. Deoarece o formulă nu este vizitată niciodată înaintea precedentelor sale, fiecare nod are nevoie de o singură evaluare — ceea ce face ca trecerea să fie O(dirty)
Ordinea topologică rezolvă de asemenea problema recursivității de la rădăcină. În timpul unei etape de recalculare, motorul trece într-un mod dedicat în care orice referire la o altă celulă cu formulă citește direct valoarea stocată în cache a acelei celule în loc să o reevalueze — ordonarea garantează că memoria cache este deja actualizată. Același mecanism înseamnă că un ciclu de referințe nu poate declanșa o recursivitate nelimitată: nimic din interiorul etapei nu reintroduce vreodată evaluatorul într-o celulă vecină
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;Fiecare rezultat ajunge în valoarea Value stocată în cache a celulei, astfel încât după returnarea Recalculate citiți rezultatele în același mod în care citiți orice altă celulă. Într-o buclă de generare de rapoarte, modelul este exact codul de mai sus: încărcați sau construiți modelul o singură dată, apoi alternați între scrierea câtorva celule de intrare și apelarea Recalculate, plătind doar pentru formulele care depind de fapt de ceea ce s-a schimbat
Care funcții Excel forțează recalcularea la fiecare etapă?
HotXLS tratează NOW, TODAY, RAND, OFFSET și INDIRECT ca fiind volatile: orice formulă care conține una dintre acestea este reevaluată la fiecare apel Recalculate, indiferent dacă s-a schimbat sau nu ceva în amonte. Primele trei sunt volatile din același motiv ca și în Excel — rezultatul lor depinde de momentul evaluării, nu de alte celule. OFFSET și INDIRECT sunt volatile dintr-un motiv mai subtil: celulele pe care le citesc sunt calculate la rulare, astfel încât graficul nu poate ști în mod static ce conexiuni să stabilească pentru ele
Aceeași regulă conservatoare se aplică și referințelor pe care generatorul de grafic nu le poate asocia unui singur dreptunghi. O formulă care trece printr-un interval numit din zone multiple sau una care face referire la un registru de lucru extern este de asemenea considerată volatilă și reevaluată la fiecare trecere. Această politică este deliberată: o evaluare suplimentară costă puțin timp, dar o conexiune de dependență lipsă înseamnă o valoare veche neobservată într-un raport trimis, ceea ce reprezintă un eșec mult mai grav. Dacă modelul dvs. se bazează pe nume la nivel de registru de lucru, articolul asociat despre numele definite și formulele între foie explică modul în care se rezolvă numele din zone unice — acestea participă normal în grafic
Ghidul practic rezultă în mod direct. Păstrați secțiunile critice ale unui model mare pe referințe simple de celule și intervale, unde graficul își poate face treaba, și izolați OFFSET și INDIRECT în puținele locuri care au cu adevărat nevoie de adresare dinamică. Un model cu o mie de formule volatile rulează din nou acele o mie de formule la fiecare etapă, indiferent cât de mică a fost editarea — exact comportamentul pe care utilizatorii de Excel îl cunosc din registrele de lucru care „se recalculează la fiecare apăsare de tastă”
Cum raportează HotXLS referințele circulare?
Metoda TXLSXWorkbook.Recalculate returnează lxOk în cazul unei treceri fără probleme și lxErrorRef atunci când detectează un ciclu de referințe. Elementele ciclului sunt identificate în timpul sortării topologice — sunt nodurile pe care algoritmul lui Kahn nu le poate elibera niciodată — și sunt omise în loc să fie rulate în buclă: valorile lor din cache rămân cele anterioare, în timp ce fiecare formulă din afara ciclului se evaluează normal în ordine. Punctul dvs. de apel primește un cod de eroare clar în loc de un blocaj
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;Găsirea celulelor care formează ciclul este o sarcină de depanare, iar instrumentul de urmărire a evaluării formulelor (formula evaluation tracer) este cel potrivit pentru aceasta: urmăriți formula suspectă, iar lanțul de referințe care se întoarce la sine devine vizibil pas cu pas. Ciclurile în modelele reale sunt aproape întotdeauna o greșeală de autor — un rând de rezumat inclus accidental în propriul său interval SUM — așa că un cod de eroare clar în momentul recalculării este exact ceea ce aveți nevoie
Formulele matrice, urmărirea modificărilor și momentul reconstrucției graficului
Formulele matrice CSE primesc un singur nod pentru întregul dreptunghi ancorat, nu câte un nod pentru fiecare celulă. Formula rădăcină se evaluează o singură dată per etapă; matricea rezultată este scrisă direct în fiecare celulă membră, iar o formulă care face referire la orice celulă din intervalul ancorat — nu doar la ancora din stânga-sus — primește o conexiune de dependență de la acel nod rădăcină. Rezultatele scalare sunt difuzate pe întregul dreptunghi în modul prescris de semantica matricilor vechi din Excel
Urmărirea modificărilor se conectează la setările obișnuite ale proprietăților, așa că nu se schimbă nimic în codul dvs. Scrierea valorii Value într-o celulă notifică registrul de lucru și marchează elementele dependente ca fiind modificate (dirty); atribuirea unei noi formule Formula este o schimbare structurală, marcând întregul grafic ca învechit, iar următorul apel Recalculate îl reconstruiește înainte de evaluare. Adăugarea, ștergerea sau mutarea foilor invalidează de asemenea graficul, deoarece identitatea nodului codifică indexul foii. Când nu este activ niciun grafic — un registru de lucru pentru care nu apelați niciodată Recalculate — aceste conexiuni costă o singură verificare nil per atribuire, astfel încât sarcinile simple de citire-scriere nu sunt afectate
O limită care trebuie menționată corect: graficul urmărește dependențele dintre celule, astfel încât o funcție definită de utilizator înregistrată prin OnUserFunction este reevaluată atunci când celulele care îi furnizează argumentele se modifică, la fel ca orice altă formulă. Dacă extindeți motorul în acest mod, articolul despre funcțiile personalizate în motorul de formule HotXLS prezintă contractul de apel invers și modul în care sunt transmise valorile argumentelor
Recalcularea incrementală face parte din motorul standard XLSX din HotXLS Delphi Excel Component, alături de calculatorul de formule, numele definite și linia de import/export pe care o accelerează. Dacă aplicația dvs. Delphi sau C++Builder folosește modele active — foi de prețuri, registre de consolidare, rapoarte în cascadă — Recalculate face diferența între re-calcularea unui registru de lucru și re-calcularea unei editări