DIME MAGAZINE
ExcelのSUBTOTAL関数はさまざまな集計値を求められる便利な関数であり、資料作成などで役立つ。この記事では、SUBTOTAL関数で合計値や数値の個数などを求める方法について解説する。また、SUM関数とSUBTOTAL関数の違いや、SUBTOTAL関数を使うべき場面についてもまとめた。
目次
SUBTOTAL関数をご存じだろうか？集計をする際に便利な関数の活用術を解説する。
SUBTOTAL関数とは？
SUBTOTAL関数は、集計の種類を指定してさまざまな集計値を求めることができる関数だ。Excelには足し算が行えるSUM関数、平均値を求められるAVERAGE関数などがある。SUBTOTAL関数はそのような集計系の関数の機能がひとまとめになっている。
SUBTOTAL関数ひとつでさまざまな集計を行うことが可能だ。
SUBTOTAL関数の書式は次のようになっている。
書式:=SUBTOTAL(集計方法,参照)
■1.集計方法
1つ目の引数は「集計方法」を指定する必要がある。集計方法に関しては番号で指定する。たとえば、平均値を求めたい場合は「1」、数値の個数を求めたい場合は「2」を指定する。
各番号ごとの集計方法は次のとおりである。
| 第1引数の値 | 集計するもの | 同じことが可能な関数 |
| 1 | 平均値 | AVERAGE |
| 2 | 数値の個数 | COUNT |
| 3 | データの個数 | COUNTA |
| 4 | 最大値 | MAX |
| 5 | 最小値 | MIN |
| 6 | 積 | PRODUCT |
| 7 | 不偏標準偏差 | STDEV.S |
| 8 | 標本標準偏差 | STDEV.P |
| 9 | 合計値 | SUM |
| 10 | 不偏分散 | VAR.S |
| 11 | 標準分散 | VAR.P |
■2.参照
2つ目の引数には「参照」、すなわち集計対象を指定する。たとえば「A1:A5」とすると、A1〜A5までの値を集計対象とすることができる。
=SUBTOTAL(1,A1:A5)
このように記載した場合は、「A1〜A5」の値の平均値を求められる、ということになる。
SUBTOTAL関数を使って集計を行う方法
SUBTOTAL関数を使って次の集計を行う方法をみていこう。
- 合計値を求める
- 数値の個数を求める
- 最大値・最小値を求める
- 平均値を求める
■1.合計値を求める
SUBTOTAL関数で合計値を求めるには、1つ目の引数に「9」を指定する。
以下では、A2〜A8の値の集計を行っている。
以下のように「=SUBTOTAL(9,A2:A8)」と入力してみよう。
実行すると「26」と表示される。A2からA8までの値の合計値が表示されていることが分かる。
■2.数値の個数を求める
続いて、数値の個数を求める方法を解説する。数値の個数を求める場合は、第1引数を「2」にする。
以下では「=SUBTOTAL(2,A2:A8)」と入力している。
実行すると「7」と表示され、A2〜A8の数値の個数を表示できている。
■3.最大値・最小値を求める
続いて、最大値・最小値を求める方法を解説する。
まず最大値を求める場合は、第1引数に「4」を指定する。
以下の画像では「=SUBTOTAL(4,A2:A8)」と入力している。
実行すると「7」と表示され、A2〜A8の最大値であると分かる。
最小値を求める場合は、第1引数に「5」を指定する。
「=SUBTOTAL(5,A2:A8)」と入力してみよう。
実行すると「1」が表示され、最小値であると分かる。
■4.平均値を求める
最後に平均値を求める方法をみていこう。平均値の場合は、第1引数に「1」を指定する。
「=SUBTOTAL(1,A2:A8)」と入力する。
すると「3.714……」と表示され、平均値が取れていることが分かる。
SUM関数とSUBTOTAL関数の違いは？
SUBTOTAL関数をわざわざ使わなくても、「SUM関数などを使えば良いのでは？」と思う方もいるかもしれない。ただ、SUBTOTAL関数とSUM関数では明確にできることが異なる。
まず、SUBTOTAL関数は合計値だけでなく、平均値や個数などさまざまな集計を行うことができる。
また、SUBTOTAL関数は集計範囲を指定する必要があるのも特徴だ。SUM関数は集計範囲を指定せず、1セルずつ指定しても合計値を求めることができる。たとえば、「=SUM(A1,A2,A3)」とすれば、A1〜A3の合計値を求められる。
SUBTOTAL関数の場合はこのような書き方はできず、「=SUBTOTAL(9,A1:A3)」と、あくまで集計範囲を指定しないといけない。
■SUBTOTAL関数を使って小計を省いて合計値を求める方法
もう1つ大きな違いとして、SUBTOTAL関数は小計を省いて合計値を求めることができる。
たとえば以下のような、日付ごとの売上額が書かれた表があるとする。表の中には「1月合計」「2月合計」というように、「小計」も書かれている。SUBTOTAL関数では、「小計」を省いて売上の合計値を求めることが可能だ。
画像のように「=SUBTOTAL(9,B2:B9)」と入力してみよう。
実行すると、「¥8,300」と表示されており、「小計」を省いて合計額を求めていると分かる。
まとめ
本記事ではSUBTOTAL関数について解説した。最後にSUBTOTAL関数を使った集計方法についておさらいしよう。
- 合計値を求める
- 例：=SUBTOTAL(9,A2:A8)
- 数値の個数を求める
- 例：=SUBTOTAL(2,A2:A8)
- 最大値・最小値を求める
- 例（最大値）：=SUBTOTAL(4,A2:A8)
- 例（最小値）：=SUBTOTAL(5,A2:A8)
- 平均値を求める
- 例：=SUBTOTAL(1,A2:A8)
SUBTOTAL関数は他にも、各値の積を求めたり標準偏差を求めるといったことができる。
構成／編集部
最新号
