この記事では、SUM関数とSUBTOTAL関数の違いとSUBTOTAL関数の使い方から使用例までわかりやすく解説します。
自己紹介
IT業界でのキャリアを活かし、業務効率化に欠かせないMicrosoft365や便利ツールを紹介しています。少しのコツで膨大な可能性を最大限引き出すためのヒントやテクニックを提供し、読者のビジネスパフォーマンス向上に貢献することを目指しています！
SUBTOTAL関数とは？
SUBTOTAL関数は、エクセルで使用される数値関数の一つであり、指定されたリストやデータセット内で合計や平均などの集計を行う際に使用されます。SUBTOTAL関数は通常の集計関数（例：SUM、AVERAGE、COUNT）とは異なり、フィルタリングされたデータや非表示の行などを考慮して計算を行います。
主な用途と利点
SUBTOTAL関数の主な用途は、大きなデータセットの中から特定の条件に合致するデータのみを集計することです。例えば、特定のカテゴリに属する商品の合計金額を求めたり、特定の地域の売上合計を算出する際に活用されます。利点としては、非表示データを無視する点が挙げられます。つまり、表示されているデータのみが計算に含まれ、非表示の行や列は無視されます。
- 特定の条件に合致するデータのみを集計
- 表示されているデータのみを考慮して計算
- 非表示の行を無視して計算できる
- 商品カテゴリや地域別の売上合計などの集計に使える
利用可能な関数の種類
SUBTOTAL関数は、様々な集計関数（SUM、AVERAGE、COUNTなど）を使用することができます。以下にSUBTOTAL関数で利用可能な関数の一覧を置いておきます。
| SUBTOTAL関数で使える関数一覧 | |||
| 非表示の行 含めて計算 |
非表示の行 無視して計算 |
関数 | 関数の役割 |
| 1 | 101 | AVERAGE | 範囲内の平均値を計算 |
| 2 | 102 | COUNT | 範囲内の数値の数を数える |
| 3 | 103 | COUNTA | 範囲内の空でないセルの数を数える |
| 4 | 104 | MAX | 範囲内の最大値を求める |
| 5 | 105 | MIN | 範囲内の最小値を求める |
| 6 | 106 | PRODUCT | 範囲内の数値の積を計算 |
| 7 | 107 | STDEV | 範囲内の標本標準偏差を計算 |
| 8 | 108 | STDEVP | 範囲内の母集団標準偏差を計算 |
| 9 | 109 | SUM | 範囲内の数値の合計を計算 |
| 10 | 110 | VAR | 範囲内の標本分散を計算 |
| 11 | 111 | VARP | 範囲内の母集団分散を計算 |
SUM関数とSUBTOTAL関数の違い
SUM関数とSUBTOTAL関数は、合計を求める点では同じです。ただし、それぞれ明確に異なる部分があります。SUM関数とSUBTOTAL関数の違いを理解するために、それぞれの関数の基本的な概要を確認してみます。
SUM関数の限界
SUM関数は、非表示の行や列に含まれるデータも計算に含めるため、特定の条件に合致する表示データのみを合計することができません。要は範囲内の値をすべて合計してしまうということです。例えば、フィルタリングで一部の行を非表示にしても、それらの行のデータは合計に影響します。イメージしやすいようにSUM関数の使用方法とフィルタリング機能を使った場合の計算結果を見てみましょう！
A～E商品の販売個数・売上・利益のそれぞれを合計しています。
C商品以外を表示した場合の計算結果…
フィルタリングをする前とした後でも合計の計算結果が同じですね。結果を見てわかる通りSUM関数では、フィルタリングの結果次第で合計範囲を自動で変更することができないんです。
SUBTOTAL関数の特長と利点
SUBTOTAL関数は、非表示データを無視することができます。フィルタリング機能を使用してデータを絞った場合、表示されている数値のみで正確な合計を算出するため、データ分析や条件付きの集計に適していることが分かります。
表示している数値のみを計算して合計を出してくれるのは便利ですが、SUM関数と違って本来の合計値を知りたい場合は注意が必要です。特にチームで共有しているシートなどで誰かがフィルタリング機能を使用してデータの絞り込みをしている場合は、本来の合計値じゃないので気を付けましょう。
SUM関数とSUBTOTAL関数の違いを一覧で比較
SUM関数とSUBTOTAL関数の違いを簡単にまとめてみました！スクリーンショットや表をコピーしてご自由にお使いください！
| SUM関数 | SUBTOTAL関数 | |
| データの範囲 | 範囲内の全ての数値を合計する | 範囲内の表示されている値のみ合計する |
| 非表示データの扱い | 含む | 含まない |
| フィルタリングの影響 | 影響されない | 影響される |
| 関数番号 | なし | 関数番号を指定して使用する。（SUMは9,109） |
SUBTOTAL関数とSUM関数の違いは理解できたと思うので、SUBTOTAL関数の使い方を学んでいきましょう！
SUBTOTAL関数の基本的な使い方
ようやくSUBTOTAL関数の使い方についてです。この関数を使用すると、非表示データを無視して計算する場合も設定可能です。これにより、データの絞り込みや条件付きの集計が容易に行えます。基本構文から見ていきましょう！
SUBTOTAL関数の基本構文
=SUBTOTAL(集計方法,参照1…)
第一引数である集計方法は、使用する関数を指定します。
集計方法の1〜11は、行の非表示だけなら非表示分も計算します。フィルタリングされた場合は、表示されている数値のみ算出。行の非表示とフィルタリングがされている場合は、どちらも計算から除かれ、表示されている数値のみ計算をしてくれます。
集計方法の101〜111: 非表示の行やフィルタリングそれぞれを除いて算出してくれます。
参照1はセルの範囲指定を行ってください。
今回押さえておきたいポイントは、SUM関数の集計方法は（9, 109）になります。行の非表示やフィルタリングで絞られたデータのみを計算したい場合は、109を使います。行の非表示はせずに、フィルタリングのみ活用して表示されている数値だけを計算したい場合は、9を使用します。
テキストだと複雑に見えて混乱しますよね…。
理解できるように画像を使って使用例を見ていきましょう！
SUBTOTAL関数の簡単な使用例
実際にSUBTOTAL関数をエクセルで使用してみます。今回の使用例では、SUBTOTAL関数で合計と平均を求めていきたいと思います。また合計を求められるSUM関数や平均を求められるAverage関数も一緒に使って結果の違いを確認できればと思います！
合計値を求める｜SUM関数とSUBTOTAL関数を比較
実際にエクセルで数値の合計を算出していきたいと思います。合計を計算する関数は、SUM関数とSUBTOTAL関数を使用していきます。
合計を求めるために、次のような表を用意しました。
表の内容はB3～B7まで商品が記載されており、各商品の売上がC列に入力されています。各商品の売上合計を「SUM関数・SUBTOTAL関数の9・SUBTOTAL関数の109」で計算してみたいと思います。
では、合計値を求める以下の関数式を入力していきます。
C8セル…. =SUM(C3:C7)
C9セル…. =SUBTOTAL(9,C3:C7)
C10セル… =SUBTOTAL(109,C3:C7)
行の非表示やフィルタリングで絞られていないため、すべて同じ合計値になっています。今回、皆さんに行の非表示がある場合やフィルタリングで絞られた場合、両方されている場合のSUBTOTAL関数の結果を見ていただきたいです。
まずは、行の非表示をしてみたいと思います。例としてD商品が入力されている6行目を非表示にしてみます。
非表示の行だけなら3ケタ以外（9）の集計方法は、本来の範囲の集計結果を返します。
次はフィルタリングで絞られた場合を見ていきましょう。フィルタリングはどの集計方法でも含まれないはずなので、9行目と10行目は同じ結果になると思います。フィルタリングではD商品以外を表示させてみます。
SUBTOTAL関数（9,109）を入力しているセルは、同じ数字になりましたね。このようにフィルタリングで絞っている場合は、表示されている数値のみ合計値を求めることができます。では、「フィルタリングで絞る＆行を非表示」両方した場合はどのような結果になるでしょうか？結果を見てみましょう！
フィルタリングでD商品以外を表示している状態で、B商品が入力されている4行目を非表示にしてみましょう。
8行目と9行目の合計値は同じになりましたね！
なんとなくSUBTOTAL関数の使用方法は分かりましたか？一旦使用例の振り返りをしてみましょう。
| SUBTOTAL関数（9） | SUBTOTAL関数（109） | |
| ①行の非表示のみ | 本来の範囲を算出 | 表示されている値のみ計算 |
| ②フィルタリングのみ | 表示されている値のみ計算 | 表示されている値のみ計算 |
| ①＋②両方 | 表示されている値のみ計算 | 表示されている値のみ計算 |
その気持ち凄くわかります。私も最初はどれ使ったらいいんだ？と思っていました。ただ、合計を求める基本の関数はSUM関数です。SUBTOTAL関数（9,109）は表示されている値の合計を算出する関数なので、私は基本的に合計はSUM関数を使用して、表示している値の合計が知りたい時はSUBTOTAL関数（109）を使用しています！
平均を求める｜AVERAGE関数とSUBTOTAL関数を比較
エクセルで平均を求められるAVERAGE関数との計算結果の違いも確認しつつ、SUBTOTAL関数で平均を求めていきましょう。
例を分かりやすく進めるために下記の表を用意しました。8～10行目にAVERAGE関数とSUBTOTAL関数（1,101）を使用して平均を求めていきます。
実際に以下の関数を入力していきます。
C8セル…. =AVERAGE(C3:C7)
C9セル…. =SUBTOTAL(1,C3:C7)
C10セル… =SUBTOTAL(101,C3:C7)
それぞれフィルタリングや行の非表示をしていないので、同じ計算結果が返されています。試しにA商品が入力されている3行目を非表示にしてみましょう。
行を非表示にする手順は、範囲を「Shift+スペース」で選択して「Ctrl+9」で出来ます。3行目を非表示にした結果は次の通りです。
行を非表示にした場合は、SUBTOTAL関数（101）だけ計算結果が異なります。これは3ケタの集計方法が行の非表示やフィルタリングで絞られている場合、表示されている値のみ計算をする仕組みになっているためです。
次にフィルタリングをかけてみましょう。フィルタリングでは、D商品以外を表示したいと思います。
フィルタリングで絞った場合は、SUBTOTAL関数が計算する範囲は表示のみになります。では、フィルタリングと行の非表示が同時に行われたときの計算結果を見てみましょう！
非表示にする行は3行目、フィルタリングではD商品以外を表示します。
フィルタリングで絞る＋行の非表示がある場合は、SUBTOTAL関数（1,101）どちらとも表示されている値のみ平均を求めて返してくれます。
ビジネスシーンでの活用例
使い勝手のいいSUBTOTAL関数ですが、ビジネスシーンでどのように活用するのでしょうか？実際にSUBTOTAL関数を活用できる業務内容を一部チェックしておきましょう。
活用できる実務の例
SUBTOTAL関数は幅広い場面で活用することができます。活用できる業務の一部を表にして共有します！皆さんの業務でも活用してください
| 活用例 | 手順 |
| 売上データの管理 | 1. 売上データを選択する。 |
| 2. フィルタリングで表示したいデータを絞り込む。 | |
| 3. SUBTOTAL関数でフィルタリングされた範囲のみの合計を求める。 | |
| 在庫データの分析 | 1. 在庫データを選択する。 |
| 2. 在庫状況やカテゴリごとにフィルタリングを行い、分析したいデータを表示する。 | |
| 3. SUBTOTAL関数を使用して、フィルタリングされたデータの合計や平均、個数などを求める。 | |
| 複数のシートでの SUBTOTAL関数の応用 |
1. データを含む複数のシートを選択する。 |
| 2. 各シートで必要なフィルタリング条件を設定し、データを表示する。 | |
| 3. 各シートごとにSUBTOTAL関数を使用して集計を行い、それぞれのシートの合計をさらに合計する。 | |
| 他の関数と一緒に使う | 1. 必要なデータをフィルタリングし、表示する。 |
| 2. SUBTOTAL関数を使用して、フィルタリングされたデータの合計を求める。 | |
| 3. 他の関数（例えば、AVERAGE関数やCOUNT関数など）で本来の数値と表示されている範囲の数値の比較ができる。 |
まとめ
SUBTOTAL関数は、範囲の中で表示中の値を計算するのに優れている関数でしたね。しかし、集計方法の関数番号次第で計算結果が異なるので注意が必要です。計算結果の違いは次の通りでした。
関数番号は用途によって使い分けたほうがよさそうですね。注意は必要ですが、非表示のデータを無視して正確な合計値を求めることができるため、データの解析や集計業務の作業効率をUPさせることができるでしょう。
