関数(Excel)

エクセルでSUBTOTAL関数を使いこなす

スポンサーリンク

こんにちは。システム担当の父親、KATSUです。

エクセル歴20年、アクセス歴10年ですが、ブログは25記事投稿した程度の初心者ですので、引き続き量産に努めていきます。


「Excelのフィルターが便利なことが分かったけど、もっと便利に活用したい。」

そのように考えたことはありませんか?

実は、フィルターされたデータのみを集計出来る関数があるんです。

今回は、フィルターされたデータを集計できる関数であるSUBTOTALについて紹介していきます。

この記事を読むことで、

  • フィルターを活用しながらSUBTOTAL関数で集計できるようになる
  • SUBTOTAL関数をフィルター以外での用途でも活用できる

といった効果を期待出来ます。

SUBTOTAL関数を挿入する

SUBTOTAL関数は、数式バーに直接挿入できますが、フィルタを設定していると、オートSUMボタンでも挿入できます。

まずは、データが入力された表にフィルタを設定し、データを絞り込んでください。

フィルターが設定された状態

ここでは、品名をりんごにしました。

フィルター後の合計を計算したい

データを絞り込んでいないと、SUBTOTAL関数が挿入されません。

また、表はデータベース化されていることが必須です。こちらの記事を参考にしてください。

ホームタブのオートSUMボタンをクリックし、合計をクリックします。

オートSUMの合計をクリック

Subtotal関数が挿入されました。

SUBTOTAL関数が挿入される

りんごのみを合計した値が計算されました。

合計値が計算された
ここで、平均や個数などをクリックしてもSUBTOTAL関数は挿入されませんので注意してください。

品名がりんごになっていますが、にんじんに変更してみます。

集計結果が変わりました。

フィルターによって結果が変わる

SUBTOTALの集計方法

SUBTOTAL関数は、以下のようにして構成されています。

  • 第一引数・・・集計方法
  • 第二引数・・・集計範囲

オートSUMボタンからSUBTOTAL関数を挿入すると、集計方法は9になっています。

第一引数を変えれば、集計方法を変えることができます。

第一引数は以下の表のようになっています。

引数集計の名前計算方法
1AVERAGE平均
2COUNT個数(数値のみ)
3COUNTA個数(文字も含む)
4MAX最大値
5MIN最小値
6PRODUCT掛け算
7STDEV標準偏差(n-1で割る)
8STDEVP標準偏差(nで割る)
9SUM合計
10VAR分散(n-1で割る)
11VARP分散(nで割る)

ここでは、個数を集計する3を入力してみます。

データ件数が算出されました。

SUBTOTALで件数を算出

非表示行を反映させない集計

ここで、第一引数を9を109に変更してみます。

非表示の行を集計しない方法

にんじんが表示されていますが、一行を非表示にしてみます。

非表示にしてみる

集計値に非表示の行が排除されました。

非表示のデータが削除された

このように、第一引数に100を足すと、非表示の行を排除することができます。

引数集計の方法計算方法
101AVERAGE平均
102COUNT個数(数値のみ)
103COUNTA個数(文字も含む)
104MAX最大値
105MIN最小値
106PRODUCT掛け算
107STDEV標準偏差(n-1で割る)
108STDEVP標準偏差(nで割る)
109SUM合計
110VAR分散(n-1で割る)
111VARP分散(nで割る)

SUBTOTAL関数を活用して小計を計算する

SUBTOTAL関数はフィルタ以外でも使える場面があります。それは、小計を含む表を作る時です。

ここで、小計、総計ともにSUBTOTAL関数を使ってみます。

SUBTOTAL関数を使った数式

どちらも正しく計算することができました。

小計を除外して計算された

SUBTOTAL関数をSUM関数にしてみます。

SUM関数を使った数式

総計が、小計とリストが重複してしまい、誤った集計値を表示してしまいました。

小計も重複して計算された

このように小計を表の途中に入れて集計する場合は、SUBTOTAL関数にしておくと総計の変更が非常に楽になります。

まとめ

SUBTOTAL関数は、フィルタを設定した時にオートSUMボタンで自動挿入されますが、引数を変えることで様々な集計方法にすることが出来ます。

データ件数が少なめでフィルタを使用しながら集計値を確認する場合には有効ですが、データ件数が多くなりすぎると使いづらくなるので、SUMIFやCOUNTIFなど他の関数へ切り替えた方がいいでしょう。

一方、小計を含む表は、SUM関数よりSUBTOTAL関数を使った方がメンテナンス性は高くなります。

実は、「SUBTOTAL」を和訳すると「小計」となります。

もともとSUBTOTAL関数は小計を計算する時に、便利に使えるように設計されたものなのではないでしょうか?

このように関数を和訳すると、本来の使い方のヒントになることもあります。

いつもとちょっと違った見方をすると、このように面白いことやためになることがありますよね?


本日もありがとうございました。

スポンサーリンク

-関数(Excel)