Excelでフィルター後に表示されている行だけを合計したい場合は、通常のSUMではなく、SUBTOTAL関数を検討します。SUBTOTALはフィルターで除外された行を集計から外せます。
非表示行とフィルター除外行を区別
- 合計対象の金額列とヘッダー行の範囲を確定します。
- 手動で非表示にした行も除外する必要があるかを決めます。
- 集計範囲の途中に既存の小計が入っていないか確認します。
- 値が文字列になっている行やエラーセルが混ざらないかを確かめます。
SUBTOTALの9と109を使い分ける
- 集計対象がD2:D100の場合、
=SUBTOTAL(9,D2:D100)でフィルター対象の合計を計算できます。 - 手動で非表示にした行も除きたい場合は、集計方法の番号に109を使います。
- ［データ］→［フィルター］でカテゴリを1つに絞ります。
- 合計値が絞り込みによって変化するか確認します。
- 表示中の数行を手計算し、SUBTOTALの結果と一致するか確認します。
合計行の二重計算を避ける
SUBTOTALの9は合計、109は手動非表示行も除外した合計を意味します。- 小計を入れ子にすると二重計上しない仕様があるので、集計対象が純粋な明細か確認します。
- 横方向の範囲では非表示列の扱いが異なるため、縦の一覧表での用途と分けます。
表示行の手計算と照合する
- フィルターを解除すると全体合計へ戻るか確認します。
- フィルター適用前後の値を記録し、対象の明細と一致するか確かめます。
SUBTOTALの9と109を数字で理解する
例えばD2:D5に 100、200、300、400 の金額があり、行4をフィルターで除外した場合、=SUM(D2:D5) は1,000のままですが、 =SUBTOTAL(9,D2:D5) は700になります。さらに行3を手動で非表示にした場合、SUBTOTAL(9,...) は原則その行を含む一方、=SUBTOTAL(109,D2:D5) は非表示行も除いた500になります。9と109は合計の計算方法を指定する番号です。
Microsoft公式ヘルプによれば、フィルターで除外された行は9でも109でも集計から除かれます。違いは手動で非表示にした行を含むかどうかです。「画面で見えている行のみ」を厳密に合計したいなら109が適しています。
フィルターの結果と元データの検算を分ける
返品額・未入金など、集計対象にするかどうかが業務判断に関係する行は、フィルター条件自体を確認してください。途中に別のSUBTOTAL小計行がある場合、SUBTOTALが入れ子の小計を無視する仕様もあります。表の中で明細と小計が混在しているなら、集計前にどの範囲が「明細のみ」かを記録します。3〜5行の少数例で結果を手計算し、先に式の解釈を確かめると安全です。
参考資料
