はじめに：
Excelユーザーの皆さん、データ分析や集計作業で頭を悩ませたことはありませんか？特に、フィルター機能を使用しながら正確な集計を行いたい場合や、多層構造のデータを効果的に処理したい場合など、一般的な関数では対応しきれないシーンに遭遇することがあります。ここでSUBTOTAL関数を使います。
この記事では、多くのExcelユーザーが見落としがちなSUBTOTAL関数の魅力と、実用的な利用方法を詳細に解説します。SUBTOTAL関数を使いこなすことで、あなたのExcel作業が劇的に効率化されること間違いなしです。
SUBTOTAL関数
構成
SUBTOTAL関数は、パッと見た感じ複雑そうに思えるかもしれません。しかし、その基本構造は意外にシンプルです。関数の構文は以下の通りです：
=SUBTOTAL(集計方法, 参照1, [参照2], ...)- 集計方法：実行したい集計の種類を指定します（例：合計、平均、カウントなど）
- 参照1, 参照2, …：集計用のセル範囲を指定します
集計方法は1から11まであり、それぞれが特定の集計方法に対応しています。例えば、9は合計（SUM）、1は平均（AVERAGE）を表します。さらに、これらの番号に100を加えた101から111の番号も存在し、非表示セルの扱いが異なります。
使用例
SUBTOTAL関数の真価は、実際の使用例を見ることでより明確になります。以下に、よく見られるシナリオを紹介します：
フィルター適用時の動的集計
以下のような売上データがあるとします：
このデータに対して、SUBTOTAL関数を使用して合計を計算します：
=SUBTOTAL(9, C2:C6)結果：22,000
ここで、商品Aのみにフィルターをかけた場合：
SUBTOTAL関数の結果：9500
この例では、データの参照範囲がC2:C6ですが、フィルターで非表示になったセルを除外して合計を計算しています。
階層データの集計
部門別、チーム別の売上を集計する例を見てみましょう：
SUBTOTAL関数を使用して各レベルの集計を行います：
=SUBTOTAL(9, C2:C3) // チームAの小計
=SUBTOTAL(9, C2:C5) // 営業部門の小計
=SUBTOTAL(9, C2:C9) // 全体の総計結果：
- チームAの小計：25,000
- 営業部門の小計：55,000
- 全体の総計：150,000
応用例
以下に、高度な使用例をいくつか紹介します：
条件付き集計
次のようなデータが与えられているとします：
利益率が20%を超える商品の売上合計を計算したい場合：
- フィルターを適用します。(下図)
- データ範囲を選択（A1）
- 「データ」タブに移動し、「フィルター」をクリックします。（短縮キー：Shift + Ctrl + L)
- フィルターを設定します。
- 利益率の列（C列）のフィルターをクリックします。
- 数値フィルターを選択し、「0.2より大きい」を選びます。
- SUBTOTAL関数を使用します。
- フィルターを適用した状態で、別のセルに以下のようなSUBTOTAL関数を入力します：
=SUBTOTAL(9, B2:B6)この操作により、利益率が20％を超える商品の売上金額の合計を計算できます。SUBTOTAL関数は、フィルタリングされたデータに対してのみ計算を行います。
結果：14000（商品C、Eの売上合計）
<以下、参考>
フィルターを解除すると合計26,000なります。即ち、SUBTOTAL関数は見えている部分を計算します。
複数のデータ集計を同時に行う
同じデータを使用して、売上の合計と平均を同時に計算します：
=SUBTOTAL(9, B2:B6) & " (平均: " & SUBTOTAL(1, B2:B6) & ")"結果：26000 (平均: 5200)
<以下、参考>
- 集計方法１：平均（AVERAAGE）
- 集計方法９：合計（SUM）
まとめ
SUBTOTAL関数は、一見すると単なる集計関数に過ぎないように見えるかもしれません。しかし、その真の威力は、複雑なデータ構造や動的な集計要件に直面したときに発揮されます。フィルター機能との親和性、非表示セルの柔軟な扱い、そして他の関数との組み合わせの容易さなど、SUBTOTAL関数はたくさんの優れた特性を有しています。
本記事で紹介した使用例や応用例は、SUBTOTAL関数の可能性のほんの一部に過ぎません。実際の業務データに適用してみることで、さらに多くの活用法を見出すことができるでしょう。
Excelでのデータ分析や報告書作成において、SUBTOTAL関数を積極的に活用することをお勧めします。複雑な集計作業を簡略化し、動的なレポート作成を可能にします。
