ExcelのSUBTOTAL関数を完全解説｜集計作業を劇的に効率化する方法

Excelを使って表を作成し、合計や平均などの集計作業をすることは日常的によくある作業です。そんなときに便利なのが「SUBTOTAL（サブトータル）」関数です。通常のSUM関数では実現できない、フィルターに対応した集計や、表示中のデータだけを集計するなど、柔軟で実用的な関数として多くの場面で活用できます。本記事では、SUBTOTAL関数の基本的な使い方から、具体的な活用例、注意点までをわかりやすく解説します。Excel初心者から中級者まで、すぐに実践できる内容となっていますので、ぜひ最後までご覧ください。

SUBTOTAL関数とは何か？

SUBTOTAL関数とは、Excelでリスト（表）データを集計するための関数です。特に、フィルター機能と組み合わせて使うことで「表示されているデータのみ」の合計や平均などを簡単に求めることができます。

主な特徴：

  • フィルターで非表示になった行を自動で除外して集計できる
  • 合計、平均、件数など複数の集計方法に対応
  • 数式をコピーしたときに柔軟に対応できる

SUBTOTAL関数の基本構文

SUBTOTAL関数の構文は以下のとおりです。

=SUBTOTAL(集計方法, 範囲1, [範囲2], ...)

引数の説明：

  • 集計方法（function_num）：1～11または101～111の番号を指定します。
  • 範囲：集計したいセル範囲を指定します。

集計方法の指定番号一覧

集計方法非表示の行を含む非表示の行を除く
平均1101
数値の個数2102
最大値4104
最小値5105
積6106
合計9109

例：

=SUBTOTAL(9, B2:B100)   → 通常の合計（非表示行含む）
=SUBTOTAL(109, B2:B100) → フィルターで非表示の行は無視

SUBTOTAL関数とSUM関数の違い

SUM関数は単純に範囲の数値をすべて合計しますが、SUBTOTAL関数はフィルターで非表示になった行を無視することが可能です。

比較例

=SUM(B2:B100)
=SUBTOTAL(109, B2:B100)

フィルターを使って一部の行を非表示にした場合、SUM関数はすべての行を計算しますが、SUBTOTAL関数（109）を使うと、表示されている行だけが集計されます。

フィルター機能と組み合わせた使い方

SUBTOTAL関数の真価を発揮するのは、Excelの「オートフィルター」と組み合わせたときです。

手順：

  1. 集計したい表の先頭行にフィルターを設定（[データ]→[フィルター]）
  2. フィルターで条件を指定して表示される行を絞る
  3. 表の下部に =SUBTOTAL(109, 対象範囲) を入力

これで、表示されている行のみが自動で集計されます。

表の途中に小計を入れる「集計」機能との違い

Excelには「データ」タブにある[集計]機能もあります。これは表に自動的にグループ単位の小計を挿入してくれますが、行の構造が変更されてしまうため注意が必要です。

一方、SUBTOTAL関数を使えば、自分で場所を決めて柔軟に集計できるため、データ構造を壊さずに集計可能です。

実務で役立つSUBTOTAL活用例

1. 売上表の合計をフィルターごとに表示

商品名売上金額
A1000
B1500
A2000
  • フィルターで商品Aだけ表示
  • セルに =SUBTOTAL(109, B2:B100) と入力
    → 商品Aの売上合計だけが表示される

2. 平均単価の算出

  • セルに =SUBTOTAL(101, C2:C100) を入力
  • フィルターで絞り込んだ商品の平均単価を算出可能

SUBTOTAL関数の注意点

  • SUBTOTAL関数同士をネスト（入れ子）にはできません
    → 例：=SUBTOTAL(9, SUBTOTAL(9, B2:B10)) のような使い方は不可
  • 数式の引数にテキストが混ざっているとエラーにはならないが、集計対象にならない
  • 手動で非表示にした行は、101～111の形式では集計対象になります
    → 行全体を右クリックで「非表示」にした場合と、フィルターで非表示にした場合とで挙動が異なるため注意

小技：関数のオートフィルター自動対応

Excelの表機能（[挿入]→[テーブル]）を使うと、SUBTOTAL関数の対象範囲を自動で拡張してくれるため便利です。表の最下部にSUBTOTAL関数を置いておけば、新しいデータを追加しても自動的に反映されます。

まとめ

SUBTOTAL関数は、Excelの集計作業を格段に効率化してくれる強力なツールです。特に、フィルターでデータを絞り込みながら合計・平均・件数などを確認したい場面で大活躍します。通常のSUM関数だけでは対応できない集計にも柔軟に対応できるため、日々の業務でExcelを使うすべての人におすすめしたい関数です。

まずは基本的な構文を覚え、フィルターと併用する形で実践してみてください。データ処理の精度とスピードが一段とアップするはずです。

タイトルとURLをコピーしました