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~11、101~111のいずれかの数値(番号)で指定します。
まずは、数値(番号)は、9 を使えるようになりましょう。
なお、どの数値(何番)が、どの集計方法に対応しているのかを覚える必要はありません。
以下のように、支援機能でわかりやすく表示してくれます。
SUBTOTAL関数、集計方法[9]
SUBTOTAL関数の代表的な使い方である、集計方法を9とする場合を解説します。
SUM関数ではなく、SUBTOTAL関数を使うのには大事な意味が込められています。
SUM関数との書式の違い
今、C5セルには、C2セル~C4セルの小計を、C10セルにはC6~C9セルの小計を、そして、C11セルにはそれらの合計を求めることにします。
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 のまま。
(adsbygoogle = window.adsbygoogle || []).push({});
一方、SUBTOTAL関数は、フィルタを使って表示・非表示を切り替えたときに、表示されているセルだけを合計するため、計算結果が変化します。
フィルタで、営業2課だけにすると、表示されている部分だけが合計されて、合計は 4,300 になります。
関連記事小計が複数あるときはSUM関数よりSUBTOTAL( 9、合計範囲 )を使おう!
SUBTOTAL関数を使用する際の注意点
今回使用したSUBTOTAL関数の第1引数・集計方法の数値(番号)9は、フィルタを使って非表示になった部分は合計しません。
ですが、手動や[Ctrl]+[9]を使って非表示にした部分は、合計されてしまいます。
このことに注意をしてください。
手動や[Ctrl]+[9]を使って非表示にした部分を(も)合計したくない場合は、SUBTOTAL関数の第1引数・集計方法の数値(番号)を109にしてください。
おすすめ・関連記事
- Excel関数の基本的な使い方と応用テクニック辞典(関数一覧)
- Excel 厳選ショートカットキー・印刷用・早見表PDF付き
- Windows10 厳選ショートカットキー・早見表・印刷用PDF付き
- Word 厳選ショートカットキー・印刷用・早見表PDF付き
- PowerPoint 厳選ショートカットキー・早見表・印刷用PDF付き