SUBTOTAL関数の使い方:さまざまな方法でデータの集計値を求める関数:Excel関数

SUBTOTAL関数の使い方:さまざまな方法でデータの集計値を求める関数:Excel関数

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関数で「合計」を算出しています。

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関数と同じ「合計/平均」系の関数は以下のページで探せます。

Scroll Up