これで何ができるか

集計欄が小計・中計・総合計の3段になっても、今回の実測では数式が崩れませんでした。架空の店舗別売上表（エリア→店舗の2階層）と部門別経費表（部署→担当者の2階層）を用意し、それぞれ3段の数式を作らせました。

  • 自由に数式を選ばせた場合——材料2種×5回=10回とも、小計・中計・総合計の3段すべてが正しい数式でした
  • SUBTOTAL関数を禁じても——5回とも崩れませんでした（かなり込み入った式にはなりました）
  • 「自分自身を含めない」という注意書きを外しても——3回とも自分から二重計上を避ける形を選びました
  • 参考——同じ言い回しを2段構造で試した既存記事では、小計欄は10回中3回しか正しくありませんでした
集計欄を3段にしても、いちばん下の段は崩れるかを実測した結果を示す表。すべて「当たった回数／聞いた回数」で、多いほど良い。3段・自由記述で最下位(小計)欄が正しかった回数は材料2種×5回の10回中10回、中間(中計)欄が正しかった回数も10回中10回、総合計欄が正しかった回数も10回中10回。SUBTOTALを禁じても3段すべてが正しかった回数は材料Aの5回中5回。参考として、2段構造の既存実測(別記事)で最下位(小計)欄が正しかった回数は10回中3回。下の枠には、3段に増やした自由記述10回・SUBTOTALを禁じた5回・注意書きを外した3回の合計18回すべてで小計・中計・総合計の3段とも正しい数式になったこと、この材料・この回数の範囲では段数を増やしても最下位の段はさらに悪化しなかったこと（H26棄却）が書かれている。
3段に増やしても、今回はすべて正しい数式でした。

「3段にしても絶対に崩れない」という意味ではありません。同じ指示文で試した2段構造の実測は3割しか合っていませんでした。段数の深さそのものより、その回にAIがどの関数を選ぶかのほうが結果を左右すると考えられます。

この記事は集計欄が2段でも、危ういのは小計欄の続きです。あちらは小計＋総合計の2段で、本文が自ら「集計欄が3段以上になる場合は、また違う結果になるかもしれません」と書いて終わっていました。その3段を実際に作って試したのが今回です。関連する実測に集計表の合計欄は、データの右に置くもあります。

前提
かかる時間 25分
費用 無料。無料のチャットAIで足ります
必要なもの 表計算ソフト（Excel または Google スプレッドシート）と、小計・中計・総合計の3段がある表
プログラミング 不要。この記事の指示文をコピーして貼るだけです

納品先の実データを、そのままAIに貼らないでください。この記事の材料はすべて架空です。

AIへの頼み方

1. 全国チェーンの店舗別売上表で試す

架空の店舗別売上表を用意しました。3エリア（関東・関西・中部）に各2店舗、店舗ごとに3日分の売上が並びます。店舗の小計欄（6か所）・エリアの中計欄（3か所）・全社の総合計欄（1か所）が空欄になっています。

小計欄・中計欄・総合計欄のどれも、それぞれ自分自身を範囲に含めない形で、かつ行が増えても直さなくていい数式にしてください。

5回とも、小計・中計・総合計のすべてをSUBTOTAL関数（集計方法9）で組みました。SUBTOTALには、範囲内にある別のSUBTOTAL結果を自動で無視する性質があります。そのため、中計・総合計の範囲に小計行が含まれていても二重計上されません。5回とも、店舗の小計6個・エリアの中計3個・全社の総合計1個、計10個の数式が正しいことを確認しました。判定はPythonによる機械判定です。

2. 同じ指示文を、部門別経費表でも試す

材料を部門別経費表（3部署×各2担当者、担当者ごとに3費目）に替えて、同じ指示文を試しました。

小計欄・中計欄・総合計欄のどれも、それぞれ自分自身を範囲に含めない形で、かつ行が増えても直さなくていい数式にしてください。

こちらも5回とも満点でした。店舗別売上表と合わせて、材料2種×5回=10回・のべ100個の数式チェックがすべて正しいという結果です。この記事の仮説（3段にすると最下位の段がさらに崩れやすくなる）は棄却します。

うまくいかないときの言い直し方

SUBTOTAL関数を使わせたくないとき

10回とも同じ関数（SUBTOTAL）を選んでいたので、その関数を封じても同じ結果になるか確かめました。

SUBTOTAL関数は使わずに、SUM関数とSUMIF関数だけで、小計欄・中計欄・総合計欄のどれも自分自身を範囲に含めない形で、行が増えても直さなくていい数式にしてください。

5回とも、値としては正しい数式にたどり着きました。ただし手段はかなり込み入っていました。中計欄の工夫は「範囲に明細と小計の両方が入って2倍になることを見越して2で割る」というものです。総合計欄の工夫は「区分列が『売上』の行だけを拾う」というものです。SUBTOTALという便利な関数を封じても、この5回では崩れませんでした。

「自分自身を含めない」の一文を外したいとき

縛りの一文自体が結果を左右しているのか、外して確かめました。

小計欄・中計欄・総合計欄に入れる数式を作ってください。

3回とも、自分から「二重集計を避けるため」とSUBTOTALを選び、崩れませんでした。指示文1・2と同じ数式パターンです。この材料では、注意書きの一文が無くても結果は変わりませんでした。

応用・次の一手

なぜSUBTOTALだと二重計上にならないのか、理由も聞いてみました。

SUBTOTAL関数を使うと、なぜ中計や総合計の範囲に小計を含めても二重計上にならないのか、理由を説明してください。

2回とも正確に説明しました。「自分と同じ種類の集計（SUBTOTAL関数）で計算されたセルを、集計対象から自動的に無視するという仕様」があるためです。架空の例まで示して図解した回もあり、捏造や誤りはありませんでした。

店舗の増減があっても崩れない保守の手順も聞きました。

毎月、新しい店舗がエリアに追加されたり、店舗が無くなったりします。集計表の小計・中計・総合計の数式を、そのたびに直さなくていいように保守したいです。プログラミングはできません。私が新しい店舗を追加するときにやることを、番号を振って箇条書きで教えてください。

2回とも、次の骨格が共通していました。

  • 区切り行・見出し行を用意する
  • SUBTOTALで数式を作る
  • 既存行をコピーして挿入する（合計行のすぐ上には入れない）
  • 削除は行ごと削除する
  • 金額が合っているか検算する

⚠️ この結果は、この2種類の架空データ・合計18回の実測です。「AIは集計表を何段にしても絶対に崩れない」と一般化しないでください。試したのは、次の範囲までです。

  • 階層は3段まで（4段以上は試していません）
  • 各段の呼び方（小計・中計・総合計）を指示文にそのまま書いた場合だけ
  • 材料はいずれも18明細・28行程度の小さな表（数百行規模の実データは試していません）

同じ指示文でも、2段構造では小計欄の的中率が3割でした。この事実を踏まえると、別の日・別のセッションで試したら違う結果になる可能性があります。返ってきた数式は、行を1つ足して合計が動くかを自分の目で確かめてください。

今回の生の返りは docs/evidence/three-tier-subtotal-still-correct.md に全文置いてあります。