Na výpočet úmyselných cyklických odkazov v Delphi vystavuje HotXLS iteračný výpočet vo svojom engine XLSX: nastavte TXLSXWorkbook.Iterate na True a metóda TXLSXWorkbook.Recalculate prechádza každý detegovaný cyklus odkazov smerom k pevnému bodu — až do počtu prechodov IterateCount, alebo kým sa každá bunka nezmení o menej ako IterateDelta — namiesto toho, aby vrátila chybu #REF! a vzdala to
Tento rozdiel je dôležitejší, než naznačuje jediná hodnota boolean. Rovnaký engine, ktorý deteguje cyklus odkazov a odmietne v ňom slučovať, po prepnutí jednej vlastnosti úmyselne vyhodnocuje tento cyklus, kým sa neustáli. Udržiavanie správneho rozlíšenia týchto dvoch typov správania — kedy je cyklus chybou, ktorú treba nahlásiť, a kedy modelom, ktorý treba vyriešiť — je hlavnou témou tohto článku
Prečo cyklický odkaz predvolene končí chybou?
HotXLS predvolene považuje akýkoľvek cyklus odkazov za chybu autora a nahlási ho namiesto toho, aby ho počítal. Metóda TXLSXWorkbook.Recalculate zostaví graf závislostí vzorcov, vyhodnotí každú bunku so vzorcom v topologickom poradí a v momente, keď nájde cyklus — uzly, ktoré sa počas topologického triedenia nikdy nedajú uvoľniť —, vráti lxErrorRef. Títo členovia cyklu si zachovajú svoje predchádzajúce uložené hodnoty; každý vzorec mimo cyklu sa naďalej vyhodnocuje normálne. Mechanika tohto grafu a dôvody, prečo sa členovia cyklu preskakujú namiesto slučovania, sú popísané v sprievodnom článku o prírastkovom prepočítaní vzorcov a grafe závislostí
Predvolené správanie je to bezpečnejšie, pretože väčšina cyklov sú chyby: súhrnný riadok omylom zahrnutý do vlastného rozsahu SUM, alebo kopírovanie a vkladanie, ktoré posunulo odkaz na seba samého. Výrazný chybový kód v čase prepočtu je presne to, čo v takýchto prípadoch chcete. Avšak špecifická a dôležitá skupina modelov je cyklická zámerne. Plány úročenia úrokov (úroky z úrokov), cyklické alokácie nákladov alebo réžie medzi oddeleniami a výpočty poplatkov odvodené od zostatku — to všetko popisuje hodnotu, ktorá legitímne vstupuje späť do svojich vlastných vstupov —, a Excel ich počíta až vtedy, keď používateľ začiarkne Súbor → Možnosti → Vzorce → Povoliť iteračné výpočty
Ako povoliť iteračný výpočet v HotXLS?
HotXLS kopíruje toto začiarkavacie políčko z Excelu pomocou troch vlastností triedy TXLSXWorkbook: Iterate zapína tento režim a keď je nastavený na True, zistený cyklus sa odovzdá iteračnému riešiteľovi (solveru) namiesto vytvorenia chyby lxErrorRef. Predstavte si klasickú dvojicu buniek s úročením úrokov, kde konečný zostatok závisí od úroku a úrok závisí od zostatku
var
Book: TXLSXWorkbook;
Sheet: TXLSXWorksheet;
begin
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 odkazuje na B4 a B4 odkazuje na B3, takže graf závislostí hlási dvojuzlový cyklus. Ak ponecháte Iterate v predvolenom nastavení False, táto dvojica sa vráti ako chyba lxErrorRef a ani jedna bunka sa neustáli. S hodnotou True metóda Recalculate inicializuje cyklus z aktuálnych uložených hodnôt a prehodnocuje jeho členov prechod za prechodom, pričom výstupy každého prechodu odovzdáva ako vstupy pre ďalší prechod, kým sa čísla neprestanú meniť. V tomto prípade je uzavretý vzorec principal / (1 - rate), takže zostatok sa ustáli na hodnote 1052.63 a úrok na hodnote 52.63 — čo sú rovnaké čísla, aké vyprodukuje Excel s povolenou iteráciou
Čo zastaví iteráciu?
Riešiteľ (solver) je ohraničený dvoma nezávislými podmienkami zastavenia a pochopenie oboch zabráni tomu, aby model buď skončil chybou, alebo sa zacyklil do nekonečna. IterateCount je pevný limit počtu prehodnotení členov cyklu; predvolene je nastavený na 100, čo zodpovedá Excelu. IterateDelta je prah konvergencie: po každom prechode riešiteľ zmeria najväčšiu číselnú zmenu vo všetkých bunkách cyklu a akonáhle táto maximálna zmena klesne pod hodnotu IterateDelta — predvolene 0.001 —, slučka prechodov sa predčasne preruší. Splnenie ktorejkoľvek z podmienok ako prvej ukončí iteráciu
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;
Jeden dôsledok stojí za to uviesť na rovinu, pretože predstavuje skutočnú hranicu tejto funkcie. Keď sa dosiahne limit bez toho, aby zmena klesla pod IterateDelta, metóda SolveCycleIteratively nevyvolá chybu — vráti lxOk a ponechá bunky s ich hodnotami z poslednej iterácie, presne tak, ako Excel zapíše naposledy vypočítané čísla, keď sa dosiahne jeho vlastný iteračný limit bez konvergencie. Úspešný návratový kód z metódy Recalculate v iteračnom režime teda znamená „riešiteľ bežal“, a nie „riešiteľ konvergoval“. Model, ktorého spätná väzba diverguje alebo osciluje, potichu spotrebuje všetkých IterateCount prechodov a vráti čísla, ktoré vôbec nie sú pevným bodom, pričom tento rozdiel neoznačuje žiadna výnimka
Ako sa toto nastavenie ukladá do súborov XLSX a XLS?
Nastavenia iteračného výpočtu pretrvávajú v oboch formátoch tabuliek, takže zošit otvorený v Exceli sa správa tak, ako ho nakonfiguroval váš kód v Delphi. Na strane XLSX zapisovač vygeneruje element OOXML <calcPr> len vtedy, keď je Iterate nastavené na True, a vynechá akýkoľvek atribút, ktorý je stále v predvolenom stave, aby bol výstup minimalný: zošit používajúci predvolené hodnoty zapíše iba <calcPr iterate="1"/>, kým iterateCount sa objaví len vtedy, keď sa líši od 100, a iterateDelta len vtedy, keď sa líši od 0.001. Pri otvorení TXLSXWorkbook načíta rovnaké tri atribúty späť, takže obojsmerný prenos je symetrický
Starší BIFF8 (.xls) engine, TXLSWorkbook, nesie ekvivalentný stav prostredníctvom troch samostatných záznamov pod inou trojicou vlastností. EnableIteration sa mapuje na záznam CalcIter ($0011, [MS-XLS] §2.4.33), MaxIterations na záznam CalcCount ($000C, [MS-XLS] §2.4.31) a MaxIterationChange na záznam CalcDelta ($0010, [MS-XLS] §2.4.32). Settery vynucujú rozsahy špecifikácie — CalcCount must lie in 1..32767, so MaxIterations is clamped, and a negative MaxIterationChange snaps back to the 0.001 default. Set them on a loaded .xls workbook and the three calc records are written faithfully on save
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;Všimnite si zámerné rozdelenie pomenovaní: engine XLSX používa vlastnosti Iterate / IterateCount / IterateDelta (slovník OOXML), zatiaľ čo engine BIFF8 používa EnableIteration / MaxIterations / MaxIterationChange (v súlade s názvami záznamov [MS-XLS]). Obe trojice popisujú tie isté tri gombíky — vypínač (on/off), limit iterácií a deltu konvergencie — s rovnakými predvolenými hodnotami vypnuté, 100 a 0.001
Kedy je cyklický odkaz chybou a nie modelom?
Povolenie iterácie nie je spôsob, ako nechať zmiznúť varovania o cyklických odkazoch, a zaobchádzanie s ním týmto spôsobom je pascou. Globálne zapnutie vlastnosti Iterate premení každý náhodný cyklus — ten, na zachytenie ktorého tam bol predvolený chybový kód — na potichu konvergované alebo potichu nekonvergované číslo. Správny prístup je opačný: ponechajte Iterate v predvolenom nastavení False ako normálny režim, aby sa skutočné chyby autorstva stále prejavovali ako lxErrorRef, a iteráciu povoľte len v zošitoch, ktorých cyklickosť je zámerná a zrozumiteľná
Keď sa objaví cyklus a nie ste si istí, o ktorý typ ide, nástrojom na ich rozlíšenie je trasovač vyhodnocovania vzorcov: trasujte podozrivý vzorec a reťazec odkazov, ktorý sa vracia k sebe, sa vizualizuje krok za krokom, takže sa môžete rozhodnúť, či kóduje skutočnú spätnú väzbu alebo len náhodný odkaz na seba samého. Pomáha tiež pamätať na to, že bunka cyklu môže pri prechode slučkou volať akúkoľvek vstavanú funkciu — rovnaká kalkulačka, ktorá rieši technický vzorec alebo vzorec s komplexnými číslami, vyhodnocuje členov cyklu pri každom prechode —, takže divergujúci model je často problémom vzorca vo vnútri slučky a nie nastavenia iterácií
Praktický zoznam úloh je krátky. Predtým, než sa spoľahnete na iteráciu, overte, či má slučka skutočný pevný bod. Ponechajte IterateDelta dostatočne tesnú, aby „konvergované“ znamenalo to, čo váš model naozaj potrebuje. A po prepočte Recalculate, o ktorom predpokladáte, že konverguje, urobte kontrolu známeho výstupu a nespoliehajte sa len na samotný kód lxOk, keďže tento kód nedokáže odlíšiť konvergenciu od dosiahnutého limitu iterácií
Iteračný výpočet cyklických odkazov je súčasťou XLSX enginu v komponente HotXLS Delphi Excel Component, spolu s prírastkovým prepočtom grafe závislostí, na ktorom stavia, a perzistenciou OOXML a BIFF8, ktorá prenáša toto nastavenie do každého zapisovaného súboru. Pre finančné a technické modely, ktoré sú zámerne cyklické, je to rozdiel medzi chybovým kódom a odpoveďou