SUBTOTAL関数の使い方

エクセル表の数値を合計する際、SUM関数等が用いられますが、フィルタがかかっている表でSUM関数を使うとフィルタがかかっていない、非表示となった数値も集計してしまい、思い通りの結果とならないことがあります。

そこでSUBTOTAL関数が便利になります。 フィルタがかけられた表でその表示がされている数値のみの集計を行うことができます。

=SUBTOTAL(集計方法、参照範囲)

 

集計方法:指定したセルを集計する方法を1~11の数値で指定(対応する数値は下記表を参照)
参照範囲:集計したい範囲を指定

数値集計方法対応関数
1平均AVERAGE
2数値の個数COUNT
3データの個数COUNTA
4最大値MAX
5最小値MIN
6PRODUCT
7標本標準偏差STDEV.S
8標準偏差STDEV.P
9合計SUM
10標本分散VAR.S
11分散VAR.P

~使用例~

上の表のD14セルに売上の合計を求めるため、SUM関数が用いられています。

その合計は「1,590,000」ですね。

ここから「支店名」を「東京支店」でフィルタをかけると…

合計が先ほどと同じ「1,590,000」となりました。(というか変化していません。)
これはフィルタで非表示となったセルの数値も含めて合計されるため、フィルタしても結果が変わらないのです。

フィルタで非表示となったセルを含めず合計するには、(表示されているもののみの合計するには)
SUM関数ではなく、SUBTOTAL関数を用います。
D14セルへ「=SUBTOTAL(9,D3:D13)」と入力します。
集計方法は合計の「9」、参照範囲は「D3:D13」です。

ちなみにこの合計は「1,590,000」です。
この時点ではフィルタされていないため、SUM関数を使った場合と同じです。

先ほどと同じように「支店名」を「東京支店」でフィルタをかけると…

数値が変わりました。
100,000+80,000+350,000=530,000となり、フィルタで非表示となったセルは含めず合計されました。

まとめ

SUBTOTAL関数の合計について解説しました。
これは主に表にかけられたフィルタをいじって、その結果(合計等)を確認したいときに使えます。
本記事では合計(集計方法9)を例としましたが、非表示を含めず、平均(集計方法1)や最大値(集計方法4)等を求めたい場合も同様に使えるので、業務等に活用してみてください。

コメント

タイトルとURLをコピーしました