フィルタで抽出された範囲だけ合計値や平均値を出したいSUBTOTAL関数とフィルタ-GSS/Excel/関数

フィルタした部分だけ●●のSUBTOTAL関数

今回はSUBTOTAL関数について説明します。その前に一緒に使うフィルタについてもご説明していきますね。

フィルタ

こちらもあまり使ったことがない人もいるようですが、表から条件抽出する方法でとても便利です。

上の図を見てください。フィルタを適用したい[A4:K4]を選択した状態です。このまま、上部メニューバーの「データ」＞「フィルタを作成」を押してください。

[A4:K4]のセルに三角マークがつきました。これがフィルタのマークです。早速フィルタを使ってみます。

[C]列を「AAA」の条件でフィルタかけます。

すると上の図のようになります。「BBB」「CCC」が非表示になり、「AAA」に該当する部分だけ抽出されました。

このフィルタをかけた部分だけで合計の売り上げを見たい場合どうすればいいかわかりますか？

3行目にsum関数で範囲指定をしてしまうと、非表示になっている「BBB」「CCC」もsumの計算範囲に含まれてしまうので、不正解です。

そこで使うのがSUBTOTAL関数です。早速答えの式です。

構文

=SUBTOTAL(集計方法,集計する範囲)

正解

[J3]=SUBTOTAL(9,J5:J19)

今は5行目から19行目全て表示されているので、2200000と出ています。ここから「AAA」だけでフィルタを使って抽出してみます。

[J3]の関数自体は変わっていませんが、[J3]に表示されている数値が変わりました。1150000になっており、AAAに該当する行だけで計算されたことがわかります。

試しに「BBB」でフィルタをかけてみます。

[J3]が450000になりました。フィルタされた行を見ると、

100000+100000+200000+50000=450000になるので、間違いないことがわかりますね。

ちなみに範囲選択をすると、画面右下に選択されたセルの合計値や平均値が出る場所があります。ここも知っておくと使い勝手がいいですよ。

合計:450000とありますね。これで関数の結果が間違っていないかを確認することが多いので覚えておいてください。

この部分をクリックしてみると、合計、平均、最小、最大、カウント、個数などの数値もみられます。

上記の範囲指定の状態でそれぞれの値をみると次のようになります。

合計:450000

平均:112500

最小:50000

最大:200000

カウント:4

個数:4

SUBTOTAL関数に話を戻します。

SUBTOTAL関数は表示されている値を任意の方法で演算する関数なので、sumしかできないわけではありません。今回はsumで計算するので、関数内でsumをしてねと言う指定を入れています。

[J3]=SUBTOTAL(9,J5:J19)

上記式の「9」がその指定です。

その他の計算式と指定をまとめてご紹介します。

集計方法	集計機能	同等の関数
1または101	平均値を求める	AVERAGE
2または102	数値の個数を求める	COUNT
3または103	データの個数を求める	COUNTA
4または104	最大値を求める	MAX
5または105	最小値を求める	MIN
6または106	積を求める	PRODUCT
7または107	不偏標準偏差を求める	STDEV.S
8または108	標本標準偏差を求める	STDEV.P
9または109	合計値を求める	SUM
10または110	不偏分散を求める	VAR.S
11または111	標本分散を求める	VAR.P

SUBTOTAL関数は、まず何をして欲しいかを入れます、その後にそれをして欲しい範囲を入れます。

[H3]で単価の平均値を出したい場合、

[H3]=SUBTOTAL(1,H5:H19)

実際にやってみるとこのようになります。

いかがでしょうか？SUBTOTAL関数とフィルタの組み合わせは条件抽出でさっと数値を見たいときにとても便利です。

2016年に発表されたaggregate関数はこのSUBTOTAL関数の強化版です。こちらも改めてご説明していきますのでぜひ使ってみてくださいね。

まとめ

• SUBTOTAL関数の使い所
  • フィルタした部分だけ集計したいとき
• 構文
  • =SUBTOTAL(集計方法,集計する範囲)
• 例文
  • =SUBTOTAL(9,J5:J19)