【Excel】フィルター後の合計をSUBTOTALで出す方法|9と109の違い

フィルターで一部の行だけ表示しても、SUMの合計が変わらないのは通常の動作です。全体の合計と、抽出後の合計は目的が違います。抽出結果に連動させたい時はSUBTOTALを使い、手動で隠した行を含めるかどうかを集計番号で選びます。

1. 合計したいのは全体か、表示中の行か

B2:B4に100・200・300があり、フィルターで200の行を除いたとします。SUMは600のままですが、SUBTOTALの9と109はどちらも400になります。今度はフィルターを解除し、200の行を手動で非表示にすると、9は600、109は400です。

行の状態	SUM	SUBTOTAL 9	SUBTOTAL 109
フィルターで除外	含む	含まない	含まない
手動で非表示	含む	含む	含まない

2. 合計セルをデータ範囲の外へ置く

売上金額がE2:E100なら、集計セルへ次の式を入力します。E2:E100の中にこの集計セル自身を置くと循環参照になるので、E101や表の上など範囲外を選びます。

=SUBTOTAL(109,E2:E100)

フィルター条件を変え、抽出件数と合計が連動するか確認します。部署ごとの合計なら、部署名の空欄や表記揺れがフィルターから漏れていないかも確かめます。計算式が正しくても、抽出条件が違えば欲しい集計にはなりません。

3. 9と109に優劣はない

一時的に見づらい行を隠しただけで売上総額を変えたくないなら9が適します。手動で隠した行も集計から外したい時は109です。「109なら常に安全」と決めると、他の人が表示を整えるために隠した行まで集計から消えるおそれがあります。

集計セルの横へ「抽出分の合計」「非表示行を除く合計」などの説明を付けると、後から見た人が数値の意味を確認できます。全体の売上も必要なら、SUMによる全体合計を別に残して比較します。

4. 件数と平均にも使える

平均は1または101、数値セルの個数は2または102、空でないセルの個数は3または103です。100を加えた側が手動非表示行を除きます。番号3のCOUNTA相当では、空文字を返す数式も数える点に注意してください。

件数は、必ず値が入る伝票番号などの列で数えると目的をそろえやすくなります。任意入力の備考列では、備考が空の取引が件数から漏れます。平均も空白と0で結果が異なるため、未入力を一律に0へ置き換えないでください。

5. テーブルの集計行で範囲を管理する

行が増える表はテーブル化し、「テーブルデザイン」の「集計行」を使う方法もあります。集計行のメニューで合計を選ぶとSUBTOTALの式が入るため、集計番号と列名を数式バーで確認します。

通常のE2:E100という式のまま101行目へ追加した値は範囲外です。テーブルでも、追加行が実際にテーブル内へ取り込まれたかは確認が必要です。色が似ているだけで範囲に含まれるとは判断しないようにします。

6. 非表示の列を除外する関数ではない

SUBTOTALは縦方向の一覧に使う集計です。B2:G2を横に合計した時、C列を隠してもその値は除外されません。AGGREGATEへ置き換えれば非表示列を自動除外できる、という使い方でもありません。

横方向の対象を切り替えたいなら、集計対象を明示する行を設けるなど、表示状態とは別に条件を管理します。また、範囲内の別のSUBTOTALは二重集計を避けるため除かれますが、手入力した「小計」や通常のSUMが自動的に除かれるわけではありません。

7. 合計が変わらない時の確認順

式がSUMのままではないか、対象セルが参照範囲内か、フィルターが表全体に掛かっているか、計算方法が手動になっていないかを確認します。数値に見える文字列も合計漏れの原因になるので、問題の数件を取り出して確かめると切り分けやすくなります。

参考: Microsoft公式: SUBTOTAL関数

📊

Excelトラブル完全解決データベースこの記事以外にも、様々なエラー解決策をまとめています。困った時の逆引きに活用してください。

🏆

超解決 Excel検定 あなたのExcel実務能力を3分で測定！【1級・2級・3級】