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

2020/8/27

もりの

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

やまの

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

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

スポンサーリンク

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

もりの
 
 

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

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

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

たとえば、次の図のような各店舗ごとの売り上げがかかれた表。

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

合計を出した表
次のような表とは私のこと
もりの

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

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

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

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

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

もりの

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

やまの

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

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

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

SUBTOTAL関数を使えばSUM関数と違い、可視セルだけの合計を確認できます。

フィルターをかけるたびに再計算してくれるので、見たいデータの合計などがすぐ分かります。

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

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

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

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

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

SUBTOTAL関数の書き方

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

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

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

SUBTOTAL関数の使用例

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

SUBTOTAL関数の練習問題
表示された数だけで合計したい
やまの

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

ステップ1

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

=SUBTOTALと入力した画像
まずは関数名を入力

集計方法

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

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

合計は「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」ですね。

というわけで、E2~E12をドラッグしてください。

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

ステップ3

やまの

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

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

完成!

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

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

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

やまの

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

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

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

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

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

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

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

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

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

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

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

SUBTOTAL関数を動画で見る