Excelでフィルター後に表示されている行だけを合計するには、SUBTOTAL関数の集計方法に109を指定します。SUMと違い、フィルターで除外された行を合計に含めません。手動で非表示にした行も除外できます。
SUMではフィルターで隠れた行も合計する
サンプルには営業3件、経理2件の金額があります。まだ絞り込んでいない状態では、SUBTOTALもSUMも12,000円です。違いはフィルターをかけた後に現れます。
画像をタップすると拡大
手順1：合計セルを表の外に置く
元データはA8:C13で、C9:C13が金額です。C4に表示中の合計を出す次の式を入力します。合計セルを金額の範囲に含めると循環参照になるので、表の上など、別の場所に置きます。
=SUBTOTAL(109,C9:C13)109は「合計し、手動で非表示にした行も除外する」という集計方法です。サンプルのC5には比較用として次の式を入れています。
=SUM(C9:C13)手順2：部署を営業に絞り込む
- サンプルはフィルター付きのテーブルになっています。B8「部署」のフィルターボタンを開きます。
- いったんすべての選択を外し、「営業」だけにチェックしてOKを押します。
- 田中・鈴木・高橋の3行が表示されていることを確認します。
- C4が6,000、C5が12,000であることを確認します。
- 部署のフィルターを解除すると、C4も12,000へ戻ります。
画像をタップすると拡大
自分の表にフィルターがない場合は、見出しを含むA8:C13を選び、「データ」→「フィルター」を設定します。合計セルC4・C5は選択範囲に含めません。フィルター操作と、別の場所に結果を出すFILTER関数は別の機能です。
結果の確かめ方：営業の3件を足す
営業は1,000円・3,000円・2,000円なので、合計6,000円です。サンプル右側のE8:F10には、部署条件で求めた照合用の合計を置いています。この画像は「フィルター操作後の画面」ではなく、期待する合計の確認表です。
画像をタップすると拡大
9と109の違いは手動で隠した行の扱い
| 式 | フィルターで非表示の行 | 手動で非表示の行 |
|---|---|---|
| SUBTOTAL(9,範囲) | 除外する | 含める |
| SUBTOTAL(109,範囲) | 除外する | 除外する |
| SUM(範囲) | 含める | 含める |
9でもフィルターで除外された行は集計されません。109を使うと、行番号のメニューで手動非表示にした行も除外できます。SUBTOTALは縦方向の一覧向けの関数で、列を隠しただけでは同じように集計対象から外れません。詳細はMicrosoftのSUBTOTALの仕様でも確認できます。
実際に Excel で試した結果です。部署を「営業」に絞り込むと、9 も 109 も営業の4件だけを足して 4,900 になり、SUM は全部の 8,100 のままでした。
フィルターを使わずに 7・8行目を手で隠すと、9 は隠した行も足して 8,100、109 は隠した2行（800 と 1,500）を除いて 5,800 になりました。
【実機で検証】9 と 109 で結果が同じになる場面
「9 は手で隠した行も足す」とよく説明されますが、フィルターを使っているときは違う結果になりました。Excel の実機で確かめた結果です。
| 状態 | SUM | SUBTOTAL(9) | SUBTOTAL(109) | AGGREGATE(9,5) |
|---|---|---|---|---|
| 何もしない | 3100 | 3100 | 3100 | 3100 |
| 8 行目を手で非表示 | 3100 | 3100 | 2900 | 2900 |
| 「東」で絞り込み | 3100 | 2100 | 2100 | 2100 |
| 絞り込み＋手で非表示 | 3100 | 2000 | 2000 | 2000 |
| 金額の列を非表示 | 3100 | 3100 | 3100 | 3100 |
フィルターで絞り込んでいるときは、手で隠した行も 9 の合計から除かれました。また、列を隠しても、どの式も合計は変わりません。9 と 109 が見ているのは行の表示だけです。
エラーと入れ子の動き
| 場面 | 結果 |
|---|---|
| 範囲の中に別の SUBTOTAL がある | 二重に数えない。SUBTOTAL だけの列を SUBTOTAL で合計すると 0 |
| 同じ列を SUM で合計 | 3100（小計どうしを足す） |
| 範囲に文字がある | 文字を無視して合計 |
| 範囲に #DIV/0! がある | SUBTOTAL は #DIV/0!。AGGREGATE(9,3,範囲) なら無視して合計 |
表示中の件数や平均も計算できる
| 目的 | 集計方法 | 例 |
|---|---|---|
| 合計 | 109 | =SUBTOTAL(109,C9:C13) |
| 数値の個数 | 102 | =SUBTOTAL(102,C9:C13) |
| 空でないセルの個数 | 103 | =SUBTOTAL(103,A9:A13) |
| 平均 | 101 | =SUBTOTAL(101,C9:C13) |
行数を数えたいなら、必ず値が入る担当名や管理IDの列を103で数えます。空欄がある列を使うと行数と一致しません。また、数式の空文字は完全な空セルとは異なり、COUNTA系の件数に含まれることがあります。営業の例では、担当名の件数は3、金額の平均は2,000円です。
集計方法の番号一覧（1〜11・101〜111）
SUBTOTAL の1つ目の番号で、集計のしかたを選びます。1〜11 は手で隠した行も含め、101〜111 は手で隠した行も除きます。どちらもフィルターで除外された行は含めません。試したところ、それぞれ右の関数と同じ結果になりました。
| 番号 | 手で隠した行も除く番号 | 集計 | 同じ結果の関数 |
|---|---|---|---|
| 1 | 101 | 平均 | AVERAGE |
| 2 | 102 | 数値の個数 | COUNT |
| 3 | 103 | 空でないセルの個数 | COUNTA |
| 4 | 104 | 最大 | MAX |
| 5 | 105 | 最小 | MIN |
| 6 | 106 | 積 | PRODUCT |
| 7 | 107 | 標準偏差（標本） | STDEV.S |
| 8 | 108 | 標準偏差（母集団） | STDEV.P |
| 9 | 109 | 合計 | SUM |
| 10 | 110 | 分散（標本） | VAR.S |
| 11 | 111 | 分散（母集団） | VAR.P |
合計が変わらない・合わないとき
| 症状 | 確認する場所 |
|---|---|
| 絞っても12,000のまま | 合計セルがSUMではなくSUBTOTALになっているか。 |
| 一部の金額が足されない | 金額が文字列になっていないか。 |
| 追加した行が集計されない | 式の終了行が追加した行まで延びているか。 |
| 手動で隠した行が含まれる | 集計方法が9ではなく109か。 |
| 更新が遅れる | 計算方法が手動になっていないか。必要なら再計算する。 |
文字列の金額は数値への変換方法で整えます。固定範囲のC9:C13を使うサンプルでは、14行目以降を足したときに式の範囲も変更してください。テーブルの構造化参照を使えば、行の追加に合わせて範囲を追従させる設計もできます。
よくある質問
SUBTOTAL の 9 と 109、どちらを使えばいいですか？
フィルターだけを使うなら、どちらも同じ結果です。手で行を隠すこともあるなら、見えている行だけを集計する 109 が安心です。
件数や平均も、表示されている行だけで出せますか？
件数は 103（空でないセル）や 102（数値）、平均は 101 を使います。営業に絞り込んだ状態で試したところ、=SUBTOTAL(101,C6:C13) は 1,225 でした。
SUBTOTAL の結果を、さらに SUBTOTAL で合計するとどうなりますか？
SUBTOTAL は、範囲の中にある別の SUBTOTAL の結果を足さないので、小計と総計を同じ列に置いても二重に足されません。試したところ、100・200 の小計（SUBTOTAL）と 10・20 の小計を含む範囲を合計すると、SUBTOTAL は 330、SUM は小計まで足して 660 になりました。
条件の行を別シートに取り出したい場合
今の表を絞って合計する用途にはSUBTOTALが向いています。元の表を残し、条件に合う行を別の場所へ表示したいなら、FILTERで条件に合う行を抽出する方法を使います。重複や空白を整理してから集計する場合は、空白行を除外する方法も役立ちます。
次に確認したいこと
自分の表に使う前に、サンプルで入力セルと結果の変化を確認しましょう。数式の参照範囲は、実際の表の列と最終行に合わせて変更してください。
参考資料
連番の振り方を、オートフィルや0埋めも含めて確認したいときは、エクセルで連番を振る方法もあわせてご覧ください。
印刷する範囲を決めたいときは、エクセルの印刷範囲を設定する方法もあわせてご覧ください。
フィルターの基本操作（設定・解除・かからない原因）は、エクセルのフィルターのかけ方もあわせてご覧ください。
グループごとの小計は、エクセルの小計の出し方もあわせてご覧ください。
エラーを無視して合計・平均を出すなら、AGGREGATE関数の使い方もあわせてご覧ください。
コメント