Norint apskaičiuoti tikslines ciklines nuorodas Delphi aplinkoje, HotXLS pateikia iteracinį skaičiavimą savo XLSX variklyje: nustatykite TXLSXWorkbook.Iterate reikšmę į True ir TXLSXWorkbook.Recalculate nukreips kiekvieną aptiktą nuorodų ciklą link fiksuoto taško — iki IterateCount perėjimų arba kol kiekvienas langelis pasikeis mažiau nei IterateDelta —, užuot grąžinęs #REF! klaidą ir pasidavęs
Šis skirtumas yra svarbesnis, nei leidžia manyti viena loginė reikšmė. Tas pats variklis, kuris aptinka nuorodų ciklą ir atsisako jame suktis, pakeitus vieną savybę, sąmoningai įvertins tą ciklą, kol jis nusistovės. Šių dviejų elgsenų suderinamumas — kada ciklas yra defektas, apie kurį reikia pranešti, o kada tai yra sprendžiamas modelis — ir yra visa šio straipsnio tema
Kodėl ciklinė nuoroda pagal nutylėjimą sukelia klaidą?
Pagal nutylėjimą HotXLS traktuoja bet kokį nuorodų ciklą kaip kūrimo klaidą ir praneša apie ją, užuot skaičiavęs. TXLSXWorkbook.Recalculate sukuria formulių priklausomybių grafiką, įvertina kiekvieną formulės langelį topologine tvarka ir grąžina lxErrorRef klaidą tą akimirką, kai randa ciklą — mazgus, kurie niekada negali būti atlaisvinti topologinio rikiavimo metu. Šie ciklo nariai išlaiko savo ankstesnes talpykloje esančias reikšmes; kiekviena formulė už ciklo ribų vis tiek vertinama normaliai. Šio grafiko mechanika ir kodėl ciklo nariai yra praleidžiami, o ne vykdomi cikle, aprašyti susijusiame straipsnyje apie prieaugio formulių perskaičiavimą ir priklausomybių grafiką
Numatytasis nustatymas yra saugus, nes dauguma ciklų yra klaidos: suvestinė eilutė, netyčia įtraukta į savo pačios SUM diapazoną, kopijavimo-įklijavimo veiksmas, kuris pastūmė nuorodą į pačią save. Tokiems atvejams garsus klaidos kodas perskaičiavimo metu yra būtent tai, ko norite. Tačiau tam tikra specifinė ir svarbi modelių klasė yra cikliška tikslingai. Palūkanų skaičiavimo grafikai, netiesioginių išlaidų paskirstymai tarp skyrių ir nuo balanso priklausantys mokesčių skaičiavimai aprašo reikšmę, kuri pagrįstai grįžta į savo pačios įvestis — o „Excel“ jas apskaičiuoja tik tada, kai vartotojas pažymi Failas → Parinktys → Formulės → Įjungti iteracinį skaičiavimą
Kaip įjungti iteracinį skaičiavimą HotXLS aplinkoje?
HotXLS atspindi šį „Excel“ žymimąjį laukelį trimis TXLSXWorkbook savybėmis: Iterate įjungia režimą, ir kai jo reikšmė yra True, aptiktas ciklas perduodamas iteraciniam sprendėjui, užuot sukūrus lxErrorRef klaidą. Paimkime kanoninę palūkanų skaičiavimo langelių porą, kur galutinis balansas priklauso nuo palūkanų, o palūkanos priklauso nuo balanso
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 nurodo į B4, o B4 nurodo į B3, todėl priklausomybių grafikas praneša apie dviejų mazgų ciklą. Jei Iterate paliekama numatytojoje reikšmėje False, ši pora grąžintų lxErrorRef ir nė vienas langelis nepasiektų rezultato. Kai reikšmė yra True, Recalculate užsėja ciklą iš dabartinių talpykloje esančių reikšmių ir iš naujo įvertina jo narius ciklą po ciklo, perduodama kiekvieno žingsnio išvestis kaip kito žingsnio įvestis, kol skaičiai nustoja keistis. Čia galutinė forma yra pagrindinė suma / (1 - tarifas), todėl balansas nusistovi ties 1052,63, o palūkanos — ties 52,63; tai yra tie patys skaičiai, kuriuos sukuria „Excel“ su įjungta iteracija
Kas sustabdo iteraciją?
Dvi nepriklausomos stabdymo sąlygos riboja sprendėją, ir abiejų supratimas apsaugo modelį nuo klaidos arba begalinio sukimosi. IterateCount yra griežta riba, kiek kartų ciklo nariai bus vertinami iš naujo; pagal nutylėjimą ji yra 100, kaip ir „Excel“ programoje. IterateDelta yra konvergencijos slenkstis: po kiekvieno žingsnio sprendėjas matuoja didžiausią skaitinį pokytį visuose ciklo langeliuose, ir kai tik šis didžiausias pokytis nukrenta žemiau IterateDelta (pagal nutylėjimą 0,001), žingsnių ciklas nutrūksta anksčiau laiko. Iteracija baigiama, kai įvykdoma bet kuri iš šių sąlygų
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;
Viena pasekmė yra verta aiškaus paminėjimo, nes tai yra reali šios funkcijos riba. Kai pasiekiama riba, o pokytis nenukrenta žemiau IterateDelta, SolveCycleIteratively nesukelia klaidos — ji grąžina lxOk ir palieka langeliuose paskutinės iteracijos reikšmes, lygiai taip pat, kaip „Excel“ įrašo paskutinius apskaičiuotus skaičius, kai pasiekiama jos pačios iteracijos riba be konvergencijos. Taigi sėkmingas Recalculate grąžinimo kodas iteraciniame režime reiškia „sprendėjas veikė“, o ne „sprendėjas suėjo“. Modelis, kurio grįžtamojo ryšio ciklas skiriasi arba svyruoja, tyliai sunaudos visus IterateCount žingsnius ir grąžins skaičius, kurie apskritai nėra fiksuotas taškas, ir jokia išimtis nepažymės šio skirtumo
Kaip nustatymas išsaugomas XLSX ir XLS failuose?
Iteracinio skaičiavimo nustatymai išlieka abiejuose skaičiuoklės formatuose, so „Excel“ atidaryta darbo knyga elgiasi taip, kaip sukonfigūravo jūsų Delphi kodas. XLSX pusėje rašytuvas sugeneruoja OOXML <calcPr> elementą tik tada, kai Iterate yra True, ir praleidžia bet kokį atributą, kuris vis dar atitinka numatytąją reikšmę, kad išvestis būtų minimali: darbo knyga, naudojanti numatytuosius nustatymus, įrašo tik <calcPr iterate="1"/>, tuo tarpu iterateCount pasirodo tik tada, kai skiriasi nuo 100, o iterateDelta — tik tada, kai skiriasi nuo 0,001. Atidarant dokumentą, TXLSXWorkbook nuskaito tuos pačius tris atributus atgal, todėl perkėlimas pirmyn ir atgal yra simetriškas
Senasis BIFF8 (.xls) variklis, TXLSWorkbook, perneša lygiavertę būseną per tris atskirus įrašus pagal kitą savybių trejetą. EnableIteration susiejamas su CalcIter įrašu ($0011, [MS-XLS] §2.4.33), MaxIterations — su CalcCount įrašu ($000C, [MS-XLS] §2.4.31), o MaxIterationChange — su CalcDelta įrašu ($0010, [MS-XLS] §2.4.32). Nustatymo funkcijos priverstinai taiko specifikacijos diapazonus — CalcCount turi būti 1..32767 ribose, todėl MaxIterations yra ribojamas, o neigiamas MaxIterationChange grįžta prie numatytosios 0,001 reikšmės. Nustatykite juos įkeltoje .xls darbo knygoje ir trys skaičiavimo įrašai bus ištikimai įrašyti saugojimo metu
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;Atkreipkite dėmesį į sąmoningą pavadinimų padalijimą: XLSX variklis naudoja Iterate / IterateCount / IterateDelta (OOXML žodyną), o BIFF8 variklis naudoja EnableIteration / MaxIterations / MaxIterationChange (suderinta su [MS-XLS] įrašų pavadinimais). Abu trejetai aprašo tuos pačius tris valdiklius — įjungimo/išjungimo jungiklį, iteracijų ribą bei konvergencijos pokytį — su tomis pačiomis numatytosiomis reikšmėmis: išjungta, 100 ir 0,001
Kada ciklinė nuoroda yra klaida, o kada — modelis?
Iteracijos įjungimas nėra būdas paslėpti ciklinių nuorodų įspėjimus, o toks požiūris yra spąstai. Visuotinis Iterate įjungimas paverčia kiekvieną atsitiktinį ciklą — tuos, kuriuos turėjo pagauti numatytasis klaidos kodas — į tyliai suėjusį arba tyliai nesuėjusį skaičių. Tvarka yra priešinga: palikite Iterate reikšmę False kaip įprastą režimą, kad tikros kūrimo klaidos vis tiek iškiltų kaip lxErrorRef, ir įjunkite iteraciją tik tose darbo knygose, kurių cikliškumas yra suprojektuotas bei suprantamas
Priemonė, padedanti juos atskirti: sekite įtartiną formulę ir nuorodų grandinė, kuri užsilenkia ant savęs, taps matoma žingsnis po žingsnio, todėl galėsite nuspręsti, ar ji koduoja tikrąjį grįžtamojo ryšio ciklą, ar nuklydusią nuorodą į save. Taip pat naudinga prisiminti, kad ciklo langelis gali iškviesti bet kurią integruotą funkciją pakeliui — tas pats skaičiuotuvas, kuris sprendžia inžinerinę ar kompleksinių skaičių formulę, įvertina ciklo narius kiekviename žingsnyje —, todėl besiskiriantis modelis dažnai yra formulės problema ciklo viduje, o ne iteracijos nustatymų problema
Praktinis kontrolinis sąrašas yra trumpas. Prieš pasikliaudami iteracija įsitikinkite, kad ciklas turi tikrą fiksuotą tašką; laikykite IterateDelta pakankamai griežtą, kad „suėjimas“ reikštų būtent tai, ko reikia jūsų modeliui; o po Recalculate, iš kurio tikitės konvergencijos, patikrinkite žinomą išvestį, užuot pasitikėję vien tik lxOk kodu, nes šis kodas negali atskirti konvergencijos nuo pasiektos iteracijų ribos
Iteracinis ciklinių nuorodų skaičiavimas yra XLSX variklio dalis HotXLS Delphi Excel komponente, kartu su priaugio priklausomybių grafiko perskaičiavimu, kuriuo jis remiasi, bei OOXML bei BIFF8 išsaugojimu, kuris perkelia nustatymus į kiekvieną jūsų įrašomą failą. Finansiniams ir inžineriniams modeliams, kurie yra cikliški tikslingai, tai yra skirtumas tarp klaidos kodo bei atsakymo