エクセル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関数の集計方法

集計方法(非表示の値も含める)集計方法(非表示の値を無視する)関数説明
1101 AVERAGE 平均値を求める
2102 COUNT 数値の個数を数える
3103 COUNTA 空白でないセルの個数を数える
4104 MAX 最大値を求める
5105 MIN 最小値を求める
6106 PRODUCT 積を求める
7107 STDEV 標準偏差(標本)
8108 STDEVP 標準偏差(母集団)
9109 SUM 合計を求める
10110 VAR 不偏分散
11111 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関数で求める