Excelでフィルターをかけたとき、表示されている行だけを合計したいことがあります。
このような場合に便利なのがSUBTOTAL関数です。SUM関数では非表示になった行も合計しますが、SUBTOTAL関数ならフィルターで除外された行を自動的に集計から外せます。
合計だけでなく、平均や件数も求められます。ただし、最初の引数に指定する「9」と「109」では、手動で非表示にした行の扱いが異なります。
この記事では、フィルター後の合計を求める基本操作から、集計方法の番号、9と109の違い、うまく集計できない場合の確認点まで順番に解説します。
1. SUBTOTAL関数は表示されている行を集計できる
SUBTOTAL関数は、リストや表の小計を求める関数です。
フィルターで行を絞り込むと、絞り込みで非表示になった行を自動的に除外して再計算します。担当者別・地域別・商品別など、表示内容を切り替えながら集計したい場合に向いています。
たとえば、7件の売上金額の合計が75,000円だったとします。地域を「東」に絞り込むと、表示された3件だけが対象になり、合計は38,000円になります。
SUBTOTAL関数の基本構文は次のとおりです。
=SUBTOTAL(集計方法, 範囲1, [範囲2], ...)
集計方法：合計、平均、件数などを番号で指定する範囲1：集計するセル範囲を指定する範囲2以降：必要な場合だけ追加する
売上金額がD2からD8に入力されている場合、表示行だけを合計する式は次のとおりです。
=SUBTOTAL(109,D2:D8)
109は「手動で非表示にした行も除外して合計する」という指定です。
2. フィルター後の合計を求める手順
（1）見出しを含む表を用意する
1行目に「日付」「地域」「担当者」「売上金額」などの見出しを置き、2行目以降に1件ずつデータを入力します。
フィルターや集計を安定させるには、1行に1件のデータを入れることが大切です。表の作り方から確認したい場合は、「1行1データ」の原則も参考にしてください。
（2）合計を表示するセルに式を入力する
集計結果を表示するセルへ、次の式を入力します。
=SUBTOTAL(109,D2:D8)
この時点では全行が表示されているため、D2からD8までの合計が表示されます。
（3）表にフィルターを設定する
見出し行を選択し、「データ」タブの「フィルター」をクリックします。
見出しの右側に表示されたボタンから、地域や担当者などの条件を選びます。絞り込みで行が非表示になると、SUBTOTAL関数の結果も表示行だけの値へ変わります。
フィルターの条件を解除すると、再び全行の合計へ戻ります。式を入力し直す必要はありません。
3. 集計方法9と109の違い
合計を求める番号には9と109があります。
どちらも、フィルターによって除外された行は集計しません。違いが出るのは、行番号を右クリックして「非表示」を選ぶなど、行を手動で非表示にした場合です。
=SUBTOTAL(9,D2:D8)
9は、手動で非表示にした行を合計に含めます。
=SUBTOTAL(109,D2:D8)
109は、手動で非表示にした行も合計から除外します。
表示されている行だけを集計したい場合は、基本的に109を使うと分かりやすいでしょう。
ただし、横方向の範囲を集計して列を非表示にしても、109ではその列を除外できません。SUBTOTAL関数は、列方向に並んだデータを縦に集計する用途を前提にしています。
4. 合計・平均・件数で使う番号
SUBTOTAL関数では、最初の引数を変えると、合計以外の集計もできます。
| やりたいこと | 手動非表示行を含む | 手動非表示行を除外 |
|---|---|---|
| 平均 | 1 | 101 |
| 数値が入ったセルの件数 | 2 | 102 |
| 空白でないセルの件数 | 3 | 103 |
| 最大値 | 4 | 104 |
| 最小値 | 5 | 105 |
| 合計 | 9 | 109 |
フィルターで除外された行は、どちらの番号でも集計されません。
（1）平均を求める
表示行だけの平均は、次の式で求めます。
=SUBTOTAL(101,D2:D8)
（2）数値が入ったセルの件数を数える
表示行のうち、数値が入ったセルを数える場合は102を使います。
=SUBTOTAL(102,D2:D8)
（3）空白でないセルの件数を数える
商品名や担当者名など、文字列を含む入力済みセルの件数を数える場合は103を使います。
=SUBTOTAL(103,C2:C8)
COUNTに相当する102は数値だけを数え、COUNTAに相当する103は文字列を含む空白でないセルを数えます。件数が合わないときは、数えたい列のデータ形式を確認してください。
5. SUM・SUMIF・COUNTIFとの違い
SUM関数は、指定範囲の数値をそのまま合計します。フィルターで行が非表示になっても結果は変わりません。
=SUM(D2:D8)
SUMIF関数は、「地域が東」など、数式の中に条件を指定して合計します。フィルターの表示状態ではなく、指定した条件に合うかどうかで集計します。
=SUMIF(B2:B8,"東",D2:D8)
SUMIF関数の条件指定は、SUMIF関数で条件に合う数値だけを合計する方法で詳しく解説しています。
COUNTIF関数も同様に、数式で指定した条件に合うセルを数える関数です。フィルターで見えなくなった行を自動的に除外する関数ではありません。
| 関数 | 集計の基準 | 向いている場面 |
|---|---|---|
| SUBTOTAL | フィルター後の表示状態 | 表示中の行だけを集計したい |
| SUM | 指定範囲の数値 | 非表示に関係なく全体を合計したい |
| SUMIF | 数式に書いた条件 | 「東」「商品A」など決まった条件で合計したい |
| COUNTIF | 数式に書いた条件 | 条件に合うセルの件数を数えたい |
6. SUBTOTAL関数が合わないときの確認点
（1）合計がフィルター後も変わらない
集計式がSUMになっていないか確認します。表示行だけを合計する場合は、次のようにSUBTOTAL関数を使います。
=SUBTOTAL(109,D2:D8)
また、集計範囲がフィルター対象の表とずれていないかも確認してください。
（2）非表示にした行まで合計される
手動で非表示にした行を除外したい場合は、9ではなく109を使います。
フィルターによる非表示は9でも109でも除外されます。どの操作で行を隠したかを分けて考えると、原因を見つけやすくなります。
（3）件数が想定より少ない
数値の件数を数える102は、文字列を数えません。商品名や担当者名を数える場合は、空白でないセルを数える103を使います。
（4）小計を含む範囲で二重に合計されるのが心配
参照範囲内に別のSUBTOTAL関数がある場合、そのSUBTOTALの結果は集計対象から除外されます。そのため、小計行を含む範囲をさらにSUBTOTALで集計しても、同じ値を二重に足さない仕組みになっています。
（5）横方向の非表示列が除外されない
SUBTOTAL関数は、縦方向のデータを集計するための関数です。SUBTOTAL(109,B2:G2)のように横方向を集計した場合、列を非表示にしても結果は変わりません。
集計しやすい表は、同じ種類の値を同じ列へ入れます。詳しくは、同じ列に同じ意味のデータを入れる理由を参考にしてください。
7. テーブルの集計行でもSUBTOTAL関数を使える
通常のセル範囲を、Excelの「テーブル」に変換して集計行を表示すると、合計や平均を一覧から選択できます。
テーブルの集計行にはSUBTOTAL関数が使われるため、フィルターで表示内容を変えると集計結果も連動します。データを追加することが多い表では、範囲を毎回広げなくてよい点も便利です。
表をテーブルへ変換する操作は、グラフを自動更新する表の作り方でも紹介しています。
8. まとめ
SUBTOTAL関数を使うと、Excelでフィルター後に表示されている行だけを集計できます。
- 表示行の合計は
SUBTOTAL(109,範囲)で求める - 9と109は、手動で非表示にした行を含めるかどうかが異なる
- フィルターで除外された行は、どちらの番号でも集計されない
- 平均は101、数値の件数は102、空白でないセルの件数は103を使う
- SUMIFやCOUNTIFは、フィルター状態ではなく数式に書いた条件で集計する
- 範囲内にある別のSUBTOTALは二重計算されない
フィルターで見たいデータを切り替えながら集計する表では、まず109を使った合計から試してみましょう。
