今回は、Excelでフィルター機能を使ってデータを絞り込んだ際、表示されているデータだけを正確に合計したり平均を出したりできる「SUBTOTAL（サブトータル）関数」の使い方について紹介します。
SUM関数とフィルターの落とし穴
Excelで売上表などのデータを作成し、一番下の行に「SUM関数」を使って合計金額を出しているケースは非常に多いです。
しかし、この状態で「オートフィルター」を使い、「A支店のデータだけ」を表示するように絞り込みを行ったとします。画面上にはA支店のデータしか見えていませんが、一番下のSUM関数の合計金額は、「隠れているB支店やC支店の金額」も含んだ、全体の合計金額のまま変わらないという問題が起こります。
「今見えている行（抽出されたデータ）だけを計算したい」という時に、SUM関数の代わりに使うべきなのが、この「SUBTOTAL関数」です。
SUBTOTAL関数の基本的な使い方
SUBTOTAL関数は、単純な足し算だけでなく、設定する「集計方法の番号」を変えることで、平均やデータの個数など、様々な計算を行える多機能な関数です。
関数の書き方（構文）
=SUBTOTAL(集計方法, 参照1, [参照2], ...)
最初に「何の計算をするか」を番号で指定し、次に「計算する範囲」を指定するというシンプルな構造です。
見えているデータだけを合計する
合計を出したい場合は、集計方法の番号として「9」を使います。
- 計算結果を表示させたいセル（表の一番下など）を選択します。
=SUBTOTAL(9,と入力します。（9は合計・SUMを意味する番号です）- 計算したいセル範囲（例：C2からC100まで）をマウスでドラッグして選択します。
- カッコを閉じてEnterキーを押します。（例：
=SUBTOTAL(9, C2:C100)）
この数式を設定しておけば、フィルターで「A支店」だけを抽出した時、合計金額も連動して「A支店だけの合計」に自動的に切り替わるようになります。
集計方法の番号（1〜11と101〜111）の違い
SUBTOTAL関数の最初の引数である「集計方法」には、1から11までの番号と、101から111までの番号の2種類が用意されています。この違いを理解することが、関数を完璧に使いこなす鍵です。
よく使う集計方法の番号一覧
- 1（または101）： AVERAGE（平均）
- 2（または102）： COUNT（数値の個数）
- 3（または103）： COUNTA（空白以外のセルの個数）
- 9（または109）： SUM（合計）
フィルターで隠した行と、手動で隠した行
1桁の番号（1〜11）と100番台の番号（101〜111）の決定的な違いは、「手動で非表示にした行を計算に含めるかどうか」です。
- 1桁の番号（例：9）：
フィルター機能によって隠れた行は計算から除外しますが、行番号を右クリックして「非表示」にした手動の隠し行は、計算に含めてしまいます。 - 100番台の番号（例：109）：
フィルター機能で隠れた行も、手動で「非表示」にした行も、見えていないものはすべて計算から除外します。
「とにかく画面に見えているものだけを計算したい」という確実な結果を求める場合は、常に100番台の番号（合計なら109）を使うように習慣づけておくと、計算ミスを防ぐことができます。
SUBTOTAL関数をワンクリックで挿入する
関数をわざわざ手打ちしなくても、Excelにはこれを自動で入力してくれる便利な機能があります。
フィルターがかかっている（見出しに▼マークがついている）表のすぐ下の空白セルを選択し、「ホーム」タブの右端にある「オートサム（Σ）」ボタンをクリックしてみてください。Excelが自動的に表がフィルター状態であることを検知し、SUM関数ではなくSUBTOTAL関数を自動で入力してくれます。
まとめ
Excelでリスト形式のデータを管理し、フィルターで絞り込みながら数値を分析する際には、SUM関数ではなくSUBTOTAL関数を使うのが鉄則です。「見えているものだけを計算する」という特性と、「109（完全に表示されているものだけを合計）」という番号の指定方法を覚えておけば、集計作業の正確性と効率が大きく向上します。表を作成する時は、一番下の合計行をこの関数に変えておくことをおすすめします。