【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関数は小計セルを気にせず、計算範囲に含められるので感覚的にも使いやすいと思います。

【無料ダウンロード可能】あなたのPCスキル診断シート

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関数の結果だけが変化しています。