Google スプレッドシートを使いこなせるようになると、データ分析が格段にスムーズになります。特に大量のデータを扱う場合、小計や合計を効率的に計算できる関数があると便利ですよね。今回は意外と知られていないけれど非常に役立つ「SUBTOTAL 関数」について徹底解説します。
SUBTOTAL 関数とは？
SUBTOTAL 関数は、指定した範囲のデータに対して様々な集計処理（合計、平均、カウントなど）を行うことができる関数です。特に優れているのは、フィルターで非表示になっているデータや他の SUBTOTAL 関数を無視して計算できる点です。
基本的な書式は次の通りです：
=SUBTOTAL(関数コード, 範囲1, [範囲2, ...])「関数コード」は、どのような集計を行うかを指定する数値です。例えば「9」を指定すると SUM（合計）と同じ働きをします。
SUBTOTAL 関数の特徴
SUM 関数などの通常の集計関数と比較して、SUBTOTAL 関数には以下のような特徴があります：
- フィルターで非表示になったセルを無視する：フィルターを使ってデータの一部だけを表示している場合、SUBTOTAL 関数は表示されているデータだけを集計します。
- 他の SUBTOTAL 関数を含むセルを無視する：これにより二重計算を防止できます。
- 非表示行の取り扱いを指定できる：関数コードの選び方で、手動で非表示にした行を計算に含めるか除外するかを選択できます。
- 複数の計算タイプに対応：合計、平均、カウントなど様々な計算が可能です。
SUBTOTAL 関数の使い方
それでは、SUBTOTAL 関数の基本的な使い方を見ていきましょう。
関数コードの種類
SUBTOTAL 関数では、第一引数の「関数コード」によって実行する計算を指定します。主な関数コードは以下の通りです：
| 計算の種類 | 非表示行を含める (1-11) | 非表示行を無視する (101-111) |
|---|---|---|
| 平均 | 1 | 101 |
| カウント（数値のみ） | 2 | 102 |
| カウント（空白以外） | 3 | 103 |
| 最大値 | 4 | 104 |
| 最小値 | 5 | 105 |
| 積 | 6 | 106 |
| 標準偏差 | 7 | 107 |
| 標準偏差（母集団） | 8 | 108 |
| 合計 | 9 | 109 |
| 分散 | 10 | 110 |
| 分散（母集団） | 11 | 111 |
非表示行を無視する場合は、関数コードに100を足した数値（101～111）を使用します。例えば、非表示行を無視して合計を計算したい場合は「109」を使います。
基本的な使い方
以下に、SUBTOTAL 関数を使った基本的な例を見ていきましょう：
例1：データの合計を計算する
=SUBTOTAL(9, A2:A10)このように書くと、A2からA10までの範囲の合計を計算します。フィルターを適用していても、表示されているデータのみを計算します。
例2：非表示行を無視して平均を計算する
=SUBTOTAL(101, B2:B20)この場合、B2からB20までのデータのうち、表示されているセルの平均値を計算します。手動で非表示にした行は計算に含まれません。
SUBTOTAL 関数の実践的な使い方
二重計算を防ぐ
以下のような表があるとします。各部門のデータがあり、それぞれに小計が計算されています。
このような表で全体の総計を計算すると、SUMを使うと小計も含めて二重計算してしまいます。
=SUM(B2:B17) // 小計も含めて合計するため、値が重複して計算される代わりに SUBTOTAL を使うと：
=SUBTOTAL(9, B2:B17) // 他のSUBTOTAL関数を無視して正確に計算SUBTOTAL 関数は他の SUBTOTAL 関数の結果を無視するため、正しい総計を計算できます。
フィルター適用時の計算
データにフィルターを適用して一部のデータだけを表示している場合：
- SUM 関数はすべてのデータを計算します（フィルターで非表示になっているデータも含む）
- SUBTOTAL(9,…) 関数はフィルターで非表示になったデータを無視しますが、手動で非表示にしたデータは計算に含めます
- SUBTOTAL(109,…) 関数はフィルターで非表示になったデータと手動で非表示にしたデータの両方を無視します
動的な関数セレクター
SUBTOTAL 関数を使って、ドロップダウンリストから計算方法を選択できる動的なダッシュボードを作成することもできます。
例えば、セル B1 にドロップダウンリストで「合計」「平均」「最大値」などを選択できるようにしておき、以下のような数式を使用します：
=SUBTOTAL(
IF(B1="合計",9,IF(B1="平均",1,IF(B1="最大値",4,IF(B1="カウント",2,9)))),
A2:A20
)これにより、ドロップダウンリストの選択に応じて異なる計算結果が表示されます。
SUBTOTAL 関数の注意点
SUBTOTAL 関数を使う際の注意点をいくつか紹介します：
- フィルターと非表示行の違い：フィルターで非表示になったデータは常に計算から除外されますが、手動で非表示にした行は関数コードによって扱いが異なります。
- 列方向での利用：SUBTOTAL 関数は主に列データ（縦方向のデータ）を対象に設計されています。非表示にした列のデータは常に計算に含まれるため、行方向のデータに使用する場合は注意が必要です。
- 関数コードの覚え方：「9」は「SUM」の頭文字、「1」は「AVERAGE」の頭文字といったように、関連付けて覚えると便利です。
実践例：売上データの分析
実際のビジネスシーンでの利用例を見てみましょう。以下のような四半期ごとの売上データがあるとします：
A B
1 四半期 売上
2 Q1-1 1000
3 Q1-2 1200
4 Q1-3 1500
5 Q1小計 =SUBTOTAL(9,B2:B4)
6 Q2-1 1800
7 Q2-2 1600
8 Q2-3 2000
9 Q2小計 =SUBTOTAL(9,B6:B8)
10 Q3-1 2200
11 Q3-2 2300
12 Q3-3 1900
13 Q3小計 =SUBTOTAL(9,B10:B12)
14 Q4-1 2400
15 Q4-2 2500
16 Q4-3 2700
17 Q4小計 =SUBTOTAL(9,B14:B16)
18 総計 =SUBTOTAL(9,B2:B17)セル B18 では、SUBTOTAL(9,B2:B17) を使用することで、各四半期の小計を二重計算せず、正確な総計を算出しています。もし SUM 関数を使った場合、小計の値も含めて合計され、実際の売上よりも大きな金額が表示されてしまいます。
また、特定の四半期だけをフィルタリングして表示したいときも、総計のセルに SUBTOTAL 関数を使っていれば、表示されているデータだけの合計が自動的に計算されます。
SUBTOTAL 関数と他の関数の組み合わせ
SUBTOTAL と IF の組み合わせ
特定の条件に一致するデータのみを計算したい場合、SUBTOTAL と IF を組み合わせることができます：
=SUBTOTAL(9, IF(A2:A10>100, B2:B10, ""))この数式は、A列の値が100より大きい行のB列の値だけを合計します。
SUBTOTAL の動的な使用
ドロップダウンリストと組み合わせて、集計方法を動的に変更できます：
=SUBTOTAL(VLOOKUP(C1, 集計方法テーブル, 2, FALSE), A2:A10)C1 セルにドロップダウンリストがあり、その選択に応じて適切な関数コードを VLOOKUP で検索して SUBTOTAL に適用します。
まとめ
SUBTOTAL 関数は、以下のような特徴を持つ非常に便利な関数です：
- フィルターで非表示になったデータを自動的に除外する
- 二重計算を防止する
- 様々な集計方法（合計、平均、カウントなど）に対応している
- 非表示行の取り扱いを柔軟に設定できる
特に大きなデータセットやフィルターを多用するスプレッドシートでは、SUM や AVERAGE などの基本的な関数の代わりに SUBTOTAL 関数を使うことで、より正確で柔軟な計算が可能になります。
Dチームメルマガ登録｜eBOOK「逆引きAIツールブック」プレゼント中
