AGGREGATE関数の使い方とSUBTOTAL関数との違い

エクセルで非表示の行を合計しないときに使える、AGGREGATE関数についてご紹介します。エラー値の無視するなどその他のオプションや、SUBTOTAL関数との違いについても解説しているので、ぜひ参考にしてみてください。

AGGREGATE関数の使い方

AGGREGATE関数の使い方について、SUM関数と比較しながら解説します。集計コードやオプションは代表的なものを例にしてご紹介しているので、一覧もチェックしてみてください。

AGGREGATE関数はSUBTOTAL関数の上位互換です。1つまたは複数の「範囲」を、指定した「集計コード」と「オプション」に従って集計します。

AGGREGATE関数の構成要素：(集計コード, オプション, 範囲 1, [範囲 2], …)

集計コード９：SUM(合計)
オプション５：非表示の行を無視します。

AGGREGATE関数のオプションに「5」を指定すると、非表示の行を除いた集計が可能です。

SUM関数は非表示にしているセルの値も、集計結果に含みます。

SUBTOTAL関数に関しては、非表示の値を含めるかどうか集計コードで選択可能です。

集計コード９：SUM(合計)
オプション６：エラー値を無視します。

AGGREGATE関数のオプションに「6」を指定すると、エラー値を含む範囲の集計が可能です。

SUM関数とSUBTOTAL関数は、範囲内にエラー値が存在すると集計結果もエラーになります。

例では3行目に、それぞれの関数で一部の範囲を合計した「小計」を作成しています。

集計コード９：SUM(合計)
オプション０：ネスト(入れ子)されたSUBTOTAL関数とAGGREGATE関数を無視します。

AGGREGATE関数のオプションに「0」を指定すると、ネストされたSUBTOTAL関数とAGGREGATE関数を除いた集計が可能です。

SUM関数はネストされた集計関数を含め、範囲内のすべての値を集計します。

SUBTOTAL関数は、ネストされたSUBTOTAL関数とAGGREGATE関数を除いて集計します。

AGGREGATE関数の集計コードとオプションを一覧にまとめました。使用頻度の高いSUM(合計)の集計コードは覚えておくと便利です。

SUBTOTAL関数には無かった「LARGE」や「SMALL」などが追加され、合計19個の集計コードが使えます。最も重要度が高いのは、コード9の「SUM」です。

集計コード	関数名と内容
1	AVERAGE(平均)
2	COUNT(数値を含むセルの個数)
3	COUNTA(空白でないセルの個数)
4	MAX(最大値)
5	MIN(最小値)
6	PRODUCT(積)
7	STDEV.S(標準偏差の推定値)
8	STDEV.P(母集団の標準偏差)
9	SUM(合計)
10	VAR.S(不偏分散)
11	VAR.P(分散)
12	MEDIAN(中央値)
13	MODE.SNGL(最頻値)
14	LARGE(n番目に大きい値)
15	SMALL(n番目に小さい値)
16	PERCENTILE.INC(百分位数)
17	QUARTILE.INC(四分位数)
18	PERCENTILE.EXC(0%と100%を除いた範囲の百分位数)
19	QUARTILE.EXC(0%と100%を除いた範囲の四分位数)

AGGREGATE関数では、範囲内の無視する値を選べるのが特徴です。複数の動作を組み合わせたオプションも用意されています。

オプション	内容
0	入れ子になっているSUBTOTAL関数とAGGREGATE関数を無視します。
1	非表示の行、入れ子になっているSUBTOTAL関数とAGGREGATE関数を無視します。
2	エラー値、入れ子になっているSUBTOTAL関数とAGGREGATE関数を無視します。
3	非表示の行、エラー値、入れ子になっているSUBTOTAL関数とAGGREGATE関数を無視します。
4	すべてを検索対象とします。
5	非表示の行を無視します。
6	エラー値を無視します。
7	非表示の行とエラー値を無視します。