Excelで表を絞り込んで合計する方法！オートフィルター×SUBTOTAL関数で「表示中だけ」を集計

Excelで表を絞り込んで合計する方法を知りたいときはないでしょうか。

けど、そんな中で悩むことは、

・オートフィルターで絞り込んでも合計が変わらない
・表示中の行だけを合計する方法がわからない
・毎回手で計算し直すのが手間で、ミスが起きる

ですよね。

ご安心ください。今回はそんなお悩みを解決する

・Excelで表を絞り込んで合計する方法（SUBTOTAL関数）

についてまとめます！

完成イメージ

絞り込んだ分だけを合計したい場合、SUM関数では非表示になっている行まで合計してしまうため、絞り込みを変えても結果が変わりません。
今回は
・オートフィルターで絞り込んだ「表示中の行だけ」を合計する方法（=SUBTOTAL(109, 範囲)）
をゴールにします。
絞り込みを切り替えると、合計値も自動で追従するようになります。

なお、毎回手で数式を入れるのが手間な方向けにVBAで合計行を一括挿入する方法と、うまくいかないときの対処法（トラブルシュート）もあわせてご紹介します。
早速実装して試してみましょう！

STEP1. 元データを用意する

まずは集計したい売上表を用意します。
1行目が見出し、2行目以降がデータです。
金額列（F列）は手入力ではなく =D2*E2 の数式にしておきましょう。
数式にしておくと、あとから数量や単価を変更しても金額が自動で変わるのでおすすめです。

STEP2. オートフィルターを設定する

表の中のセルを選択し、データタブ → フィルターをクリックします。
見出し行に▼ボタンが付きます。
ショートカットは Ctrl+Shift+L です。
マウスで操作しなくても、このショートカット1つで設定できるのでカンタンですね。

STEP3. 支店で絞り込む

「支店」の▼をクリックし、東京だけにチェックを入れてOK。
東京の行だけが残り、他の行は非表示になりました。
絞り込みは何度でも切り替えられるので、操作を間違えてもご安心ください。

STEP4. 絞り込んだ分だけを合計する

合計を出したいセルに SUBTOTAL関数 を入力します。
ここでは合計行の F24セル に入れてみましょう。

=SUBTOTAL(109, F2:F21)

POINT

絞り込み集計の合計は =SUBTOTAL(109, 範囲)。
SUM関数は非表示の行も合計してしまうため、フィルター集計には向きません。

数式はどのセルに入れる？

セルを選んで数式バーに入力するだけなので、ご安心ください。
実際の入力画面がこちらです。
入力中は関数の引数のヒントが表示されるので、引数の順番を確認しながら進められます。

SUMとSUBTOTALで結果が違う理由

同じ範囲を指しているのに、SUMは全件合計、SUBTOTALは表示中だけになります。

関数	東京で絞り込んだときの結果	理由
=SUM(F2:F21)	3,308,000（全20件）	SUMは非表示の行も合計に含めてしまう
=SUBTOTAL(109,F2:F21)	1,647,800（東京8件のみ）	先頭の「1」で非表示行を除外する

VBAで合計行を一括挿入する

毎回手で数式を入れるのが面倒なときは、VBAで合計行を一括挿入するのもおすすめです。
ボタン1つで毎回の集計作業をラクにできます。

Sub InsSubtotal()
    Dim lastRow As Long
    lastRow = Cells(Rows.Count, 4).End(xlUp).Row
    Cells(lastRow + 1, 6).Formula = "=SUBTOTAL(109, F2:F" & lastRow & ")"
    Cells(lastRow + 1, 5).Value = "合計（表示中のみ）"
End Sub

タカヒロ

最初の 109 がミソです。
ここを 9 にすると絞り込みを無視して全件合計になってしまうので、絞り込み集計なら 109 と覚えてしまいましょう。
むずかしく感じますが、覚える数字はたった1つなのでカンタンですね。

うまくいかないときの対処法（トラブルシュート）

実際に試すと、思ったように動かないこともあるかもしれません。
よくあるつまずきと対処法をまとめましたので、ご安心ください。

症状	原因	対処
合計が 0 になる	金額が文字列として入力されている	F列を選択して数値に変換する／=VALUE() で数値化する
絞り込んでも合計が変わらない	先頭の引数が 9（SUM）になっている	=SUBTOTAL(109, 範囲) に変更する
手で非表示にした行も除外したい	109 と 9 の違い	手動の非表示も除外するなら 109、全件合計なら 9
合計行が絞り込みで消える	合計行がフィルター範囲に入っている	フィルター範囲を A1:F21（データ行のみ）に限定する
合計が二重に足される	SUBTOTALの中にSUBTOTALがある	ネストしたSUBTOTALは集計対象外になるため、参照範囲を見直す

関数の説明

今回使う2つの関数の概要です。
どちらも集計の基本になる関数なので、違いを押さえておきましょう。

SUM関数（サム）

書式	=SUM(数値1, [数値2], …)
できること	指定した範囲や数値の合計を求める、もっとも基本の集計関数です。
戻り値	合計値（数値）
こんなときに	絞り込みをせず、範囲全体の合計を出したいとき。
注意	非表示の行も合計に含まれます。フィルターで絞り込んでも結果は変わりません。

SUBTOTAL関数（サブトータル）

書式	=SUBTOTAL(集計方法, 範囲)
できること	リストやデータベースの集計を行います。第1引数で合計・平均・個数などを選べ、非表示の行を除外するかも指定できます。
第1引数（抜粋）	9=SUM／1=AVERAGE／2=COUNT／4=MAX／5=MIN 先頭に 1 を付けた 109 / 101 / 102 … は「手動で非表示にした行も除外」
こんなときに	オートフィルターで絞り込んだ表示中の行だけを集計したいとき。
ポイント	絞り込みを変えると結果が自動で追従します。合計だけでなく平均・個数にも使えます。