こんにちは。
Excel事務研究員です。
月次の売上データを集計していたとき、合計欄の数字を見て首をかしげたことがあります。
「この合計、大きすぎないか……？」
実際の売上は200万円台のはずなのに、合計は350万円を超えていました。
原因を調べてみると、すぐに分かりました。
表の途中に入れていた「4月小計」「5月小計」「6月小計」の行が、SUM関数の計算範囲に含まれていて、データと小計が両方足されていたのです。
このシリーズでは、Excel初心者がやりがちなミス100を一つずつ研究しています。
今回の研究テーマはこちらです。
表の途中に小計を入れる
「月ごとに小計を入れると管理しやすい」という、丁寧さからくる工夫がトラブルの原因になります。
今回は、なぜ問題なのか、どんなトラブルになるのか、そして正しい集計の作り方を丁寧に研究していきましょう。 関連記事⑲「表の途中にタイトル行を入れる」もご覧ください
なぜ小計行を途中に入れてしまうのか
月ごとにデータを管理しているとき、「月の区切りに小計を入れると見やすくなる」という発想は、とても自然なものです。
人間が見ると、整理されていて非常に読みやすい表です。
「4月は122,000円、5月は118,000円、6月は139,000円」と、月ごとの状況がひと目で分かります。
でも、この「合計」欄にSUM関数を使うと、深刻な問題が起きます。
SUM関数が小計行を区別できない理由
SUM関数は、指定した範囲に含まれるすべての数値を足します。
=SUM(C2:C15) という数式を書けば、C2からC15のすべての数値が合計されます。
ここで問題になるのは、SUM関数が「この行はデータで、この行は小計だから除く」という判断をしないことです。
小計行のセルにも数値が入っているため、SUM関数はデータ行と同じように計算対象に含めます。
今回の例で計算してみます。
データの合計：50,000 ＋ 30,000 ＋ 42,000 ＋ 45,000 ＋ 35,000 ＋ 38,000 ＋ 60,000 ＋ 28,000 ＋ 51,000 ＝ 379,000
小計の合計：122,000 ＋ 118,000 ＋ 139,000 ＝ 379,000
SUM関数で範囲全体を合計すると：379,000（データ）＋ 379,000（小計）＝ 758,000
正しい合計は「379,000」なのに、小計行が二重計算されて「758,000」という、ほぼ2倍の数字が出てしまいます。
エラーは出ません。
「758,000」という数字が、何事もなかったように表示されます。
よく起きるトラブル① SUM関数が二重計算する
前述の通り、最も典型的なトラブルが集計の二重計算です。
小計行を含む範囲をSUM関数で合計すると、正しい合計の倍近い数字が出ることがあります。
「なんか合計が大きすぎる気がする」という違和感で気づけるケースはまだいい方です。
問題は、小計行が1〜2行しか入っていない場合です。
全体の合計から見ると「ちょっと大きい気がするけど、そんなものかも」という範囲の誤差にしか見えないことがあります。
こういった「なんとなく合っていそう」な二重計算が、月次報告書や集計資料にそのまま使われてしまうリスクがあります。
よく起きるトラブル② フィルターに小計行が混入する
フィルターをかけると、小計行もデータ行と一緒に表示されることがあります。
「担当者が田中さんの行だけ見たい」とフィルターをかけたとき、小計行の「担当者」列は空白です。
フィルターの設定によっては、空白行が一緒に表示されたり、逆に除外されたりします。
件数を数えると「3件のはずなのに4件ある」「合計が合わない」という状況が生まれます。
フィルター結果を見た別の人が「このデータ、正しいの？」と疑問を持つ原因にもなります。
よく起きるトラブル③ 並び替えで小計行が動いてしまう
売上の多い順に並び替えをかけると、小計行もデータと一緒に動いてしまいます。
「4月小計：122,000」という行が、「売上：50,000」のデータ行と「売上：60,000」のデータ行の間に割り込んでくることがあります。
並び替え後には、どの数字が小計でどれがデータなのかが、見た目だけでは判断できない状態になります。
これは⑲「表の途中にタイトル行を入れる」と同じ構造の問題です。
表の途中に「特別な行」を入れると、並び替えやフィルターのたびにその行が動いてしまいます。 <!– 内部リンク挿入位置：⑲「表の途中にタイトル行を入れる」の記事へのリンクをここに挿入 –>
よく起きるトラブル④ ピボットテーブルが二重集計する
ピボットテーブルを使って集計しようとしたとき、小計行も「データ行」として取り込まれます。
「4月小計：122,000」という行が、ピボットテーブルのデータとして読み込まれるため、集計結果が二重になります。
「担当者別の合計を出したい」という目的でピボットテーブルを作っても、小計行に担当者名が入っていない場合は「（空白）」という項目として集計されてしまいます。
「ピボットテーブルの合計がなぜか大きい」という症状の原因が、小計行の混入にあることがよくあります。
よく起きるトラブル⑤ テーブル機能が正しく動かない
Excelのテーブル機能は、データが連続して並んでいることを前提に動作します。
途中に小計行が入っていると、テーブルの範囲認識がおかしくなったり、自動集計行の数値が二重になったりします。
テーブル機能の「集計行」を使って合計を出そうとしても、小計行が含まれているため正しい数字が出ません。
解決方法
方法① 小計を表の下に置く（基本の対処法）
最もシンプルな解決策は、データ行だけの表を作り、集計は表の下にまとめることです。
この形なら、SUM関数はデータ行だけを合計します。
月ごとの小計が見たい場合は、SUMIF関数で後から求めます。
=SUMIF(A2:A10,"4/*",C2:C10)または、別の集計エリアを表の下に作って、そこにSUMIF関数で月別集計を並べます。
方法② SUBTOTAL関数を使う
SUBTOTAL関数は、フィルターで表示されている行だけを集計できる関数です。
=SUBTOTAL(9, C2:C10)第1引数の「9」は「合計」を意味します。
フィルターをかけると、表示されている行だけが自動的に集計対象になります。
「田中さんの行だけにフィルターをかけると、合計も田中さんの分だけになる」という動きです。
SUBTOTAL関数はフィルター状態に連動して集計結果が変わるため、表の下に置いておくだけで、フィルターのたびに自動更新されます。
方法③ ピボットテーブルで月別集計を作る
データ行だけのシンプルな表を作っておいて、月別の小計はピボットテーブルで作る方法です。
ピボットテーブルは、行に「月」を設定するだけで自動的に月別の小計と総合計を表示してくれます。
データを追加するたびに「更新」ボタンを押すだけで、集計が自動的に最新の状態になります。
小計を手動で管理する必要がなくなります。
SUBTOTAL関数の主な使い方
SUBTOTAL関数の第1引数で、集計の種類を指定できます。
| 番号 | 機能 |
|---|---|
| 1 | AVERAGE（平均） |
| 2 | COUNT（数値の個数） |
| 3 | COUNTA（空白以外の個数） |
| 9 | SUM（合計） |
| 11 | STDEV（標準偏差） |
実務で最もよく使うのは「9（合計）」と「2（件数）」です。
フィルターと組み合わせて使うことで、「今表示されているデータの件数と合計」を常に確認できる集計エリアを表の下に作れます。
研究員メモ
「整理しようとした工夫が、計算を狂わせる原因になっていた」
これは、Excelを使い始めた頃によく経験したことです。
丁寧にやろうとした結果がトラブルになるのは、「Excelのルールをまだ知らなかった」からです。
小計行を表の途中に入れることは、人間の目線では「整理された表」です。
でもExcelの目線では「計算対象に余計な数値が入っている表」です。
この2つの目線の違いを知ることが、Excelを使いこなすための最初の一歩だと思っています。
データ行には数値だけを入れる。
集計は表の外で行う。
この2つのルールを守るだけで、今回のようなトラブルはほぼ防げます。
今日の研究まとめ
集計がうまくいかない理由⑳
表の途中に小計を入れる
チェックポイント
- 表の途中に「〇月小計」「小計」という行がある
- SUM関数の合計がなんとなく大きすぎる気がする
- フィルターをかけると件数が合わない
- 並び替えをすると小計行が動いてしまう
- ピボットテーブルの合計がおかしい
解決方法
✔ データ行には数値だけを入れる（小計行はデータの外に出す）
✔ 合計・小計は表の下か別シートにまとめる
✔ 月別の集計はSUMIF関数で条件付き集計する
✔ SUBTOTAL(9, 範囲) でフィルター後の自動集計を作る
✔ ピボットテーブルで月別小計を自動生成する
次回の研究予告
Excel初心者がやりがちなミス研究。
次回はこちらです。
表の作り方のミス㉑「データが1行1データになっていない」
「1行に複数のデータをまとめて入れている」という構造のミスです。
集計・フィルター・分析のすべてがうまくいかなくなる、根の深いミスです。
次回も一緒に研究していきましょう。
コメント