エクセル 知っていると便利な SUBTOTAL(サブトータル)関数

スポンサーリンク
スポンサーリンク

SUBTOTAL(サブトータル)関数

SUBTOTAL関数は、選択範囲の集計をする関数です。

引数で集計方法を選択できるため集計に関しては、
この関数だけで、ほぼ、まかなえると思います。

但し、集計できるのは列方向(縦)のみで行方向(横)のデータは
集計できません。

また、3D参照(他のシートからの値の参照)の値が含まれていると
エラーになります。単独のシートのデータ集計に利用してください。


【SUBTOTAL関数の構文】
SUBTOTAL(集計方法,集計範囲1[,集計範囲2],・・・)
第一引数の「集計方法」第二引数の「集計範囲1」は必須です
集計範囲2~255までの集計範囲が指定できます。


SUBTOTAL関数 第一引数(集計方法)の引数一覧表

集計方法引数
(非表示の値含める)
引数
(非表示の値含めない)
同等の関数
平均値1101AVERAGE(アベレージ)
数値のあるセル個数2102COUNT(カウント)
空白を除くセル個数3103COUNTA(カウントエー)
最大値4104MAX(マックス)
最小値5105MIN(ミニマム)
6106PRODUCT(プロダクト)
標本の標準偏差7107STDEV(スタンダードディービエーション)
母集団全体の標準偏差8108STDEVP(スタンダードディービエーションピー)
合計9109SUM(サム)
標本による分散の予測値10110VAR(バリアンス)
母集団全体の分散11111VARP(バリアンスピー)

SUBTOTAL(サブトータル)関数は上記の表の第一引数を指定することにより
さまざまな集計が行えるメリットがあります。

引数9の合計に関してはSUM関数と違い、データの中に小計が含まれる場合
小計を除いて総合計を計算することができます。

SUBTOTAL(サブトータル)関数で小計と総合計を計算する例

例として
A店、B店、C店、3店舗の商品カテゴリ別の第一四半期売上表
をもとに解説します。


※SUBTOTAL関数の合計は列方向(縦)の合計しかできないので注意が必要です。
行方向(横)の合計はSUM関数を使います。

SUBTOTAL(サブトータル)関数で売上の平均、最大値、最小値を計算する例

上記の商品カテゴリ別売上表から、SUBTOTAL関数を使って
商品カテゴリ別の平均売上、最大売上、最小売上、売上合計の
値を取得してみます。


平均売上の表だけ先に作成して、
SUBTOTAL関数を入力 第一引数は平均なので1を指定します。

これ以降の最大売上、最小売上、売上合計の集計する範囲は同じなので
第二引数の範囲を絶対参照にすることにより、

平均売上の表をオートフィルでコピーして貼り付つけ
表題とSUBTOTAL関数の第一引数の集計方法の値を変更するだけで
最大売上、最小売上、売上合計の表と値が取得できます。

選択範囲を相対参照から絶対参照に変更する方法

「エクセル」では、標準の参照範囲は相対参照になっていて
オートフィルなどでセルを移動するとそれに合わせて参照範囲も
移動します。

セルは移動するけど参照範囲は移動したくない場合に
絶対参照を指定します。

絶対参照は$(ダラー)を使います$は絶対番地という意味があります。
具体的には、絶対参照にしたいセル番地の前でF4キーを押します

複数の範囲の場合は、範囲を選択状態にしてF4キーを押すと
範囲すべてが絶対参照になります。



F4キーを押すと範囲が絶対参照になります。

SUBTOTAL関数の簡単な入力方法

関数ボタンを押して
関数入力メニューを表示して入力すると簡単に入力できます。
SUBTOTAL関数を挿入するセルを選択します。

関数ボタンを押します。



テキストボックスに「集計」と入力します。

SUBTOTALを選択してOKボタンを押します。



集計方法のテキストボックスに集計方法の番号を入力します。

集計範囲をマウスでドラッグして選択してOKボタンを押します。



以上でSUBTOTAL関数の解説を終了します。ありがとございました。

スポンサーリンク
スポンサーリンク

フォローする

スポンサーリンク
スポンサーリンク