Da bi izračunao namerne kružne reference u Delphi-ju, HotXLS izlaže iterativni proračun na svom XLSX motoru: postavite TXLSXWorkbook.Iterate na True i TXLSXWorkbook.Recalculate vodi svaki detektovani krug referenci ka fiksnoj tački — do IterateCount prolaza, ili dok se svaka ćelija ne promeni za manje od IterateDelta — umesto da vrati grešku #REF! i odustane
Ta razlika je važnija nego što to sugeriše jedna bulova (boolean) vrednost. Isti motor koji detektuje krug referenci i odbija da se vrti u petlji oko njega će, sa jednom promenjenom osobinom, namerno procenjivati taj krug dok se on ne stabilizuje. Pravilno razlikovanje ova dva ponašanja — kada je krug defekt koji treba prijaviti, a kada je model koji treba rešiti — predstavlja celokupnu temu ovog članka
Zašto kružna referenca podrazumevano javlja grešku?
Podrazumevano, HotXLS tretira svaki krug referenci kao grešku u kreiranju i prijavljuje je umesto da je izračunava. Metod TXLSXWorkbook.Recalculate gradi grafikon zavisnosti formula, procenjuje svaku ćeliju sa formulom u topološkom redosledu i vraća lxErrorRef onog trenutka kada pronađe krug — čvorove koji se nikada ne mogu osloboditi tokom topološkog sortiranja. Ti članovi kruga zadržavaju svoje prethodne keširane vrednosti; svaka formula van kruga se i dalje normalno procenjuje. Mehanika tog grafikona, kao i razlog zašto se članovi kruga preskaču umesto da se vrte u petlji, pokriveni su u pratećem članku o inkrementalnom ponovnom izračunavanju formula i grafikonu zavisnosti
Podrazumevano ponašanje je bezbedno jer su većina krugova zapravo greške: zbirni red slučajno uvučen u sopstveni SUM opseg, ili kopiranje-lepljenje koje je pomerilo referencu na samu sebe. Jasna šifra greške u vreme ponovnog izračunavanja je upravo ono što želite u takvim slučajevima. Međutim, specifična i važna klasa modela je kružna sa namerom. Planovi obračuna kamate na kamatu, kružne raspodele troškova ili režijskih troškova između odeljenja i proračuni naknada zasnovani na saldu opisuju vrednost koja se legitimno vraća u sopstvene ulaze — a Excel ih izračunava tek kada korisnik označi opciju Datoteka → Opcije → Formule → Omogući iterativni proračun (File → Options → Formulas → Enable iterative calculation)
Kako omogućiti iterativni proračun u HotXLS-u?
HotXLS preslikava to Excel polje za potvrdu pomoću tri osobine u klasi TXLSXWorkbook: Iterate uključuje ovaj režim, i kada je True, detektovani krug se predaje iterativnom rešavaču umesto da proizvede grešku lxErrorRef. Uzmite u obzir kanonski par ćelija za obračun kamate na kamatu, gde završni saldo zavisi od kamate, a kamata zavisi od salda
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 referencira B4, a B4 referencira B3, tako da grafikon zavisnosti prijavljuje krug sa dva čvora. Ako se Iterate ostavi na podrazumevanoj vrednosti False, taj par bi vratio lxErrorRef i nijedna ćelija se ne bi stabilizovala. Sa vrednošću True, metod Recalculate inicijalizuje krug iz trenutnih keširanih vrednosti i ponovo procenjuje njegove članove iz prolaza u prolaz, hraneći izlaze svakog prolaza nazad kao ulaze sledećeg prolaza, sve dok brojevi ne prestanu da se menjaju. Ovde je zatvoreni oblik glavnica / (1 - stopa), pa se saldo stabilizuje na 1052.63, a kamata na 52.63 — što su iste cifre koje Excel daje sa omogućenom iteracijom
Šta zaustavlja iteraciju?
Dva nezavisna uslova zaustavljanja ograničavaju rešavač, a razumevanje oba je ono što sprečava da model ili javi grešku ili upadne u beskonačnu petlju. IterateCount je čvrsta granica za broj ponovnih procena članova kruga; podrazumevano je 100, što odgovara Excelu. IterateDelta je prag konvergencije: nakon svakog prolaza rešavač meri najveću numeričku promenu u svim ćelijama kruga, i čim ta maksimalna promena padne ispod IterateDelta — podrazumevano 0.001 — petlja prolaza se prekida ranije. Koji god uslov bude ispunjen prvi, prekida iteraciju
Book.Iterate := True;
Book.IterateCount := 1000; // hard cap: at most 1000 passes over the cycle
Book.IterateDelta := 0.0001; // convergence: stop once every cell moves < 0.0001
case Book.Recalculate of
lxOk:
// the cycle converged, OR it hit the 1000-pass cap and kept its
// last-iteration values -- both paths return lxOk when Iterate is True
SaveWorkbook(Book);
lxErrorRef:
// only reachable with Iterate = False: the cycle was reported, not solved
LogWarning('Circular reference with iteration disabled');
end;
Jednu posledicu vredi jasno navesti, jer ona predstavlja stvarnu granicu ove funkcije. Kada se dostigne granica bez pada promene ispod praga IterateDelta, metod SolveCycleIteratively ne podiže grešku — on vraća lxOk i ostavlja ćelije sa vrednostima iz poslednje iteracije, baš kao što Excel upisuje poslednje izračunate brojeve kada se dostigne njegova granica iteracija bez konvergencije. Dakle, uspešan povratni kod iz Recalculate u iterativnom režimu znači "rešavač je pokrenut", a ne "rešavač je konvergirao". Model čija povratna petlja divergira ili osciluje će tiho potrošiti svih IterateCount prolaza i vratiti brojeve koji uopšte ne predstavljaju fiksnu tačku, a da nikakav izuzetak ne označi tu razliku
Kako se ovo podešavanje čuva u XLSX i XLS datotekama?
Podešavanja iterativnog proračuna se čuvaju u oba formata tabela, tako da se radna sveska otvorena u Excelu ponaša onako kako je vaš Delphi kod konfigurisao. Na XLSX strani, pisač emituje OOXML element <calcPr> samo kada je Iterate postavljeno na True, i izostavlja bilo koji atribut koji je i dalje na podrazumevanoj vrednosti kako bi izlaz bio minimalan: radna sveska koja koristi podrazumevane vrednosti piše samo <calcPr iterate="1"/>, dok se iterateCount pojavljuje samo kada se razlikuje od 100, a iterateDelta samo kada se razlikuje od 0.001. Prilikom otvaranja, TXLSXWorkbook čita ista tri atributa nazad, tako da je prenos u oba smera simetričan
Stariji BIFF8 (.xls) motor, TXLSWorkbook, nosi ekvivalentno stanje kroz tri odvojena zapisa pod različitom trojkom osobina. EnableIteration se preslikava na CalcIter zapis ($0011, [MS-XLS] §2.4.33), MaxIterations na CalcCount zapis ($000C, [MS-XLS] §2.4.31), a MaxIterationChange na CalcDelta zapis ($0010, [MS-XLS] §2.4.32). Seteri primenjuju opsege iz specifikacije — CalcCount mora biti u opsegu 1..32767, pa se MaxIterations ograničava, a negativna vrednost MaxIterationChange se vraća na podrazumevanu vrednost 0.001. Podesite ih na učitanoj .xls radnoj svesci i tri zapisa proračuna biće verno upisana pri čuvanju
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;Obratite pažnju na namernu razliku u nazivu: XLSX motor koristi Iterate / IterateCount / IterateDelta (rečnik OOXML-a), dok BIFF8 motor koristi EnableIteration / MaxIterations / MaxIterationChange (usklađeno sa nazivima zapisa iz [MS-XLS] standarda). Obe trojke opisuju ista tri dugmeta — prekidač za uključivanje/isključivanje, granicu iteracija i deltu konvergencije — sa istim podrazumevanim vrednostima isključeno, 100 i 0.001
Kada je kružna referenca bag, a kada model?
Omogućavanje iteracije nije način da se učini da upozorenja o kružnim referencama nestanu, a takav pristup predstavlja zamku. Globalno uključivanje Iterate pretvara svaki slučajni krug — one koje je podrazumevani kod greške trebalo da uhvati — u tiho konvergiran ili tiho nekonvergiran broj. Pravilo je obrnuto: držite Iterate na False kao normalan režim kako bi se stvarne greške u kreiranju i dalje prikazivale kao lxErrorRef, a omogućite iteraciju samo na radnim sveskama čija je kružnost namerna i jasna
Kada se krug zaista pojavi i niste sigurni o kom se tipu radi, tragač za evaluaciju formula (formula evaluation tracer) je alat koji ih razlikuje: pratite sumnjivu formulu i lanac referenci koji se savija nazad na samog sebe postaće vidljiv korak po korak, tako da možete odlučiti da li on kodira stvarnu petlju povratnih informacija ili zalutalu samoreferencu. Takođe pomaže da se zapamti da ćelija u krugu može pozvati bilo koju ugrađenu funkciju na putu kroz petlju — isti kalkulator koji razrešava inženjersku formulu ili formulu sa kompleksnim brojevima procenjuje članove kruga u svakom prolazu — tako da je model koji divergira često problem sa formulom unutar petlje, a ne problem sa podešavanjima iteracije
Praktična lista je kratka. Potvrdite da petlja ima stvarnu fiksnu tačku pre nego što se oslonite na iteraciju; držite IterateDelta dovoljno usko tako da "konvergirano" znači ono što vašem modelu zaista treba; i nakon poziva Recalculate za koji očekujete da konvergira, proverite poznati izlaz umesto da verujete samo kodu lxOk, pošto taj kod ne može razlikovati konvergenciju od dostignute granice iteracija
Iterativni proračun kružnih referenci je deo XLSX motora u komponente HotXLS Delphi Excel Component, zajedno sa inkrementalnim ponovnim izračunavanjem zasnovanim na grafikonu zavisnosti i OOXML i BIFF8 čuvanjem koje prenosi ovo podešavanje u svaku datoteku koju zapišete. Za finansijske i inženjerske modele koji su kružni sa namerom, to predstavlja razliku između šifre greške i odgovora