HotXLS, biblioteca Excel nativă pentru Delphi și C++Builder, execută recalcularea incrementală a formulelor prin TXLSXWorkbook.Recalculate. Primul apel construiește un graf de dependențe între formule și evaluează fiecare celulă cu formulă; fiecare apel ulterior reevaluează doar celulele afectate de scrierile de valori de la trecerea anterioară, în ordine topologică, într-o singură baleiere al cărei cost este proporțional cu numărul de celule murdare, nu cu dimensiunea registrului de lucru
Această unică decizie de proiectare face diferența dintre un model financiar care răspunde la o ipoteză editată în milisecunde și unul care se blochează secunde întregi. Dacă generați rapoarte în care o mână de celule de intrare alimentează mii de formule din aval, restul acestui articol explică ce face graful, ce funcții se retrag din regimul incremental și cum sunt raportate referințele circulare în loc să bucleze la nesfârșit
De ce schimbarea unei celule recalculează o sută de mii de formule?
Un motor de formule naiv nu are memoria cui de cine depinde, așa că singura lui mișcare sigură după orice editare este să evalueze totul din nou. Mai rău, strategia recursivă clasică — când formula A referă formula B, evaluează B pe loc — reevaluează necondiționat celulele referite, ignorând orice valoare din cache. Un lanț de n formule, fiecare referind-o pe precedenta, costă O(n²) evaluări per trecere completă, iar o referință circulară trimite recursia în prăpastie. Orice dezvoltator de foi de calcul care a cablat un model în cascadă într-un evaluator recursiv a văzut ambele moduri de eșec petrecându-se
Excel însuși a rezolvat asta acum zeci de ani prin lanțul lui de calcul: o ordonare a celulelor cu formule, menținută astfel încât o editare marchează un set mic de celule ca murdare, iar motorul parcurge doar coada afectată a lanțului. HotXLS aplică aceeași idee ca graf de dependențe explicit, construit o dată din arborii de formule compilați și refolosit peste treceri succesive de recalculare. Ideea nu este ingeniozitatea; este că prețul recalculării ar trebui să urmărească dimensiunea editării dumneavoastră, nu dimensiunea registrului de lucru
Cum transformă graful de dependențe o editare într-o singură trecere
Graful de dependențe HotXLS dă fiecărei celule cu formulă câte un nod, cu muchii care merg de la precedent la dependent. Când codul dumneavoastră scrie o valoare într-o celulă, registrul de lucru înregistrează celula ca murdară; când rulează Recalculate, starea de murdărie se propagă de-a lungul muchiilor către fiecare formulă din aval, iar subgraful murdar este evaluat exact o dată, în ordine topologică, folosind algoritmul lui Kahn. Fiindcă o formulă nu este vizitată niciodată înaintea precedentelor ei, fiecare nod are nevoie de o singură evaluare — asta face trecerea O(murdare)
Ordinea topologică rezolvă și problema recursiei, la rădăcină. În timpul unei treceri de recalculare, motorul comută într-un mod dedicat în care orice referință la altă celulă cu formulă citește direct valoarea din cache a acelei celule, în loc să o reevalueze — ordonarea garantează că acel cache este deja proaspăt. Același mecanism înseamnă că un ciclu de referințe nu poate declanșa recursie nemărginită: nimic din interiorul trecerii nu reintră vreodată în evaluator pentru 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; // ipoteza de creștere
Model.Cells[2, 2].Formula := 'Inputs!B2*1000'; // formulele XLSX nu iau '=' la început
Model.Cells[3, 2].Formula := 'B2*(1+Inputs!B2)';
// ... alte mii de rânduri în cascadă pornind de la aceeași ipoteză ...
Book.Recalculate; // primul apel: construiește graful, evaluare completă
Inputs.Cells[2, 2].Value := 0.07; // o editare marchează o celulă ca murdară
Book.Recalculate; // al doilea apel: rulează doar lanțul din aval
finally
Book.Free;
end;
end;Fiecare rezultat aterizează în Value din cache-ul celulei, deci după ce Recalculate se întoarce, citiți ieșirile la fel cum citiți orice altă celulă. Într-o buclă de generare de rapoarte, tiparul este exact codul de mai sus: încărcați sau construiți modelul o dată, apoi alternați între scrierea câtorva celule de intrare și apelarea lui Recalculate, plătind doar pentru formulele care depind cu adevărat de ce s-a schimbat
Ce funcții Excel forțează recalcularea la fiecare trecere?
HotXLS tratează NOW, TODAY, RAND, OFFSET și INDIRECT ca volatile: orice formulă care conține una dintre ele este reevaluată la fiecare trecere Recalculate, indiferent dacă s-a schimbat sau nu ceva în amonte. Primele trei sunt volatile din același motiv pentru care sunt ș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 execuție, deci graful nu poate ști static ce muchii să traseze pentru ele
Aceeași regulă conservatoare se extinde la referințele pe care constructorul grafului nu le poate fixa la un singur dreptunghi. O formulă care trece printr-un nume de interval cu mai multe zone, sau una care referă un registru de lucru extern, este la fel degradată la volatilă și reevaluată la fiecare trecere. Politica este deliberată: o evaluare în plus costă puțin timp, dar o muchie de dependență lipsă înseamnă o valoare silențios învechită într-un raport livrat, iar acesta este eșecul mult mai grav. Dacă modelul dumneavoastră se sprijină pe nume la nivel de registru, articolul complementar despre nume definite și formule între foi acoperă modul în care se rezolvă numele cu o singură zonă — acelea participă normal în graf
Îndrumarea practică decurge direct. Țineți traseele fierbinți ale unui model mare pe referințe simple de celule și intervale, unde graful îș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 le rulează din nou pe toate o mie la fiecare trecere, oricât de mică ar fi fost editarea — exact comportamentul pe care utilizatorii Excel îl cunosc din registrele care „recalculează la fiecare apăsare de tastă”
Cum raportează HotXLS referințele circulare?
TXLSXWorkbook.Recalculate returnează lxOk la o trecere curată și lxErrorRef atunci când detectează un ciclu de referințe. Membrii ciclului sunt identificați în timpul sortării topologice — sunt nodurile pe care algoritmul lui Kahn nu le poate elibera niciodată — și sunt săriți, nu buclați: valorile lor din cache rămân cum erau, în timp ce fiecare formulă din afara ciclului se evaluează normal, în ordine. Locul dumneavoastră de apel primește un cod de eroare cert, în loc de o blocare
case Book.Recalculate of
lxOk:
SaveReport(Book);
lxErrorRef:
// există un ciclu de referințe; membrii ciclului și-au păstrat
// valorile anterioare din cache, iar tot ce e în afara ciclului e la zi
LogWarning('Circular reference detected - review model inputs');
end;Găsirea celulelor care formează ciclul este o sarcină de depanare, iar trasorul de evaluare a formulelor este unealta potrivită pentru ea: urmăriți formula suspectă și lanțul de referințe care se pliază înapoi asupra lui însuși devine vizibil pas cu pas. Ciclurile din modelele reale sunt aproape întotdeauna o greșeală de redactare — un rând de totaluri inclus din greșeală în propriul interval SUM — așa că un cod de eroare zgomotos la momentul recalculării este exact ce vă doriți
Formule matriciale, urmărirea murdăriei și momentul reconstruirii grafului
Formulele matriciale CSE primesc un singur nod pentru tot dreptunghiul ancorat, nu câte un nod per celulă. Formula rădăcină se evaluează o dată per trecere; matricea rezultată este scrisă direct în fiecare celulă membră, iar o formulă care referă orice celulă din interiorul intervalului ancorat — nu doar ancora din stânga sus — capătă o muchie de dependență de la acel nod rădăcină. Rezultatele scalare se difuzează peste dreptunghi așa cum prescrie semantica matricială clasică din Excel
Urmărirea murdăriei se agață de setterele obișnuite de proprietăți, deci nimic din codul dumneavoastră nu se schimbă. Scrierea lui Value pe o celulă notifică registrul de lucru și marchează dependenții ca murdari; atribuirea unei noi Formula este o schimbare structurală, deci marchează tot graful ca învechit, iar următorul Recalculate îl reconstruiește înainte de a evalua. Adăugarea, ștergerea sau mutarea foilor invalidează de asemenea graful, fiindcă identitatea nodului codifică indexul foii. Când niciun graf nu este activ — un registru pe care nu apelați niciodată Recalculate — cârligele costă o singură verificare de nil per atribuire, deci sarcinile simple de citire-scriere nu sunt afectate
O limită care merită spusă cinstit: graful urmărește dependențe între celule, deci o funcție definită de utilizator, înregistrată prin OnUserFunction, este reevaluată când se schimbă celulele care îi alimentează argumentele, ca orice altă formulă. Dacă extindeți motorul în acest fel, articolul despre funcții personalizate în motorul de formule HotXLS parcurge contractul de callback și modul în care sosesc valorile argumentelor
Recalcularea incrementală face parte din motorul XLSX standard din HotXLS Delphi Excel Component, alături de calculatorul de formule, numele definite și lanțul de import/export pe care îl accelerează. Dacă aplicația dumneavoastră Delphi sau C++Builder întreține modele vii — foi de prețuri, registre de consolidare, cascade de rapoarte — Recalculate este diferența dintre a recalcula un registru de lucru și a recalcula o editare