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

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

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

SUBTOTAL関数

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

SUBTOTAL関数の書式と引数

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

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

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関数の式を使わなければいけません。

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

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

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

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

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

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

D17　=SUBTOTAL(9,D2:D16)

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

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

D17　=SUBTOTAL(9,D2:D16)

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

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

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

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

D17　=SUBTOTAL(9,D2:D16)

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

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

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

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

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

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

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

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

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

集計方法を変更して集計

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

平均値を求める

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

C17に、式を入力します。

C17　=SUBTOTAL(1,C2:C16)

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

「73」が返されます。

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

最大値を求める

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

C17に、式を入力します。

C17　=SUBTOTAL(4,C2:C16)

集計方法は「４」です。

「92」が返されます。

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

最小値を求める

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

C17に、式を入力します。

C17　=SUBTOTAL(5,C2:C16)

集計方法は「５」です。

「60」が返されます。

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

合計を求める

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

C17に、式を入力します。

C17　=SUBTOTAL(9,C2:C16)

集計方法は「９」です。

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

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

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

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

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

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