「Excelのフィルターが便利なことが分かったけど、もっと便利に活用したい。」
そのように考えたことはありませんか？
実は、フィルターされたデータのみを集計出来る関数があるんです。
今回は、フィルターされたデータを集計できる関数であるSUBTOTALについて紹介していきます。
この記事を読むことで、
- フィルターを活用しながらSUBTOTAL関数で集計できるようになる
- SUBTOTAL関数をフィルター以外での用途でも活用できる
といった効果を期待出来ます。
SUBTOTAL関数を挿入する
SUBTOTAL関数は、数式バーに直接挿入できますが、フィルタを設定していると、オートSUMボタンでも挿入できます。
まずは、データが入力された表にフィルタを設定し、データを絞り込んでください。
ここでは、品名をりんごにしました。
データを絞り込んでいないと、SUBTOTAL関数が挿入されません。
また、表はデータベース化されていることが必須です。こちらの記事を参考にしてください。
ホームタブのオートSUMボタンをクリックし、合計をクリックします。
Subtotal関数が挿入されました。
りんごのみを合計した値が計算されました。
ここで、平均や個数などをクリックしてもSUBTOTAL関数は挿入されませんので注意してください。
品名がりんごになっていますが、にんじんに変更してみます。
集計結果が変わりました。
SUBTOTALの集計方法
SUBTOTAL関数は、以下のようにして構成されています。
- 第一引数・・・集計方法
- 第二引数・・・集計範囲
オートSUMボタンからSUBTOTAL関数を挿入すると、集計方法は9になっています。
第一引数を変えれば、集計方法を変えることができます。
第一引数は以下の表のようになっています。
| 引数 | 集計の名前 | 計算方法 |
|---|---|---|
| 1 | AVERAGE | 平均 |
| 2 | COUNT | 個数(数値のみ) |
| 3 | COUNTA | 個数(文字も含む) |
| 4 | MAX | 最大値 |
| 5 | MIN | 最小値 |
| 6 | PRODUCT | 掛け算 |
| 7 | STDEV | 標準偏差(n-1で割る) |
| 8 | STDEVP | 標準偏差(nで割る) |
| 9 | SUM | 合計 |
| 10 | VAR | 分散(n-1で割る) |
| 11 | VARP | 分散(nで割る) |
ここでは、個数を集計する3を入力してみます。
データ件数が算出されました。
非表示行を反映させない集計
ここで、第一引数を9を109に変更してみます。
にんじんが表示されていますが、一行を非表示にしてみます。
集計値に非表示の行が排除されました。
このように、第一引数に100を足すと、非表示の行を排除することができます。
| 引数 | 集計の方法 | 計算方法 |
|---|---|---|
| 101 | AVERAGE | 平均 |
| 102 | COUNT | 個数(数値のみ) |
| 103 | COUNTA | 個数(文字も含む) |
| 104 | MAX | 最大値 |
| 105 | MIN | 最小値 |
| 106 | PRODUCT | 掛け算 |
| 107 | STDEV | 標準偏差(n-1で割る) |
| 108 | STDEVP | 標準偏差(nで割る) |
| 109 | SUM | 合計 |
| 110 | VAR | 分散(n-1で割る) |
| 111 | VARP | 分散(nで割る) |
SUBTOTAL関数を活用して小計を計算する
SUBTOTAL関数はフィルタ以外でも使える場面があります。それは、小計を含む表を作る時です。
ここで、小計、総計ともにSUBTOTAL関数を使ってみます。
どちらも正しく計算することができました。
SUBTOTAL関数をSUM関数にしてみます。
総計が、小計とリストが重複してしまい、誤った集計値を表示してしまいました。
このように小計を表の途中に入れて集計する場合は、SUBTOTAL関数にしておくと総計の変更が非常に楽になります。
まとめ
SUBTOTAL関数は、フィルタを設定した時にオートSUMボタンで自動挿入されますが、引数を変えることで様々な集計方法にすることが出来ます。
データ件数が少なめでフィルタを使用しながら集計値を確認する場合には有効ですが、データ件数が多くなりすぎると使いづらくなるので、SUMIFやCOUNTIFなど他の関数へ切り替えた方がいいでしょう。
一方、小計を含む表は、SUM関数よりSUBTOTAL関数を使った方がメンテナンス性は高くなります。
実は、「SUBTOTAL」を和訳すると「小計」となります。
もともとSUBTOTAL関数は小計を計算する時に、便利に使えるように設計されたものなのではないでしょうか？
このように関数を和訳すると、本来の使い方のヒントになることもあります。
いつもとちょっと違った見方をすると、このように面白いことやためになることがありますよね？
本日もありがとうございました。