はじめに
Excelでは、表の合計値を算出する場合 SUM 関数がよく使われます。
しかし、SUM 関数よりも優れた機能を持つ SUBTOTAL 関数はあまり知られていません。
データの合計を算出するだけなら SUM 関数でも問題ないのですが、 SUBTOTAL 関数は関数一つで、平均値や最大値、最小値などさまざまな集計値を算出できたり、表の小計値を求める場合にも便利な関数です。
このページでは、SUBTOTAL 関数の使い方をわかりやすく説明します。
SUBTOTAL関数
SUBTOTAL関数は選択したデータ範囲のさまざまな統計値（合計、平均など）を算出する関数です。
算出する統計値を「集計方法」で指定し、「参照」でデータ範囲を指定します。
集計方法
以下の表の「集計方法」の値を指定します。
「集計方法」には、非表示にしたセルの値を含めるか含めないかどちらかを選択できます。
- 非表示にしたセルの値を含める場合 → 1,2,3, .... 11 (表の1列目)
- 非表示にしたセルの値を含めない場合 → 101,102,103, .... 111 (表の2列目)
| 集計方法（非表示含める） | 集計方法（非表示除く） | 関数 | 説明 |
| 1 | 101 | AVERAGE | 平均値 |
| 2 | 102 | COUNT | 数値の個数 |
| 3 | 103 | COUNTA | 空白でないセルの個数 |
| 4 | 104 | MAX | 最大値 |
| 5 | 105 | MIN | 最大値 |
| 6 | 106 | PRODUCT | 積 |
| 7 | 107 | STDEV.S | 標準偏差(標本) |
| 8 | 108 | STDEV.P | 標準偏差(母集団) |
| 9 | 109 | SUM | 合計 |
| 10 | 110 | VAR.S | 不偏分散 |
| 11 | 111 | VAR.P | 標本分散 |
参照1,参照2 ....
参照するデータ範囲を指定します。参照１,参照2 というように複数指定することもできます。
具体例
下記表の売上（B列）の合計値を求めるとします。その場合、下記のように指定します。
- 「集計方法」= 1
- 「参照」= $B$2:$B$B7
これだけなら 「SUM 関数でよくない？」と思うかもしれません。
それでは次に、SUM関数との違いについて説明します。
SUM関数との違い
非表示セルに対応できる
例えば、下記データの「みかんB」という行を非表示にします。
すると非表示前後で SUM 関数は同じ値ですが、SUBTOTAL関数では非表示のセルは集計しなくなります。
ただし、SUBTOTAL関数の「集計方法」に指定する値を 101 ～ 111 にすることを忘れないでください。
下記画面では、E2セルに "=SUBTOTAL(109,B2:B7)" と設定しています。
小計がラクに出せる！
下記赤枠をデータ範囲とし、そのデータ範囲の合計を算出します。
なお、５行目と９行目はそれぞれ「りんご」の小計と「みかん」の小計とします。
すると、SUM関数の場合、「りんご」の小計と「みかん」の小計を含めて計算してしまいますが、
SUBTOTAL 関数の場合、小計を除いて計算してくれます。
SUM関数で小計を除いて計算する場合いちいち範囲を決めて合計を求めますが、SUBTOTAL 関数はその手間なく計算できます。
※ ただし、小計値（B列の5行目、9行目）は SUBTOTAL 関数にしてください（SUM関数では小計を含んで計算されます）。
最後に
いかがでしたでしょうか？
SUM 関数の知名度と比べて、SUBTOTAL関数はあまり知られていない関数ですが、小計の計算などをする場合に非常に便利な関数です。
また、合計だけを例にあげましたが、平均や標準偏差などの集計も行うことができる万能な関数です。
ぜひ使いこなしてみてください！
