A szándékos körkörös hivatkozások Delphiben történő kiszámításához a HotXLS iteratív számítást kínál az XLSX motorján: ha a TXLSXWorkbook.Iterate tulajdonságot True értékre állítja, a TXLSXWorkbook.Recalculate végigmegy minden észlelt hivatkozási cikluson egy fix pont felé – legfeljebb az IterateCount lépésszámig, vagy amíg minden cella változása kisebb nem lesz, mint az IterateDelta –, ahelyett, hogy #REF! hibát adna vissza és feladná
Ez a különbség többet számít, mint amit az egyetlen logikai érték sugall. Ugyanaz a motor, amely felismeri a hivatkozási ciklust és megtagadja az azon való végtelen ciklust, egyetlen tulajdonság átkapcsolásával szándékosan kiértékeli azt a ciklust, amíg meg nem nyugszik (stabilizálódik). A két viselkedés tisztán tartása – mikor hiba a ciklus, amit jelenteni kell, és mikor egy megoldandó modell – a cikk teljes témája
Miért ad hibát alapértelmezés szerint a körkörös hivatkozás?
Alapértelmezés szerint a HotXLS minden hivatkozási ciklust szerkesztési hibaként kezel, és jelenti azt ahelyett, hogy kiszámítaná. A TXLSXWorkbook.Recalculate felépít egy képletfüggőségi gráfot, kiértékel minden képletcellát topológiai sorrendben, és abban a pillanatban, amikor ciklust talál – azokat a csomópontokat, amelyeket a topológiai rendezés során soha nem lehet felszabadítani –, a lxErrorRef hibát adja vissza. A ciklus tagjai megtartják korábbi gyorsítótárazott értékeiket; a cikluson kívüli összes képlet továbbra is normálisan értékelődik ki. Ennek a gráfnak a működését, és azt, hogy a ciklus tagjait miért hagyja ki a motor ahelyett, hogy ciklusba lépne velük, a képletek inkrementális újraszámításáról és a függőségi gráfról szóló kísérő cikk tárgyalja
Az alapértelmezett beállítás a biztonságos, mert a legtöbb ciklus hiba: egy összesítő sor véletlenül bekerül a saját SUM tartományába, vagy egy másolás-beillesztés, amely önmagára helyezett át egy hivatkozást. Egy hangos hibakód újraszámításkor pontosan az, amit ezekben az esetekben szeretne. Azonban a modellek egy konkrét és fontos osztálya szándékosan körkörös. A kamatos kamat ütemezések, a részlegek közötti körkörös költség- vagy rezsiallokációk és az egyenleg-alapú díjkalkulációk mind olyan értéket írnak le, amely jogosan csatol vissza a saját bemeneteire – és az Excel is csak akkor számítja ki őket, ha a felhasználó bejelöli a Fájl → Opciók → Képletek → Iteratív számítás engedélyezése opciót
Hogyan engedélyezhető az iteratív számítás a HotXLS-ben?
A HotXLS ezt az Excel jelölőnégyzetet három tulajdonsággal tükrözi a TXLSXWorkbook-on: az Iterate bekapcsolja a módot, és ha ez True, az észlelt ciklust egy iteratív megoldó kapja meg ahelyett, hogy lxErrorRef-et produkálna. Tekintsük a klasszikus kamatos kamat cellapárt, ahol a záróegyenleg a kamattól, a kamat pedig az egyenlegtől függ
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;A B3 a B4-re, a B4 pedig a B3-ra hivatkozik, így a függőségi gráf kéttagú ciklust jelez. Ha az Iterate az alapértelmezett False értéken marad, ez a pár lxErrorRef hibát adna vissza, és egyik cella sem állna be. Ha True, a Recalculate beülteti a ciklust a jelenlegi gyorsítótárazott értékekből, és ciklusról ciklusra újraértékeli a tagjait, az egyes menetek kimeneteit visszaforgatva a következő menetek bemeneteiként, amíg a számok meg nem állnak. Itt a zárt forma principal / (1 - rate), így az egyenleg 1052,63-on, a kamat pedig 52,63-on állapodik meg – ugyanazokkal a számokkal, mint amiket az Excel produkál engedélyezett iteráció mellett
Mi állítja meg az iterációt?
Két független leállási feltétel határolja be a megoldót, és mindkettő megértése szükséges ahhoz, hogy a modell ne adjon hibát vagy ne pörögjön végtelenül. Az IterateCount a kemény korlát arra vonatkozóan, hogy a ciklus tagjai hányszor értékelődnek újra; alapértelmezett értéke 100, megegyezően az Excellel. Az IterateDelta a konvergencia-küszöb: minden menet után a megoldó megméri a legnagyobb numerikus változást az összes cikluscellán, és amint ez a maximális változás az IterateDelta alá esik – alapértelmezetten 0,001 –, a menetek ciklusa korán megszakad. Amelyik feltétel előbb teljesül, az véget vet az iterációnak
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;
Egy következményt érdemes nyíltan kimondani, mert ez a funkció valódi határa. Ha a korlátot úgy éri el a rendszer, hogy a változás nem csökken az IterateDelta alá, a SolveCycleIteratively nem vált ki hibát – lxOk-ot ad vissza, és a cellákat az utolsó iterációs értékükön hagyja, pontosan úgy, ahogy az Excel is leírja a legutóbb kiszámított számokat, amikor a saját iterációs korlátját konvergencia nélkül éri el. Tehát a sikeres visszatérési kód a Recalculate metódustól iteratív módban azt jelenti, hogy „a megoldó lefutott”, nem pedig azt, hogy „a megoldó konvergált”. Egy olyan modell, amelynek visszacsatolási ciklusa divergál vagy oszcillál, csendben elfogyasztja az összes IterateCount menetet, és olyan számokat ad vissza, amelyek egyáltalán nem jelentenek fix pontot, és semmilyen kivétel nem jelzi a különbséget
Hogyan mentődik a beállítás XLSX és XLS fájlokba?
Az iteratív számítási beállítások mindkét táblázatkezelő formátumban megmaradnak, így az Excelben megnyitott munkafüzet úgy viselkedik, ahogy azt a Delphi kódja konfigurálta. Az XLSX oldalon az író csak akkor bocsátja ki az OOXML <calcPr> elemet, ha az Iterate értéke True, és kihagy minden olyan attribútumot, amely még az alapértelmezett értékén van, hogy a kimenet minimális maradjon: az alapértelmezéseket használó munkafüzet csak a <calcPr iterate="1"/>-et írja ki, míg az iterateCount csak akkor jelenik meg, ha eltér a 100-tól, és az iterateDelta is csak akkor, ha eltér a 0,001-től. Megnyitáskor a TXLSXWorkbook ugyanazt a három attribútumot olvassa vissza, így az oda-vissza út szimmetrikus
A régi BIFF8 (.xls) motor, a TXLSWorkbook a megfelelő állapotot három különálló rekordon keresztül hordozza egy eltérő tulajdonság-hármas alatt. Az EnableIteration a CalcIter rekordra képeződik le ($0011, [MS-XLS] §2.4.33), a MaxIterations a CalcCount rekordra ($000C, [MS-XLS] §2.4.31), és a MaxIterationChange a CalcDelta rekordra ($0010, [MS-XLS] §2.4.32). A beállító metódusok kikényszerítik a specifikáció tartományait – a CalcCount-nak az 1..32767 tartományba kell esnie, így a MaxIterations korlátozva van, a negatív MaxIterationChange pedig visszaugrik a 0,001-es alapértelmezésre. Állítsa be őket egy betöltött .xls munkafüzeten, és a három számítási rekord hűen kiíródik mentéskor
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;Vegye figyelembe a szándékos elnevezési megosztást: az XLSX motor az Iterate / IterateCount / IterateDelta szavakat használja (az OOXML szókincs), míg a BIFF8 motor a EnableIteration / MaxIterations / MaxIterationChange szavakat (az [MS-XLS] rekordnevekhez igazodva). Mindkét hármas ugyanazt a három gombot írja le – egy be/ki kapcsolót, egy iterációs korlátot és egy konvergencia-deltát –, azonos alapértelmezett ki, 100 és 0,001 értékekkel
Mikor hiba a körkörös hivatkozás a modell helyett?
Az iteráció engedélyezése nem a körkörös hivatkozási figyelmeztetések eltüntetésének a módja, és ennek a kezelésnek a csapdája. Az Iterate globális bekapcsolása minden véletlen ciklust – amelyeket az alapértelmezett hibakódnak kellene elcsípnie – csendben konvergált vagy csendben nem konvergált számmá alakít. A fegyelem ennek az ellenkezője: tartsa az Iterate értéket False-on normál módként, hogy a valódi szerkesztési hibák továbbra is lxErrorRef-ként jelentkezzenek, és csak olyan munkafüzeteken engedélyezze az iterációt, amelyek körkörössége tervezett és érthető
Ha megjelenik egy ciklus, és nem biztos benne, melyik fajta, a képlet-kiértékelés nyomkövető az az eszköz, amely megkülönbözteti őket: kövesse nyomon a gyanús képletet, és az önmagába visszahajló hivatkozási lánc lépésről lépésre láthatóvá válik, így eldöntheti, hogy valós visszacsatolási kört vagy egy eltévedt önhivatkozást kódol-e. Az is segít, ha észben tartja, hogy egy ciklusos cella meghívhat bármilyen beépített függvényt a kör bejárása során – ugyanaz a kalkulátor, amely felold egy mérnöki vagy komplexszámos képletet, minden menetben kiértékeli a ciklus tagjait is –, így a divergáló modell gyakran a körön belüli képletprobléma, nem pedig iterációs-beállítási probléma
A gyakorlati ellenőrzőlista rövid. Győződjön meg arról, hogy a körnek van valódi fix pontja, mielőtt az iterációra támaszkodna; tartsa az IterateDelta-t elég szorosan ahhoz, hogy a „konvergált” azt jelentse, amire a modelljének szüksége van; és egy olyan Recalculate után, amelytől elvárja a konvergálást, ellenőrizze szúrópróbaszerűen a ismert kimenetet, ahelyett, hogy önmagában bízna a lxOk-ban, mivel ez a kód nem tudja megkülönböztetni a konvergenciát az elért iterációs korláttól
Az iteratív körkörös hivatkozás számítás az XLSX motor része a Delphihez készült HotXLS Delphi Excel Component-ben, az inkrementális függőségigráf-újraszámítás mellett, amelyre épül, valamint az OOXML és BIFF8 perzisztencia mellett, amely a beállítást minden kiírt fájlba átviszi. A szándékosan körkörös pénzügyi és mérnöki modellek esetében ez a különbség egy hibakód és a válasz között