Za izračunavanje namjernih kružnih referenci u Delphiju, HotXLS izlaže iterativni izračun na svom XLSX pokretaču: postavite TXLSXWorkbook.Iterate na True i TXLSXWorkbook.Recalculate vodi svaki otkriveni ciklus referenci prema fiksnoj točki — do IterateCount prolaza, ili dok se svaka ćelija ne promijeni za manje od IterateDelta — umjesto da vrati #REF! i odustane
Ta je razlika važnija nego što to sugerira jedna logička (boolean) vrijednost. Isti pokretač koji otkriva ciklus referenci i odbija raditi u petlji s njim će, s jednim promijenjenim svojstvom, namjerno procijeniti taj ciklus dok se ne ustali. Držanje ova dva ponašanja odvojenima — kada je ciklus greška koju treba prijaviti, a kada model koji treba riješiti — cijela je tema ovog članka
Zašto kružna referenca prema zadanim postavkama javlja pogrešku?
Prema zadanim postavkama, HotXLS tretira svaki ciklus referenci kao pogrešku autora i prijavljuje je umjesto da je izračunava. TXLSXWorkbook.Recalculate gradi grafikom ovisnosti formula, procjenjuje svaku ćeliju s formulom u topološkom redoslijedu i vraća lxErrorRef onog trenutka kada pronađe ciklus — čvorove koji se nikada ne mogu osloboditi tijekom topološkog razvrstavanja. Ti članovi ciklusa zadržavaju svoje prethodne predmemorirane vrijednosti; svaka formula izvan ciklusa i dalje se normalno procjenjuje. Mehanika tog grafikona, kao i razlog zašto se članovi ciklusa preskaču umjesto da se vrte u petlji, opisani su u popratnom članku o inkrementalnom ponovnom izračunu formula i grafikonu ovisnosti
Zadana postavka je sigurna jer je većina ciklusa zapravo pogreška: sažeti redak slučajno povučen u vlastiti raspon SUM, ili kopiranje-lijepljenje koje je pomaknulo referencu na samu sebe. Uočljiv kod pogreške pri ponovnom izračunu upravo je ono što želite za takve slučajeve. Međutim, specifična i važna klasa modela je kružna s namjerom. Tablice kamata na kamate, kružne raspodjele troškova ili režijskih troškova između odjela i izračuni naknada na temelju stanja opisuju vrijednost koja se legitimno vraća u vlastite ulaze — a Excel ih izračunava tek kada korisnik označi Datoteka → Opcije → Formule → Omogući iterativni izračun
Kako omogućiti iterativni izračun u HotXLS-u?
HotXLS preslikava taj Excelov potvrdni okvir s tri svojstva na TXLSXWorkbook: Iterate uključuje način rada, a kada je True, otkriveni ciklus se predaje iterativnom rješavaču umjesto proizvodnje lxErrorRef. Razmotrite kanonski par ćelija za kamatu na kamatu, gdje završno stanje ovisi o kamati, a kamata ovisi o stanju
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, pa grafikon ovisnosti prijavljuje ciklus od dva čvora. Kada bi Iterate ostao na zadanom statusu False, taj bi se par vratio kao lxErrorRef i nijedna se ćelija ne bi ustalila. Kada je True, Recalculate postavlja početne vrijednosti ciklusa iz trenutačnih predmemoriranih vrijednosti i procjenjuje njegove članove prolaz za prolazom, vraćajući izlaze svakog prolaza kao ulaze za sljedeći prolaz, dok se brojevi ne prestanu pomicati. Ovdje je zatvoreni oblik principal / (1 - rate), pa se stanje smiruje na 1052.63, a kamata na 52.63 — što su iste brojke koje Excel proizvodi s omogućenom iteracijom
Što zaustavlja iteraciju?
Dva neovisna uvjeta zaustavljanja ograničavaju rješavač (solver), a razumijevanje oba je ono što sprječava da model javlja pogrešku ili se vrti u beskonačnost. IterateCount je čvrsto ograničenje broja ponovnih procjena članova ciklusa; zadano je 100, isto kao u Excelu. IterateDelta je prag konvergencije: nakon svakog prolaza rješavač mjeri najveću brojčanu promjenu u svim ćelijama ciklusa, i čim ta maksimalna promjena padne ispod IterateDelta — zadano 0.001 — petlja prolaza se prekida ranije. Uvjet koji se prvi ispuni završava 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;
Jedna je posljedica vrijedna jasnog isticanja jer predstavlja stvarnu granicu ove značajke. Kada se dosegne ograničenje bez pada promjene ispod IterateDelta vrijednosti, SolveCycleIteratively ne podiže pogrešku — vraća lxOk i ostavlja ćelije s vrijednostima iz posljednje iteracije, točno onako kako Excel zapisuje posljednje izračunate brojeve kada se dosegne njegovo ograničenje iteracija bez konvergencije. Stoga uspješan povratni kod iz Recalculate u iterativnom načinu rada znači "rješavač je radio", a ne "rješavač je konvergirao". Model čija petlja povratne sprege divergira ili oscilira tiho će potrošiti sve IterateCount prolaze i vratiti brojeve koji uopće nisu fiksna točka, a nikakva iznimka ne označava tu razliku
Kako se postavka sprema u XLSX i XLS datoteke?
Postavke iterativnog izračuna ostaju u oba formata proračunskih tablica, pa se radna knjiga otvorena u Excelu ponaša onako kako ju je vaš Delphi kôd konfigurirao. Na strani XLSX-a, pisac ispušta OOXML element <calcPr> samo kada je Iterate True, i izostavlja bilo koji atribut koji je na zadanom statusu kako bi izlaz ostao minimalan: radna knjiga koja koristi zadane postavke zapisuje 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 natrag ista tri atributa, pa je dvosmjerni proces simetričan
Naslijeđeni BIFF8 (.xls) pokretač, TXLSWorkbook, prenosi ekvivalentno stanje kroz tri zasebna zapisa pod različitom trojkom svojstava. EnableIteration se mapira na zapis CalcIter ($0011, [MS-XLS] §2.4.33), MaxIterations na zapis CalcCount ($000C, [MS-XLS] §2.4.31), a MaxIterationChange na zapis CalcDelta ($0010, [MS-XLS] §2.4.32). Postavljači (setters) nameću raspon specifikacije — CalcCount must lie in 1..32767, pa je MaxIterations ograničen, a negativni MaxIterationChange se vraća na zadani 0.001. Postavite ih na učitanu .xls radnu knjigu i tri zapisa izračuna bit će vjerno zapisana pri spremanju
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;Primijetite namjernu razliku u nazivu: XLSX pokretač koristi Iterate / IterateCount / IterateDelta (rječnik OOXML-a), dok BIFF8 pokretač koristi EnableIteration / MaxIterations / MaxIterationChange (usklađeno s nazivima zapisa [MS-XLS]). Obje trojke opisuju ista tri gumba — prekidač za uključivanje/isključivanje, ograničenje iteracije i deltu konvergencije — s istim zadanim vrijednostima: isključeno, 100 i 0.001
Kada je kružna referenca pogreška, a ne model?
Omogućivanje iteracije nije način da kružne reference nestanu bez upozorenja, i takvo postupanje je zamka. Globalno uključivanje Iterate pretvara svaki slučajni ciklus — one koje je zadani kôd pogreške trebao uhvatiti — u tiho konvergirani ili tiho nekonvergirani broj. Pravilo je obrnuto: držite Iterate na False kao uobičajeni način rada kako bi se stvarne pogreške autora i dalje prikazivale kao lxErrorRef, a omogućite iteraciju samo na radnim knjigama čija je kružnost namjerna i razumljiva
Kada se ciklus ipak pojavi, a niste sigurni o kojoj se vrsti radi, tragač procjene formula je alat koji ih razlikuje: pratite sumnjivu formulu i lanac referenci koji se savija natrag na samog sebe postat će vidljiv korak po korak, tako da možete odlučiti kodira li stvarnu povratnu petlju ili zalutalu samoreferencu. Također pomaže upamtiti da ćelija ciklusa može pozvati bilo koju ugrađenu funkciju na putu oko petlje — isti kalkulator koji rješava inženjersku formulu ili formulu s kompleksnim brojevima procjenjuje članove ciklusa u svakom prolazu — pa je model koji divergira često problem s formulom unutar petlje, a ne problem s postavkama iteracije
Praktični kontrolni popis je kratak. Potvrdite da petlja ima stvarnu fiksnu točku prije nego što se oslonite na iteraciju; držite IterateDelta dovoljno uskom kako bi "konvergirano" značilo ono što je vašem modelu stvarno potrebno; i nakon Recalculate za koji očekujete da će konvergirati, provjerite poznati izlaz umjesto da vjerujete samo kodu lxOk, jer taj kôd ne može razlikovati konvergenciju od dosegnutog ograničenja iteracija
Iterativni izračun kružnih referenci dio je XLSX pokretača u HotXLS Delphi Excel komponente, uz inkrementalni ponovni izračun grafikona ovisnosti na kojem se gradi te OOXML i BIFF8 trajnost koja prenosi postavku u svaku datoteku koju zapišete. Za financijske i inženjerske modele koji su kružni s namjerom, to predstavlja razliku između koda pogreške i odgovora