【Excel・エクセル】SUBTOTAL関数で合計や個数を集計する

もりの

商品ごとの合計を集計したいんだ。

やまの

SUBTOTAL関数を使ってみよう。

今回のポイント
  • 集計する関数は「SUBTOTAL関数」
  • =SUBTOTAL(やりたい計算の番号,セル範囲)
  • やりたい計算の番号は、平均:1、個数:2or3、合計:9など

スポンサーリンク

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

もりの
 
 

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

【Excel・エクセル】合計や個数を集計するSUBTOTAL関数

月の売上を商品ごとに集計して、さらに全体の合計も出したいとき。

これはもう、SUBTOTAL関数の出番です。

SUBTOTAL関数の練習問題
それぞれの合計を集計したい
もりの

え、合計するならSUM関数でいいんじゃないの?

確かにそれぞれの合計だけなら、SUM関数でもOKです。
が、全体の合計も出したいというのが今回のポイント!

SUM関数だと、それぞれの合計も含めてしまうので、値がずれてしまいます。

各合計を範囲から除くか、各合計だけで計算してもいいですが、めっちゃ面倒ですよね……

SUM関数で合計した画像
合計も合計した合計が出る……ん?

しかしSUBTOTAL関数を使えば、小計を省いて合計してくれるので、全体の合計も簡単に計算できるんです。

SUBTOTAL関数で集計した画像
各合計は除いて合計されている

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

では、そんなSUBTOTAL関数の使い方を、見ていきましょう!

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

SUBTOTAL関数の書き方

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

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

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

SUBTOTAL関数の使用例

では先ほどの表のセルA4に、SUBTOTAL関数を使って合計を集計してみましょう。

SUBTOTAL関数の練習問題
それぞれの合計を集計したい
やまの

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

ステップ1

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

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

ステップ2

最初の引数は、「集計方法」……まぁ、計算方法ですね。
今回は、「合計」を出したいんでしたよね。

合計の番号は「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
もりのの笑った顔
もりの

個数や平均も集計できるんだね!

ステップ3

次の引数は「このセル範囲」、合計したいセル範囲ですね。

合計したいセル範囲をドラッグしてください。

今回は、「E2:E3」ですね。

セル範囲をドラッグする画像
ここはSUM関数と一緒

ステップ4

式は完成なので、カッコを閉じてエンターキーを押しましょう。

無事、カムカムガムの合計が出せました!

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

SUM関数とほとんど変わらないね。

ステップ5

残りの商品の合計も、同じようにSUBTOTAL関数で集計していきましょう。

SUBTOTAL関数で集計を出した画像
同じように計算していく

完成!

最後に全体の合計ですが、これもSUBTOTAL関数で同じように計算すればOKです。

範囲をセルE2~E11にすると……無事、全体の合計が出てきました!

SUBTOTAL関数は省いて合計された画像
SUBTOTAL関数は省いてくれる
やまの

SUBTOTAL関数を使うと、SUBTOTAL関数で集計したセルを省いて、集計してくれるんよ。

SUBTOTAL関数のもう一つの特徴

SUBTOTAL関数は、もう一つ面白い特徴があります。
それは、フィルターで非表示にしたセルは計算されない、ということ!

SUM関数を使って合計を出すと、フィルターで一部非表示にしても、全体の合計が表示されますよね。

フィルターを使った表
フィルターを使っても、SUM関数だと全体の合計になる

しかしSUBTOTAL関数を使えば、フィルターで表示されたセルだけで、合計してくれるんです!

フィルターを使った表をSUBTOTAL関数を使って合計を出した
SUBTOTAL関数なら、可視セルだけ合計される!
もりのの笑った顔
もりの

詳しくは、こっちのページで教えてもらったよ!

【Excel・エクセル】同じように集計できる、小計機能

「データタブ」→アウトラインのところにある「小計」でも、同じように合計などが集計できます。

小計の場所
小計の場所

こちらはわざわざ各合計の行を作る必要はなく、アウトラインで表示非表示を切り替えられるので、使いやすいですよ。

アウトラインの画像
ここから表示非表示を切り替えられる
やまの

詳しくは、こっちのページを見てね。

SUBTOTAL関数を使えば合計だけでなく、平均や個数の集計が簡単にできますね!
それでは、お疲れさまでした!

今回のポイント
  • 集計する関数は「SUBTOTAL関数」
  • =SUBTOTAL(やりたい計算の番号,セル範囲)
  • やりたい計算の番号は、平均:1、個数:2or3、合計:9など

数式・関数,Excel集計

Posted by morino