スポンサーリンク

【EXCEL関数】フィルターとの合わせ技で自在に集計出来る関数(SUBTOTAL)

2019年7月15日

スポンサーリンク

EXCELで集計表を作成するときに合計や平均などをそれぞれ別々につかっていませんか?

SUBTOTAL関数は1つでマルチタレントのように色んな集計を行うことが出来ます。

絞り込みのフィルターと組み合わせることでちょっとした分析にも使えますよ。

では見ていきましょう。

スポンサーリンク

1.SUBTOTAL関数について

まず以下のような集計表で合計値の集計をしてみたいと思います。

これはある小売店の年月毎の店舗別売上表です。

集計対象の売上表

この表にフィルターを設定して合計金額を計算するセルを追加します。

表の中の売上金額が合計されています。

この表にフィルターを設定して「店舗」を新宿で絞り込んでみます。

すると合計はフィルターで絞り込んだ結果、隠れてしまった金額も含んだ合計が表示されたままです。

絞り込んだ結果の合計を計算するにはどうすればいいでしょう。

この場合はSUBTOTAL関数を使用します。

SUBTOTAL関数は表示されている値を計算対象としてくれます。

スポンサードサーチ

2.SUBTOTAL関数の説明

関数名 SUBTOTAL
形式 「=SUBTOTAL(集計方法,集計範囲1[,集計範囲2,集計範囲3,)」
パラメータ 集計方法 集計方法を数字で指定します。

集計方法 集計方法
(非表示含む) (非表示含まず)
1 101 平均
2 102 数値の個数
3 103 空白以外の個数
4 104 最大値
5 105 最小値
6 106 積の計算
7 107 標本標準偏差
8 108 標準偏差
9 109 合計
10 110 不偏分散
11 111 標本分散

フィルターで絞り込んだ場合は集計方法にかかわらず集計の対象外になります。

集計範囲1,2、、、 集計の対象を指定します。最大254個
説明 範囲で指定された数値を指定された集計方法で集計します。

フィルターで絞りこまれた場合は集計の対象外となります。

スポンサードサーチ

3.SUBTOTAL関数の使用例

前述のケースの場合は以下のように記述します。

下は絞り込みがされていない状態です。

合計を計算するのに比較の為「SUM関数」と「SUBTOTAL関数」を記述しました。

この状態では「SUM関数」と「SUBTOTAL関数」の結果は一致しています。

 

ではフィルターで新宿で絞り込みしてみます。

「SUM関数」は絞り込む前と合計値は変わっていません。絞り込んで表示されていない金額も合計の計算に含まれているのです。

一方「SUBTOTAL関数」は絞り込んで表示されている金額のみが合計に反映されています。
絞り込みした場合はこちらの「SUBTOTAL関数」の方が自然です。

 

4.おわりに

今回は集計方法として「9」を指定して「合計」で説明しましたが平均や「標準偏差」など様々な集計が出来ます。

是非SUBTOTAL関数をマスターして活用してください。

 

 

ブログランキング・にほんブログ村へにほんブログ村