エクセルSUBTOTAL関数で色々な集計方法でデータを集計!SUMにはできない集計

エクセルで集計をするとき、SUM関数! となりがちですが、集計にはもっと便利な関数があります。エクセルSUBTOTAL関数を使うと、合計だけでなく、集計方法を変えるだけで、平均値、最大値、セルや数値の個数を数えるなど、色々な集計ができます。SUBTOTAL関数にフィルターを使うと、フィルターで抽出したデータの合計だけ、求めることができます。SUM関数との大きな違いと、便利さは、この点にあります。SUM関数にはできない集計です。SUBTOTAL関数を使うと、1つの集計表で、集計方法を変えて、フィルターをかけることで、色々な集計ができます。

一度使うと、SUBTOTAL関数の素晴らしさが分かります。ここでは、SUBTOTAL関数に、フィルターをかけて合計を求めたり、集計方法を変えて集計したりしています

 

 

SUBTOTAL関数

SUBTOTAL関数は、指定した集計方法で集計します。

SUBTOTAL関数の書式と引数

SUBTOTAL(集計方法,範囲 1,[範囲 2],…)

集計方法 1 ~ 11 または 101 ~ 111 の数字で、集計方法を指定します。
範囲 1 集計する最初の範囲を指定します。

範囲 1、範囲 2,…集計する範囲を 254個まで指定できます。

SUBTOTAL関数の集計方法

集計方法(非表示の値も含める) 集計方法(非表示の値を無視する) 関数 説明
1 101  AVERAGE  平均値を求める
2 102  COUNT  数値の個数を数える
3 103  COUNTA  空白でないセルの個数を数える
4 104  MAX  最大値を求める
5 105  MIN  最小値を求める
6 106  PRODUCT  積を求める
7 107  STDEV  標準偏差(標本)
8 108  STDEVP  標準偏差(母集団)
9 109  SUM  合計を求める
10 110  VAR  不偏分散
11 111  VARP  標本分散

 

 

SUBTOTAL関数の基本的な使い方

エクセル SUBTOTAL関数には、その特性を生かした使い方があります。使い方によって効率アップができます。

SUBTOTAL関数で小計、総合計を求める

集計表の小計、合計の式にSUBTOTAL関数を使います。

 

小計に、SUBTOTAL関数の式を入力しましょう。

D6 =SUBTOTAL(9,D2:D5)

D12 =SUBTOTAL(9,D7:D11)

D16 =SUBTOTAL(9,D13:D15)

合計に、SUBTOTAL関数の式を入力しましょう。

D17 =SUBTOTAL(9,D2:D16)

D17には、総合計「282,900」が返されます。

SUBTOTAL関数の引数の範囲は、「D2:D16」です。

小計は無視されて、合計が算出されます。

総合計にSUBTOTAL関数を使うときの注意点

合計にSUBTOTAL関数の式を入力する場合、小計にはSUBTOTAL関数の式を使わなければいけません。

例えば、SUM関数を小計に入力している場合、この小計は無視されず、合計に反映されてしまします。

SUBTOTAL関数使う場合は、小計、合計、全てにSUBTOTAL関数を使いましょう。

 

 

SUBTOTAL関数にフィルターを使う

フィルター機能を使って表示されているセルの合計を求める

合計に、SUBTOTAL関数を入力した場合、フィルターで抽出されたデータの合計が求められます。

フィルターで抽出されなかった、他のセルは、集計に含まれません。

1.合計に、SUBTOTAL関数の式を入力しましょう。

 

 D17 =SUBTOTAL(9,D2:D16)

「D2:D16」の合計、「389,900」が返されます。

2.「商品№」で、「M2035」でフィルターをかけます。

 

D17 =SUBTOTAL(9,D2:D16)

D17の式は、変わりません。

「商品№」で、「M2035」の合計、「97,000」が返されます。

表示されている、「M2035」だけが集計できました!

3.次に、「地域」で、「東京都」でフィルターをかけてみましょう。

 

D17 =SUBTOTAL(9,D2:D16)

D17の式は、変わりません。

「地域」で「東京都」の合計、「149,000」が返されます。

表示されている、「東京都」だけが集計できました!

これは、SUM関数では、出来ない集計です。

 

 

非表示のセルの合計は集計方法で変わります

非表示のセルを集計から除外する場合は、集計方法101~111を使いましょう。

非表示とは、フィルターで抽出されなかった、見えていないセルとは違います。

「書式」の「表示設定」の、「非表示」のことです。

1~11で設定すると、非表示にしたセルは、SUBTOTAL関数の計算から除外されません。

集計方法で合計が変わるので、気をつけましょう。

SUBTOTAL関数で色々な集計方法で集計

集計方法を変更して集計

合計にSUBTOTAL関数の式を設定すると、集計方法を変更するだけでさまざまな集計ができます。

平均値を求める

点数の平均値を、求めてみましょう。

C17に、式を入力します。

 

C17 =SUBTOTAL(1,C2:C16)

集計方法は、「1」です。

「73」が返されます。

「点数」の平均値が求められます。

最大値を求める

点数の最大値を、求めてみましょう。

C17に、式を入力します。

 

C17 =SUBTOTAL(4,C2:C16)

集計方法は「4」です。

「92」が返されます。

「点数」の最大値が求められます。

最小値を求める

点数の最小値を、求めてみましょう。

C17に、式を入力します。

 

C17 =SUBTOTAL(5,C2:C16)

集計方法は「5」です。

「60」が返されます。

「点数」の最小値が求められます。

合計を求める

点数の合計を、求めてみましょう。

C17に、式を入力します。

 

C17 =SUBTOTAL(9,C2:C16)

集計方法は「9」です。

「1,099」が返されます。

「点数」の合計が求められます。

小計、総合計にSUBTOTAL関数を使う利点

SUBTOTAL関数を使うと、他の関数にはない利点があります。集計範囲に入力されている、

SUBTOTAL関数の集計値は、重複を防ぐために除外されます。

SUBTOTAL関数の集計値は無視されるので、総合計に入力する、SUBTOTAL関数の引数の範囲には、表の集計範囲を指定することができます。

後で行を挿入してデータを追加、または削除したときなど、小計のメンテナンスをする必要はありません。

AGGREGATE関数でオプションと集計方法を変えて集計

エクセルSUBTOTAL関数では、集計方法を変えて色々な集計ができますが、似ている関数に、AGGREGATE関数があります。AGGREGATE関数では、非表示の行やエラー値を無視するオプションを使用して、さまざまな集計をすることができます。SUBTOTAL関数には、引数にオプションがありません。

AGGREGATE関数は、エクセル2010以から追加された関数で、あまり知られていないですが、集計方法を変えて複雑な集計ができる便利な関数です。SUBTOTAL関数とAGGREGATE関数をマスターして、集計に役立てましょう。

こちらの記事もご覧ください。⇒さまざまな集計をエクセルAGGREGATE関数で求める