エクセルは合計を求めるなんてことは得意中の得意なんですが、見えている範囲（行や列）だけを計算することが基本になっています。
なので、オートフィルターによる絞り込み（フィルター）や非表示の行があったりすると、通常は見えてない行までも合計されてしまいます。
でも、やっぱり見えてないところは合計してほしくないですよね＾＾
- オートフィルターで絞り込まれた行（見えている行）だけを合計する。
- 行を非表示にした時に、表示行（見えている行）だけを合計する。
- SUBTOTAL関数の万能な計算方法を紹介。
表を作っているとオートフィルターをかけたり、行を非表示にすることがよくありますが、いざ見えてない行を除外した計算をしようとしても、どうやったらいいかわからないという人が多いです。
そんな時に、今回紹介する方法を参考にしてください。
フィルター後の表示行だけを合計できるのはSUBTOTAL関数
合計する関数といえばSUM関数ですが、このSUM関数はフィルターや非表示などで見えなくなった行までも合計してしまいます。
それとは逆に、見えている行だけを合計してくれるのがSUBTOTAL関数。
下のエクセルではわかりやすくするために、『商品券の購入金額の合計』をSUM関数とSUBTOTAL関数の両方で計算させています。
まだフィルターによる絞り込みはしていないので、SUM関数、SUBTOTAL関数とも同じ合計が計算されています。
この状態で、『会員に●がついている人だけ』をフィルターした結果がこれです。
SUM関数は見えてない行まで合計しているので、フィルターする前と同じ金額になっています。
対して、SUBTOTAL関数は見えている行だけを合計していることがわかります。
表示行だけを合計するSUBTOTAL関数の使い方
フィルターで見えている行だけを合計したい時のSUBTOTAL関数は、次のように入力します。
例として、下のエクセルようにセル『F3』から『F10』までの合計を求める場合です。
=SUBTOTAL(9, F3:F10)
第一引数には『9』、第二引数に合計の計算範囲（F3:F10）を指定します。
第一引数は『計算方法』を指定する引数で、『9』は『合計』を求める計算方法となります。
第一引数の『計算方法』については、後述の『SUBTOTAL関数の万能な計算方法』で詳しく説明します。
これで、SUM関数『＝SUM(F3:F10)』と同じ計算をしますが、SUM関数との違いは『フィルターで見えなくなった行は除外して合計する』ということです。
行の非表示で表示されている行だけを合計する
続いては、フィルターではなく、行を非表示にした場合に表示されている行だけを合計する方法です。
下のエクセルでは、男の行の5行目と7行目を『非表示』にした結果です。
こちらもフィルター同様に、『見えている行』だけが合計されていることがわかります。
非表示行を除外した行を合計するSUBTOTAL関数は、次のように入力します。
=SUBTOTAL(109, F3:F10)
フィルターの場合とは第一引数が違います。
合計の計算方法には『9』に100を足した『109』を指定できます。
『109』は『非表示行を除外する合計』という意味にになります。
行を非表示にした場合は、第一引数の計算方法は『109』を指定する必要がありますが、フィルターの場合はこの計算方法は『109』を指定しても構いません。
SUBTOTAL関数の万能な計算方法を紹介
最後に、SUBTOTAL関数の計算方法（引数）について補足します。
SUBTOTAL関数はちょっとした万能な計算関数です。
第一引数に『9』(または『109』）を指定することで、SUM関数のように合計を求めることができ、同じく第一引数に『1』(または『101』）を指定するとAVERAGE関数のように平均を求めることができます。
SUBTOTAL関数で指定できる第一引数の計算方法を次の通り。
| 非表示行も含めて計算 | 非表示行を除外して計算 | 対応する計算 |
|---|---|---|
| 1 | 101 | AVERAGE（平均） |
| 2 | 102 | COUNT（数値が入力されているセルの個数） |
| 3 | 102 | COUNTA（何か入力されているセルの個数） |
| 4 | 104 | MAX（最大値） |
| 5 | 105 | MIN（最小値） |
| 9 | 109 | SUM（合計） |
他にも、PRODUCT（数値の積）、STDEV（標準偏差）などの計算もありますが、ほとんど使わないので割愛しています。
SUBTOTAL関数には便利な使い方が他にもある
SUBTOTALは日本語に訳すと『小計』という意味で、『小計』だけを合計するということが簡単にできます。
表に小計を使っている場合には重宝する関数だと思うので、次の記事で使い方を参考にしてください。
また、SUBTOTAL関数は、フィルターがかかっていたり、非表示行や削除された行があっても、正しく連番を入力することもできます。
こちらもかなり便利な機能なので、次の記事をあわせて参考にしてください。