データの集計では「合計」や「平均」などの算出によってデータを分析が行われます。
しかし、この算出結果はフィルターなどで表示範囲を絞っても計算範囲は変わらない為、正確なデータの算出結果が得られず別の箇所に再計算しなければならないなど手間がかかる事があります。
実はこうした問題への対処にはSUBTOTAL関数が役に立ちます。
SUBTOTAL関数はデータを絞った形での集計を行う事が可能で、簡単に正確なデータを算出する事ができます。
本記事ではSUBTOTAL関数を使用する事で得られるメリット3選について解説していきます。
- SUBTOTAL関数の使い方が分かり、様々な集計方法を扱えるようになる。
- SUBTOTAL関数を使う上での注意点が分かる。
SUBTOTAL関数とは？
SUBTOTAL関数は次の様に定義されています。
SUBTOTAL関数の定義
定義：SUBTOTAL(集計方法,範囲1,範囲2,…)
- 集計方法を指定する事で、様々な集計に対応可能
- 非表示行を除外してデータを絞り込んで計算する事が可能
次の項目からそれぞれの引数について解説をしていきます。
SUBTOTAL関数の引数
SUBTOTAL関数の引数は次の様になっています。
集計方法
各集計方法が数字で表されており、同じ集計方法を指定していても非表示データを含めるか否かで値が異なっています。
| 集計方法(非表示を含む) | 集計方法(非表示項目を含まない) | 集計内容 |
|---|---|---|
| 1 | 101 | 平均 |
| 2 | 102 | 数値の個数 |
| 3 | 103 | データの個数 |
| 4 | 104 | 最大値 |
| 5 | 105 | 最小値 |
| 6 | 106 | 積 |
| 7 | 107 | 不偏標準偏差 |
| 8 | 108 | 標本標準偏差 |
| 9 | 109 | 合計 |
| 10 | 110 | 不偏分散 |
| 11 | 111 | 標準分散 |
範囲1,範囲2,…
- 集計したい範囲を指定する引数。
- 範囲1以降はオプションで省略でき最大254まで指定可能。
SUBTOTAL関数の使用メリット3選
次のデータを元に使用メリットを3つ解説していきます。
同じ範囲で様々な集計をする事が出来る
同じ範囲でも指定する集計方法によって異なる集計を行い、「合計」や「平均」など柔軟に対応することができます。
非表示行を除き集計出来る
フィルターを掛けた時など非表示項目が範囲に含まれる場合、それらを除外した集計結果を自動で算出してくれるので条件を絞ったデータの集計に役立ちます。
例)同一範囲(E4:E12)でSUBTOTAL関数とSUM関数で合計をそれぞれ算出した場合
7月25日分のデータをオートフィルターを使って非表示にすると、SUBTOTAL関数は非表示になったデータを除外した計算結果を算出してくれます。
鳩野SUM関数で計算した場合は非表示分のデータも含んだままですが、SUBTOTAL関数は除外してくれていますね！
※除外の例外
手動でデータを非表示にした場合は、除外データの対象外となるので注意が必要です。
対策としては引数の「集計方法」を非表示を含まない’101~111’の数字を指定する事でこの問題を解決出来ます。
範囲内のSUBTOTAL関数で出した集計を除外してくれる
範囲内にSUBTOTAL関数で算出したデータが含まれている場合それを除外し、正確な集計を算出してくれます。
悠小計など途中で集計を含んでいるデータを扱う際に便利ですね！
以上で解説は終了となります。
SUBTOTAL関数を使用する事でデータを絞った形でより正確な集計が出来る様になります。
またSUBTOTAL関数1つで様々な集計を行うことができ、柔軟な対応が出来るのでデータ分析の際には是非使用してみて下さい！
最後までお読みいただきありがとうございました。
