Za izračun namernih krožnih sklicev v Delphi HotXLS izpostavlja iterativni izračun na svojem motorju XLSX: nastavite TXLSXWorkbook.Iterate na True in TXLSXWorkbook.Recalculate prehodi vsak zaznani cikel sklicev proti fiksni točki — do IterateCount prehodov ali dokler se vsaka celica ne spremeni za manj kot IterateDelta — namesto da bi vrnil #REF! in odstopil
Ta razlika je pomembnejša, kot nakazuje ena sama logična vrednost. Isti motor, ki zazna cikel sklicev in se vanj noče zankati, bo ob preklopu ene lastnosti namerno ovrednotil ta cikel, dokler se ne ustali. Ohranjanje jasne meje med obema vedenjema — kdaj je cikel napaka, o kateri je treba poročati, in kdaj je model, ki ga je treba rešiti — je celotna tema tega članka
Zakaj krožni sklic privzeto javi napako?
HotXLS privzeto obravnava vsak cikel sklicev kot avtorsko napako in poroča o njej, namesto da bi jo izračunal. TXLSXWorkbook.Recalculate zgradi graf odvisnosti formul, ovrednoti vsako celico s formulo v topološkem vrstnem redu in vrne lxErrorRef v trenutku, ko najde cikel — vozlišča, ki jih med topološkim razvrščanjem nikoli ni mogoče sprostiti. Ti člani cikla ohranijo svoje prejšnje predpomnjene vrednosti; vsaka formula zunaj cikla se še vedno ovrednoti normalno. Mehanika tega grafa in zakaj so člani cikla preskočeni, namesto da bi se vanje zankali, sta zajeta v spremnem članku o inkrementalnem preračunavanju formul in grafu odvisnosti
Privzeta možnost je varna, ker je večina ciklov hroščev: zbirna vrstica, pomotoma potegnjena v lasten obseg SUM, ali kopiranje in lepljenje, ki je sklic premaknilo nase. Glasna koda napake ob preračunavanju je natanko tisto, wat želite za takšne primere. Vendar pa je določen in pomemben razred modelov namerno krožen. Načrti obresti na obresti, krožna razporeditev stroškov ali splošnih stroškov med oddelki in izračuni provizij na podlagi stanja opisujejo vrednost, ki se legitimno vrača v lastne vhode — Excel pa jih izračuna šele, ko uporabnik označi Datoteka → Možnosti → Formule → Omogoči iterativni izračun
Kako omogočiti iterativni izračun v HotXLS?
HotXLS zrcali to Excelovo potrditveno polje s tremi lastnostmi na TXLSXWorkbook: Iterate vklopi ta način, in ko je True, se zaznani cikel preda iterativnemu reševalniku, namesto da bi ustvaril lxErrorRef. Poglejmo kanonični par celic obresti na obresti, kjer je končno stanje odvisno od obresti, obresti pa so odvisne od stanja
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 se sklicuje na B4 in B4 se sklicuje na B3, zato graf odvisnosti poroča o dvovozliščnem ciklu. Če bi Iterate pustili na privzeti vrednosti False, bi se ta par vrnil kot lxErrorRef in nobena celica se ne bi ustalila. Ko je True, Recalculate cepi cikel iz trenutnih predpomnjenih vrednosti in ponovno ovrednoti njegove člane prehod za prehodom, pri čemer izhode vsakega prehoda hrani nazaj kot vhode naslednjega prehoda, dokler se številke ne nehajo premikati. Tukaj je zaključena oblika principal / (1 - rate), zato se stanje ustali pri 1052.63, obresti pa pri 52.63 — enake številke, kot jih ustvari Excel z omogočeno iteracijo
Kaj ustavi iteracijo?
Dva neodvisna pogoja ustavljanja omejujeta reševalnik, razumevanje obeh pa preprečuje, da bi model javil napako ali se vrtel v nedogled. IterateCount je trda omejitev števila ponovnih ovrednotenj članov cikla; privzeto je nastavljena na 100, kar se ujema z Excelom. IterateDelta je prag konvergence: po vsakem prehodu reševalnik izmeri največjo številsko spremembo v vseh celicah cikla, in ko ta največja sprememba pade pod IterateDelta — privzeto 0.001 —, se zanka prehodov predčasno prekine. Kateri koli pogoj je izpolnjen prvi, konča iteracijo
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;
Eno posledico je treba odkrito zapisati, saj gre za iskreno mejo te funkcije. Ko se doseže meja, ne da bi sprememba padla pod IterateDelta, SolveCycleIteratively ne sproži napake — vrne lxOk in pusti celicam vrednosti iz zadnje iteracije, natanko tako, kot Excel zapiše zadnje izračunane številke, ko doseže lastno mejo iteracij brez konvergence. Uspešna povratna koda iz Recalculate v iterativnem načinu torej pomeni "reševalnik je tekel" in ne "reševalnik je konvergiral". Model, katerega povratna zanka divergira ali oscilira, bo tiho porabil vse prehode IterateCount in vrnil številke, ki sploh niso fiksna točka, in nobena izjema ne označuje te razlike
Kako se nastavitev shrani v datoteke XLSX in XLS?
Nastavitve iterativnega izračuna se ohranijo v obeh formatih delovnih zvezkov, tako da se delovni zvezek, odprt v Excelu, obnaša tako, kot ga je konfigurirala vaša koda Delphi. Na strani XLSX pisalnik odda element OOXML <calcPr> le, ko je Iterate nastavljen na True, in opusti kateri koli atribut, ki je še vedno na privzeti vrednosti, da ohrani minimalen izhod: delovni zvezek, ki uporablja privzete vrednosti, zapiše le <calcPr iterate="1"/>, medtem ko se iterateCount pojavi le, ko se razlikuje od 100, iterateDelta pa le, ko se razlikuje od 0.001. Ob odpiranju TXLSXWorkbook prebere nazaj iste tri atribute, tako da je prenos v obe smeri simetričen
Starejši motor BIFF8 (.xls), TXLSWorkbook, prenaša enakovredno stanje prek treh ločenih zapisov pod drugo trojico lastnosti. EnableIteration se preslika v zapis CalcIter ($0011, [MS-XLS] §2.4.33), MaxIterations v zapis CalcCount ($000C, [MS-XLS] §2.4.31), MaxIterationChange pa v zapis CalcDelta ($0010, [MS-XLS] §2.4.32). Nastavitvene metode uveljavljajo obsege specifikacije — CalcCount mora ležati v 1..32767, zato se MaxIterations vgne, negativni MaxIterationChange pa skoči nazaj na privzeto vrednost 0.001. Nastavite jih na naloženem delovnem zvezku .xls in trije zapisi calc se bodo zvesto zapisali ob shranjevanju
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;Upoštevajte namerno razdelitev poimenovanj: motor XLSX govori o Iterate / IterateCount / IterateDelta (besednjak OOXML), medtem ko motor BIFF8 govori o EnableIteration / MaxIterations / MaxIterationChange (poravnano z imeni zapisov [MS-XLS]). Obe trojici opisujeta iste tri gumbe — stikalo za vklop/izklop, mejo iteracij in delto konvergence — z enakimi privzetimi nastavitvami (izklopljeno, 100 in 0.001)
Kdaj je krožni sklic hrošč in ne model?
Omogočanje iteracije ni način, da opozorila o krožnih sklicih izginejo, in tako obravnavanje je past. Globalni vklop Iterate pretvori vsak nenameren cikel — tiste, za katere je bila privzeta koda napake —, v tiho konvergirano ali tiho nekonvergirano številko. Pravilo je obratno: pustite Iterate na False kot običajen način delovanja, da se pristne avtorske napake še vedno pojavijo kot lxErrorRef, in omogočite iteracijo le na delovnih zvezkih, katerih krožnost je načrtovana in razumljena
Ko se cikel pojavi in niste prepričani, za katero vrsto gre, je sledilnik vrednotenja formul orodje, ki jih loči: sledite sumljivi formuli in veriga sklicev, ki se prepogne nase, postane vidna korak za korakom, tako da se lahko odločite, ali kodira dejansko povratno zanko ali zalutali lastni sklic. Pomaga tudi pomisliti, da lahko celica cikla na poti okoli zanke pokliče katero koli vgrajeno funkcijo — isti kalkulator, ki razreši inženirsko formulo ali formulo s kompleksnimi številkami, ovrednoti člane cikla pri vsakem prehodu —, zato je divergirajoč model pogosto težava s formulo znotraj zanke in ne težava z nastavitvami iteracije
Praktični kontrolni seznam je kratek. Preden se zanesete na iteracijo, potrdite, da ima zanka dejansko fiksno točko; ohranite IterateDelta dovolj tesno, da "konvergirano" pomeni tisto, kar vaš model potrebuje; po Recalculate, za katerega pričakujete, da bo konvergiral, pa preverite znan izhod, namesto da bi zaupali le kodi lxOk, saj ta koda ne more ločiti konvergence od dosežene meje iteracij
Iterativni izračun krožnih sklicev je del motorja XLSX v komponenti HotXLS Component za Delphi in C++Builder, skupaj z inkrementalnim preračunavanjem grafa odvisnosti, na katerem gradi, ter vztrajnostjo OOXML in BIFF8, ki prenaša nastavitev v vsako datoteko, ki jo zapišete. Za finančne in inženirske modele, ki so namerno krožni, je to razlika med kodo napake in odgovorom