Excel

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

 

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

 

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

関連記事小計が複数あるときはSUM関数よりSUBTOTAL( 9、合計範囲 )を使おう!

関連記事手動で非表示にした行を合計したくないならSUBTOTAL( 109、合計範囲 )を使おう!

 

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

 

 

SUBTOTAL関数の書式

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

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

 

集計方法
1~11
101~111のいずれかの数値(番号)で指定します。

 

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

 

集計方法

SUBTOTAL関数は、集計方法を1~11101~111のいずれかの数値(番号)で指定します。

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

 

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

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

SUBTOTAL|支援機能1

SUBTOTAL|支援機能2

 

 

SUBTOTAL関数、集計方法[9]

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

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

 

SUM関数との書式の違い

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

ST|売上表

 

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

C5セル =SUM(C2:C4)
C10セル =SUM(C6:C9)
C11セル =SUM(C5,C10)or =SUM(C2:C4,C6:C9)

 

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

C5セル =SUBTOTAL(9,C2:C4)
C10セル =SUBTOTAL(9,C6:C9)
C11セル =SUBTOTAL(9,C2:C10)

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

 

フィルタで表示されているセルのみ合計する

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

 

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

フィルタをかける前

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

SUM関数にフィルタをかけた

 




 

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

フィルタをかける前

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

SUBTOTAL関数にフィルタをかけた

 

関連記事小計が複数あるときはSUM関数よりSUBTOTAL( 9、合計範囲 )を使おう!

 

SUBTOTAL関数を使用する際の注意点

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

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

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

 

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

 

 

おすすめ・関連記事

参考文献

-Excel
-

© 2021 まなびっと Powered by AFFINGER5