Iterativní výpočet cyklických odkazů v Delphi s HotXLS

Pro výpočet záměrných cyklických odkazů v Delphi nabízí HotXLS na svém enginu XLSX iterativní výpočet: nastavte TXLSXWorkbook.Iterate na True a TXLSXWorkbook.Recalculate vede každý zjištěný cyklus odkazů k pevnému bodu — nejvýše IterateCount průchodů, nebo dokud se každá buňka nezmění o méně než IterateDelta — místo aby vrátil #REF! a vzdal to

Tento rozdíl je důležitější, než jediný boolean naznačuje. Tentýž engine, který zjistí cyklus odkazů a odmítne se v něm točit, jej s jednou přepnutou vlastností záměrně vyhodnocuje, dokud se neustálí. Udržet obě chování oddělená — kdy je cyklus vada, kterou je třeba nahlásit, a kdy model, který je třeba vyřešit — je celým tématem tohoto článku

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

Ve výchozím nastavení HotXLS bere každý cyklus odkazů jako autorskou chybu a hlásí jej, místo aby jej počítal. TXLSXWorkbook.Recalculate sestaví graf závislostí vzorců, vyhodnotí každou buňku se vzorcem v topologickém pořadí a vrátí lxErrorRef ve chvíli, kdy najde cyklus — uzly, které během topologického řazení nelze nikdy uvolnit. Tito členové cyklu si ponechají své předchozí hodnoty z cache; každý vzorec mimo cyklus se stále vyhodnotí normálně. Mechaniku tohoto grafu a to, proč se členové cyklu přeskakují místo opakovaného průchodu, popisuje doprovodný článek o inkrementálním přepočtu vzorců a grafu závislostí

Výchozí chování je to bezpečné, protože většina cyklů jsou chyby: souhrnný řádek omylem zahrnutý do vlastní oblasti SUM, kopírování a vložení, které posunulo odkaz na sebe sama. Hlasitý chybový kód při přepočtu je přesně to, co u nich chcete. Ale jedna konkrétní a důležitá třída modelů je cyklická záměrně. Plány úroků z úroků, cyklické alokace nákladů nebo režie mezi odděleními a výpočty poplatků odvozené od zůstatku popisují hodnotu, která se legitimně vrací do vlastních vstupů — a Excel je spočítá teprve tehdy, když uživatel zaškrtne Soubor → Možnosti → Vzorce → Povolit iterativní přepočet

Jak zapnout iterativní výpočet v HotXLS?

HotXLS toto zaškrtávací políčko Excelu zrcadlí třemi vlastnostmi na TXLSXWorkbook: Iterate režim zapíná, a když je True, zjištěný cyklus se předá iterativnímu řešiči, místo aby vznikl lxErrorRef. Uvažujme kanonickou dvojici buněk úroku z úroku, 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: 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;                   // zapnutí iterativního 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 na B3, takže graf závislostí nahlásí dvouuzlový cyklus. S Iterate ponechaným na výchozí hodnotě False by se tato dvojice vrátila jako lxErrorRef a ani jedna buňka by se neustálila. S hodnotou True Recalculate naplní cyklus aktuálními hodnotami z cache a znovu vyhodnocuje jeho členy průchod za průchodem, přičemž výstupy každého průchodu vrací jako vstupy dalšího, dokud se čísla nepřestanou hýbat. Zde je uzavřený tvar principal / (1 - rate), takže zůstatek se ustálí na 1052.63 a úrok na 52.63 — stejná čísla, jaká dává Excel se zapnutou iterací

Co iteraci zastaví?

Řešič ohraničují dvě nezávislé podmínky zastavení a pochopení obou je to, co modelu brání buď skončit chybou, nebo se točit donekonečna. IterateCount je tvrdý strop toho, kolikrát se členové cyklu znovu vyhodnotí; výchozí hodnota je 100, stejně jako v Excelu. IterateDelta je práh konvergence: po každém průchodu řešič změří největší číselnou změnu napříč všemi buňkami cyklu, a jakmile tato maximální změna klesne pod IterateDelta — výchozí 0.001 — smyčka průchodů se předčasně ukončí. Iteraci ukončí ta podmínka, která je splněna dřív

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

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

Jeden důsledek stojí za to vyslovit jasně, protože je to poctivá hranice této funkce. Když se dosáhne stropu, aniž změna klesla pod IterateDelta, SolveCycleIteratively nevyvolá chybu — vrátí lxOk a ponechá v buňkách hodnoty z poslední iterace, přesně jako Excel zapíše poslední spočítaná čísla, když narazí na vlastní limit iterací bez konvergence. Úspěšný návratový kód z Recalculate v iterativním režimu tedy znamená „řešič běžel“, ne „řešič konvergoval“. Model, jehož zpětnovazební smyčka diverguje nebo osciluje, tiše spotřebuje všech IterateCount průchodů a vrátí čísla, která vůbec nejsou pevným bodem, a žádná výjimka tento rozdíl neoznačí

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

Nastavení iterativního výpočtu se uchovává v obou tabulkových formátech, takže sešit otevřený v Excelu se chová tak, jak jej váš kód v Delphi nakonfiguroval. Na straně XLSX zapisovatel vydá prvek OOXML <calcPr> jen tehdy, když je Iterate True, a vynechá každý atribut, který je stále na výchozí hodnotě, aby výstup zůstal minimální: sešit s výchozími hodnotami zapíše jen <calcPr iterate="1"/>, zatímco iterateCount se objeví jen tehdy, když se liší od 100, a iterateDelta jen tehdy, když se liší od 0.001. Při otevření TXLSXWorkbook tytéž tři atributy načte zpět, takže obousměrný přenos je symetrický

Starší engine BIFF8 (.xls), TXLSWorkbook, nese ekvivalentní stav ve třech samostatných záznamech 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 ze specifikace — CalcCount musí ležet v 1..32767, takže MaxIterations je omezen, a záporná hodnota MaxIterationChange se vrátí na výchozí 0.001. Nastavte je na načteném sešitu .xls a tři záznamy calc 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;    // záznam CalcIter  $0011
    Book.MaxIterations      := 500;     // záznam CalcCount $000C (omezeno na 1..32767)
    Book.MaxIterationChange := 0.0001;  // záznam CalcDelta $0010
    Book.SaveAs('model.xls');           // tři záznamy calc se zachovají
  finally
    Book.Free;
  end;
end;

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

Kdy je cyklický odkaz chyba, a ne model?

Zapnutí iterace není způsob, jak nechat zmizet varování o cyklických odkazech, a brát to tak je past. Globální zapnutí Iterate promění každý náhodný cyklus — ty, které měl výchozí chybový kód zachytit — v tiše konvergované nebo tiše nekonvergované číslo. Disciplína je opačná: ponechte Iterate na False jako normální režim, aby skutečné autorské chyby stále vyplouvaly jako lxErrorRef, a iteraci zapínejte jen u sešitů, jejichž cykličnost je záměrná a pochopená

Když se cyklus objeví a nejste si jisti, o který druh jde, je trasovač vyhodnocování vzorců nástrojem, který je rozliší: trasujte podezřelý vzorec a řetězec odkazů, který se stáčí zpět na sebe, se zviditelní krok za krokem, takže se můžete rozhodnout, zda kóduje skutečnou zpětnovazební smyčku, nebo zbloudilý odkaz na sebe sama. Pomáhá také pamatovat si, že buňka cyklu může cestou smyčkou volat kteroukoli vestavěnou funkci — tentýž kalkulátor, který řeší inženýrský vzorec nebo vzorec s komplexními čísly, vyhodnocuje členy cyklu při každém průchodu — takže divergující model je často problém vzorce uvnitř smyčky, ne problém nastavení iterace

Praktický kontrolní seznam je krátký. Než se na iteraci spolehnete, ověřte, že smyčka má skutečný pevný bod; držte IterateDelta dost těsné, aby „konvergováno“ znamenalo to, co váš model potřebuje; a po Recalculate, u něhož konvergenci očekáváte, zkontrolujte známý výstup, místo abyste důvěřovali samotnému lxOk, protože tento kód nedokáže rozlišit konvergenci od dosažení stropu iterací

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