Excel関数　～SUBTOTAL関数～

Excelシート上で使える関数の使い方を説明。
今回は　SUBTOTAL関数　について、使い方や活用例を紹介。

SUBTOTAL関数とは

SUBTOTAL関数とは

　複数の集計関数を任意で切り替えて計算結果を表示する

という機能を持った関数です。

集計関数とは、複数の数値データを計算して1つの値を計算する関数で、
例えば、　平均、合計、最大値、最小値　といった関数です。

通常の関数は１つの計算しかできません。
　AVERAGE関数であれば平均値を
　SUM関数であれば合計値を
　MAX関数であれば最大値を
　MIN関数であれば最小値を
関数を入力したセルには、その関数で得られる結果しか表示されません。
そのため、仮に合計値を表示させるためにSUM関数を使って計算していたとして、後から合計値ではなく平均値を表示させたい、となった場合は基本的には関数を書き換える必要があります。

しかしこのSUBTOTAL関数だと、引数の値を切り替えることで、
　AVERAGE関数にも
　SUM関数にも
　MAX関数にも
　MIN関数にも
様々な関数に変わることが出来る、という非常に特殊な関数です。

この関数を使う場合は、下記の２つを指定する必要があります。
　
　SUBTOTAL（集計方法、配列）

詳しく説明していきます。

入力情報①　集計方法

この部分でどのような集計方法で計算するか、つまりは、
　　どの関数と同じ計算をするのか
について指定します。

ここでは数値を入力しますが、入力する数値によって計算方法が変わります。
各数値と計算方法の対比表は下記の通りです。

上記のいずれかの値を入力しても良いですが、
上記の対比表を基に計算したい集計方法を選んで、
対応する数値を別のセルに入力し、そのセルを指定することで、
関数を書き換えることなく、自由に計算結果を変えることが出来ます。

入力情報②　配列

ここで指定する『配列』は、上記の入力情報で指定した計算方法で、
実際に計算したいデータの範囲を指定します。

このとき指定するデータの範囲に特別制約は無く、
　縦１行のデータ範囲
　横１行のデータ範囲
　縦横複数列・複数行のデータ範囲
これらいずれの条件でも指定可能です。

使用方法の例

この関数を使用する場合、『入力情報①　集計方法』の引数で数値を直接入力してしまうと、この関数を使用する必要性が半減してしまうといっても過言ではないと思われます。
ですので、基本的には別のセルを指定し、そこに集計方法の番号を入力できるようにしておきましょう。

使用方法の例としては、下記のように記載することで、
ドロップダウンリストを選択するだけで計算結果を書き換えることが出来ます。

上記では「集計セレクト」の部分に入力規制をかけており、関数対応表の関数の名前しか選べないようにしています。

またSUBTOTAL関数の集計方法の引数に入力している下記関数、
　OFFSET(A2,MATCH(E1,B3:B13,0),0)
この部分で「集計セレクト」で選択した関数が、「関数対応表」の何番に対応するのかをチェックしています。
このOFFSET関数とMATCH関数の組み合わせの方法については過去にも説明していますので、ご参考ください。

Excel関数組合せ_汎用性が高いデータ検索・抽出　～OFFSET、MATCH～

Excelシート上で使える、複数の関数を組み合わせた使い方を説明。今回は　OFFSET、MATCH　について。OFFSETとMATCHを組み合わせることで、VLOOKUP関数やHLOOKUP関数の欠点を解消しつつ、より汎用性が高くデータ検索が可能になります。

yorozuya-tanuki.com

あとはその番号をSUBTOTAL関数の集計方法の引数に入れ、集計したい範囲を指定すれば、
ドロップダウンリストを選択するだけで集計方法を変更できるようになります。