ExcelのSUBTOTAL関数とは、集計の種類を指定してさまざまな集計値を求めることができる関数です。この記事ではSUBTOTAL関数の使い方をご紹介します。
SUBTOTAL関数とは？
SUBTOTAL関数とは、指定したセルの値を、指定した集計方法（平均・合計・個数など）で集計することができる関数です。単純に数値の合計を求めるSUM関数に対して、SUBTOTAL関数は集計範囲と集計方法を指定して計算することができます。
SUBTOTAL関数の求め方
書式
=SUBTOTAL（集計方法,範囲 1,[範囲 2],...）
SUBTOTAL関数には、次の引数があります。
①集計方法
集計方法を、番号「1～11」または「101～111」で指定します。「1～11」は手動で非表示にした値を集計に含む場合に、「101～111」は非表示にした値を含まない場合に使用します。集計方法の引数の指定は必須です。各番号の集計方法は以下となっています。
|
非表示の値を含む |
非表示の値を含まない |
集計の方法 |
同等の関数 |
|
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
集計したいセルの範囲を指定します。範囲 1の引数の指定は必須です。
③範囲 2以降
集計したいセルの範囲が範囲 1以外にもある場合に指定します。範囲 2以降は省略可能です。
SUBTOTAL関数の使用例
以下の表を例に、SUBTOTAL関数を使用して、各ブランドの小計と3つのブランドの合計を求めます。
①小計を求めよう！
まずは、表の中から各ブランドの10月～12月の売り上げ小計を求める方法をご紹介いたします。
１．「mouse」ブランドの小計を求めるため、D6のセルを選択し、数式バーの横にある「fx」ボタンをクリックします。「関数の挿入」ダイアログボックスを表示し、「関数の検索」に「SUBTOTAL」と入力して「検索開始」をクリックします。
２．検索結果の関数名の項目に「SUBTOTAL」が表示されるので「OK」をクリックします。
３．関数の引数のプロパティが表示されるので、「集計方法」と「参照1」を入力します。小計を求める場合は、「集計方法」に合計値を求める番号の「9」、「参照1」には集計したい範囲のセル（例ではD3～D5）を入力し、「OK」を選択します。
４．はじめに選択したセルに、mouseブランドの10月～12月の売上の小計が表示されました。
５．「DAIV」「MousePro」も同様に小計を求めます。
②合計を求めよう！
次に3ブランドの10月～12月の売り上げの合計を求めます。
１．合計を求めたいD15セルを選択し、数式バーの横にある「fx」ボタンをクリックします。「関数の挿入」ダイアログボックスを表示し、「関数の検索」に「SUBTOTAL」と入力して「検索開始」をクリックします。
２．検索結果の関数名の項目に「SUBTOTAL」が表示されるので「OK」をクリックします。
３．関数の引数のプロパティが表示されたら、「集計方法」に小計と同じく合計を求める「9」、「参照1」には集計したい範囲のセル（例ではD3～D14）を入力し「OK」を選択します。
４．はじめに選択したセルに、3ブランドの10月～12月の売り上げの合計が表示されました。小計・合計ともにSUBTOTAL関数で求めたため、選択範囲に小計のセルが含まれていても合計の計算には反映されていません。
③表にフィルターがある場合
表にフィルターがある場合、SUBTOTAL関数で小計や合計を求めていると、フィルターで絞り込んだ時に選択した項目のみが計算されます。
「月」の絞り込み項目で10月のみを選択し表示すると、合計にも10月の3ブランドの売り上げを計算した数値が自動で反映されます。
まとめ
SUBTOTAL関数は、集計方法を指定することで様々な値を求めることができます。
また、選択範囲の中に計算に含みたくない値がある場合や表にフィルターがかかっている場合でも、SUBTOTAL関数を使用して求めることで、自動的に計算に反映され大変便利です。使用するデータによっては効率的に数値を求められるため、ぜひ活用してみてください。
