エクセルやGoogleスプレッドシートでSUBTOTAL関数を使えば、非表示の行を合計しない集計ができます。今回は使用頻度の高い集計方法(SUM)を例に挙げて、表示されているセルだけを集計する方法を解説するので、ぜひ参考にしてみてください。
SUBTOTAL関数の使い方
SUBTOTAL関数の基本的な構成や、使い方について解説します。集計方法に使えるコード一覧もご紹介しているので、色々な集計方法で試してみましょう。
SUBTOTAL関数で非表示の行を除いた集計する方法
SUBTOTAL関数は1つまたは複数の「範囲」を、指定した「集計コード」に従って集計します。
ほかの集計関数との違いは、非表示行を除外した集計ができることです。
例ではSUBTOTAL関数の集計コードに「109」を指定しています。集計コード「109」は、非表示の行を含まない合計を返す集計方法です。
SUBTOTAL関数の構成要素：(集計コード,範囲 1,[範囲 2],…)
4行目を非表示にすると、SUBTOTAL関数は4行目を含まない合計値「45」を返します。
一方SUM関数は非表示行を含めて合計するので、同じ範囲の合計値は「50」です。
フィルター機能で除外された行は、SUBTOTAL関数の集計コードに関わらず結果に含まれません。これはSUM関数を使用した場合も同様です。
SUBTOTAL関数で小計を除いた集計をする方法
SUBTOTAL関数には、二重集計を回避できるという特徴もあります。
指定した範囲にSUBTOTAL関数が含まれる場合、これらのセルを無視して集計することが可能です。一方SUM関数は、範囲内の値はすべて合計します。
範囲に含まれる関数がSUBTOTAL関数以外(SUM関数等)の場合、集計の対象になります。
SUBTOTAL関数の集計コード一覧
集計方法に関するコードについては以下の通りです。1~11が非表示の行を含む集計コード、101~111が非表示の行を含まない集計コードになります。
| 非表示の行を含む | 非表示の行を含まない | 関数名と集計方法 |
|---|---|---|
| 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(分散) |
SUBTOTAL関数の上位互換である、AGGREGATE関数についても解説しています。AGGREGATE関数の主な特徴は以下の通りです。
- 非表示の行を除いて集計するか選択可能
- エラー値を除いて集計するか選択可能
- 二重集計を回避するかは選択可能
