「先月の経費、部署ごとの合計を出しておいて」
上司からそう頼まれて、経理システムから吐き出した経費データをExcelでまとめていたときのことです。
営業部・総務部・開発部……部署ごとに小計を入れながら表を作っていくと、見た目はとてもきれいに整いました。「営業部小計：284,000円」「総務部小計：196,000円」というふうに、区切りがひと目でわかる表です。
ところが、いちばん下の「全社合計」にSUM関数を入れて出てきた数字を見て、手が止まりました。
想定していた金額よりも、明らかに大きい。
「経費、こんなに使ってたっけ……？」
慌てて上司に報告する前に、自分でもう一度表を見直してみると、原因はすぐに見つかりました。各部署の小計行が、SUM関数の合計範囲にそのまま含まれていたのです。
このシリーズでは、Excel初心者がやりがちなミスを1つずつ研究しています。今回のテーマは「表の途中に小計を入れる」ミスです。なぜこれが問題になるのか、どんなトラブルに広がるのか、そして正しい集計の作り方まで、順番に見ていきましょう。
表の途中に小計を入れるとは
「部署ごとに小計があると見やすい」「月ごとに区切りたい」という気持ちは、表を作るうえでとても自然な工夫です。人間の目にとっては、区切りがあることでかえって情報が整理されて見えます。
しかし、Excelの計算ロジックから見ると、この「親切な工夫」がそのまま計算エラーの温床になります。SUM関数やフィルター、並び替え、ピボットテーブルは、表の中にある行をすべて「同じ種類のデータ」として扱おうとするからです。
Before / After で見比べる
まず、何が変わるのかを表で見比べてみましょう。
こうして並べてみると、違いは「小計行を表の中に混ぜているかどうか」だけです。たったこれだけの違いが、集計・フィルター・並び替え・ピボットテーブルという、Excelの主要機能すべてに影響します。
自分の表は大丈夫？ チェックリスト
本題に入る前に、自分の表が当てはまっていないか確認してみてください。
- 表の途中に「◯◯小計」「小計」という行がある
- SUM関数の合計が、なんとなく大きすぎる気がする
- フィルターをかけると件数や合計が合わない
- 並び替えをすると、小計らしき行が動いてしまう
- ピボットテーブルの合計欄に「（空白）」という項目が出てくる
3つ以上当てはまった場合は、後述する二重計算がすでに起きている可能性があります。
なぜSUM関数は小計を区別できないのか
SUM関数は、指定した範囲に入っている数値をすべて機械的に足し合わせます。=SUM(C2:C15) と書けば、C2からC15までのセルにある数値を、それが「元データ」なのか「小計」なのかを判断せずに合計します。
先ほどの経費精算を例に、実際の数字で確認してみましょう。
営業部のデータ：120,000＋98,000＋66,000＝284,000
総務部のデータ：85,000＋74,000＋37,000＝196,000
開発部のデータ：142,000＋110,000＋88,000＝340,000
データだけの正しい合計：284,000＋196,000＋340,000＝820,000
ここに「営業部小計：284,000」「総務部小計：196,000」「開発部小計：340,000」という3行が範囲の中に混ざっていると、SUM関数はこの3つの数字も合計対象に含めてしまいます。
小計の合計：284,000＋196,000＋340,000＝820,000
範囲全体のSUM：820,000（データ）＋820,000（小計）＝1,640,000
正しい金額の、ちょうど2倍です。エラーメッセージは一切出ません。「1,640,000」という数字が、何の警告もなく画面に表示されるだけです。この「エラーが出ない」という点こそが、このミスの一番厄介なところです。
実務で起きる4つのトラブル
トラブル① SUM関数の二重計算
前述のとおり、最も典型的なトラブルは合計の二重計算です。小計が1〜2行程度だと、全体の合計から見て「ちょっと多い気もするけど、まあこんなものか」という誤差の範囲に紛れてしまうことがあります。この「なんとなく合っていそう」な数字が、そのまま月次報告書に使われてしまうリスクがあります。
トラブル② フィルターに小計行が混ざる
「担当者ごとに絞り込みたい」とフィルターをかけると、小計行の担当者欄は空白のため、表示・非表示の挙動が不安定になります。件数を数えると「3件のはずが4件ある」といった食い違いが発生し、集計結果への信頼性が揺らぎます。
トラブル③ 並び替えで小計行が迷子になる
金額の多い順に並び替えをかけると、小計行もデータと同列に扱われて動いてしまいます。「営業部小計：284,000」という行が、まったく無関係なデータ行の間に割り込んでくることがあります。これは⑲「表の途中にタイトル行を入れる」で紹介した現象と同じ構造の問題です。表の途中に「特別な行」を挟むと、並び替えのたびにその行が一緒に動いてしまいます。
トラブル④ ピボットテーブルが二重集計する
ピボットテーブルで集計しようとすると、小計行も1件のデータとして読み込まれます。部署名が入っていない小計行は「（空白）」という項目として扱われ、本来の合計に上乗せされる形で二重集計が起きます。「ピボットの合計だけがなぜか合わない」という症状の裏側に、この小計行の混入が隠れていることがよくあります。
解決方法
方法① 小計を表の外に出す
もっともシンプルな解決策は、データ行だけの表を作り、集計は表の下や別シートにまとめることです。この形であれば、SUM関数はデータ行だけを対象に計算します。部署ごとの小計を見たい場合は、SUMIF関数で後から求められます。
=SUMIF(A2:A10,"営業部",C2:C10)方法② SUBTOTAL関数を使う
SUBTOTAL関数は、フィルターで表示されている行だけを集計できる関数です。
=SUBTOTAL(9, C2:C10)第1引数の「9」は「合計」を意味します。フィルターをかけると、表示されている行だけが自動的に集計対象になるため、表の下に一度置いておけば、フィルターのたびに数字が自動更新されます。
方法③ ピボットテーブルで部署別集計を作る
データ行だけのシンプルな表を用意し、部署ごとの小計はピボットテーブルに任せる方法です。行に「部署」を設定するだけで、部署別の小計と全社合計が自動的に表示されます。データを追加したときも「更新」ボタン一つで最新の集計に反映されるため、小計を手作業で管理する必要がなくなります。
SUBTOTAL関数の早見表
SUBTOTAL関数は第1引数の数字を変えるだけで、集計の種類を切り替えられます。
実務でよく使うのは「9（合計）」と「2（件数）」です。フィルターと組み合わせておくと、「今表示されている範囲の件数と合計」を常時確認できる集計エリアになります。
よくある質問
Q. 小計はまったく入れてはいけないのですか？
小計そのものが悪いわけではありません。問題は「小計行をデータ行と同じ列・同じ範囲に混在させてしまうこと」です。SUMIF関数やピボットテーブルを使えば、小計を見たいという目的自体はきちんと叶えられます。
Q. すでに小計入りの表がたくさんある場合、全部作り直す必要がありますか？
作り直す必要はありません。まずはSUM関数の範囲から小計行を除外するか、SUBTOTAL関数に置き換えるだけでも二重計算は防げます。表の構造自体を整理する時間があるときに、少しずつデータ行だけの形に近づけていけば十分です。
Q. 小計行と似たミスに、他にどんなものがありますか？
表の途中に「特別な行」を挟むという意味では、㉑「表の途中にメモを書く」も同じ系統のミスです。どちらも「読みやすくしよう」という工夫が、Excelの計算・並び替え・フィルターの前提を崩してしまうという共通点があります。
今すぐできる応急処置
表の作り直しに時間が取れないときは、次の手順で今ある表の安全性だけでも確認しておきましょう。
Ctrl+F（検索）で「小計」「計」などのキーワードを検索し、小計行がどこに何行あるかを洗い出す- 洗い出した小計行のセルを選択し、フォントの色を変えるなどして「これは集計対象ではない」と目印を付ける
- SUM関数の数式を開き、範囲に小計行が含まれていないか実際に確認する
- 含まれていた場合は、範囲をデータ行だけに修正するか、SUBTOTAL関数に置き換える
この応急処置はあくまで「今の表の危険度を下げる」ための一時対応です。次に表を作るときは、最初からデータ行と集計行を分けて設計しておくことをおすすめします。
表を作る前にできる予防策
一度この問題を経験すると、次からは表を作る段階で予防できるようになります。ポイントは「入力するデータ」と「集計して見せるデータ」を、最初から別の場所に分けて設計することです。
具体的には、データ入力用のシートと、集計・レポート用のシートを分ける方法が効果的です。入力側のシートには数値と項目名だけを淡々と並べ、集計側のシートでSUMIF関数やピボットテーブルを使って必要な小計・合計を表示します。こうしておけば、入力側の表に小計行が紛れ込む余地そのものがなくなります。
研究員メモ
「整理しようとした工夫が、そのまま計算を狂わせる原因になっていた」
これは、経理データを扱うようになってから何度も経験したことです。丁寧にやろうとした結果がトラブルにつながるのは、決して不注意だったからではなく、単に「Excelのルールをまだ知らなかった」からです。
小計行を表の途中に入れることは、人間の目線では「整理された、見やすい表」です。しかしExcelの目線では「計算対象に余計な数値が混ざっている表」でしかありません。この2つの目線のズレを知ることが、Excelを使いこなすための最初の一歩だと思っています。
データ行には数値だけを入れる。集計は表の外で行う。このシンプルな2つのルールを守るだけで、今回のようなトラブルはほとんど防げます。
今日の研究まとめ
表の途中に小計を入れる
チェックポイント
- 表の途中に「◯◯小計」という行がある
- SUM関数の合計がなんとなく大きすぎる気がする
- フィルターをかけると件数や合計が合わない
- 並び替えをすると小計行が動いてしまう
- ピボットテーブルの合計に「（空白）」の項目が出る
解決方法
- データ行には数値だけを入れ、小計行は表の外に出す
- 部署別・月別の小計はSUMIF関数で条件付き集計する
- SUBTOTAL(9, 範囲) でフィルター後の自動集計を作る
- ピボットテーブルで小計・合計を自動生成する
おわりに
小計を表の途中に入れるという工夫は、決して間違った発想ではありません。むしろ「誰かに見せるとき、少しでも分かりやすくしよう」という気配りから生まれたものだと思います。問題は、その気配りをExcelの計算範囲の中に置いてしまったことだけです。
データはデータとして淡々と並べ、集計は集計として別の場所にまとめる。この役割分担さえ意識できれば、SUM関数もフィルターも並び替えもピボットテーブルも、本来の力をそのまま発揮してくれます。
もし今、手元の表に小計行が紛れ込んでいたら、今日紹介したSUBTOTAL関数やSUMIF関数を使って、集計を表の外に出すところから始めてみてください。数字が急に落ち着いて見えてくるはずです。
コメント