SybilGambleyyu

CIを通過できるスプレッドシート変更

重要なスプレッドシートでは、痛い問題はめったに「どのファイルが変更されたか」ではありません。それは「誰かがこの数値の背後にある計算を置き換えたのか、それが何に依存しているのか、そしてモデルが共有される前に私たちは気づいたのか？」です。

Excel は柔軟性が高い理由は、モデルとそのロジックが一緒に存在するためです。その同じ柔軟性が、通常のバージョン履歴を弱いレビューの対象にします：Git はバイナリファイルを見ますが、レビュアーは意図の変化を見る必要があります。

ブック対応の Git diff と 数式異常分析 はすでに問題の価値ある部分に対処しています。私は欠けているマージ境界層を求めました：セマンティックなブック変更を記述し、その目に見える結果を追跡し、レビュアーが読めるルールを強制できる、小さなローカルツールです。

そのツールが FormulaFence で、MIT ライセンスの .xlsx および .xlsm ブック向けローカルファースト CLI です。

制御をマージ境界に置く

FormulaFence は数式やマクロを実行せずに 2 つのブックを比較します。数式から値への置換、数式の変更、シートの可視性と定義名の変更、明示的な外部参照、破損した #REF! 数式、計算設定の変更、マクロペイロードの変更を検出します。

変更された各セルについて、静的に可視な A1 スタイルおよび通常の名前付き範囲依存関係を追跡し、下流の数式セルを決定論的最短経路サンプルで報告します。

formulafence check approved.xlsx candidate.xlsx \
  --policy formulafence.yml \
  --format sarif --output results.sarif

Enter fullscreen mode Exit fullscreen mode

ポリシーはプレーンな YAML です。出力セルを保護し、入力範囲への日常的な編集を制限し、数式オーバーライドを禁止し、下流への影響を制限できます。判断に取って代わるものではありませんが、レビューの期待を可視化し、繰り返し可能にします。

version: 1
rules:
  no_formula_to_value: true
  no_new_external_links: true
  no_new_broken_references: true
  no_new_parser_warnings: true
  no_new_unresolved_references: true
  no_new_dynamic_references: true
  max_downstream_impact: 100

protected_cells:
  - Dashboard!B12

Enter fullscreen mode Exit fullscreen mode

分析に盲点がある場合はフェイルクローズ

FormulaFence 0.3.0 は、通常のブックおよびシートローカル名を静的な A1 宛先で解決するため、これらの参照が影響パスに参加するようになりました。静的分析にはまだ限界があります：INDIRECT、OFFSET、名前付き数式、構造化テーブル参照、アドイン、その他の Excel 機能は依存関係を隠蔽できます。FormulaFence は正当化できないグラフを発明しません。

実用的なセーフガードの 1 つはカバレッジの可視性です。ブックパーサーが完全に解釈できない OOXML 拡張機能に遭遇すると、FormulaFence はカバレッジノートを記録します。1 つを追加する候補は no_new_parser_warnings で拒否できます。プロファイルは未解決の範囲トークンと動的参照関数もリスト化するようになりました。変更は no_new_unresolved_references または no_new_dynamic_references で拒否できます。

おもちゃのファイルを超えてテストする

ユニットフィクスチャは必要ですが、Office ファイルリーダーも実際のブックに対応する必要があります。私は FormulaFence を公開されている Foresight Cap Table and Exit Waterfall Tool で検証しました：18 シート、6,623 の非空セル、4,228 の数式セル。

検査により、サポートされていない OOXML 拡張機能が見つかり、生の依存関係警告を CI 出力に漏らす代わりに構造化カバレッジノートとして記録されました。また、INDIRECT を使用する 36 セルを特定し、モデルの動的参照サーフェスを明示的にしました。ローカルで非分散コピーでは、終了ウォーターフォール数式の 1 つを数値に置き換えると 330 の下流数式セルを追跡しました。スターターポリシーは数式オーバーライドと影響制限の両方を拒否しました。

これらの結果は互換性のデモンストレーションであり、ソースモデルが正しいという主張ではありません。完全な制限と検証記録は FormulaFence リポジトリ にあります。

主張しないこと

FormulaFence は Excel を計算したり、金融モデルが正しいことを証明したりしません。重要なモデルには依然として適格な所有者、承認されたスプレッドシートエンジンでの再計算、独立したレビューが必要です。

しかしレビュープロセスは、少なくとも数式を静かに数値に置き換えることを難しくするべきです。それが FormulaFence が強制するよう設計された、狭いが有用な境界です。

現在のリリースは GitHub 上の FormulaFence 0.3.0 です。この投稿の正規バージョンは sybilgambleyyu.github.io/posts/formulafence.html にあります。