Iterační výpočty pro cyklické odkazy v Delphi

Pro výpočet záměrných cyklických odkazů v Delphi vystavuje HotXLS iterační výpočty ve svém XLSX enginu: nastavte `TXLSXWorkbook.Iterate` na True a `TXLSXWorkbook.Recalculate` prochází každý detekovaný referenční cyklus směrem k pevnému bodu — až do počtu průchodů `IterateCount`, nebo dokud se každá buňka nezmění o méně než `IterateDelta` —, místo aby vrátila `#REF!` a vzdala to

Tento rozdíl je důležitější, než naznačuje jediná logická hodnota. Stejný engine, který detekuje referenční cyklus a odmítne ve smyčce běžet, bude při přepnutí jedné vlastnosti tento cyklus záměrně vyhodnocovat, dokud se neusadí. Udržení obou těchto chování v patrnosti — kdy je cyklus chybou k nahlášení a kdy je to model k vyřešení — je celým tématem tohoto článku

Proč cyklický odkaz ve výchozím nastavení končí chybou?

Ve výchozím nastavení považuje HotXLS jakýkoli referenční cyklus za autorskou chybu a nahlásí jej, místo aby jej spočítal. `TXLSXWorkbook.Recalculate` sestaví graf závislostí vzorců, vyhodne každou buňku se vzorcem v topologickém pořadí a vrátí `lxErrorRef` v okamžiku, kdy najde cyklus — tedy uzly, které nelze při topologickém třídění nikdy uvolnit. Tyto členské buňky cyklu si ponechají své předchozí cached hodnoty; každý vzorec mimo cyklus se přesto normálně vyhodnotí. Mechanika tohoto grafu a důvod, proč se členové cyklu přeskakují, namísto toho, aby se zacyklili, jsou popsány v doprovodném článku o inkrementálním přepočtu vzorců a grafu závislostí

Výchozí nastavení je bezpečné, protože většina cyklů jsou chyby: souhrnný řádek nechtěně zatažený do vlastního rozsahu `SUM`, kopírování a vkládání, které posunulo odkaz na sebe sama. Hlasitý chybový kód při přepočtu je přesně to, co pro tyto případy chcete. Avšak specifická a důležitá třída modelů je cyklická záměrně. Plány úroků z úroků, cyklické alokace nákladů nebo režijních nákladů mezi odděleními a výpočty poplatků založené na zůstatku, to vše popisuje hodnotu, která legitimně napájí své vlastní vstupy — a Excel je spočítá až poté, co uživatel zaškrtne Soubor → Možnosti → Vzorce → Povolit iterační výpočty

Jak povolit iterační výpočty v HotXLS?

HotXLS zrcadlí toto zaškrtávací políčko z Excelu třemi vlastnostmi třídy `TXLSXWorkbook`: `Iterate` zapíná tento režim, a pokud je True, detekovaný cyklus se předá iteračnímu řešiteli, namísto toho, aby vygeneroval `lxErrorRef`. Zvažte kanonickou dvojici buněk s úroky z úroků, kde konečný zůstatek závisí na úroku a úrok závisí na zůstatku

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
begin
  Book := TXLSXWorkbook.Create;
  try
    Sheet := Book.Sheets.Add('Model');
    Sheet.Cells[1, 2].Value   := 1000;     // B1: počáteční jistina
    Sheet.Cells[2, 2].Value   := 0.05;     // B2: úroková sazba za období
    Sheet.Cells[3, 2].Formula := 'B1+B4';  // B3: zůstatek = jistina + úrok
    Sheet.Cells[4, 2].Formula := 'B3*B2';  // B4: úrok = zůstatek * sazba

    Book.Iterate := True;                   // přihlásit se k iteračnímu výpočtu
    if Book.Recalculate = lxOk then
      // B3 konverguje k 1052.63..., B4 k 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ásí dvouuzlový cyklus. Pokud ponecháte vlastnost `Iterate` na výchozí hodnotě False, vrátí se tato dvojice jako `lxErrorRef` a ani jedna buňka se neusadí. Pokud je True, `Recalculate` dosadí do cyklu aktuální cached hodnoty a reevaluuje jeho členy průchod za průchodem, přičemž výstupy každého průchodu vrací jako vstupy dalšího průchodu, dokud se čísla nepřestanou pohybovat. Zde je uzavřeným tvarem `principal / (1 - rate)`, takže zůstatek se ustálí na hodnotě 1052.63 a úrok na 52.63 — tedy na stejných hodnotách, jaké generuje Excel s povolenou iterací

Co iteraci zastaví?

Dvě nezávislé stop podmínky omezují řešitele a pochopení obou zabrání tomu, aby model skončil chybou nebo se nekonečně točil. `IterateCount` je pevný strop počtu vyhodnocení členů cyklu; ve výchozím nastavení je to 100, což odpovídá Excelu. `IterateDelta` je práh konvergence: po každém průchodu řešitel změří největší změnu hodnoty napříč všemi buňkami cyklu, a jakmile tato maximální změna klesne pod `IterateDelta` — výchozí hodnota 0.001 —, smyčka průchodů se předčasně ukončí. Kterákoli podmínka je splněna dříve, iteraci ukončí

Book.Iterate := True;
Book.IterateCount := 1000;    // pevný strop: nejvýše 1000 průchodů cyklem
Book.IterateDelta := 0.0001;  // konvergence: zastavit, jakmile se každá buňka pohne o < 0.0001

case Book.Recalculate of
  lxOk:
    // cyklus konvergoval, NEBO narazil na limit 1000 průchodů a ponechal si
    // hodnoty z poslední iterace -- obě cesty vracejí lxOk, pokud je Iterate True
    SaveWorkbook(Book);
  lxErrorRef:
    // dosažitelné pouze s Iterate = False: cyklus byl nahlášen, nikoli vyřešen
    LogWarning('Circular reference with iteration disabled');
end;

Jeden důsledek stojí za to uvést na rovinu, protože se jedná o upřímnou hranici této funkce. Když je limit dosažen bez toho, aby změna klesla pod `IterateDelta`, `SolveCycleIteratively` nevyvolá chybu — vrátí `lxOk` a ponechá buňky s hodnotami z poslední iterace, přesně tak, jak Excel zapisuje poslední vypočtená čísla, když je dosažen jeho vlastní limit iterace bez konvergence. Úspěšný návratový kód z `Recalculate` v iteračním režimu tedy znamená „řešitel se spustil“, nikoli „řešitel konvergoval“. Model, jehož smyčka zpětné vazby diverguje nebo osciluje, tiše spotřebuje všech `IterateCount` průchodů a vrátí čísla, která nejsou pevným bodem vůbec, přičemž žádná výjimka na tento rozdíl neupozorní

Jak se toto nastavení ukládá do souborů XLSX a XLS?

Nastavení iteračního výpočtu přetrvává v obou formátech tabulek, takže sešit otevřený v Excelu se chová tak, jak jej nakonfiguroval váš kód v Delphi. Na straně XLSX zapisuje writer prvek OOXML `` pouze tehdy, když je `Iterate` nastaveno na True, a vynechává jakýkoli atribut, který má stále výchozí hodnotu, aby byl výstup minimální: sešit používající výchozí hodnoty zapisuje pouze ``, zatímco `iterateCount` se objevuje pouze v případě, že se liší od 100, a `iterateDelta` pouze tehdy, když se liší od 0.001. Při otevření načítá `TXLSXWorkbook` tytéž tři atributy zpět, takže obousměrná cesta je symetrická

Starší engine BIFF8 (.xls), `TXLSWorkbook`, nese ekvivalentní stav prostřednictvím tří samostatných záznamů pod jinou trojicí vlastností. `EnableIteration` se 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 specifikace — CalcCount musí ležet v rozmezí 1..32767, takže `MaxIterations` je oříznut, a záporná hodnota `MaxIterationChange` se vrací k výchozí hodnotě 0.001. Nastavte je na načteném sešitu .xls a tyto tři záznamy se při uložení věrně zapíší

var
  Book: TXLSWorkbook;   // engine BIFF8 (.xls)
begin
  Book := TXLSWorkbook.Create;
  try
    Book.Open('model.xls');
    Book.EnableIteration    := True;    // CalcIter  record $0011
    Book.MaxIterations      := 500;     // záznam CalcCount $000C (oříznuto na 1..32767)
    Book.MaxIterationChange := 0.0001;  // CalcDelta record $0010
    Book.SaveAs('model.xls');           // tři záznamy výpočtu projdou obousměrným přenosem
  finally
    Book.Free;
  end;
end;

Všimněte si záměrného rozdělení pojmenování: XLSX engine používá `Iterate` / `IterateCount` / `IterateDelta` (slovník OOXML), zatímco engine BIFF8 používá `EnableIteration` / `MaxIterations` / `MaxIterationChange` (zarovnáno s názvy záznamů [MS-XLS]). Obě trojice popisují stejné tři knoflíky — přepínač zapnuto/vypnuto, strop iterací a konvergenční delta — se stejnými výchozími hodnotami vypnuto, 100 a 0.001

Kdy je cyklický odkaz chybou a kdy naopak modelem?

Povolení iterace není způsob, jak nechat zmizet varování o cyklických odkazech, a zacházet s ním tímto způsobem je pastí. Zapnutím `Iterate` globálně přeměníte každý náhodný cyklus — ten, který měl odhalit výchozí chybový kód — v tiše konvergované nebo tiše nekonvergované číslo. Disciplína je opačná: ponechávejte `Iterate` nastavené na False jako normální režim, aby se skutečné autorské chyby stále projevovaly jako `lxErrorRef`, a iteraci povolujte pouze u sešitů, jejichž cykličnost je záměrná a srozumitelná

Když se cyklus objeví a vy si nejste jisti, o jaký typ jde, trasovač vyhodnocování vzorců je nástroj, který je rozliší: trasujte podezřelý vzorec a referenční řetězec, který se skládá sám do sebe, se stane viditelným krok za krokem, takže se můžete rozhodnout, zda představuje skutečnou zpětnou vazbu nebo chybný odkaz na sebe sama. Pomáhá také pamatovat na to, že buňka cyklu může cestou volat jakoukoli vestavěnou funkci — stejná kalkulačka, která řeší technický vzorec nebo vzorec s komplexními čísly, vyhodnocuje členy cyklu při každém průchodu —, takže divergujícím modelem je často problém se vzorcem uvnitř smyčky, nikoli problém s nastavením iterace

Praktický kontrolní seznam

Praktický kontrolní seznam je krátký. Před spoléháním se na iteraci potvrďte, že smyčka má skutečný pevný bod; udržujte `IterateDelta` dostatečně těsnou, aby „konvergováno“ znamenalo to, co váš model potřebuje; a po přepočtu, u kterého očekáváte konvergenci, proveďte namátkovou kontrolu známého výstupu, namísto důvěry v samotné `lxOk`, protože tento chybový kód nedokáže rozlišit konvergenci od dosažení limitu iterace

Iterační výpočet cyklických odkazů je součástí XLSX enginu v komponentě HotXLS Delphi Excel Component, spolu s grafem závislostí inkrementálního přepočtu, na kterém staví, a perzistencí OOXML a BIFF8, která přenáší nastavení do každého souboru, který zapíšete. Pro finanční a technické modely, které jsou cyklické záměrně, představuje rozdíl mezi chybovým kódem a odpovědí