SUBTOTAL関数:フィルターで絞り込んだ行だけ合計・件数を出す

SUMで合計を出していると、フィルターで絞り込んでも合計は全行のままです。SUBTOTALを使うと、表示されている行だけを集計できます。絞り込んでも崩れない連番の作り方まで説明します。

目次(6項目)

💡 基本の使い方

第1引数に集計の種類を番号で指定します。

=SUBTOTAL(集計方法, 範囲)
サンプルデータ:売上明細
行A: 支店B: 部署C: 売上
2東京営業部120,000
3大阪営業部200,000
4東京企画部85,000
5福岡営業部150,000
6東京営業部95,000

C7以降に行を足す可能性があるので、合計は表の上(E1など)に置きます。

=SUBTOTAL(9, C2:C100)

絞り込んでいないときは650,000です。A列を「東京」で絞り込むと、300,000(120,000+85,000+95,000)に変わります。

💡 集計欄は表の上に置く
表のすぐ下に置くと、並べ替えやフィルターの対象に入ったり、行の追加の邪魔になったりすることがあります。見出しより上に置くと扱いやすくなります。

🔢 集計方法の番号と、9と109の違い

集計方法の番号
番号手動で隠した行も除く番号集計「東京」で絞り込んだ結果
9109合計(SUM)300,000
3103空白以外の個数(COUNTA)3
2102数値の個数(COUNT)3
1101平均(AVERAGE)100,000
4104最大(MAX)120,000
5105最小(MIN)85,000

他に、6(積)、7・8(標準偏差)、10・11(分散)があります。

9と109の違い

9と109の違い
行の状態9(1〜11)109(101〜111)
フィルターで隠れた行集計しない集計しない
右クリックの「非表示」で隠した行集計する集計しない

どちらもフィルターで隠れた行は除きます。手で非表示にした行も除きたいときは109を使います。迷ったら、見えている行だけを集計する109が分かりやすいでしょう。

小計を重ねても二重に数えない
範囲の中に別のSUBTOTALがあると、その値は集計から除かれます。部署ごとの小計を入れた表でも、全体を =SUBTOTAL(9, C2:C100) で集計できます。

🔁 絞り込んでも崩れない連番

ROW関数の連番は、絞り込むと飛び飛びになります。SUBTOTALで「ここまでに見えている行の数」を数えると、表示された行に1から順に番号が付きます。

=SUBTOTAL(103, $B$2:B2)

連番用に追加した列の先頭(2行目)に入力して、下へコピーします。範囲の始まりを固定し、終わりが1行ずつ伸びるので、見えている行だけを順に数えます。B列は空白のない列を指定してください。

行数が多いと、絞り込みのたびに範囲を数え直すため、再計算が重くなる場合があります。

🎯 表示されている行を、さらに条件で集計する

SUBTOTALには条件を指定できません。「絞り込んだ結果のうち営業部だけ」を集計するときは、表示中かどうかを判定する作業列を作り、SUMIFSと組み合わせます。

ステップ1:D列に判定を入れる

=SUBTOTAL(103, A2)

D2に入力して下へコピーします。その行が表示されていれば1、隠れていれば0になります。

ステップ2:SUMIFSで集計する

=SUMIFS(C2:C100, B2:B100, "営業部", D2:D100, 1)

「東京」で絞り込んでいるときは、215,000(120,000+95,000)になります。

🛠️ エラーがある列はAGGREGATE

範囲の中に #N/A などのエラーがあると、SUBTOTALの結果もエラーになります。AGGREGATE関数なら、隠れた行とエラーを両方除いて集計できます。

=AGGREGATE(9, 7, C2:C100)

第1引数の9は合計、第2引数の7は「隠れた行とエラーを無視する」指定です。

SUBTOTALとAGGREGATEの比較
SUBTOTALAGGREGATE
隠れた行を除くできるできる
エラーを除くできないできる
分かりやすさ番号が少なく読みやすい番号の指定が多い

エラーの原因を直せるなら、先に直してSUBTOTALを使うほうが、数式を読む人にとって分かりやすくなります。

⚠️ つまずきやすい点

SUBTOTALのよくある疑問
症状確認すること
絞り込んでも合計が変わらないSUMのままになっていないか確認します。
非表示にした行が集計に入る第1引数を9から109に変えます。
列を非表示にしても結果が変わらないSUBTOTALが除くのは行だけです。隠した列は集計されます。
結果がエラーになる範囲内のエラーを直すか、AGGREGATEを使います。

テーブル機能の「集計行」は、内部でこのSUBTOTALを使っています。テーブルで管理している表なら、集計行をオンにするだけで同じ集計ができます。

🔗 関連記事