Excel SUBTOTAL、AGGREGATE関数(2)

先回の「Excel SUBTOTAL、AGGREGATE関数(1)」記事では、データ集計によく使われる「SUBTOTAL」関数についてご紹介しました。
今回は、同じく集計に使われる「AGGREGATE」関数についてご紹介します。
「AGGREGATE」関数は、Excel 2010で追加された関数です。

一言で表わすと、「SUBTOTAL」関数のアップグレード版が「AGGREGATE」関数です。

「AGGREGATE」関数の書式は、次のようになります：
　AGGREGATE(集計方法, オプション, 範囲 1, [範囲 2], …)
　AGGREGATE(集計方法, オプション, 配列, [k])
です。

第1引数「集計方法」は、使用する関数を 1～19 の番号で指定します。
第2引数「オプション」は、集計するセル範囲内で非表示の行やエラー値を無視する対象を 1～7 の番号で指定します。
そして、最初の書式は、第3引数はセル範囲形式である場合で、2番めの書式は配列形式であるときのものです。

「集計方法」の番号と対応関数の関係は次のようになります：

集計方法	関数	機能
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	大きい方からの順位
15	SMALL	小さい方からの順位
16	PERCENTILE.INC	百分位数
17	QUARTILE.INC	四分位数
18	PERCENTILE.EXC	百分位数（0%と100%を除く）
19	QUARTILE.EXC	四分位数（0%と100%を除く）

「オプション」の番号と対応動作の関係は次のようになります：

オプション	動作
0 または省略	ネストされた SUBTOTAL関数と AGGREGATE関数を無視
1	非表示の行、ネストされた SUBTOTAL関数と AGGREGATE関数を無視
2	エラー値、ネストされた SUBTOTAL関数と AGGREGATE関数を無視
3	非表示の行、エラー値、ネストされた SUBTOTAL関数と AGGREGATE関数を無視
4	何も無視しない
5	非表示の行を無視
6	エラー値を無視
7	非表示の行とエラー値を無視

第3引数に「配列」形式のデータを指定できることは「SUBTOTAL」関数にないユニークな機能です。
配列形式で直接入力してもよいし、上記「集計方法」の 14～19 に対応する関数を使って関数の戻り値を使うことも出来ます。

「配列」形式の場合の第4引数は、「集計方法」で 14～19 を指定したとき、これで指定する「順位」は省略できません。
例えば、降順で大きい方から何番目のデータを取り出す、のような使い方ができます。

以上のように、「AGGREGATE」関数は「SUBTOTAL」関数に比べ対応する関数が 19に増え、また、非表示の行だけでなくエラー値となるセルを無視する動作が加わり、より柔軟な指定ができるようになりました。

簡単な例ですが、下図の表データで、B列の数値の平均を求めてみます。

「=AGGREGATE(1,6,B1:B10)」と入力すると「65」と求められます。
第1引数「1」で「AVERAGE（平均）」を指定、第2引数「6」でエラー値を無視します。

平均を求めるのだから単に「=AVERAGE(B1:B10)」でも良さそうですが、セル範囲にエラー値（上図では「＃VALUE!」）を含んでいるためエラーとなってしまいます。

同じ表データについて「=AGGREGATE(14,6,B1:B10,3)」とすると、セル範囲 B1～B10の中でエラーとを無視して 3番目に大きい値「79」が求まります。
これも「=LARGE(B1:B10,3)」とすると、セル範囲にエラー値を含んでいるためエラーとなってしまいます。

このように、「AGGREGATE」関数は「SUBTOTAL」関数の上位版として位置づけられ、Excel 2010以降で使える、つまり現有の Excelの殆どすべてで動作するので、2つを覚えたり、使い分けたりするより、「AGGREGATE」関数だけを使っていくのがよろしいと感じます。