Excelでは大量のデータを扱う際に、特定の条件に基づいてデータを集計したい場面がよくあります。SUBTOTAL関数は、データの集計や計算を効率よく行うための便利な関数で、特にフィルタ機能と組み合わせて使うと真価を発揮します。この記事では、SUBTOTAL関数の基本的な使い方から、フィルタを使った応用的なテクニックまでを解説します。
SUBTOTAL関数とは？
SUBTOTAL関数は、指定したデータ範囲に対してさまざまな集計（合計、平均、カウントなど）を行う関数です。SUBTOTAL関数の最大の特徴は、フィルタで隠されたデータや、手動で非表示にされたデータを無視して集計できる点です。
SUBTOTAL関数の基本的な構文
=SUBTOTAL(集計方法, 範囲1, [範囲2, …])
- 集計方法: 実行したい計算の種類を指定します。例として、「合計」や「平均」、「個数」などが選べます（後述）。
- 範囲: 集計を行うデータ範囲を指定します。複数の範囲を指定することも可能です。
集計方法のコード一覧
SUBTOTAL関数を使う際、最初に指定する数値（集計方法）によってどの集計方法を使うかが決まります。以下に、主な集計方法を紹介します。
| コード | 集計方法 | フィルタ後のデータ | 手動非表示データ |
| 1 | 平均 | 無視 | 集計する |
| 2 | 個数 | 無視 | 集計する |
| 3 | 個数（数値） | 無視 | 集計する |
| 4 | 最大値 | 無視 | 集計する |
| 5 | 最小値 | 無視 | 集計する |
| 6 | 合計 | 無視 | 集計する |
| 7 | 分散（標本） | 無視 | 集計する |
| 8 | 標準偏差 | 無視 | 集計する |
| 9 | 合計 | 無視 | 無視 |
| 10 | 平均 | 無視 | 無視 |
| 11 | 個数 | 無視 | 無視 |
| 12 | 個数（数値） | 無視 | 無視 |
| 13 | 最大値 | 無視 | 無視 |
| 14 | 最小値 | 無視 | 無視 |
| 15 | 分散（標本） | 無視 | 無視 |
| 16 | 標準偏差 | 無視 | 無視 |
例えば、「合計」を計算する場合、集計方法に「9」を指定すると、フィルタで非表示にされたデータや手動で非表示にされたデータを無視して合計が算出されます。手動で非表示のデータを無視せずに計算したい場合は「6」を使います。
実際の例
例えば、次のような売上データがあるとします。
| A | B |
| 商品名 | 売上金額 |
| 商品A | 100 |
| 商品B | 200 |
| 商品C | 300 |
| 商品D | 150 |
このデータで、フィルタを使って一部のデータが隠されている場合、フィルタ後の売上金額の合計を計算したい場合は、次のようにSUBTOTAL関数を使います。
=SUBTOTAL(9, B2:B5)
この場合、フィルタで非表示にされた行の売上金額は無視され、表示されている行の合計だけが計算されます。
例えば、商品Aと商品Bのみをフィルタで表示している場合、結果として「300」（100 + 200）が返されます。
SUBTOTAL関数の応用例
フィルタ後の平均値を求める
フィルタを使って特定の商品や条件を満たすデータだけを表示し、そのデータの平均を計算したい場合、SUBTOTAL関数を使って簡単に計算できます。
=SUBTOTAL(101, B2:B5)
ここでは、集計方法に「101」（フィルタ後のデータを無視して平均を計算）を指定しています。フィルタで表示されているデータのみの平均値が返されます。
手動で非表示にしたデータを含めて合計を計算する
SUBTOTAL関数を使えば、フィルタで隠されたデータは無視しつつ、手動で非表示にしたデータも含めた集計を行うことができます。手動で非表示にしたデータも含めて売上の合計を計算したい場合、次のように入力します。
=SUBTOTAL(6, B2:B5)
これにより、フィルタされたデータは無視されますが、手動で非表示にしたデータは合計に含まれます。
データが増えても自動的に集計する
SUBTOTAL関数を使うと、データが追加されても自動的にその範囲を集計してくれます。例えば、B2からB5の範囲に売上データを追加した場合、SUBTOTAL関数を使っているセルは新しいデータを含めた合計や平均を自動的に更新します。
| A | B |
| 商品名 | 売上金額 |
| 商品A | 100 |
| 商品B | 200 |
| 商品C | 300 |
| 商品D | 150 |
| 商品E | 250 |
この表でSUBTOTAL関数を使っている場合、追加された商品Eの売上金額も合計や平均に含まれるようになります。
SUBTOTAL関数の便利な豆知識
フィルタと一緒に使うと真価を発揮
SUBTOTAL関数の最大の利点は、フィルタで非表示にされたデータを無視して計算できることです。通常のSUM関数やAVERAGE関数では、フィルタで隠されたデータも含めて集計してしまいますが、SUBTOTAL関数を使えば、フィルタで表示されたデータのみを対象に正確な集計ができます。
データの種類に応じて集計方法を変える
SUBTOTAL関数は、集計方法として「合計」だけでなく「平均」や「個数」「最大値」「最小値」など、さまざまな方法を選択できます。データの内容に応じて適切な集計方法を使い分けることで、効率的にデータを分析できます。
ネストしたSUBTOTAL関数を使用しない
SUBTOTAL関数を入れ子（ネスト）にすることはできません。例えば、SUBTOTAL関数の中にさらにSUBTOTAL関数を含めることはサポートされていません。同じ範囲や別の範囲に対して複数の集計が必要な場合、それぞれ個別にSUBTOTAL関数を使うようにしましょう。
SUBTOTAL関数のよくあるエラーと対処法
#VALUE!エラー
SUBTOTAL関数で#VALUE!エラーが発生することがあります。これは、集計方法や範囲の指定が間違っている場合に起こります。特に、集計方法に指定する数値（1～11または101～111）が正しいかどうかを確認してください。
正しく集計されない
SUBTOTAL関数で正しい結果が表示されない場合、フィルタや手動で非表示にしたデータの影響を受けている可能性があります。集計方法のコードが「手動で非表示にしたデータを含める」設定になっているか、「無視する」設定になっているかを確認し、目的に合ったコードを使用してください。
まとめ
SUBTOTAL関数は、Excelでのデータ集計やフィルタ機能を効果的に使うために欠かせないツールです。通常のSUMやAVERAGE関数では対応できない、フィルタされたデータの集計や手動で非表示にしたデータの管理を簡単に行うことができます。特に、大量のデータを扱う場合や、フィルタを頻繁に使う場面ではSUBTOTAL関数が非常に役立ちます。
これらのテクニックを活用して、Excelでのデータ集計作業をより効率的に進めましょう！
