Excelで大量のデータを扱う際、小計行を挟んで集計することはよくありますよね。しかし、その状態で全体の合計をSUM関数で計算すると、「あれ？合計が合わない…」という経験はありませんか？
実はこれ、SUM関数が小計行の値まで含めて計算してしまうため、数字が二重にカウントされてしまう「あるある」な落とし穴なんです。
この記事では、このExcel集計における二重計算の問題を解決し、常に正確な合計値を導き出すための強力な味方「SUBTOTAL（サブトータル）関数」の使い方を、初心者の方でも理解できるよう具体例を交えて徹底解説します。
ExcelのSUM関数で二重計算が発生するメカニズム
まずは、なぜSUM関数で二重計算が発生するのか、具体的な例を見てみましょう。
例えば、以下のような商品ごとの売上データがあり、エリアごとに小計を挿入しているとします。
サンプルデータ
| A列 | B列 | C列 |
|---------|---------|---------|
| エリア | 商品名 | 売上高 |
| 東京 | りんご | 100 |
| 東京 | みかん | 150 |
| 東京 | いちご | 200 |
| 東京小計| | 450 |
| 大阪 | りんご | 120 |
| 大阪 | みかん | 180 |
| 大阪小計| | 300 |
| 名古屋 | りんご | 90 |
| 名古屋 | いちご | 210 |
| 名古屋小計| | 300 |
| 全体合計| | ? |
このデータで、C列の「売上高」を単純にSUM関数で合計してみましょう。
=SUM(C2:C12)
この計算結果はどうなるでしょうか？
- りんご(東京): 100
- みかん(東京): 150
- いちご(東京): 200
- 東京小計: 450
- りんご(大阪): 120
- みかん(大阪): 180
- 大阪小計: 300
- りんご(名古屋): 90
- いちご(名古屋): 210
- 名古屋小計: 300
SUM関数は、各商品の売上高だけでなく、「東京小計 (450)」「大阪小計 (300)」「名古屋小計 (300)」といった小計行の値まで含めて合計してしまいます。
本来の全体合計は「100+150+200+120+180+90+210 = 1250」であるべきなのに、SUM関数では「100+150+200+450+120+180+300+90+210+300 = 2000」となってしまい、大きく数字が狂ってしまいます。
これが、SUM関数で小計を含むデータを集計する際に発生する二重計算のメカニズムです。
SUBTOTAL関数で二重計算を防ぐ！その仕組みと使い方
この問題をスマートに解決してくれるのが、SUBTOTAL関数です。SUBTOTAL関数は、指定された範囲内の「小計行」や「非表示行」を自動的に除外して計算してくれる賢い関数なのです。
SUBTOTAL関数の基本構文
=SUBTOTAL(集計方法, 範囲1, [範囲2], ...)
- 集計方法（必須）: どのような計算を行うかを指定する数値（1～11または101～111）。
- 範囲1（必須）: 計算対象となるセルまたはセル範囲。
- 範囲2以降（省略可能）: 追加の計算対象範囲。
「集計方法」の選び方
SUBTOTAL関数の肝となるのが「集計方法」の数値です。特に重要なのは、以下の2つのグループです。
1. 非表示行も計算に含める場合（1～11）
例えば、9はSUMと同じく合計を計算しますが、手動で非表示にした行は計算に含めません。ただし、Excelの「データ」タブにある「小計」機能で自動挿入された小計行は、このグループの関数でも二重計算の原因となることがあります。
1: AVERAGE (平均)2: COUNT (数値の個数)3: COUNTA (空白ではないセルの個数)4: MAX (最大値)5: MIN (最小値)9: SUM (合計)- …など
2. 非表示行と小計行を計算に含めない場合（101～111）
このグループの数値を使うことで、手動で非表示にした行はもちろん、オートフィルターで絞り込まれて非表示になった行や、Excelの「小計」機能で挿入された小計行も計算に含めません。今回の二重計算問題を解決するには、主にこのグループを使います。
101: AVERAGE (平均)102: COUNT (数値の個数)103: COUNTA (空白ではないセルの個数)104: MAX (最大値)105: MIN (最小値)109: SUM (合計)- …など
今回の目的は「二重計算を防ぎ、正確な合計値を出す」ことなので、109（合計）を使用します。
SUBTOTAL関数で二重計算を解決する具体例
先ほどのサンプルデータを使って、SUBTOTAL(109, ...)関数で全体合計を計算してみましょう。
C列の「全体合計」を求めたいセル（C13など）に、以下の関数を入力します。
=SUBTOTAL(109, C2:C12)
すると、結果は「1250」となります。
これは、SUBTOTAL関数が「東京小計 (450)」「大阪小計 (300)」「名古屋小計 (300)」の行を自動的に除外して、純粋な各商品の売上高のみを合計してくれたためです。見事に二重計算が解消されました！
SUBTOTAL関数が真価を発揮する場面
SUBTOTAL関数は、小計の二重計算防止以外にも、以下のような場面で非常に役立ちます。
1. オートフィルターで絞り込んだデータの合計
オートフィルターで特定の条件に絞り込んだ後、その表示されているデータだけの合計を知りたい場合にもSUBTOTAL関数が活躍します。
例えば、先ほどのデータで「商品名」を「りんご」に絞り込んだとします。
| A列 | B列 | C列 |
|---------|---------|---------|
| エリア | 商品名 | 売上高 |
| 東京 | りんご | 100 |
| 東京小計| | 450 | <-- 非表示
| 大阪 | りんご | 120 |
| 大阪小計| | 300 | <-- 非表示
| 名古屋 | りんご | 90 |
| 名古屋小計| | 300 | <-- 非表示
| 全体合計| | ? |
この状態で=SUM(C2:C12)を使うと、フィルターで非表示になった行も含めて合計されてしまいます。
しかし、=SUBTOTAL(109, C2:C12)を使えば、フィルターで表示されている「りんご」の売上高（100+120+90 = 310）だけを正確に合計してくれます。
2. Excelの「小計」機能で挿入した行の合計
Excelの「データ」タブにある「小計」機能を使って自動的に小計行を挿入した場合も、SUBTOTAL関数はその小計行を無視して、元のデータのみを合計してくれます。
この機能を使うと、小計行自体もSUBTOTAL関数で挿入されるため、さらに複雑な集計でもSUBTOTAL関数一つで対応できるようになります。
SUM関数とSUBTOTAL関数の使い分け
では、SUM関数はもう使わない方がいいのでしょうか？そんなことはありません。それぞれの関数には得意な場面があります。
SUM関数が適している場合
- 単純に指定した範囲内のすべての数値を合計したい場合。
- 非表示行や小計行を考慮する必要がなく、常に範囲全体の合計が必要な場合。
SUBTOTAL関数が適している場合
- 小計行を含むデータから全体の合計を正確に求めたい場合。
- オートフィルターなどで絞り込んだ「表示されているデータのみ」を合計、平均、カウントしたい場合。
- Excelの「小計」機能と組み合わせて集計を行う場合。
まとめると、動的な集計や、小計・非表示行を考慮した集計が必要な場合はSUBTOTAL関数、そうでない場合はSUM関数と使い分けるのがベストプラクティスと言えるでしょう。
まとめ
ExcelのSUM関数で小計行が二重計算されてしまう問題は、多くの人が経験する「あるある」な集計ミスです。しかし、SUBTOTAL関数を正しく理解し活用することで、この問題をスマートに回避し、常に正確な集計結果を得ることができます。
SUM関数は範囲内のすべての数値を合計するため、小計行を二重計算してしまう。SUBTOTAL関数は、集計方法の指定によって、小計行や非表示行を計算から除外できる。- 特に、集計方法に
109を指定することで、小計行と非表示行を無視した正確な合計値が求められる。 - オートフィルターとの連携や、Excelの「小計」機能との組み合わせで真価を発揮する。
これからは、Excelで集計を行う際に「あれ？」と思ったら、ぜひSUBTOTAL関数の活用を思い出してください。あなたのExcel作業が、より正確に、より効率的になること間違いなしです！
コメント