EXCELを使って作業していると、非表示の行を集計から除外したい時がありますね。そんな時にも役立つのがSUBTOTAL関数です。使い方はかんたんですが、注意点もあるので分かりやすく解説していきます。
SUM関数との比較
SUM関数と比較すると分かりやすいです。日付と売上を入力した同じ表を使って解説します。
左側の表ではSUM関数を使って合計を出します。
右側の表はSUBTOTAL関数で合計を出します。第1引数（集計方法）は「9 – SUM」を使っています。
結果は変わりません。
しかし、行を非表示にするとSUM関数とSUBTOTAL関数では結果に違いが生じます。
非表示の行を集計から除外する方法
上の画像のように、データ範囲を選択して「データ」タブの「フィルター」を選択します。
その後、日付の部分にある▼をクリックして、フィルターの設定をします。画像の通り、データの中で「4月」だけにチェックを入れ「OK」します。4月のデータだけを残し、5，6月のデータは非表示にするということです。「合計」はどのように変化するでしょうか。
- SUM関数：非表示の行も含めて集計している
- SUBTOTAL関数：非表示の行は含めないで集計している
実際の作業では、非表示の行を計算から除外したいことが多いのではないでしょうか。SUBTOTAL関数の特徴は、こうした場合に有効です。かんたんに言えば、SUBTOTAL関数は、基本的に表示されている行のみを集計してくれます。SUM関数を使った状態でデータにフィルターをかけると、非表示になっている行も含めて計算してしまうわけです。
注意点
同じSUBTOTAL関数を使っていても、どのように行を非表示にしたかで集計結果が変わってきます。
フィルターで非表示にした場合
前章で説明した通り、オートフィルターで合計を出すと非表示部分は計算されません。しかし、特定の行を手動で非表示にした場合は結果が異なります。
手動で非表示にした場合
先程の表で説明します。まず、赤囲みの部分でフィルターをクリアします。
「データ」タブの「フィルター」をクリックして、オートフィルターを解除します。
この状態で、5，6月の行を手動で非表示してみます。該当する行を選択して、その部分で右クリックし、表示されるメニューから「非表示」を選択します。結果はどうなるでしょうか。
何とSUM関数と同じように、非表示の行も含めて集計されてしまいます。赤囲み部分で確認できますが、集計方法は「9 – SUM 」、範囲は「E4:E12」となっています。
このように、手動で非表示にした場合は非表示の行を含めて集計されます。ここが注意点です。
集計方法を109にする
| 集計方法（非表示を含む） | 集計方法（非表示を含まない） | 関数 | 関数の説明 |
| 1 | 101 | AVERAGE | 平均値 |
| 2 | 102 | COUNT | 数値の数 |
| 3 | 103 | COUNTA | データの数 |
| 4 | 104 | MAX | 最大値 |
| 5 | 105 | MIN | 最小値 |
| 6 | 106 | PRODUCT | 積 |
| 7 | 107 | STDEV.S | 不偏標準偏差 |
| 8 | 108 | STDEV.P | 標本標準偏差 |
| 9 | 109 | SUM | 合計 |
| 10 | 110 | VAR.S | 不偏分散 |
| 11 | 111 | VAR.P | 標準分散 |
上の表の通り、集計方法は11種類です。しかし、非表示を含むか否かで1～11を使うのか、101～111を使うのかが変わります。100番台は「非表示を含まない」ようにしたい時に使います。つまり、手動で非表示にした行は集計から除外できるということです。
画像の通り、第1引数（集計方法）に「109」を選んでいます。第2引数（範囲）はE4:E12までに指定します。集計結果はどうなるでしょうか。
表示されている行だけで集計されていることが分かります。
これは別の集計方法を選んでも同じです。例えば、1もしくは101を使用してAVERAGE（平均値）を計算する場合でも同じということです。使用用途に合わせて、1～11を使うか101～111を使うかを決めるようにしましょう。特に手動でフィルターをかける場合は注意が必要ですね。
まとめ
いかがでしたか。SUBTOTAL関数は、基本的に非表示の行を除外して集計してくれます。注意点は、手動で非表示にした行の場合です。その場合は、集計方法で「109 – SUM」を選ぶ必要があります。
次回はSUBTOTAL関数とよく似ていて非なる関数「AGGREGATE」についてです。次回もお楽しみに！
