SUBTOTAL関数とは…
SUBTOTAL関数とは、合計(SUM関数)や平均(AVERAGE関数)、カウント(COUNT関数)等の計算ができる便利関数です。特に次のような場面で大活躍します。
・フィルターをかけて、見えてるセルだけ合計したい
・小計と合計がある表を効率化したい(小計を除いて合計を計算したい)
こんなときはSUBTOTAL関数で解決!
![](https://excel-skillup.com/wp-content/uploads/2023/09/SUBTOTAL.png)
![](https://excel-skillup.com/wp-content/uploads/2023/09/SUBTOTAL-2.png)
![](https://excel-skillup.com/wp-content/uploads/2023/09/SUBTOTAL-3.png)
それでは、早速SUBTOTAL関数の使い方を詳しく見てみましょう。
SUBTOTAL関数の基本説明
SUBTOTAL関数は次のように入力します。
集計方法と参照データとは
集計方法
集計方法は次のリストから番号で指定します。たくさんありますが、今回は「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 |
どちらもフィルター機能で見えていないセルを除外する点は同じです。行選択で非表示化したセルの集計方法が異なります。
・集計方法1~11▶行選択で非表示化している行を集計する
・集計方法101~111▶行選択で非表示化している行を集計しない
参照データ
集計したいデータのセルを指定します。「A1:A10」や「A1,A3,A5」等とセルを指定すればOKです。
SUBTOTAL関数 実践編
実践編① 合計を計算
それでは次の場面でSUBTOTAL関数を入力してみましょう。
▶集計方法:9(109でもOK)
▶参照データ:D4:D10(合計したい範囲を入力)
![](https://excel-skillup.com/wp-content/uploads/2023/09/SUBTOTAL-7.png)
SUBTOTAL関数とSUM関数の違い① 見えてるセルだけ合計
![](https://excel-skillup.com/wp-content/uploads/2023/09/SUBTOTAL-8.png)
![](https://excel-skillup.com/wp-content/uploads/2023/09/SUBTOTAL-9.png)
SUM関数▶フィルターで見えていないセルも合計
SUBTOTAL関数▶フィルターで見えているセルだけ合計!
集計方法:9と109の違い
続いて集計方法「9」と「109」の違いを見てみましょう。先程までの例では集計方法は「9」を指定していましたが、「109」を指定した場合を並べて比較します。
ここでフィルターをかけてみると…
フィルターをかけた場合は、集計方法「9」と「109」に違いはありません。
続いて行を選択して、非表示にすると…
集計方法によって計算結果に違いが出ましたね。どちらもフィルター機能で見えていないセルを除外する点は同じですが、行選択で非表示化すると違いが出ます。
・集計方法9▶行選択で非表示化している行も集計する
・集計方法109▶行選択で非表示化している行は集計しない
実践編② 小計を除いて合計を計算
次のように途中に「小計」があり、最後に合計を出すという表。SUM関数を使うと、参照範囲がとびとびで入力が面倒ですよね。
ここでSUBTOTAL関数を使えば、グッと効率化できます。
SUBTOTAL関数には「SUBTOTAL関数が入力された行は、集計から除外する」という特徴があるので、この特徴を利用します。
つまり、あらかじめ「小計」をSUBTOTAL関数で入力しておけば、「合計」はSUBTOTAL関数でデータ全体を指定して集計すればOKです。きちんと小計を除いて計算してくれますよ。
小計がたくさんある表だと、かなり時短になりますし、範囲選択のミスも防止できますね!
ぜひ便利なSUBTOTAL関数を使って、作業を効率化してくださいね!
最後までご覧いただき、ありがとうございました。
よかったら他の記事もご覧ください。
【Excel初級】SUM関数の基本・使い方を完全解説!【初心者必見】
コメント