Excel(エクセル)で数値の合計を求めるためにSUM関数を挿入したはずが、SUBTOTAL関数(サブトータル）が挿入されたことありませんか！？
今回は知っているようで知らないSUBTOTAL関数の便利な使い方をご紹介します。
SUBTOTAL関数は、『集計範囲内のSUBTOTAL関数を合計値から除外出来る』など以外と役立つ機能が存在します。
👇SUM関数がSUBTOTAL関数に変換されて困っている方はどうぞご覧ください
SUBTOTAL関数の構文
SUBTOTAL関数は、指定した集計方法でセル範囲の数値を集計する方法です。
=SUBTOTAL(集計方法,参照１,参照２, ...,）
集計方法には、SUMと同じ『合計』やAVERAGEと同じ『平均値』など１１種類あります。
（意外に多いですよね！？）
| 集計方法 | 引数 (非表示含む） | 引数 (非表示含めず） |
| 平均値 | 1 | 101 |
| 数値のあるセルの個数 | 2 | 102 |
| 空白を除くセルの個数 | 3 | 103 |
| 最大値 | 4 | 104 |
| 最小値 | 5 | 105 |
| 積 | 6 | 106 |
| 標本による標準偏差予測値 | 7 | 107 |
| 母集団全体の標準偏差 | 8 | 108 |
| 合計 | 9 | 109 |
| 標本による分散の予測値 | 10 | 110 |
| 母集団全体の分散 | 11 | 111 |
集計方法毎に、非表示のセル範囲を含める場合と含めない場合で２つの引数が存在しています。
特に集計方法が「合計」の引数である「9」と「109」が良く見かける引数ではないでしょうか！？
SUBTOTAL関数とSUM関数は何が違う？
集計方法は１１種類もあり引数を変えるだけで集計方法を変更出来ますが、SUM関数やAVERAGE関数などと何が違うのでしょうか。
それは、下の2つのメリットがあるからです。
１.セル範囲内のSUBTOTAL関数は自動で集計から除外出来る。
２.非表示の行や列を集計から除外出来る。
この２つのメリットについて、具体的に以下の見出しでご紹介します。
メリットその１ SUBTOTAL関数を集計から除外
まずメリットの１つ目です。
下の図のように、SUBTOTAL関数は、集計するセル範囲内のSUBTOTAL関数を自動で集計から除外出来ます。
食品と飲料の売上小計をSUBTOTAL関数を利用することで、最下行の合計はSUBTOTAL関数をD5～D12のセル範囲にしても、小計を重複して集計することはありません。
このように小計が多い表の集計にはSUBTOTAL関数が便利です！
メリットその2 非表示の行や列を集計から除外出来る
続いてメリットの２つ目です。
SUBTOTAL関数は、非表示を含まない３桁の引数を用いると、非表示のセル範囲を集計から除外出来ます。
こちらも下の表を用いて具体的に説明します。
下の表は、４月売上合計を非表示を含む引数「9」と非表示を含まない引数「109」の列を比較できるよう横並びで作成しています。
この集計結果はどちらも「2,100」と同じ状態です。
次に、８行目のパスタの行を非表示にします。
すると、引数「109」の集計結果は、「2,100」から「1,700」と減り、パスタの「400」が除外されました。
引数「9」については、行を非表示にしても結果は同じです。
このように、SUBTOTAL関数の３桁の引数は、非表示の値を集計に含めたくない時に便利です。
