SUBTOTAL関数の多機能集計：SUM以外の特徴も徹底解説

集計作業を行う人は、必ずマスターしておきたいSUBTOTAL関数。
合計を求めるだけならSUM関数でいいのでは・・と思っているあなたが知らない便利な集計方法を徹底解説します。例題を使って、基本操作から実務活用事例を紹介しますので、明日から役に立つ情報になっております。一緒にSUBTOTAL関数をマスターして集計作業のプロの技を身につけましょう！

SUBTOTAL関数とは

SUBTOTAL関数は、11種類の集計が行える関数です。

集計方法 （非表示の値を含む）	集計方法 （表示の値のみ）	集計の種類	同等の関数
1	101	平均値を求める	AVERAGE
2	102	数値の個数を求める	COUNT
3	103	データの個数を求める	COUNTA
4	104	最大値を求める	MAX
5	105	最小値を求める	MIN
6	106	積を求める	PRODUCT
7	107	不偏標準偏差を求める	STDEV
8	108	標本標準偏差を求める	STDEVP
9	109	合計値を求める	SUM
10	110	不偏分散を求める	VAR
11	111	標本分散を求める	VARP

単体で集計をする関数（例：SUM関数）との違いとして、下記の3つの特徴を持っています。

単体での集計関数との違い：SUM関数との比較

SUBTOTAL関数の集計方法に、「合計値（SUM）」があります。
SUM関数に似ている機能ですが、SUBTOTAL関数との違いがあります。

1. フィルターの抽出結果で計算結果に差がでる
2. 小計を無視して計算できる

1つ目の特徴を見ていきましょう。
フィルターで抽出を行っていない状態では、集計結果に差は出ません。

しかし、フィルターで抽出すると集計結果に差が出ます。

SUM関数：目に見えていないデータ（非表示）もすべて含めて合計する
SUBTOTAL関数：目に見えているデータのみを合計する

２つ目の特徴「小計を無視して計算できる」については、基本的な使い方で問題を解きながら解説します。

【基礎】SUBTOTAL関数の使い方と注意事項

ここからは、基本的な使い方と、はじめにおさえておきたいよくある間違いについて解説します。

基本的な使い方｜小計を無視して足し算の合計を求める

小計がある表で合計を求める時、SUBTOTAL関数が便利です。

SUBTOTAL関数は前述した通り、
「その範囲内にSUBTOTAL関数がある場合、その値を無視して計算する」
という特徴をもっています。

下図の例では、D19に売上の総合計を出力しています。
＊条件としては、SUBTOTAL関数が入っている小計（D8、D13、D18）を無視

このように、参照範囲内にあるSUBTOTAL関数を無視してくれるので、合計を求めたい箇所の参照範囲を単純にすることができます。
同じことをSUM関数で行おうとすると、参照範囲が細切れになり、参照範囲を間違える小さなミスが発生しやすくなります。

【実践】SUBTOTAL関数の実務活用例

ここからは実務でSUBTOTAL関数が、どのような活用がされているのかを確認していきます。
注意しておきたいポイントと一緒に、2つの事例を確認していきましょう！

行の非表示に対応した集計を行う：SUMの場合

「合計値を求める」ためには、第一引数に9または109を指定します。
しかし、この2つには明確な違いがあります。

フィルターをかけた場合、集計の計算結果には影響が出ませんが、手動で「行の非表示」を使った場合は、集計結果に差が出ます。
意外と理解できていないポイントなので、ここでしっかり押さえておきましょう。

SUBTOTAL関数だけに頼らない集計をする

SUBTOTAL関数で集計方法を9（合計値）に指定したにも関わらず、正しく計算されない場合があります。
下の例を見てみましょう。

正しく関数を入力しているのに、集計結果が０になってしまう箇所ができてしまいました。
どこに原因があるのか、表の中身を探ってみると・・

一見すると問題ないように見えるのですが、赤枠の第二引数に注目してください。
実は、第二引数の参照範囲（オレンジ部分）には、SUBTOTAL関数のみが含まれています。

「その範囲内にSUBTOTAL関数がある場合、その値を無視して計算する」
という特徴があるため、参照範囲内にSUBTOTAL関数しかない場合、計算結果は０となります。

この現象を回避するには、SUM関数を組み合わせることが有効です。

次の2点の変更をして、正しい集計結果を得ることができました。

• 各支店の第2四半期の合計をSUM関数を用いて計算する
• 各地区の第2四半期の小計の参照範囲を、行方向から列方向にする

SUBTOTAL関数の特徴を理解し、他の関数と組み合わせて活用しましょう。

【小技】便利テクニック

ここでは、SUBTOTAL関数の特徴「集計」とは少し違うテクニックを1つ紹介します。

フィルターに対応する連番を振る方法

テーブルを設定した表に、第一引数「3（データの個数）」を設定すると、フィルターに対応した連番を、自動で振ることができます。

番号を表示させたいセル（今回はB3）に以下の式を入力します。
「=SUBTOTAL(3,$C$3:C3)」

第二引数のスタート部分を絶対参照にする！

テーブルが設定されていない場合の注意点

テーブルが設定されていない場合、フィルターをかけると範囲の最終行が常に表示されてしまいます。

この現象は、SUBTOTAL関数が最終行を「合計行」と認識してしまい、データ行として扱わないのでフィルターに含めない処理がされていることが原因と考えられます。

SUBTOTAL関数が入った列（B列）のフィルターを外す

SUBTOTAL関数が入った列（B列）のフィルターを外すことで対応できますが
対象の表を初めからテーブルに設定しておくことをオススメします。

フィルターを外して対応したい方に、特定の列だけフィルターを外す方法も紹介しておきます。

まとめ

SUBTOTAL関数は、集計方法を指定することで、さまざまな集計ができる関数です。
重要なポイントは3つでした。

実務でも使用できる場面があるので、ぜひ業務効率の向上にお役立て下さい。

「面倒な業務を効率化して残業を減らしたい」
「プロのExcel技を身に着けて社内で頼られる存在になりたい」

そんな方にはユースフルの公式LINEがオススメです！
MicrosoftからもMVPとして評価されているExcel実務のプロ、長内孝平（おさ）が監修、
LINE会員限定の豪華4大特典を無料でプレゼント中です！
プレゼント内容には、業務効率化のヒントになるものがたくさん入っています！

ぜひ下記の画像をタップして受け取ってくださいね！