【Excel・エクセル】フィルターの結果だけ合計をしたい!SUBTOTAL関数

もりの

フィルターを使って、見えるデータだけ合計ってできないかな?

やまの

SUBTOTALって関数を使えば、フィルターの結果での合計や平均を出せるんよ。

今回のポイント

  • 非表示の行を含めず集計したいなら「SUBTOTAL関数」
  • =SUBTOTAL(やりたい計算の数字,セル範囲)
  • やりたい計算の数字は、平均:1、個数:2or3、合計:9など

 SUBTOTAL関数の練習ファイルダウンロード

もりの
 
 

一緒にやってみませんか?

【Excel・エクセル】フィルターで表示されたセルだけで合計する、とは

フィルターを使えば、見たいデータだけ表示できますよね。

たとえば、次のような各店舗ごとの売り上げがかかれた表があったとします。

一番下の行には、全店分の売上合計が出ていますね。

例題
次のような表とは私のこと
もりの

これにフィルターを使って、関西地区のデータだけ表示しよう!

そして、関西地区だけのデータと合計行を表示させた結果がこちら。

合計行はSUM関数で出しています。

フィルターを使った表
フィルターを使って、関西だけ見えるようにした

しっかりできていま……おや?

もりの

3つのお店の合計が「3882」みたいになってて分かりにくいよ!

やまの

3つのお店の合計を表示させたいよね。

そんな時に使えるのが、SUBTOTAL関数なのです!

【Excel・エクセル】可視セルだけ計算できるSUBTOTAL関数とは

表示されたセルだけの合計が出せる表
こうなるのが理想!

SUBTOTAL関数を使えば、指定したセルの合計を出すSUM関数と違い、可視セルだけの合計を確認できます。
フィルターをかけ直すたびに再計算してくれるので、見たいデータの合計などがすぐ分かります。

やまの

可視セルは、見えているセル、表示されているセルってことだよ。

SUBTOTAL関数は合計のほかにも、「平均」や「個数」、「最大値」「最小値」なんかも出してくれますよ!

ちなみに読み方は「サブトータル」です。

【Excel・エクセル】SUBTOTAL関数の使い方

関数の書き方

=SUBTOTAL(集計方法,参照1,参照2……)

SUBTOTALこのセル範囲の中で、表示されたセルにこの計算をして
集計方法この計算
参照1このセル範囲
参照2このセル範囲(省略可)
やまの

参照は3つ以上でもOKだよ。

SUBTOTAL関数の使用例

では、セルE13にある合計をSUBTOTAL関数を使って、表示されたセルだけで合計するようにしていきましょう。

例題
例題
やまの

=SUBTOTAL(9,E2:E12)で、求められるよ。説明するね。

ステップ1

まずはセルE13に、「=SUBTOTAL(」と入力してください。

もりの

半角英数入力モードにしてから、入力するんだよね。

集計方法

最初の引数は、「この計算」という、計算方法を指定するものでした。
今回やりたい計算は、「合計」ですね。

合計の場合は「9」なので、「=(」の続きに、「9,」と入力してください。

もりの

合計は「9」って、どういうこと?

やまの

集計方法は数字で決まってるんよ。

それぞれの番号を、表にまとめてみました。
合計以外のことがしたい場合は、該当する番号を入力すればOKです。

番号やりたいこと該当する関数
1平均AVERAGE
2数値が入ったセルの個数COUNT
3空白ではないセルの個数COUNTA
4最大値MAX
5最小値MIN
6積(掛け算する)PRODUCT
7標本に基づいた偏差STDEV
8母集団の偏差STDEVP
9合計SUM
10不偏分散VAR
11分散VARP
もりの

聞きなれないものもある……

やまの

使わないものはとりあえず気にしないでいいよ!

参照1

次の引数は、「このセル範囲」という、合計したい数値を指定するものでした。
今回はセルE2からE12までの合計を出したいので、「E2:E12」ですね。

「9,」の続きに、「E2:E12」と入力してください。

セル範囲
フィルターのことは、とりあえず置いて考える

ステップ3

やまの

今回のセル範囲はここだけだから、参照2は省略だよ!

では、「)」で関数を閉じて、エンターキーを押してみてください。

完成!

無事、売り上げの合計が出せました!

SUBTOTAL関数で合計が出せた
SUBTOTAL関数で合計が出せた!
もりの

でもこれ、SUM関数と何が違うの?

やまの

フィルターを使って、セルを非表示にすると分かるよ。

SUBTOTAL関数で表示されたセルだけを計算する

では、SUBTOTAL関数を入力した状態で、改めてフィルター機能を使って関西地区のデータだけを表示してみましょう。

  1. 「フィルターのマーク(▼)」
  2. 「すべて選択」
  3. 「関西」
  4. 「OK」

とクリックしていきます。

フィルターを使って関西だけを表示させる
フィルターを使って関西だけを表示させる

するとなんと、表示された3店舗だけの合計値に自動で変わったではありませんか!

これが、SUM関数とSUBTOTAL関数の違いです。

フィルターを使った表をSUBTOTAL関数を使って合計を出した
関数は変えなくても、表示されたセルの合計に変った!

これでフィルターを使っても、合計や平均といった集計が楽にできますね!
それでは、お疲れさまでした!

今回のポイント

  • 非表示の行を含めず集計したいなら「SUBTOTAL関数」
  • =SUBTOTAL(やりたい計算の数字,セル範囲)
  • やりたい計算の数字は、平均:1、個数:2or3、合計:9など