Excelでフィルター後の合計が変わらない｜SUBTOTALで表示行だけ合計する方法
Excelで売上表を絞ったのに合計が変わらないと、「フィルターが壊れたのでは」と戸惑います。この例での原因は、合計にSUMを使っていることです。表示中の行だけを合計したいときは、SUBTOTALへ置き換えます。
SUBTOTALの109で表示中の行だけ合計する
合計を表示するセルに、次の式を入力します。
=SUBTOTAL(109,B2:B6)
109は「合計し、フィルターで除外された行と手動で非表示にした行を除く」という指定です。B2:B6は売上が入る縦の範囲なので、自分の表ではこの部分だけを合わせます。
MicrosoftのSUBTOTAL関数の説明によると、9と109はいずれも合計を求め、フィルター対象外の行は除きます。違いは、手動で隠した行を9は含め、109は除く点です。
SUMは指定範囲の数値を足す関数です。フィルターは条件外の行を削除せず隠すため、SUMの参照範囲は変わりません。その結果、見えている行が減っても合計は元のままになることがあります。
この記事の手順はWindowsデスクトップ版を想定しています。表と図は架空データおよび公式仕様から求めた期待値で、実際のExcel画面やアプリでの実測結果ではありません。
本文
ここからは、5行の売上表を使い、全行表示、フィルター後、手動非表示後の順に結果を比べます。式をコピーすれば、9と109を使い分ける基準も確認できます。
5行の売上表でフィルター前後を比べる
元表と3つの式を用意する
新しい空白シートのA1:B6に、次の架空データを入力します。売上は「1,200円」ではなく、1200のように数値だけを入力してください。
| セル | 地域 | 売上 |
|---|---|---|
| A2:B2 | 東 | 1200 |
| A3:B3 | 西 | 800 |
| A4:B4 | 東 | 1500 |
| A5:B5 | 西 | 500 |
| A6:B6 | 東 | 300 |
A1は「地域」、B1は「売上」です。次に、集計欄を表の外にあるD8:E10へ作ります。式を8行目以降に置くのは、A1:B6へフィルターをかけても集計欄を隠さないためです。
| 行 | D列のラベル | E列の式 | 全行表示の期待値 |
|---|---|---|---|
| 8 | SUM | =SUM(B2:B6) |
4300 |
| 9 | SUBTOTAL 9 | =SUBTOTAL(9,B2:B6) |
4300 |
| 10 | SUBTOTAL 109 | =SUBTOTAL(109,B2:B6) |
4300 |
全行表示では、1200+800+1500+500+300=4300です。SUM、SUBTOTALの9、109のどれを使っても、この時点の期待値は同じになります。
地域を「東」だけに絞る
操作はA1:B6だけを対象にします。A列・B列全体を選ぶ必要はありません。
- A1:B6を選択する
- 「データ」タブから「フィルター」を選ぶ
- A1の矢印を開き、「すべて選択」を外して「東」だけを選ぶ
- 「OK」を選ぶ
Microsoftのフィルター手順のとおり、条件に合わない行は削除されず、いったん非表示になります。
表示される東の売上は、2・4・6行目の1200+1500+300=3000です。図1のとおり、SUMは西の行も含む4300のままですが、SUBTOTALは9も109も3000になります。
SUBTOTALの9と109は手動で行を隠したときに違う
東の1500を手動で非表示にする
「東」だけのフィルターを保ったまま、4行目の行番号を右クリックし、「非表示」を選びます。セルを削除する操作ではありません。
4行目の1500を手動で隠すと、見える東の売上は1200と300だけです。9は手動非表示の1500も含めて3000、109は1200+300=1500になります。
| 表の状態 | SUM | SUBTOTAL 9 | SUBTOTAL 109 |
|---|---|---|---|
| 全行を表示 | 4300 | 4300 | 4300 |
| フィルターで東だけ表示 | 4300 | 3000 | 3000 |
| 東だけ表示し、4行目を手動非表示 | 4300 | 3000 | 1500 |
つまり、フィルターだけを使う表なら9でも表示行だけを合計できます。手作業で非表示にした行も集計から外したいなら、迷いにくい109を選びます。
練習後は再表示とフィルター解除を別々に行う
元に戻す操作は二段階です。まずA1の矢印から地域のフィルターをクリアします。3行目と5行目が見える状態になったら、行番号3〜5をまとめて選び、右クリックから「再表示」を選びます。
Microsoftの行の再表示手順も参考にしてください。両方を戻すと、E8:E10の期待値はすべて4300です。フィルターの解除だけでは、手動非表示の行まで戻りません。
合計が想定と違うときの確認点
範囲の先頭と末尾を確認する
データを7行目へ追加したのに式がB2:B6のままなら、新しい値は集計されません。式を選び、参照範囲の先頭と末尾が売上データに合っているか確認します。
合計セル自身を参照範囲へ入れるのも避けます。この例のように元表をA1:B6、比較式をE8:E10に分ければ、自分自身を参照する式になりません。
範囲内に別のSUBTOTALがないか確認する
SUBTOTALは、参照範囲内にある別のSUBTOTAL結果を二重に足しません。次は地域表とは別の、新しい空白シートで試す例です。全行を表示し、フィルターは使いません。
| セル | 入力内容 | 期待値 |
|---|---|---|
| B2 | 1200 | 1200 |
| B3 | 800 | 800 |
| B4 | =SUBTOTAL(9,B2:B3) |
2000 |
| B5 | 500 | 500 |
| E2 | =SUBTOTAL(109,B2:B5) |
2500 |
E2はB4の小計2000を再加算せず、明細の1200+800+500=2500になります。見えている数値を無条件ですべて足す関数ではない点に注意しましょう。
B4へ2000を直接入力した場合や、=SUM(B2:B3)で2000を出した場合は、SUBTOTALの自動除外の対象ではありません。そのときE2の期待値は4500です。
なお、SUBTOTALは縦方向の範囲で使う前提です。109を指定しても、非表示にした列を横方向の集計から除く指定にはなりません。この記事では非表示列の集計方法は扱いません。
まとめ
Excelでフィルター後も合計が変わらないときは、まず合計式がSUMのままになっていないか確認します。
- SUMは、フィルターで隠れた行を含む指定範囲全体を合計する
- SUBTOTALは、フィルターで対象外になった行を9でも109でも除く
- 手動で非表示にした行まで除くなら109を使う
最初は元表の外にあるセルへ、=SUBTOTAL(109,B2:B6)を入力してみてください。B2:B6は、自分の売上セルの範囲へ置き換えます。
結果が合わなければ、範囲の末尾と、範囲内に別のSUBTOTALがないかを順に確認すると原因を絞れます。
