HotXLSのDeep RecalcでExcel数式キャッシュを監査

HotXLSが答えるのは、スプレッドシートのパイプラインなら誰もがいずれ向き合う問い、つまりワークブックに保存された数値が、それを生み出した数式とまだ一致しているのかという問いです。CalculateAndVerifyは依存グラフ全体を隔離オーバーレイへ再計算し、各結果をセルに既にキャッシュ済みの値と比較して、食い違いを報告します。デフォルトでは何も変更しません

これが重要なのは、スプレッドシートファイルが数式セルごとに2つのもの、つまり数式と、誰かが最後に計算した値を保存しているからです。Excelは両者を同期し続けます。世界のその他すべては、そうとは限りません。古いライブラリを通ったファイル、部分的な再計算、手で編集されたXMLパート、再計算せずに値だけ書き込んだツールの扱ったファイルは、入力からはもう導けない合計値を当然のように見せてきます。しかもファイル形式のどこにもそれを示すフラグはありません

数式と食い違うキャッシュ値はなぜそれほど危険なのか

通常の読み取り経路のどこからも見えないからです。ビューアで開く、API経由でセルを読む、CSVやPDFへエクスポートする、どれでも手に入るのはキャッシュ済みの数値です。数式は同じセルの中にちゃんとあるのに、誰も両者を比較しません。食い違いが表面化するのは、誰かがそのワークブックをExcelで開いたときだけです。多くの設定ではロード時に再計算が走り、四半期前に稟議の通ったレポートが、突然違う合計を示し始めます

この監査は、その比較を偶然に任せず、意図的で計画的な運用にするために存在します。チェックサム検証のスプレッドシート版です。取り込みパイプラインの中で回せるほど安価で、沈黙したデータ整合性の問題を、手を打てるレポートへ変えられる唯一の手段です

var
  Book: TXLSWorkbook;
  Options: TXLSRecalcAuditOptions;
  Report: TXLSCalculationAuditReport;
  I: Integer;
begin
  Book := TXLSWorkbook.Create(nil);
  try
    Book.LoadFromFile('quarterly-close.xls');
    Options := TXLSRecalcAuditOptions.Default;
    Options.MaxIssues := 500;
    Report := Book.CalculateAndVerify(Options);
    try
      for I := 0 to Report.Count - 1 do
        if Report[I].Kind = xlcaiCacheMismatch then
          Writeln(Report[I].SheetName, '!',
                  Report[I].Row, ':', Report[I].Col, '  ',
                  Report[I].Formula,
                  '  cached=', VarToStr(Report[I].Actual),
                  '  recomputed=', VarToStr(Report[I].Expected));
      if Report.Truncated then
        Writeln('issue budget reached, raise MaxIssues');
    finally
      Report.Free;
    end;
  finally
    Book.Free;
  end;
end;

オーバーロードは3つあり、それぞれ異なる問いに答えます。引数なしのCalculateAndVerifyは食い違いの件数を返します。ヘルスチェックに必要なのはこれだけです。食い違いをout配列で受け取るオーバーロードは、問題のセルを教えてくれます。TXLSRecalcAuditOptionsを取るオーバーロードは完全なTXLSCalculationAuditReportを返します。値が食い違っているだけでなく、監査が何を評価できなかったのかまで知りたいときは、これを選びます

オーバーレイ、そして監査が書き込まない理由

再計算された値はすべて、セルキャッシュではなくオーバーレイへ置かれます。このオーバーレイは、両ワークブックエンジンにおいてセル読み取りコールバックの最前列に差し込まれます。この配置こそが監査を自己整合的にしているのです。B1が再計算され、C1がB1に依存しているとき、C1が見るのは古いキャッシュ値ではなく、今回の監査パスの値です。これがなければ、上流のエラー1つが一度報告された後に吸収され、下流の全セルが誤った入力と一致しているかのように見えてしまいます

再計算の結果がキャッシュと一致したセルは、オーバーレイにすら入りません。これは微細な最適化ではなく、監査を現実的なコストに保つための仕組みです。10万数式を持つクリーンなワークブックではオーバーレイへの書き込みがゼロで、パス全体も完全再計算比1.35倍の予算内に収まります。毎回の取り込みで回せる処理と、四半期に1度しか回せない処理の違いはここで生まれます

評価は依存グラフから導かれた逐次のトポロジカル順に従い、最初に全ノードへダーティマークを付けるため、各セルは入力が揃った後に正確に1度だけ計算されます。保存済みワークブックを監査するのではなく、稼働中のワークブックを最新に保つインクリメンタル機構が欲しい場合は、それは別の仕組みで、インクリメンタル再計算と依存グラフで解説しています

失敗は分類され、ひとまとめにされない

監査が評価できないセルは、値が食い違っているセルとは別の所見です。TXLSCalculationAuditIssueKindはカテゴリを混ぜません。xlcaiCacheMismatchが値の食い違いです。xlcaiMissingFunctionとxlcaiMissingNameは、評価器が実装していないもの、解決できないものに出会ったことを示します。xlcaiUnsupportedArgumentsはサポート対象外の引数形状をカバーします。xlcaiExternalReferenceDeniedとxlcaiExternalReferenceMissingは、ポリシーによる拒否と、ワークブックが存在しないことを分けます。xlcaiCircularReference、xlcaiDataTableSkipped、xlcaiParseFailure、xlcaiCancelled、xlcaiInternalFailureが残りを補います

常識をひっくり返す区別なので、はっきり書いておく価値があります。正のExcelエラーコードは結果であって失敗ではありません。#DIV/0!と正当に評価されるセルは正しく計算できたセルであり、監査はそのエラーをオーバーレイに保存し、他の値と同じようにキャッシュと比較します。意図的なエラーセルだらけのワークブックは所見ゼロ件を生み、値がキャッシュされてからエラーが現れた、あるいは消えたワークブックは、まさに欲しい所見を生みます

循環参照は独自の扱いを受けます。循環内のノードはトポロジカル順に決して入らないため、それぞれがxlcaiCircularReferenceとして個別に報告され、監査は反復ソルバを走らせません。これは意図的な読み取り専用の契約です。反復計算の有無は結果コードの解釈の仕方に影響するものであって、監査の振る舞いには影響しません。反復評価の仕組み自体は、反復計算と循環参照で別途扱っています

失敗チェーンの読み方

数式の評価に失敗したとき、どのセルが失敗したかを知るだけではほとんど役に立ちません。失敗は通常、参照チェーンの3段目あたりで起きているからです。そこで各所見は、最も外側のフレームを先に並べたStack文字列を持ち、形はSheet1!A1 > Sheet1!B2 > Data!C7のようになります。レポートは、たまたま見ていたセルではなく、実際に壊れたセルを指し示します

レコーダには上限があります。MaxStackFramesのデフォルトは64で下限は8です。保持されるのは最も深い失敗チェーンのほうです。失敗が発生した内側のフレームがチェーンを記録し、その後に巻き戻ってくる外側のフレームがそれを上書きすることはありません。どれかのチェーンが予算を超えていた場合、Report.StackTruncatedが立ちます。短いチェーンと、最後まで見えていないチェーンの区別はここでつきます

// デフォルトは読み取り専用。ApplyResultsがオーバーレイをコミットするのは
// 監査が完全に成功した後のみ。ワークブック構造が監査実行中に変わっていたら
// コミットを拒否する書き込みガードの下で行われる
Options := TXLSRecalcAuditOptions.Default;
Options.ApplyResults := True;
Options.AbsoluteTolerance := 0;     // 厳密比較、ドリフトを浮かび上がらせる
Options.RelativeTolerance := 0;
Options.OnProgress := HandleProgress;

Report := Book.CalculateAndVerify(Options);
try
  if Report.Applied then
    Book.SaveToFile('quarterly-close-repaired.xls')
  else
    Writeln('not applied: ', Report.Count, ' issues blocked the commit');
finally
  Report.Free;
end;

procedure THarness.HandleProgress(ASender: TObject;
  ACurrent, ATotal: Integer; var ACancel: Boolean);
begin
  ACancel := FUserRequestedStop;   // 監査は次のノード境界で停止する
end;

監査にワークブックの修復を許すのはどんなときか

監査が失敗系の所見をまったく持たずに戻ってきたときだけです。これこそApplyResultsが代わりに強制してくれる条件です。コミットは完全に成功したパスの後で、キャンセルされておらず、構造ガードも通ったときにだけ起こります。バイナリエンジンはワークブック変更識別子を監視し、OOXMLエンジンはワークシート単位の構造世代をスナップショットします。監査の実行中に何かが動いていたら、その結果が記述しているのはもう存在しないワークブックなので、コミットは拒否されます

意図的な非対称に注目してください。キャッシュの食い違いは適用を妨げません。コミットが修復するために存在しているのはまさにそれだからです。妨げるのは失敗系の所見です。一部の数式が評価できなかったワークブックは半分だけ修復された状態になり、半修復のワークブックは、信用してはいけないと分かっている未修復のワークブックよりひどいからです

トレランスはポリシーの決定であって、デフォルトではない

デフォルトの比較は、絶対トレランス1E-6で相対トレランス無効です。従来の挙動を保ち、4E-7のドリフトを静かに受け入れます。これはたいてい正しい判断です。ファイルを作ったものと現在の評価器との間では、浮動小数点の評価順序の違いにより、長い合計でこの程度の差が普通に出ます。それを整合性の所見として報告するのはノイズです

問いが違うなら、両トレランスをゼロにします。評価器がバージョン間で挙動を変えていないか、サードパーティ製ツールが微妙に違う方法で値を書き換えていないかを突き止めたいときの話です。ゼロなら同じ4E-7のドリフトが見えるようになり、それ以外のすべてもそうです。どの問いを立てているかに基づいてトレランスを選び、その選択をレポートの隣に記録してください。トレランス抜きのレポートは解釈できないのです

絵を完成させるのが、隣り合う2つの機能です。1つの数式がなぜその値を出すのかを知りたいときは、数式評価トレーサーのステップ実行ビューが正しい道具です。キャッシュ済みの値を一切再計算せずにそのまま尊重したいとき、たとえば届いたファイルをそのまま再現しなければならない取り込み経路では、再計算せずキャッシュ済み数式値を読むモードが該当します。監査はその2つの間に座る存在で、キャッシュを信用して安全かどうかを教えてくれます。バイナリエンジンとOOXMLエンジンの両方を備え、HotXLS Delphiスプレッドシートコンポーネントに同梱されています