Excelで表を作成し、売上や費用の集計を行う際、「小計」と「合計」を使い分けることは重要です。しかし、SUM関数だけでは適切な集計ができないケースもあります。
そこで活用できるのがSUBTOTAL関数です。この関数を使えば、フィルター機能を適用しても正しい値を算出でき、データ分析の効率が格段に向上します。
この記事では、SUBTOTAL関数の使い方を詳しく解説し、一般的なSUM関数との違いも紹介します。
SUBTOTAL関数とは
SUBTOTAL関数は、指定した範囲のデータに対して合計・平均・個数カウントなどを動的に計算できる関数です。特に、Excelのフィルター機能と組み合わせることで、フィルター適用後のデータのみを集計することが可能です。
基本的な書式は以下の通りです。
=SUBTOTAL(集計方法, 範囲)
例えば、小計を求める場合の例として、**SUM(合計)**を利用する場合は以下のようになります。
=SUBTOTAL(9, A2:A10)
ここで「9」は「SUM（合計）」を指定するためのパラメータです。
SUBTOTAL関数の引数一覧
SUBTOTAL関数の第一引数には、さまざまな計算方法を指定することができます。主なものを以下に示します。
| パラメータ | 計算方法 | フィルタ適用時の動作 |
|---|---|---|
| 1 | AVERAGE（平均） | フィルタ適用後のデータのみ計算 |
| 2 | COUNT（数値の個数） | フィルタ適用後のデータのみ計算 |
| 3 | COUNTA（空白以外の個数） | フィルタ適用後のデータのみ計算 |
| 9 | SUM（合計） | フィルタ適用後のデータのみ計算 |
| 101 | AVERAGE（平均） | 非表示のデータを含めない |
| 109 | SUM（合計） | 非表示のデータを含めない |
「1～11」は通常の集計を行い、「101～111」を指定すると、手動で非表示にしたデータを集計対象外にすることができます。
SUBTOTAL関数とSUM関数の違い
SUM関数とSUBTOTAL関数の違いを比較してみましょう。
| 関数 | フィルター適用時 | 手動で非表示の行 |
|---|---|---|
| SUM(A2:A10) | すべての行を合計 | すべての行を合計 |
| SUBTOTAL(9, A2:A10) | フィルターで非表示の行を除外 | 含まれる |
| SUBTOTAL(109, A2:A10) | フィルターで非表示の行を除外 | 手動で非表示の行も除外 |
例えば、売上データを集計する場合、フィルターを適用しても正しい合計を求めたいなら、SUBTOTAL(9, 範囲) を使うと便利です。
実践例
小計を求めるケース
店舗ごとの売上データがある場合、各店舗の売上小計を求めるために、SUBTOTAL関数を利用できます。
=SUBTOTAL(9, B2:B10)
こうすることで、フィルター適用時に表示されたデータのみを合計できます。
複数の項目ごとに合計を求める
商品別に売上を集計する場合、商品ごとにフィルターを適用し、動的に計算するのに便利です。
=SUBTOTAL(9, D2:D50)
この数式を使用すると、特定の商品をフィルタリングした際に、正確な合計金額が求められます。
注意点
- 通常のSUM関数ではフィルター適用時に誤った合計を表示する
- SUM関数は、フィルターで非表示にした行も計算してしまうため、特定の条件下で正しく集計されません。
- 手動で非表示にした行を除外するには「109」を使用する
SUBTOTAL(9, A2:A10)では手動で非表示にしたデータも計算に含まれるため、SUBTOTAL(109, A2:A10)を使用すると正しく除外できます。
- 表の構造が適切でないと正しく計算できない
- 空白のあるデータ範囲を指定すると、SUBTOTAL関数の結果が意図しない値になることがあります。
- 計算対象の範囲を適切に選ぶことが重要です。
結論
SUBTOTAL関数を活用すると、フィルターを適用した際にも正確な集計が可能になります。SUM関数と異なり、表示データのみを対象とするため、フィルター機能との相性が抜群です。
特に、売上データや経費計算など、項目ごとの小計や合計を正確に管理する際には必須の関数です。
適切なパラメータを活用し、データ分析をより効率的に行いましょう！
コメント