SUBTOTAL関数は、さまざまな集計値を求めるスーパー関数です。
SUBTOTAL関数を使えば、数値の合計や平均、最大値、最小値などを一つの関数で求めることができます。特に、小計を含む表の合計を求める際に役立ちますので、覚えておいてください。
SUBTOTAL関数の機能を強化した「AGGREGATE関数」は下記を参照してください。
SUBTOTAL関数の使い方
対応バージョン：Excel365 2019 2016 2013 2010
SUBTOTAL関数の書式です。
=SUBTOTAL(集計方法,参照1,…,参照254）
※カッコ内の「集計方法,参照1,…,参照254」を引数といいます。
引数の説明
- 〔集計方法〕：どういう集計値を求めるか「集計方法を番号（詳細は後述）」で指定。オートフィルタで非表示にした行は集計の対象外になります。ただし、横方向に集計する場合、非表示の列は集計の対象から除かれません。オートフィルタを使わずに非表示とした行を除く場合は101~111を使う。
- 〔参照〕：集計の対象範囲を指定
集計方法の番号の種類
| 集計方法 | 意味と同等の関数 |
|---|---|
| 1または100 | 平均（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） |
SUBTOTAL関数の基本的な使い方
それでは、実際のサンプルでSUBTOTAL関数の使い方をイメージしましょう。
以下のサンプルは、果物の売上額を月別に集計した表です。月が替わるごとにSUBTOTAL関数で「小計」を算出し、最後にSUBTOTAL関数で「合計」を算出しています。
まず、4月の小計を求めるので、C6セルに「SUBTOTAL関数」を入力し「小計」を求めます。
関数式は以下の通り。
=SUBTOTAL(9,C2:C5)
関数式の説明
引数〔集計方法〕には、合計（小計）を求めるので「9」を指定。集計方法に「9」を指定することで、SUM関数と同様に対象範囲を集計することができます。
引数〔参照〕には、小計を算出する範囲を指定しますので「C2:C5」を指定しています。
以降、5月、6月も同様の方法で「小計」を算出します。
最後に、表の最終行に「合計」を「SUBTOTAL関数」で算出しています。
SUBTOTAL関数は小計やオートフィルターを使うときに便利
テーブルには、データを選別する「フィルタ」機能があります。
選別されたデータのみを集計する場合は、「SUM関数」ではなく「SUBTOTAL関数」が便利です。SUM関数はフィルタによって選別されたデータのみを集計することはできませんが、SUBTOTAL関数は非表示となった行は集計対象から除外されるので、結果として選別されたデータだけの集計値を求めることができます。
また、今回のサンプルのように、表中に「小計」があり最終行に「合計」があるようなフォーマットの場合、最終行の合計は「SUBTOTAL関数」で算出した小計を無視してくれます。ただし、小計を「SUM関数」で求めている場合は無視してくれませんので、注意しましょう。
SUBTOTAL関数と同じ分類の関数
SUBTOTAL関数と同じ「合計/平均」系の関数は以下のページで探せます。
