STUDY387

エクセルで仕事をもっと簡単にしよう

【エクセル関数】フィルターで条件に合うセルのみを対象にして計算(合計・平均等)をするSUBTOTAL

time 2014/07/29

【エクセル関数】フィルターで条件に合うセルのみを対象にして計算(合計・平均等)をするSUBTOTAL

sponsored link

SUBTOTALってどんな関数?

SUBTOTAL関数はフィルターと一緒に使う関数です。
フィルターで抽出した値を指定した計算方法(平均、合計、個数など)に従って計算します。

たとえば、アンケート結果があるとします。フィルターで性別を男性に絞り、その人数や平均年齢をSUBTOTAL関数で計算することができます。

どうやって使うの?

SUBTOTAL関数の式は「=SUBTOTAL(第1引数,第2引数)」というように、カッコの中に2つの引数が必要になります。

第1引数は必須項目で、”計算方法を指定する数字”が入ります。数字と計算方法の関係は以下のとおりです。

数字
(非表示の値を含む場合)
数字
(非表示の値を無視する場合)
計算方法(関数)
1 101  AVERAGE(平均)
102  COUNT(個数) 
103  COUNTA(個数) 
104  MAX(最大値) 
105  MIN(最小値) 
106  PRODUCT(乗算) 
107  STDEV(標準偏差) 
108  STDEVP(標準偏差) 
109  SUM(合計) 
10  110  VAR(分散)
11  111  VARP(分散)

第2引数も必須で、計算対象となるセル範囲を指定します。たとえば、セルC2からセルC20までを計算対象にしたいなら「C2:C20」が第2引数に入ります。

使用例を見てみましょう。下の表は身長の一覧表です。
excl-subtotal01

SUBTOTAL関数を使って、男子の平均身長を計算します。
式は「=SUBTOTAL(1,D2:D51)」です。第1引数の「1」は計算方法の平均を意味し、第2引数は身長のデータが入力されているセル範囲です。
excl-subtotal02

フィルターで抽出していない状態では、全員の身長の平均が計算されます。
excl-subtotal03

フィルターで「男」のみを抽出すると、SUBTOTAL関数の結果は男子の平均身長になります。
excl-subtotal04

平均以外の計算もできます。男性の人数を知りたければ、第1引数の集計方法番号を2または3にします。
式にすると、「=SUBTOTAL(2,D2:D51)」です。
excl-subtotal05

結果が24なので、24名と分かります。
excl-subtotal06

以上がSUBTOTAL関数の使い方です。
抽出結果の値のみで様々な計算をしたいときはSUBTOTAL関数をお使いください。

まとめ

SUBTOTAL関数についてのまとめです。

=SUBTOTAL(計算方法を示す数字,計算対象のセル範囲)

  • 計算方法を示す数字:必須。数字で計算方法を指定する。番号と計算方法の関係は下表を参照。
  • 計算対象のセル範囲:計算するデータが入っているセル範囲を指定する。
数字
(非表示の値を含む場合)
数字
(非表示の値を無視する場合)
計算方法(関数)
1 101  AVERAGE(平均)
102  COUNT(個数) 
103  COUNTA(個数) 
104  MAX(最大値) 
105  MIN(最小値) 
106  PRODUCT(乗算) 
107  STDEV(標準偏差) 
108  STDEVP(標準偏差) 
109  SUM(合計) 
10  110  VAR(分散)
11  111  VARP(分散)
  • フィルターの抽出結果の値を指定した計算方法で算出します。
  • 列(縦)方向に計算を行います。行(横)方向ではありません。
※ 使用バージョン:エクセル2013

sponsored link



sponsored link