LlosLabソフトウェア開発ブログPDF、Delphi、およびドキュメントソフトウェア開発者向けのエンジニアリングノート。

技術記事

Delphiで循環参照を反復計算により解く

HotXLS Delphi Component

意図した循環参照をDelphiで計算するために、HotXLSはXLSXエンジンで反復計算を公開しています。TXLSXWorkbook.IterateをTrueにすると、TXLSXWorkbook.Recalculateは#REF!を返して諦める代わりに、検出した参照の循環をそれぞれ不動点へ向けて歩きます——IterateCount回の反復まで、あるいはすべてのセルの変化量がIterateDeltaを下回るまでです

この違いは、たった1つの真偽値が示唆する以上に重要です。参照の循環を検出してそこを回るのを拒む同じエンジンが、プロパティを1つ切り替えるだけで、その循環を意図的に落ち着くまで評価するようになります。この2つの振る舞い——循環が報告すべき欠陥であるときと、解くべきモデルであるとき——を混ぜないことが、この記事の全主題です

循環参照はなぜ既定でエラーになるのか

既定でHotXLSは、あらゆる参照の循環を作成上の誤りとして扱い、計算するのではなく報告します。TXLSXWorkbook.Recalculateは数式の依存関係グラフを作り、すべての数式セルをトポロジカル順に評価し、循環を見つけた瞬間にlxErrorRefを返します——トポロジカルソートの過程で決して解放されないノードのことです。循環に属するセルは以前のキャッシュ値を保ち、循環の外の数式はすべて通常どおり評価されます。このグラフの仕組みと、循環のメンバーが繰り返されるのではなく飛ばされる理由は、対になる記事数式の増分再計算と依存関係グラフで扱っています

既定が安全側なのは、循環のほとんどが不具合だからです。集計行がうっかり自分自身のSUMの範囲に取り込まれていたり、コピーと貼り付けで参照が自分自身へずれていたり。そうしたものには、再計算のときに大きな声で出るエラーコードこそが望ましいものです。しかし、特定の重要な種類のモデルは意図して循環しています。複利の計算表、部門間で循環する原価や間接費の配賦、残高に応じた手数料の計算は、どれもある値が正当に自分自身の入力へ戻ってくる様子を記述しており——Excelがそれらを計算するのは、利用者がファイル → オプション → 数式 → 反復計算を行うにチェックを入れた後だけです

HotXLSで反復計算を有効にするには

HotXLSはそのExcelのチェックボックスをTXLSXWorkbook上の3つのプロパティで写し取っています。Iterateがモードを入れるもので、Trueのとき検出された循環はlxErrorRefを生む代わりに反復ソルバーへ渡されます。期末残高が利息に依存し、利息が残高に依存する、複利の定番のセルの組を考えてみましょう

HotXLSがDelphiで循環参照を解決する図。IterateがFalseならRecalculateはlxErrorRefを返し、Trueなら残高と利息の循環を反復ソルバーへ通してB3とB4を収束させる様子
IterateがFalseならHotXLSは循環をlxErrorRefとして報告し、Trueなら同じ循環がB3とB4を不動点へ収束させるソルバーの問題になります
var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
begin
  Book := TXLSXWorkbook.Create;
  try
    Sheet := Book.Sheets.Add('Model');
    Sheet.Cells[1, 2].Value   := 1000;     // B1: 期首の元本
    Sheet.Cells[2, 2].Value   := 0.05;     // B2: 期間あたりの利率
    Sheet.Cells[3, 2].Formula := 'B1+B4';  // B3: 残高 = 元本 + 利息
    Sheet.Cells[4, 2].Formula := 'B3*B2';  // B4: 利息 = 残高 * 利率

    Book.Iterate := True;                   // 反復計算を明示的に有効にする
    if Book.Recalculate = lxOk then
      // B3は1052.63...へ、B4は52.63...へ収束する
      Report(Sheet.Cells[3, 2].Value);
  finally
    Book.Free;
  end;
end;

B3はB4を参照し、B4はB3を参照するので、依存関係グラフは2ノードの循環を報告します。Iterateを既定のFalseのままにすると、この組はlxErrorRefとして返り、どちらのセルも落ち着きません。Trueにすると、Recalculateは現在のキャッシュ値から循環に種を与え、各回の出力を次の回の入力として戻しながら、数値が動かなくなるまでメンバーを繰り返し評価します。ここでの閉じた形はprincipal / (1 - rate)なので、残高は1052.63に、利息は52.63に落ち着きます——反復を有効にしたExcelが出すのと同じ数字です

反復を止めるものは何か

独立した2つの停止条件がソルバーを縛っており、その両方を理解していることが、モデルがエラーになることも回り続けることも防ぎます。IterateCountは循環のメンバーを何回まで再評価するかの上限で、既定は100とExcelに揃えてあります。IterateDeltaは収束のしきい値です。ソルバーは各回の後に循環のすべてのセルにわたる最大の数値変化を測り、その最大値がIterateDelta（既定は0.001）を下回った時点で反復のループを早めに抜けます。先に満たされた方の条件が反復を終わらせます

Delphiにおける HotXLSの反復計算の停止を示す判断の流れ図。各回でセルごとの最大変化量をIterateDeltaと比べて早期に抜け、そうでなければIterateCountの上限で反復が終わり、どちらの終わり方もlxOkを返すこと
各回はセルごとの最大変化量をIterateDeltaと比べ、それが叶わなければIterateCountの予算が尽きた時点で静かに止まり、それでもlxOkを返します
Book.Iterate := True;
Book.IterateCount := 1000;    // 上限：循環を最大1000回まで回す
Book.IterateDelta := 0.0001;  // 収束：全セルの変化が < 0.0001 になったら止める

case Book.Recalculate of
  lxOk:
    // 循環が収束したか、または1000回の上限に達して最後の反復の値を
    // 保っている -- IterateがTrueならどちらの経路もlxOkを返す
    SaveWorkbook(Book);
  lxErrorRef:
    // Iterate = False のときだけ到達する：循環は解かれず報告された
    LogWarning('Circular reference with iteration disabled');
end;

1つの帰結ははっきり述べておく価値があります。これがこの機能の正直な境界だからです。変化量がIterateDeltaを下回らないまま上限に達したとき、SolveCycleIterativelyはエラーを出しません——lxOkを返し、セルには最後の反復の値を保たせます。Excelが自身の反復回数の上限に収束しないまま達したとき、最後に計算した数値を書くのとまったく同じです。ですから反復モードでのRecalculateの成功の戻り値は「ソルバーが走った」という意味であって、「ソルバーが収束した」という意味ではありません。フィードバックのループが発散したり振動したりするモデルは、IterateCount回をすべて静かに消費し、まったく不動点でない数値を返してきますし、その違いを示す例外も出ません

この設定はXLSXとXLSのファイルへどう保存されるか

反復計算の設定はどちらの表計算形式でも永続化されるので、Excelで開いたブックはあなたのDelphiのコードが設定したとおりに振る舞います。XLSX側では、書き出し側がIterateがTrueのときにだけOOXMLの<calcPr>要素を出力し、既定のままの属性は出力を最小限に保つために省きます。既定値のみを使うブックは<calcPr iterate="1"/>だけを書き、iterateCountは100と異なるときにだけ、iterateDeltaは0.001と異なるときにだけ現れます。読み込み時にはTXLSXWorkbookが同じ3つの属性を読み戻すので、往復は対称です

従来のBIFF8（.xls）のエンジンであるTXLSWorkbookは、同等の状態を別のプロパティの3つ組のもとで3つの独立したレコードとして運びます。EnableIterationはCalcIterレコード（$0011、[MS-XLS] §2.4.33）に、MaxIterationsはCalcCountレコード（$000C、[MS-XLS] §2.4.31）に、MaxIterationChangeはCalcDeltaレコード（$0010、[MS-XLS] §2.4.32）に対応します。設定用のメソッドは仕様の範囲を強制します——CalcCountは1から32767の間になければならないのでMaxIterationsは丸められ、負のMaxIterationChangeは既定の0.001へ戻ります。読み込んだ.xlsのブックにこれらを設定すれば、保存時に3つの計算レコードが忠実に書かれます

var
  Book: TXLSWorkbook;   // BIFF8（.xls）のエンジン
begin
  Book := TXLSWorkbook.Create;
  try
    Book.Open('model.xls');
    Book.EnableIteration    := True;    // CalcIter  レコード $0011
    Book.MaxIterations      := 500;     // CalcCount レコード $000C（1..32767へ丸められる）
    Book.MaxIterationChange := 0.0001;  // CalcDelta レコード $0010
    Book.SaveAs('model.xls');           // 3つの計算レコードが往復する
  finally
    Book.Free;
  end;
end;

名前の付け方が意図的に分かれていることに注意してください。XLSXのエンジンはIterate、IterateCount、IterateDelta（OOXMLの語彙）を話し、BIFF8のエンジンはEnableIteration、MaxIterations、MaxIterationChange（[MS-XLS]のレコード名に揃えたもの）を話します。どちらの3つ組も同じ3つのつまみ——オンとオフの切り替え、反復の上限、収束の差——を、オフ、100、0.001という同じ既定値で記述しています

HotXLSにおける反復計算の永続化の対応図。TXLSXWorkbookのプロパティはXLSXのcalcPr属性として、TXLSWorkbookのプロパティはBIFF8のCalcIter、CalcCount、CalcDeltaレコードとして格納され、Delphiでの既定値は同一であること
どちらのエンジンも同じ3つのつまみを公開し、OOXMLはそれをcalcPrの属性として運び、BIFF8はCalcIter、CalcCount、CalcDeltaの各レコードへ詰めます

循環参照がモデルではなく不具合であるのはどんなときか

反復を有効にすることは、循環参照の警告を消す手段ではありませんし、そう扱うことこそが罠です。Iterateを全体的にオンにすると、うっかりできた循環——既定のエラーコードが捕まえるために存在していたもの——のすべてが、静かに収束した数値か静かに収束しなかった数値へ変わります。規律はその逆です。本物の作成ミスがlxErrorRefとして表に出るよう、通常のモードではIterateをFalseに保ち、循環が設計上のものであり理解されているブックに限って反復を有効にしてください

実際に循環が現れ、それがどちらの種類か判断がつかないときに両者を見分ける道具が数式評価のトレーサーです。疑わしい数式をたどれば、自分自身へ折り返す参照の連鎖が1歩ずつ見えるようになるので、それが本物のフィードバックループを表しているのか、迷子の自己参照なのかを判断できます。循環に属するセルはループを一周する途中でどの組み込み関数も呼べること——工学系や複素数の数式を解決するのと同じ計算器が、毎回の反復で循環のメンバーを評価します——も覚えておくと役に立ちます。発散するモデルは反復の設定の問題ではなく、ループの内側にある数式の問題であることが多いからです

実務上の確認事項は短いものです。反復に頼る前に、そのループに本当に不動点があることを確かめること。「収束した」がモデルにとって必要な意味になる程度にIterateDeltaを厳しく保つこと。そして収束するはずのRecalculateの後は、lxOkだけを信じるのではなく既知の出力で正気度を確かめること。あのコードは収束と反復上限への到達を区別できないからです

循環参照の反復計算はHotXLS Delphi ExcelコンポーネントのXLSXエンジンの一部であり、その土台となる増分の依存関係グラフによる再計算や、書き出すすべてのファイルへ設定を運ぶOOXMLとBIFF8の永続化と並んでいます。意図して循環している財務や工学のモデルにとって、これはエラーコードと答えとの違いです