エクセルを使用していると「合計」や「最大値」等の集計をよく使用すると思います。
それぞれに関数があるのですが、こちらを１つにまとめたのが「SUBTOTAL関数」となります。
関数は19パターンを切り替えることができ、どれも使用頻度が高い関数になってます。
さらに応用すると集計方法を選ぶだけで、望み通りの集計を1動作で
切り替えることができますから、とても便利な関数になってます。
初心者のかたも是非参考にしてみてください。
それでは見ていきましょう。
⬛︎こんな事ができるようになる！
・平均やカウント・合計といった基礎的な関数を簡易的に使用できるようになる。
・基礎的な関数の使用を簡単に切り替えれる
⬛︎関数の仕組み
=SUBTOTAL(関数番号, 範囲1, [範囲2], …)
☟関数番号一覧
■関数の使用方法
・通常の使用方法
では早速SUBTOTAL関数の基本的な使い方について解説します。
集計方法の指定 SUBTOTAL関数では、関数番号を指定することで、どのような集計方法を行うのかを指定します。関数番号には、1から19までの数字が対応しており、それぞれ異なる集計方法が割り当てられています。以下に一部の関数番号と対応する集計方法を示します。
| 関数番号 | 内容 | 関数 |
|---|---|---|
| 1 | 平均 | AVERAGE |
| 2 | カウント | COUNT |
| 3 | 空白以外のカウント | COUNTA |
| 4 | 最大値 | MAX |
| 5 | 最小値 | MIN |
| 6 | 引数の積 | PRODUCT |
| 7 | データ自体の標準偏差 | STDEV.S |
| 8 | 母集団の標準偏差 | STDEV.P |
| 9 | 合計 | SUM |
| 10 | 不偏分散 | VAR.S |
| 11 | 標本分散 | VAR.P |
| 12 | 中央値 | MEDIAN |
| 13 | 最頻値 | MODE.SNGL |
| 14 | 降順の順位 | LARGE |
| 15 | 昇順の順位 | SMALL |
| 16 | 百分位数 | PERCENTILE.INC |
| 17 | 四分位数 | QUARTILE.INC |
| 18 | 百分位数（0％と100％）を除外 | PERCENTILE.EXC |
| 19 | 四分位数（0％と100％）を除外 | QUARTILE.EXC |
では実際の見本を用いて見ていきましょう。
下図をご覧ください。
例えば、セル範囲B3:B12の合計を求めたい場合には、
関数番号9を指定してSUBTOTAL関数を使います。
では構文に当てはめていきましょう。
=SUBTOTAL(9, B3:B12)
実行した結果はこのようになります。
合計を計算できていますね。
・複数範囲での使用方法
先ほどは1つの範囲でしたが、範囲1、範囲2、範囲3…と複数の範囲を指定することができます。
では再度見本を用意しました。
今回は、セル範囲B3:B12とD3:D12の複数範囲の最大値を求めたい場合を紹介します。
では構文に当てはめてみましょう。
関数番号は「最大値」の場合「4」となります。
セル範囲は「B3:B12とD3:D12」の2つとなるため下記のようになります。
=SUBTOTAL(4,B3:B12,D3:D12)
それでは入力してみましょう。
結果はこのようになりました。
こちらもうまく計算できていますね。
・応用編
これまで集計方法は関数番号を直接入力していました。
「最大値なら4」「合計なら9」のように入力していましたが、セルの値を参照することも可能です。
では下図の見本をご覧ください。
新たに「F3」に集計方法を追加してみました。
今回は「最小値」を求めてみましょう。
なので「5」を入力しています。
では構文に当てはめてみましょう。
参照範囲は先ほどと同じく「B3:B12とD3:D12」になります。
関数番号については今回は「F3」の値を参照するので下記の関数式になります。
=SUBTOTAL(F3,B3:B12,D3:D12)
それでは入力してみましょう。
結果はこのようになりました。
最小値を計算してくれていますね。
ちなみにこの状態で集計方法の番号を変えても機能してくれます。
⬛︎よくあるエラーと対処法
ではSUBTOTAL関数を使用する上でよくあるエラーと対処法について説明します。
・隠し行や隠し列の除外
SUBTOTAL関数は、隠し行や隠し列を無視して集計を行います。隠し行や隠し列のデータを集計に含めたい場合には、SUBTOTAL関数を使用する前に、隠し行や隠し列を表示するか、別の方法でデータを集計する必要があります。
・関数番号が適切でない
関数番号は「1～19」までの数値が対象になります。
その他の数値や文字が入力された場合は「#VALUE!」のエラーとなるので注意が必要です。
・バージョンの違い:
Excelのバージョンによって動作が異なることがあります。特に、Excel 2007以前のバージョンでは、SUBTOTAL関数の動作が異なるため、注意が必要です。使用しているExcelのバージョンに合わせた情報を確認し、適切に使用してください。
⬛︎公式の説明
わかりやすいように実際の内容とは異なった語句・文字で説明しています。
公式の内容をご覧になりたい方は下記リンクをご参照ください。
■まとめ
いかがだったでしょうか。
SUBTOTAL関数は使用頻度の高い、関数を関数番号として入力することで
計算を容易にしてくれる関数です。
さらに応用の関数として「AGGREGATE関数」があります。
こちらは「SUBTOTAL関数」の機能にエラーの無視や空白の無視など
集計にオプションを付けることが出来ます。
AGGREGATE関数についてはこちらから☟
【AGGREGATE関数】エクセルで空白やエラーを無視して合計・平均・最大値・最小値を求める。 ► 独学エクセル塾 (dokugakuexcel.com)
是非参考にしてみてください。
それでは次回の記事でお会いしましょう。