SUBTOTAL関数:フィルターで絞り込んだ行だけ合計・件数を出す
SUMで合計を出していると、フィルターで絞り込んでも合計は全行のままです。SUBTOTALを使うと、表示されている行だけを集計できます。絞り込んでも崩れない連番の作り方まで説明します。
目次(6項目)
💡 基本の使い方
第1引数に集計の種類を番号で指定します。
| 行 | A: 支店 | B: 部署 | C: 売上 |
|---|---|---|---|
| 2 | 東京 | 営業部 | 120,000 |
| 3 | 大阪 | 営業部 | 200,000 |
| 4 | 東京 | 企画部 | 85,000 |
| 5 | 福岡 | 営業部 | 150,000 |
| 6 | 東京 | 営業部 | 95,000 |
C7以降に行を足す可能性があるので、合計は表の上(E1など)に置きます。
絞り込んでいないときは650,000です。A列を「東京」で絞り込むと、300,000(120,000+85,000+95,000)に変わります。
表のすぐ下に置くと、並べ替えやフィルターの対象に入ったり、行の追加の邪魔になったりすることがあります。見出しより上に置くと扱いやすくなります。
🔢 集計方法の番号と、9と109の違い
| 番号 | 手動で隠した行も除く番号 | 集計 | 「東京」で絞り込んだ結果 |
|---|---|---|---|
| 9 | 109 | 合計(SUM) | 300,000 |
| 3 | 103 | 空白以外の個数(COUNTA) | 3 |
| 2 | 102 | 数値の個数(COUNT) | 3 |
| 1 | 101 | 平均(AVERAGE) | 100,000 |
| 4 | 104 | 最大(MAX) | 120,000 |
| 5 | 105 | 最小(MIN) | 85,000 |
他に、6(積)、7・8(標準偏差)、10・11(分散)があります。
9と109の違い
| 行の状態 | 9(1〜11) | 109(101〜111) |
|---|---|---|
| フィルターで隠れた行 | 集計しない | 集計しない |
| 右クリックの「非表示」で隠した行 | 集計する | 集計しない |
どちらもフィルターで隠れた行は除きます。手で非表示にした行も除きたいときは109を使います。迷ったら、見えている行だけを集計する109が分かりやすいでしょう。
範囲の中に別のSUBTOTALがあると、その値は集計から除かれます。部署ごとの小計を入れた表でも、全体を
=SUBTOTAL(9, C2:C100) で集計できます。🔁 絞り込んでも崩れない連番
ROW関数の連番は、絞り込むと飛び飛びになります。SUBTOTALで「ここまでに見えている行の数」を数えると、表示された行に1から順に番号が付きます。
連番用に追加した列の先頭(2行目)に入力して、下へコピーします。範囲の始まりを固定し、終わりが1行ずつ伸びるので、見えている行だけを順に数えます。B列は空白のない列を指定してください。
行数が多いと、絞り込みのたびに範囲を数え直すため、再計算が重くなる場合があります。
🎯 表示されている行を、さらに条件で集計する
SUBTOTALには条件を指定できません。「絞り込んだ結果のうち営業部だけ」を集計するときは、表示中かどうかを判定する作業列を作り、SUMIFSと組み合わせます。
ステップ1:D列に判定を入れる
D2に入力して下へコピーします。その行が表示されていれば1、隠れていれば0になります。
ステップ2:SUMIFSで集計する
「東京」で絞り込んでいるときは、215,000(120,000+95,000)になります。
🛠️ エラーがある列はAGGREGATE
範囲の中に #N/A などのエラーがあると、SUBTOTALの結果もエラーになります。AGGREGATE関数なら、隠れた行とエラーを両方除いて集計できます。
第1引数の9は合計、第2引数の7は「隠れた行とエラーを無視する」指定です。
| SUBTOTAL | AGGREGATE | |
|---|---|---|
| 隠れた行を除く | できる | できる |
| エラーを除く | できない | できる |
| 分かりやすさ | 番号が少なく読みやすい | 番号の指定が多い |
エラーの原因を直せるなら、先に直してSUBTOTALを使うほうが、数式を読む人にとって分かりやすくなります。
⚠️ つまずきやすい点
| 症状 | 確認すること |
|---|---|
| 絞り込んでも合計が変わらない | SUMのままになっていないか確認します。 |
| 非表示にした行が集計に入る | 第1引数を9から109に変えます。 |
| 列を非表示にしても結果が変わらない | SUBTOTALが除くのは行だけです。隠した列は集計されます。 |
| 結果がエラーになる | 範囲内のエラーを直すか、AGGREGATEを使います。 |
テーブル機能の「集計行」は、内部でこのSUBTOTALを使っています。テーブルで管理している表なら、集計行をオンにするだけで同じ集計ができます。