【Excel】小計が含まれる表を計算するときに!SUBTOTAL関数の使い方、活用事例を紹介

この記事では、SUBTOTAL関数の使い方や活用事例などを紹介してきます。「SUM関数しか知らない」という方はぜひ参考にしてみてください。

【Excel】小計が含まれる表を計算するときに!SUBTOTAL関数の使い方、活用事例を紹介

Microsoft Excel(以下、Excel)関数のひとつであるSUBTOTAL関数は、数値の合計を算出できる関数です。使い方がSUM関数と似ているのですが、小計を含む表やフィルターを扱う際にはSUBTOTAL関数のほうが便利な場合があります。

この記事では、SUBTOTAL関数の使い方や活用事例などを紹介してきます。「SUM関数しか知らない」という方はぜひ参考にしてみてください。

 

合計はSUM関数だけではない!小計を簡単に計算できるSUBTOTAL関数



数の合計を出したい時によく使われるのはSUM関数です。しかし、そのほかにSUBTOTAL関数を使って合計を算出する方法があります。まずはSUM関数とSUBTOTAL関数の違いを理解するために以下の図をご覧ください。表内の数字は両方とも同じですが小計・総計の計算式が異なります

SUBTOTAL関数には「小計や非表示セルは計算の対象から除く」という性質を持ちます。そのため左側の表の総計(=SUBTOTAL(9,B3:B11))は、「小計を除くB3~B11までのセル」を計算しています。

SUM関数         :指定の範囲すべてを合計する
SUBTOTAL関数     :小計を除いて合計する

小計や中計などを含む大きな表の場合にはSUBTOTAL関数を使った方が便利です。SUM関数で総計を計算する場合、いちいちセルを指定しなくてはいけないので面倒。またSUBTOTAL関数は小計セルを気にせず、計算範囲に含められるので感覚的にも使いやすいと思います。

 

SUBTOTAL関数の使い方



SUBTOTAL関数は「=SUBTOTAL (集計方法 , 集計範囲)」で定義されます。

集計方法は1~11までの数で決められています。合計の場合は「9」を入れます。上記のように、集計方法の数字を変えると、得られる結果も変わってきます。

【番号と集計方法(何を求めるのか)】
1:AVERAGE関数(平均)
2:COUNT関数(数値の個数)
3:COUNTA関数(データの個数)
4:MAX関数(最大値)
5:MIN関数(最小値)
6:PRODUCT関数(積)
7:STDEVS関数(不偏標準偏差)
8:STDEVP関数(標本偏標準偏差)
9:SUM関数(合計)
10:VARS関数(不偏分散)
11:VARP関数(標準分散)

 

SUBTOTAL関数はこんな時に使える!活用事例を紹介


小計と総計を計算する


まずは先ほどからもご紹介している小計を計算する方法です。決算書や店舗集計、カテゴリー集計など、さまざまなシーンで使えると思います。

 

フィルターをかけて表示されたものだけを計算する


SUBTOTAL関数はオートフィルターやテーブル機能との相性もいいです。たとえば、フィルターをかけて絞ったデータの合計や個数を計算したい時にSUBTOTAL関数を使います。SUM関数やCOUNT関数では表全体を対象に計算しますが、SUBTOTAL関数はあくまで見えている部分だけを対象に計算してくれます。

以下のようなデータの場合、左側はフィルターをかける前、右側は「日用品」でフィルターをかけた後です。フィルターをかける前は、SUM関数とSUBTOTAL関数の結果は同じになっています(両方181,000)。フィルター後は、SUBTOTAL関数の結果だけが変化しています。

 

SUBTOTAL関数を使う時の注意点



SUBTOTAL関数を使う時の注意点は大きく2つ。

① SUBTOTAL関数とSUM関数は組み合わせて使わない
② 単に行を非表示にしただけではダメ

①についてですが、SUBTOTAL関数が計算対象外とするのは、SUBTOTAL関数による小計のみ。SUM関数で計算した小計を含めてSUBTOTAL関数で総計を計算すると正しい結果が得られませんので注意してください。

②はあくまでオートフィルター実行後、表示されているセルのみを計算します。単純にセルを非表示にしただけでは、そのセルは計算に含まれたままとなります。

 

小計とフィルターを使う時はSUBTOTAL関数を使おう!



これまで「SUM関数しか使ったことがない」という方は、これを機にSUBTOTAL関数も使ってみてください。仕事をしていれば何かしらの表や集計をすることもあると思いますので、頭の片隅においておくといいですよ。使い方はとても簡単なのですぐに覚えられると思います。

【参考記事】
Excel(エクセル)記事まとめ
覚えておけば残業知らず!Excel時短記事まとめ
見づらい資料からサヨナラ!Excel資料作成記事まとめ
覚えて仕事を効率化!Excel関数記事まとめ
PowerPoint(パワーポイント)記事まとめ

page top