フィルタと組み合わせるときはSUM関数よりSUBTOTAL( 9、合計範囲 )を使おう！

2021年4月18日

Excel（エクセル）関数の1つ、SUBTOTAL（サブトータル）関数は、指定した範囲を、指定した方法（合計・個数・空白ではないセル個数等）で集計する関数で、SUM関数やCOUNT関数、COUNTA関数、AVERAGE関数などのような機能を持っています。

この記事では、実務での使用頻度が高く、ぜひ知っておいてほしい、フィルタを使って抽出するデータを集計する際に、SUBTOTAL関数をおすすめする理由を、SUM関数と比較しながら解説します。

サブトータル	書式
SUBTOTAL関数	（集計方法 , セル範囲）

SUBTOTAL関数の書式

SUBTOTAL（サブトータル）関数は、指定した範囲を、指定した方法（合計・平均・個数等）で集計する関数です。

［=SUBTOTAL( 集計方法 , セル範囲 )］という書式で表します。

集計方法
1～11、101～111のいずれかの数値（番号）で指定します。

セル範囲
セル範囲は 1つだけでなく、2つ以上も指定できます。

SUBTOTAL関数は、集計方法を1～11、101～111のいずれかの数値（番号）で指定します。

まずは、数値（番号）は、9 を使えるようになりましょう。

なお、どの数値（何番）が、どの集計方法に対応しているのかを覚える必要はありません。

以下のように、支援機能でわかりやすく表示してくれます。

SUBTOTAL関数の代表的な使い方である、集計方法を9とする場合を解説します。

SUM関数ではなく、SUBTOTAL関数を使うのには大事な意味が込められています。

今、C5セルには、C2セル～C4セルの小計を、C10セルにはC6～C9セルの小計を、そして、C11セルにはそれらの合計を求めることにします。

SUM関数を使った場合は、以下のようにします。

一方、SUBTOTAL関数を使った場合は、以下のようにします。

※SUM関数よりは複雑に見えますが、9を入れるだけなのでたいしたことはありません。

リストで、フィルタを使ってデータを抽出する際、条件に一致しないデータは非表示になります。

SUM関数は、フィルタを使って表示・非表示を切り替えても、表示・非表示に関わらず合計するため、計算結果は同じになります。

フィルタで、営業2課だけにしても、合計は 7,400 のまま。

一方、SUBTOTAL関数は、フィルタを使って表示・非表示を切り替えたときに、表示されているセルだけを合計するため、計算結果が変化します。

フィルタで、営業2課だけにすると、表示されている部分だけが合計されて、合計は 4,300 になります。

今回使用したSUBTOTAL関数の第1引数・集計方法の数値（番号）9は、フィルタを使って非表示になった部分は合計しません。

ですが、手動や［Ctrl］＋［9］を使って非表示にした部分は、合計されてしまいます。

このことに注意をしてください。

手動や［Ctrl］＋［9］を使って非表示にした部分を（も）合計したくない場合は、SUBTOTAL関数の第1引数・集計方法の数値（番号）を109にしてください。