Excel

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

 

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

 

この記事では、実務での使用頻度が高く、ぜひ知っておいてほしい小計が複数個ある表や、小計と合計がある表に、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セルの合計を求めることにします。

ST|売上表

 

SUM関数を使うのならば、以下のようになりますよね。

C5セル =SUM(C2:C4)

 

一方、SUBTOTAL関数を使うのならば、以下のようにします。

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

C5セル =SUBTOTAL(9,C2:C4)

 

 

SUBTOTAL関数の特徴を活かす

SUBTOTAL関数で集計をする場合、SUBTOTAL関数が入力されているセルは無視される

 




 

どういうことかを、具体例を用いて説明します。

さきほどの表のC5セル、C10セル、C11セルにSUBTOTAL関数を入力します。

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

SUBTOTALの入力例2

 

C11セルの範囲を、C2セル~C10セルとしていることに注目してください。

 

SUBTOTAL関数で小計を求めているC5セルC10セルが含まれていますが、[SUBTOTAL関数で集計をする場合、SUBTOTAL関数が入力されているセルは無視される]という特徴から、二重に計上されることなく、以下のようにうまくいきます。

SUBTOTALの入力結果

 

 

組織改編などに強い

今度は、営業2課が組織改編で、営業2課と営業3課に分かれた場合を例にして説明します。

 

営業2課の渡辺さん(B8セル)と山本さん(B9セル)が、営業3課になったとしましょう。

8行目に行を挿入し、営業2課の小計を求めます。

営業2課の小計をつくる

 

C8セルに、SUBTOTAL関数を入力します。

C8セル =SUBTOTAL(9,C6:C7)

※C11セルのSUBTOTAL関数を[=SUBTOTAL(9,C9:C10)]と適正化しています。

 

SUM関数の場合、途中に小計を加えると、合計欄(ここでは、C12セル)のセル範囲を修正する必要がありますが、SUBTOTAL関数の場合はその必要がありません

 

手間が省けるとともに、修正漏れを防ぐことができ、ミスする確率を下げることになります。

営業2課の小計をつくった結果

 

以上の理由から、小計を求める、このような表を作成する際は、SUM関数ではなく、SUBTOTAL関数を使用することをオススメします。

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

 

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

SUBTOTAL関数で集計する場合、SUBTOTAL関数は無視されますが、SUM関数などが無視されるわけではありません

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

 

つまり、SUBTOTAL関数で集計する場合は、すべての集計をSUBTOTAL関数で行うようにしてください。

SUM関数とSUBTOTAL関数を混在させると、集計結果が正確ではなくなります。

 

 

おすすめ・関連記事

参考文献

-Excel
-,

© 2024 まなびっと Powered by AFFINGER5