自前の Excel 計算エンジンを別の表計算ソフトで検算する:LBO 入門ブックの1,539セル一致から47テーマの差分テストへ
自前の Excel 計算エンジンを別の表計算ソフトで検算する:LBO 入門ブックの1,539セル一致から47テーマの差分テストへ
参照をたどるビューアーは、ブックの式を自前のエンジンで計算して見せている。 その答えは、Excel が出す答えと同じなのか。 前日にカレンダーへ入れておいた突き合わせに、今日取りかかった。 対象は、LBO モデルを仕訳で読む で広げた LBO 入門のブックである。
午後は、11/10 から止まっていた連結パッケージの手本のブックを再開した。 依存パッケージの脆弱性の警告35件も、別のセッションで調べた。
LBO 入門のブックを ibisOffice で計算し直す
ブックを xlsx に書き出し、別の表計算ソフトの ibisOffice で開いて計算させる。 その値と自前のエンジンの値を、式のセルごとに照らす段取りだ。
最初の数手でつまずいた。
SHA-256 が一致するのを確かめてから、展開は PowerShell でやり直させた。
計算させたブックの保存は、open: 経由で開いたときに通った。
保存したファイルにキャッシュ値(計算結果)が入っているかも確かめさせた。
照らしてみると、LBO のパターンだけ、相対誤差の最大が0.25と出た。 どのセルかを調べさせると、値が0に近いセルに引っぱられた数字だった。 0に近いセルでは、わずかな差でも相対誤差が大きく出る。 紛らわしいので、0付近を除いて最大値を出すようにスクリプトを直させた。
最終的に、ibisOffice で計算し直した値は、3つのパターンとも式のセル513個すべてでビューアーのエンジンの値と一致した(計1,539個)。 ブックの式は1つも直していない。
この日は、どのセッションにも、別のセッションのコミット中(index.lock)で待つ場面があった。
「全部のエンジンで再計算したテスト」という見立て
結果を受けて、筆者はこう聞き返した。 「全部のエンジンで再計算したみたいなイメージになるんでしたっけ?テストコードっぽく」。 今回作ったエンジンが正確だったことの確認になっているのか、という問いも重ねた。 そうであれば、ブックを作るたびにこの検証を回すステップを、スキルにしておいたほうがよさそうだとも伝えた。
返ってきた答えは「はい、そのイメージで合っています」だった。 同じ xlsx を、作り手の違う別の計算エンジン(ibisOffice と Excel 本体)に計算させ、自前のエンジンの答えと全セルで照らし合わせた。 テストの言い方では「差分テスト」と呼ぶ。 このブックについては、自前のエンジンが Excel と同じ計算をしていることの確認になっている。 ただし、確かめられた範囲には限りがある、とも添えられていた。
ほかの冊でも同じ計算をしているのか
筆者が y と返し、突き合わせのスクリプトを全部の冊に使えるよう一般化させた。
Excel 本体と ibisOffice の両方で回せる作りにした。
スキル excel-trace-viewer には、手順の5として「最後に Excel 本体(または ibisOffice)で計算し直して突き合わせる」ステップを足させた。
同じリポジトリでは別表を扱うセッションも動いていたので、そちらには「その3ファイルは触らない」と返信させた。
途中でセッションが止まり、再開してからスクリプトで流し直した。 LBO のブックは、前回の手作業と同じ結果になった。 3つのパターンでビット単位まで同じだったセルは511個、510個、511個で、残る7個はどれも IRR だった。 反復計算の収束先が違うだけで、差は最大6.5e-12である。
7テーマの152件を流すと、照合できた112件のうち110件が一致し、2件に差が出た。 反復計算の冊40件は照合の対象から外したので、未検証のままだ。
差の出た冊を追うと、直す所はエンジンと変換器の両方にあった。
- 配列数式の印:配列数式を使う演習の冊は、変換の途中の型に配列数式の印が無かった。型に印を足して引き継がせると、式のセル3,268個すべてが Excel とビット単位で同じになった
- PMT と PV の精度:エンジンの PMT を直すと、真の値との相対誤差は1.7e-16になった(直す前は3.3e-14)。PV も4e-16である。返済計画の冊は差が98から1に減り、最後は OK になった(相対差の最大1.8e-13)
- 利率が−0.9のとき:精度を保ったまま、利率が−0.9のときも有限の値が返るように戻させ、このケースもテストに足させた
Excel を Quit したあともプロセスが残る件は、回避はしたが原因がわからないので、Issue に残させた。
初めて流した、PMT を使う不動産のテーマでは、4冊中3冊に差が出た。 10テーマ160件を Excel 本体で流し直すと、照らした120件のうち、NG は不動産の2冊だけになった。 筆者は「直していいっすよ」と返した。
不動産の2冊は、複合キーの MATCH を作っている所を探させ、変換器を直して JSON を作り直させた。 Excel が計算中で呼び出しを断ったときに、待ってやり直す処理も足させた。 ある冊は差が13,345から72まで減ったが、相対差0.023のずれが残ったので、中身をさらに見させた。 最後に10テーマ160件を流し直すと、照らした120件のうち116件が OK、4件が WARN、NG は0件になった。
47テーマ1,059件へ広げる
ここまで来て、筆者は「IbisOfficeを使った検証は結構意味があったってことっすよね、総括すると」と聞いた。 返ってきた総括は、この検証には大きな意味があった、というものだった。 ただ、効いたのは ibisOffice そのものより、「別の表計算ソフトに計算させて全部のセルを照らす」というやり方のほうだという。 コミットは、別のセッションの分も含めた48件をまとめて push させた。
続けて「ほかのテーマにも流して」と頼んだ。 残りは約40テーマ、900冊前後ある。 メモリを食わないよう、テーマは1つずつ順に流す。 16:00 に予定していた再起動に間に合うよう、小さいテーマのまとまりから裏で回させた。
途中で失敗した冊は、流し直すと再現した。 Excel の修復モードで開いて記録を見させると、workbook.xml の中のワークシートのプロパティが壊れていた。 止まっていた4テーマを流し直すと、3テーマは全部 OK で、法人税の申告書のテーマで1冊だけ NG が出た(循環参照を扱う冊)。
最終的に、全47テーマ1,059件のうち、照らした1,019件は1,014件が OK、4件が WARN、1件が NG だった。 反復計算の冊40件は、ここでも対象外である。
連結パッケージの手本のブックに PKG_ を付ける
14時すぎ、連結パッケージの手本のブック(1冊目)を、11/10 に止めた所から再開した。
パッケージの14枚のシート名の頭に PKG_ を付けさせた(PKG_P社_残高、PKG_S2社_純資産の変動 など)。
シート名を作っているのはコードの1か所だけだったので、直したのもそこだけで済んだ。
頭の文字は定数にしてある。
シート名を文字で直書きしていたのは計画書の1行だけで、そこもそろえた。
キャンバスの並びと左の一覧の見出しはまだ作っていないので、直す所はなかった。
Test には「パッケージのシートだけが PKG_ で始まる」の検査を足させた。 画面の確認では、Chrome DevTools が2回とも接続待ちで止まった(既知の issue と同じ症状)。 そこで、Playwright のヘッドレス Chromium に切り替えて確かめさせた。 Codex のレビューでは指摘がなかった。
そのあと、筆者は構想を貼り付けて伝えた。 その構想は、計画書の§10に「未了」として残させた。 コードは触っておらず、今の1冊目も消さずに残してある。
Dependabot の35件を調べる
脆弱性の調査には、LBO のセッションから別のセッションを立ち上げてもらった。
ところが、最初に立ち上がったのは新規のセッションではなく、こちらの会話の続きだった。
筆者は「コンテキストも73%引き継いでる」と気づき、新規で立ち上げ直すよう頼んだ。
起動のスクリプトを直させ(--resume が付いていても外して新規で起動し、子にも渡さない)、新規で立ち上げ直させた。
この立ち上げの手順は、スキルにさせた。
調査のセッションに頼んだのは、警告を調べて直し方の案を出すところまでである。 依存パッケージを実際に上げるのは、筆者の確認を取ってからにした。
critical と high から中身を確かめさせると、認証ライブラリの警告は、該当する機能を使っていないとわかった。 依存の利用元をたどり、本番の Worker に実際に入っているかも確かめさせた。 認証ライブラリを上げたときに壊れる点と、必要になる DB の変更も洗い出させた。 結果はメモにまとめて、Codex のレビューを通した。 指摘(案 B で切り替えている間の扱い)は妥当だったので、メモに反映させた。
判断用のフォームを Chrome で開こうとしたときは、DevTools MCP の接続がタイムアウトした(前日と同じ症状)。 もう1回試すと接続できた。
調べた結果、35件のうち、このリポジトリで実際に攻撃が成り立つものは見つからなかった。 筆者は13:28に、判断フォームの5件すべてで推奨の A を選んだ(コメントなし)。 見送る3件は、理由を付けて警告を却下させた。
ところが判断3については、回答の前提になった Claude の見立てが間違っていたと報告を受けた。 筆者は確認し直して y と返し、判断3は案 A に統合する形でメモを直させた。
調査の途中では、下書きのガードに直すべき穴の候補も見つかっていた。 これは計画書にまとめさせ、Codex のレビューを2回通した。 計画書で判断を求められた2件にもフォームで答え、回答を計画書に記録させた。
依存パッケージを上げる作業と下書きのガードを直す作業は、どちらも再起動のあとに、別々の worktree で進める。 再起動のあとにやることは3つあり、手順はメモに書かせてある。
学び
- 1冊の一致は、その1冊についての確認にとどまる。冊を広げるたびに、配列数式の印、PMT と PV の精度、不動産の冊の変換と、別々の不備が出てきた
- 相対誤差の最大値は、0に近いセルが1つあるだけで0.25まで跳ね上がった。指標の数字を見たら、まずどのセルの数字かを確かめる
振り返り
最初に照らしたのは、LBO 入門のブック1冊の式のセル1,539個だった。 最後は47テーマ1,059件まで広げ、照らした1,019件のうち1,014件で一致を確かめた。 ただ、残る NG の1冊と、照合の対象から外した反復計算の40冊には、まだ答えが出ていない。