ひとつ、作例で場面を用意しました。提出の10分前です。
経理システムから出した先月分の経費データを、部署ごとに小計を入れながら整えて、いちばん下に「全社合計」としてSUM関数を置く。見た目はとてもきれいで、あとは上司にメールで送るだけ。そう思って最後に数字を眺めたとき、全社合計の欄に「1,640,000」と表示されているのが目に入る。
先月の経費は、ざっくり80万円台だったはずです。いくらなんでも倍はありえない。
慌てて電卓を叩き直し、元データと突き合わせて、原因にたどり着くまでにかかった時間は約40分。結局、提出は30分遅れてしまいます。原因は拍子抜けするほど単純で、部署ごとに入れた「小計行」まで、SUM関数がまとめて足し合わせていたのです。正しい合計は820,000円。表示されている数字は、そのちょうど2倍です。
この記事では、そのときの表をそのまま使って「小計行が混ざった表に、Excelの主な機能がどう反応するのか」を1つずつ試した結果と、状況に合わせた直し方をまとめています。「今日中に数字を出さないといけない人」も「毎月使う表を作り直したい人」も、自分に近いところから読んでみてください。
まずは30秒で確認：あなたの表でも二重計算は起きている？
原因の説明より先に、手元の表が当てはまるかどうかを確かめておきましょう。やることは2つだけです。
1つ目は、目で見るチェックです。表の途中に「営業部小計」「4月計」「小計」のような行がはさまっていて、その下か表の最後に、列全体をまとめたSUM関数が置かれていないかを確認します。この組み合わせがあれば、二重計算の条件はそろっています。
2つ目は、数式を使ったチェックです。空いているセルに、次の2つを入れて見比べてみてください（A列に部署名や「〇〇小計」、C列に金額が入っている表を想定しています）。
=SUM(C2:C13)
=SUMIF(A2:A13,"*小計",C2:C13)*2上の数式は「今の合計」、下の数式は「小計行だけを合計して2倍したもの」です。この2つがぴったり同じ数字になったら、データと小計がまるごと二重に足されています。冒頭の例の表なら、どちらも1,640,000になります。
完全には一致しないけれど、合計が妙に大きい場合は、小計が一部のグループにだけ入っているか、小計とは別に「〇〇計」のような行が混ざっている可能性があります。その場合は、検索条件の「*小計」を「*計」に変えて試してみてください。
実験：小計入りの表に、Excelの4つの機能を使ってみた
ここからは、冒頭の例の表を使って実験していきます。表の中身は次のとおりです。
| 行 | A列（部署） | B列（担当者） | C列（金額） |
|---|---|---|---|
| 2〜4 | 営業部 | 3名分 | 120,000／98,000／66,000 |
| 5 | 営業部小計 | （空白） | 284,000 |
| 6〜8 | 総務部 | 3名分 | 85,000／74,000／37,000 |
| 9 | 総務部小計 | （空白） | 196,000 |
| 10〜12 | 開発部 | 3名分 | 142,000／110,000／88,000 |
| 13 | 開発部小計 | （空白） | 340,000 |
| 14 | 全社合計 | （空白） | =SUM(C2:C13) |
データは9件、小計は3行です。人間の目で見れば、とても整理された表だと思います。では、Excelはこの表をどう扱うのでしょうか。
実験1：SUM関数 → 合計がちょうど2倍になる
冒頭で書いたとおり、全社合計は1,640,000になりました。内訳は、データ9件の合計820,000と、小計3行の合計820,000です。小計はもともとデータを足したものなので、範囲に全部入っていれば、結果は必ず「正しい合計の2倍」になります。
厄介なのは、このときエラーの表示がまったく出ないことです。#VALUE!も#N/Aも出ず、緑の三角マークすら付きません。Excelから見れば、指定された範囲の数値を言われたとおりに足しただけなので、間違いだと判断する材料がないのです。
もうひとつ気をつけたいのが、小計が一部のグループにしか入っていないケースです。たとえば営業部にだけ小計を入れていた場合、合計は820,000＋284,000＝1,104,000になります。2倍ほど極端ではないため、「こんなものかな」と見過ごしやすくなります。
実験2：フィルター → 部署で絞ると小計行が消える
次に、A列にフィルターをかけて「営業部」だけにチェックを入れてみました。表示されたのは営業部のデータ3件だけで、「営業部小計」の行は消えてしまいました。フィルターから見ると、「営業部」と「営業部小計」はまったく別の文字だからです。
逆にB列（担当者）で1人に絞ると、担当者が空白の小計行はすべて非表示になります。一方で「（空白）」にチェックを入れると、今度は小計行だけが表示されます。フィルターをかけるたびに、見えている数字の意味が変わってしまうわけです。
さらに、全社合計のSUM関数はフィルターで非表示にした行も合計するので、絞り込んでも1,640,000のまま動きません。「営業部だけ見ているのに、合計が全社のまま」という状態になります。
実験3：並び替え → 小計が上位3位を独占する
金額の大きい順に並び替えてみたところ、いちばん上に来たのは「開発部小計 340,000」、2番目が「営業部小計 284,000」、3番目が「総務部小計 196,000」でした。個人の経費の最高額は142,000なので、小計行が上位を独占するのは当然といえば当然です。
「経費の多い人トップ3を出して」と頼まれてこの表を並び替えたら、報告書のトップ3が全部小計になってしまいます。しかも並び替えたあとは、小計行がどのデータのまとまりだったのか分からなくなります。元に戻すには、Ctrl+Zで取り消すか、もともと並び順を記録した列を用意しておくしかありません。
実験4：ピボットテーブル → 部署が6つに増える
最後に、この表からピボットテーブルを作り、行にA列（部署）、値にC列（金額）を入れてみました。行ラベルには「営業部」「営業部小計」「総務部」「総務部小計」「開発部」「開発部小計」の6項目が並び、総計は1,640,000です。
もし小計行のA列を空白にしていた場合は、「（空白）」という項目が1つ増える形になります。どちらにしても、ピボットテーブルは小計行を「そういう名前の部署のデータ」として読み込んでしまいます。ピボットテーブルの総計だけ合わないときは、元の表に小計行や合計行が紛れていないかを最初に疑うのが近道です。
なぜExcelは小計行を見分けられないのか
4つの実験で起きたことの原因は、すべて同じところにあります。Excelには「この行はデータ」「この行は集計結果」という、行の役割を区別するしくみがないのです。
私たちは「営業部小計」という文字や太字の書式、背景の色を見て、その行が特別だと自然に理解します。しかしSUM関数が見ているのは「範囲の中にある数値かどうか」だけで、フィルターが見ているのは「セルの中身が条件に合うかどうか」だけです。並び替えやピボットテーブルも同じで、表のすべての行を「同じ種類のデータが1件ずつ並んでいるもの」として扱います。
つまり、小計行を表の途中に入れた瞬間に、Excelから見たその表は「部署名が6種類あり、金額の大きい行が3つ混ざったデータ」に変わってしまうのです。これは小計に限った話ではありません。表の途中にタイトル行を入れるとフィルターや並び替えがおかしくなる理由も、表の途中に「※確認中」などのメモを書くと集計がズレる理由も、根っこは同じ「データではない行が表の中にある」という問題です。
状況別の直し方：あなたはどのケース？
直し方は1つではありません。「いつまでに」「その表をこの先も使うのか」によって、ちょうどいい方法が変わります。よくある3つのパターンに分けて紹介します。
ケースA：今日中に数字を出したい → 合計の数式だけ直す
表の形を変える時間がないときは、全社合計の数式だけを直すのがいちばん早い方法です。小計行を除いて合計するには、次のどちらかを使います。
=SUM(C2:C13)-SUMIF(A2:A13,"*小計",C2:C13)
=SUMIF(A2:A13,"<>*小計",C2:C13)1つ目は「全部足してから、小計行の分を引く」、2つ目は「A列が『〜小計』で終わらない行だけを足す」という考え方です。どちらも結果は820,000になります。「*」は「何か文字が入る」という意味の記号で、「*小計」と書くと「営業部小計」「総務部小計」のように最後が「小計」で終わる文字すべてに当てはまります。
ただし、この方法は小計行の名前がそろっていることが前提です。ある行は「営業部小計」、別の行は「総務部 計」のように表記がバラバラだと取りこぼしが出ます。数式を入れたあとは、冒頭の30秒チェックの数式でもう一度確認しておくと安心です。
ケースB：毎月使う表 → データと集計を分ける
同じ形の表を毎月作るなら、この機会に作り方そのものを変えてしまうのがおすすめです。データを入れる表には小計行を一切入れず、部署・担当者・金額を1行1件で並べるだけにします。そして、部署ごとの小計は表の外、たとえば右側の空いている列や別のシートに、SUMIF関数で出します。
=SUMIF($A$2:$A$10,"営業部",$C$2:$C$10)部署名をセルに入れておき、「”営業部”」の部分をそのセル参照に変えれば、数式を下にコピーするだけで全部署の小計が並びます。全社合計は、データ範囲をSUMで合計するだけです。データ行しかないので、二重計算が起こる余地がありません。
部署が多い、月ごとにも分けたいといった場合は、ピボットテーブルに任せるのがいちばん楽です。行に部署、値に金額を入れれば、部署別の小計と総計が自動でできあがります。翌月にデータを差し替えたら、「更新」ボタンを押すだけで集計が最新の状態になります。
ちなみに、この形に切り替えるときによく起きるのが、金額の列に「円」付きの文字や全角数字が混ざっていて、SUMIFの結果が合わないというトラブルです。同じ列に数値と文字列が混ざると集計できない原因もあわせて確認しておくと、切り替えがスムーズです。
ケースC：人から引き継いだ表で、形を変えられない → 小計をSUBTOTALに置き換える
いちばん悩ましいのがこのケースです。前任者から引き継いだ表で、「この形で提出することになっている」「ほかのシートがこの行番号を参照している」などの理由で、小計行を外せないことがあります。
そんなときに役立つのが、SUBTOTAL関数のあまり知られていない性質です。SUBTOTAL関数は、集計範囲の中に別のSUBTOTAL関数の結果が含まれていると、その数値を自動で無視してくれます。つまり、小計行も合計行も両方SUBTOTALにしておけば、表の形はそのままで二重計算が起きなくなるのです。
実際に、実験用の表をこう書き換えてみました。
5行目（営業部小計）：=SUBTOTAL(9,C2:C4)
9行目（総務部小計）：=SUBTOTAL(9,C6:C8)
13行目（開発部小計）：=SUBTOTAL(9,C10:C12)
14行目（全社合計）：=SUBTOTAL(9,C2:C13)全社合計の範囲には小計行が3つとも入っていますが、結果は820,000になりました。最初の「9」は合計を意味する番号です。行や列を1つも動かさずに直せるので、引き継いだ表にはこの方法がいちばん安全だと感じています。
しかも、SUBTOTAL(9,…)はフィルターで非表示になった行を計算しないので、実験2で起きた「絞り込んでも合計が変わらない」問題も同時に解決します。営業部だけに絞れば、全社合計の欄には営業部の合計が表示されます。
SUBTOTALで直すときに見落としやすい3つの落とし穴
ケースCの方法は便利ですが、つまずきやすい点もあります。同じところでつまずかないよう、気をつけたい点をまとめておきます。
1つ目は、小計が「数式」ではなく「数字」で入力されている場合です。前任者が小計を電卓で計算して手入力していたり、一度コピーして値で貼り付けていたりすると、SUBTOTALはそれを普通のデータとみなして合計に含めてしまいます。数式タブの「数式の表示」ボタンを押すと、セルの中身が数式なのか数値なのかが一目で分かるので、置き換える前に確認しておきましょう。
2つ目は、小計の一部だけSUMのまま残っているケースです。SUBTOTALが無視するのは、あくまでSUBTOTAL（とAGGREGATE）の結果だけです。3つの小計のうち1つでもSUMのままだと、その分だけ合計が大きくなります。例えば、12か月分の月別小計がある表で、11か月分をSUBTOTALに置き換えて1か月だけ見落とすと、その1か月分だけ合計がずれます。置き換え後は、冒頭の30秒チェックと同じ考え方で、合計が「データだけの合計」と一致するかを必ず確かめてください。
3つ目は、「9」と「109」の違いです。どちらも合計ですが、フィルターではなく右クリックの「非表示」で隠した行の扱いが違います。9は手動で隠した行も合計に含め、109は手動で隠した行を除いて合計します。「使わない行を非表示にして、見えている分だけ合計したい」という場合は109を使ってください。
Excelの「小計」機能を使うという選択肢
あまり知られていませんが、Excelには小計行を自動で入れてくれる機能があります。データタブの「アウトライン」グループにある「小計」ボタンです。
使い方は、まず部署など、まとめたい列で表を並び替えておきます。次に表の中を選んで「小計」ボタンを押し、「グループの基準」に部署、「集計の方法」に合計、「集計するフィールド」に金額を指定してOKを押します。すると部署の切れ目ごとに小計行が挿入され、いちばん下に総計が入ります。
この機能が入れる小計と総計は、すべてSUBTOTAL関数でできています。つまり、ケースCで手作業でやったことを自動でやってくれるので、二重計算は起きません。さらに画面の左側に「1」「2」「3」のボタンが出て、「総計だけ」「小計と総計だけ」「全データ」の表示を切り替えられます。報告書として見せるときにはとても便利です。
ただし、注意点もあります。この機能は、Ctrl+Tで作った「テーブル」には使えません。テーブルにしている場合は、テーブルデザインタブの「範囲に変換」で普通の表に戻してから使います。また、小計を外したいときは、行を手で削除するのではなく、もう一度「小計」ボタンを押して「すべて削除」を選んでください。手で消すと、SUBTOTALの数式やアウトラインの設定が中途半端に残ってしまいます。
「見せるための小計」が欲しいなら、この機能か、ケースBのピボットテーブルに任せる。手で小計行を打ち込むのは最後の手段にする、と決めておくと迷いません。
見やすさと計算しやすさは別物
一度、手元の過去のファイルを何本か開いて、SUMで小計行を含めてしまっている合計欄がないか確かめてみてください。同じミスが、思いのほか当たり前のように眠っていることがあります。
小計を入れる人は、決して雑な人ではありません。むしろ「受け取った人が見やすいように」と気を配れる人ほど、小計や区切りの行を入れたくなります。問題はその気配りを、計算の範囲の中に置いてしまったことだけです。
データを入れる場所は、Excelが読みやすい形にする。人に見せるための小計や合計は、SUMIF、SUBTOTAL、ピボットテーブルに作ってもらう。この役割分担を意識しておけば、提出前に数字を二度見して青ざめることはほとんどなくなります。
この記事では、1枚の表に小計行が入っているシンプルなケースを扱いました。実務では「部署ごとの小計の中に、さらに課ごとの小計がある2段階の表」や「月別シートを1枚に集計したら、各シートの小計まで持ち込んでしまった」といった、もう少し込み入った形で出てくることもあります。そうした複雑なケースへの応用パターンや、そのまま使える数式の組み合わせは、noteのほうでまとめています。
もう少し複雑な小計・集計トラブルへの応用パターンはnoteで公開しています → 「表の健康診断」12項目と診断シート（note）
あわせて読みたい関連記事
表の中に「データではないもの」が混ざって集計がずれる問題は、今回の小計行のほかにもいくつかの形で現れます。同じ系統のミスをまとめて確認しておくと、自分の表のどこが危ないのかが見えやすくなります。
コメント