Delphiで意図的な循環参照を計算するために、HotXLSはXLSXエンジンで反復計算をサポートしています。TXLSXWorkbook.IterateをTrueに設定すると、TXLSXWorkbook.Recalculateは検出された各循環参照を不動点に向かって計算します。これは、#REF!を返して処理を中断するのではなく、最大でIterateCountで指定された回数まで、またはすべてのセルの変化量がIterateDelta未満になるまで反復処理を実行します
この違いは、単一のブーリアン値が示す以上に重要です。循環参照を検出して無限ループを拒否するのと同じエンジンが、プロパティを1つ切り替えるだけで、その循環参照が収束するまで意図的に評価を実行します。循環参照が「報告すべき不具合」である場合と「解決すべき計算モデル」である場合という、これら2つの挙動を明確に整理することが、この記事のテーマです
デフォルトで循環参照がエラーになるのはなぜですか？
デフォルトでは、HotXLSは循環参照を作成上のミスとして扱い、計算を行わずにエラーとして報告します。TXLSXWorkbook.Recalculateは数式の依存関係グラフを構築し、トポロジカル順序で各数式セルを評価し、トポロジカルソート中に解放できないノードである循環参照を検出した瞬間にlxErrorRefを返します。これらの循環参照内のメンバーは以前にキャッシュされた値を維持し、循環の外側にある数式はすべて通常通りに評価されます。このグラフの仕組みや、循環参照メンバーがループ処理されずにスキップされる理由については、増分数式再計算と依存関係グラフで解説しています
デフォルト動作が安全なのは、ほとんどの循環参照がバグであるためです。たとえば、集計行が誤って自身のSUM範囲に含まれてしまったり、コピー＆ペーストによって参照が自身を指すようになってしまったりするケースです。このような場合には、再計算時に明確なエラーコードが返されるのがまさに望ましい挙動です。しかし、特定の重要な計算モデルのクラスでは、意図的に循環参照が使用されます。複利計算スケジュール、部門間の循環的なコストや間接費の配分、残高連動型の手数料計算などはすべて、値が自身の入力に論理的にフィードバックされる構造になっています。Excelでも、ユーザーが「ファイル」→「オプション」→「数式」→「反復計算を行う」にチェックを入れて初めて、これらが計算されます
HotXLSで反復計算を有効にするにはどうすればよいですか？
HotXLSは、Excelのチェックボックスの動作をTXLSXWorkbookの3つのプロパティで再現しています。Iterateプロパティでこのモードを有効にすると、循環参照が検出された際にlxErrorRefを出力する代わりに、反復ソルバーに処理が渡されます。期末残高が利息に依存し、利息が残高に依存する、典型的な複利計算のセルのペアを考えてみましょう
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;B3がB4を参照し、B4がB3を参照しているため、依存関係グラフは2つのノードによる循環参照を報告します。Iterateがデフォルト値のFalseのままであれば、このペアはlxErrorRefとして返され、どちらのセルも収束しません。Trueに設定すると、Recalculateは現在のキャッシュ値を初期値として循環計算を開始し、各パスの出力を次のパスの入力にフィードバックしながら、数値が変動しなくなるまで各メンバーを繰り返し評価します。このモデルの閉形式は元金 / (1 - 金利)であるため、残高は1052.63に、利息は52.63に収束します。これは反復計算を有効にしたExcelが出力する数値と一致します
反復計算はどのような条件で停止しますか？
ソルバーの動作を制限する2つの独立した停止条件があり、計算モデルがエラーになったり無限ループに陥ったりするのを防ぐために、双方を理解しておくことが重要です。IterateCountは循環参照メンバーが再評価される最大回数の上限であり、デフォルト値はExcelと同じ100です。IterateDeltaは収束のしきい値です。各パスの実行後、ソルバーはすべての循環セルにおける最大の数値変化量を測定し、その最大変化量がIterateDelta（デフォルトは0.001）を下回ると、パスループは早期に終了します。いずれかの条件が最初に満たされた時点で反復計算は終了します
この機能の明確な仕様制限として、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) 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;名前の付け方が意図的に分かれている点に注意してください。XLSXエンジンはIterate / IterateCount / IterateDelta（OOXMLの用語）を使用し、BIFF8エンジンはEnableIteration / MaxIterations / MaxIterationChange（[MS-XLS]のレコード名に準拠）を使用します。どちらのプロパティ群も、オン/オフの切り替え、反復回数の上限、および収束しきい値という同じ3つの設定値を表しており、デフォルト値もそれぞれ無効、100、0.001と共通しています
循環参照が計算モデルではなくバグであるのはどのような場合ですか？
反復計算を有効にすることは、循環参照の警告を消すための手段ではなく、そのような目的で使用することは罠になります。Iterateをグローバルに有効にすると、デフォルトのエラーコードでキャッチすべき偶発的な循環参照のバグが、すべてサイレントに収束または発散した数値に変換されてしまいます。正しいアプローチは逆です。通常のモードではIterateをFalseに維持して、実際の記述ミスがlxErrorRefとして検出されるようにし、循環が意図的に設計され、理解されているワークブックでのみ反復計算を有効にしてください
循環参照が発生し、それがどちらの種類であるか確信が持てない場合は、数式評価トレーサーを使用して調査できます。疑わしい数式を追跡すると、自己を指し示す参照チェーンがステップごとに可視化されるため、それが意図されたフィードバックループであるか、迷い込んだ自己参照であるかを判断できます。また、循環セルはループの途中で任意の組み込み関数を呼び出すことができる点（エンジニアリングまたは複素数数式を処理する計算エンジンが、パスごとに循環メンバーを評価します）も覚えておくと役立ちます。そのため、モデルが発散している場合、原因は反復設定の問題ではなく、ループ内部の数式自体に問題があることがよくあります
実用的なチェックリストはシンプルです。反復計算に依存する前に、ループが論理的な不動点を持っていることを確認してください。また、モデルに必要な精度を保証できるよう、IterateDeltaを十分に厳しく設定してください。そして、収束を期待してRecalculateを実行した後は、単にlxOkを信頼するのではなく、期待通りの値が出力されているかを検証してください。このエラーコードは、収束と反復上限到達の区別ができないためです
反復的な循環参照の計算は、Delphi用のHotXLS Delphi Excel ComponentのXLSXエンジンの一部であり、そのベースとなる増分依存グラフ再計算や、作成するすべてのファイルに設定を反映させるOOXMLおよびBIFF8の永続化機能とともに提供されています。意図的に循環参照を用いる財務モデルやエンジニアリングモデルにおいて、この機能はエラーコードを結果へと変える重要な仕組みです