Pentru a calcula referințele circulare intenționate în Delphi, HotXLS expune calculul iterativ în motorul său XLSX: setați TXLSXWorkbook.Iterate pe True, iar TXLSXWorkbook.Recalculate va parcurge fiecare ciclu de referințe detectat spre un punct fix — până la maximum IterateCount pași sau până când fiecare celulă se modifică cu mai puțin de IterateDelta —, în loc să returneze #REF! și să renunțe
Această distincție contează mai mult decât sugerează acea simplă proprietate booleană. Același motor care detectează un ciclu de referințe și refuză să intre în buclă va evalua deliberat acel ciclu până când se stabilizează, dacă activați acea proprietate. Păstrarea unei distincții clare între cele două comportamente — când un ciclu este un defect care trebuie raportat și când este un model care trebuie rezolvat — reprezintă întregul subiect al acestui articol
De ce generează o referință circulară o eroare în mod implicit?
În mod implicit, HotXLS tratează orice ciclu de referințe ca pe o greșeală de redactare și o raportează în loc să o calculeze. TXLSXWorkbook.Recalculate construiește un grafic de dependență al formulelor, evaluează fiecare celulă cu formulă în ordine topologică și returnează lxErrorRef în momentul în care găsește un ciclu — nodurile care nu pot fi eliberate în timpul sortării topologice. Acești membri ai ciclului își păstrează valorile stocate în cache anterior; orice formulă din afara ciclului se evaluează în continuare în mod normal. Mecanismele acestui grafic și motivele pentru care membrii ciclului sunt omiși în loc să fie rulați în buclă sunt descrise în articolul însoțitor despre recalcularea incrementală a formulelor și graful de dependență
Valoarea implicită este cea sigură, deoarece majoritatea ciclurilor reprezintă bug-uri: un rând de total inclus din greșeală în propriul interval SUM sau o operațiune de copiere-lipire care a direcționat o referință către ea însăși. O eroare vizibilă la momentul recalculării este exact ceea ce vă doriți în aceste cazuri. Totuși, o clasă specifică și importantă de modele este circulară în mod intenționat. Planurile de dobândă la dobândă, alocările circulare de costuri sau cheltuieli generale între departamente și calculele de comisioane bazate pe sold descriu toate o valoare care se întoarce în mod legitim în propriile date de intrare — iar Excel le calculează doar după ce utilizatorul bifează File → Options → Formulas → Enable iterative calculation
Cum se activează calculul iterativ în HotXLS?
HotXLS reflectă acea bifă din Excel prin trei proprietăți ale TXLSXWorkbook: Iterate activează modul, iar când este True, un ciclu detectat este transmis unui rezolvitor iterativ în loc să genereze lxErrorRef. Luați în considerare perechea clasică de celule pentru dobânda la dobândă, unde soldul final depinde de dobândă, iar dobânda depinde de sold
var
Book: TXLSXWorkbook;
Sheet: TXLSXWorksheet;
begin
Book := TXLSXWorkbook.Create;
try
Sheet := Book.Sheets.Add('Model');
Sheet.Cells[1, 2].Value := 1000; // B1: opening principal
Sheet.Cells[2, 2].Value := 0.05; // B2: period rate
Sheet.Cells[3, 2].Formula := 'B1+B4'; // B3: balance = principal + interest
Sheet.Cells[4, 2].Formula := 'B3*B2'; // B4: interest = balance * rate
Book.Iterate := True; // opt in to iterative calculation
if Book.Recalculate = lxOk then
// B3 converges to 1052.63..., B4 to 52.63...
Report(Sheet.Cells[3, 2].Value);
finally
Book.Free;
end;
end;B3 face referire la B4, iar B4 la B3, astfel încât graful de dependență raportează un ciclu de două noduri. Cu Iterate lăsat pe valoarea implicită False, această pereche ar returna lxErrorRef și nicio celulă nu s-ar stabiliza. Dacă este True, Recalculate inițializează ciclul de la valorile curente stocate în cache și reevaluează membrii săi pas cu pas, trimițând rezultatele fiecărui pas ca date de intrare pentru pasul următor, până când numerele nu se mai modifică. În acest caz, formula închisă este principal / (1 - rate), astfel încât soldul se stabilizează la 1052.63, iar dobânda la 52.63 — aceleași cifre pe care le generează Excel cu iterația activată
Ce oprește iterația?
Două condiții independente de oprire limitează rezolvitorul, iar înțelegerea ambelor este esențială pentru a preveni erorile sau buclele infinite într-un model. IterateCount reprezintă limita maximă a numărului de reevaluări ale membrilor ciclului; valoarea implicită este 100, la fel ca în Excel. IterateDelta reprezintă pragul de convergență: după fiecare pas, rezolvitorul măsoară cea mai mare modificare numerică în toate celulele ciclului, iar odată ce această modificare maximă scade sub IterateDelta — valoarea implicită 0.001 —, bucla se întrerupe mai devreme. Oricare dintre aceste condiții este îndeplinită prima, ea va opri iterația
O consecință importantă ce trebuie afirmată clar reprezintă limita onestă a acestei funcționalități. Când limita maximă este atinsă fără ca modificarea să scadă sub IterateDelta, SolveCycleIteratively nu generează o eroare — returnează lxOk și lasă celulele cu valorile de la ultima iterație, exact așa cum Excel scrie ultimele numere calculate când limita sa de iterație este atinsă fără convergență. Prin urmare, un cod de returnare de succes de la Recalculate în modul iterativ înseamnă „rezolvitorul a rulat”, nu „rezolvitorul a realizat convergența”. Un model a cărui buclă de feedback este divergentă sau oscilează va consuma în liniște toți pașii IterateCount și va returna numere care nu reprezintă deloc un pt fix, fără ca vreo excepție să semnaleze acest lucru
Cum se salvează setarea în fișierele XLSX și XLS?
Setările de calcul iterativ sunt stocate în ambele formate de foi de calcul, astfel încât un registru de calcul deschis în Excel se va comporta la fel cum a fost configurat în codul dvs. Delphi. În ceea ce privește XLSX, scriitorul emite elementul OOXML <calcPr> doar când Iterate este True și omite orice atribut aflat pe valoarea implicită pentru a menține ieșirea la dimensiuni minime: un registru care folosește valorile implicite scrie doar <calcPr iterate="1"/>, în timp ce iterateCount apare doar când diferă de 100, iar iterateDelta doar când diferă de 0.001. La deschidere, TXLSXWorkbook citește aceleași trei atribute, astfel încât procesul este complet simetric
Motorul BIFF8 moștenit (.xls), TXLSWorkbook, păstrează starea echivalentă prin trei înregistrări separate sub un set diferit de proprietăți. EnableIteration se mapează pe înregistrarea CalcIter ($0011, [MS-XLS] §2.4.33), MaxIterations pe înregistrarea CalcCount ($000C, [MS-XLS] §2.4.31), iar MaxIterationChange pe înregistrarea CalcDelta ($0010, [MS-XLS] §2.4.32). Metodele de setare impun intervalele specificate — CalcCount trebuie să fie între 1 și 32767, astfel încât MaxIterations este limitat, iar o valoare negativă a MaxIterationChange revine la valoarea implicită 0.001. Setați-le pe un registru .xls încărcat, iar cele trei înregistrări de calcul vor fi scrise fidel la salvare
var
Book: TXLSWorkbook; // BIFF8 (.xls) engine
begin
Book := TXLSWorkbook.Create;
try
Book.Open('model.xls');
Book.EnableIteration := True; // CalcIter record $0011
Book.MaxIterations := 500; // CalcCount record $000C (clamped to 1..32767)
Book.MaxIterationChange := 0.0001; // CalcDelta record $0010
Book.SaveAs('model.xls'); // the three calc records round-trip
finally
Book.Free;
end;
end;Rețineți diferențierea deliberată a denumirilor: motorul XLSX folosește Iterate / IterateCount / IterateDelta (vocabularul OOXML), în timp ce motorul BIFF8 folosește EnableIteration / MaxIterations / MaxIterationChange (aliniat cu numele înregistrărilor [MS-XLS]). Ambele seturi descriu aceleași trei opțiuni — un comutator pornit/oprit, o limită de iterații și o diferență de convergență — cu aceleași valori implicite: oprit, 100 și 0.001
Când este o referință circulară un bug și nu un model?
Activarea iterației nu este o metodă de a face să dispară avertismentele de referință circulară, iar tratarea ei ca atare reprezintă o capcană. Activarea globală a Iterate transformă fiecare ciclu accidental — cele pe care codul de eroare implicit trebuia să le identifice — într-un număr convergent sau divergent în mod silențios. Regula corectă este inversă: mențineți Iterate pe False ca mod normal de lucru, astfel încât greșelile reale de redactare să apară în continuare ca lxErrorRef, și activați iterația doar pe registrele de calcul unde circularitatea este planificată și înțeleasă
Când apare un ciclu și nu sunteți sigur de tipul acestuia, tracer-ul de evaluare a formulelor este instrumentul care le diferențiază: urmăriți formula suspectă, iar lanțul de referințe care se întoarce la sine devine vizibil pas cu pas, permițându-vă să decideți dacă acesta conține o buclă de feedback reală sau o referință proprie accidentală. De asemenea, merită reținut că o celulă din ciclu poate apela orice funcție încorporată de-a lungul buclei — același calculator care rezolvă o formulă inginerească sau de numere complexe evaluează membrii ciclului la fiecare pas —, astfel încât un model divergent este adesea o problemă de formulă din interiorul buclei, nu o problemă a setărilor de iterație
Lista practică de verificări este scurtă. Confirmați că bucla are un punct fix real înainte de a vă baza pe iterație; păstrați IterateDelta suficient de strâns pentru ca starea de „convergență” să însemne exact ceea ce are nevoie modelul dvs.; iar după o operațiune de Recalculate de la care așteptați convergență, verificați o ieșire cunoscută în loc să aveți încredere doar în rezultatul lxOk, deoarece acel cod nu poate distinge convergența de atingerea limitei de iterații
Calculul iterativ al referințelor circulare face parte din motorul XLSX din HotXLS Delphi Excel Component, alături de recalcularea incrementală pe bază de grafic de dependență pe care se bazează și de stocarea persistentă OOXML și BIFF8 care transferă setarea în fiecare fișier pe care îl scrieți. Pentru modelele financiare și inginerești care sunt circulare în mod intenționat, aceasta este diferența dintre un cod de eroare și un răspuns final