集計を行う「AGGREGATE」関数のお話その2です。
前回は、メインまで到達せず一昔前の集計用関数であった「SUBTOTAL」関数のお話だけで終わってしまいました。

今回は本題の「AGGREGATE」関数を見ていきたいと思います。

「AGGREGATE」関数とは

エクセル2010から追加された新しい集計用関数が「AGGREGATE」関数となります。
いわゆる「SUBTOTAL」関数の後継という位置づけとなります。

「SUBTOTAL」関数と比較した場合の大きな違いを2つ挙げておきましょう。

• 計算範囲内に「エラー値」があってもそのエラー対応を引数で指定できる
• 引数に指定できる集計方法が増え、細かい集計が可能となる

「AGGREGATE」関数のエラー対応

「SUBTOTAL」関数では、集計したいリスト内にエラーが発生しているセルがあった場合、計算結果もエラーが発生してしまう仕様でした。
つまり、エラー値の処理を自分で引数の指定により対処するしかなかったのです。

「AGGREGATE」関数の場合、エラー値がある場合にどう対処するかを引数の指定で決められるのです。

2番目の引数(オプション)に「6」という数字が入っています。
この引数の指定は、リスト内にエラー値があってもすべて無視する、つまり
”計算の対象にはしない”
という意味になります。

省略した場合も含めて全部で8つの引数があります。
「SUBTOTAL」関数では、非表示の行の取り扱いは、集計方法を指定する引数で一緒に決めていましたね。
集計方法に「1から11」を指定すると非表示の行を含め、
集計方法に「101から111」を指定すると非表示の行を無視する、という仕様でした。

「AGGREGATE」関数は、集計方法とは引数が切り離され、
「非表示の行の取り扱い」、「エラー値の取り扱い」、「ネストされた関数の取り扱い」を2番目の引数で指定できるようになったのです。

エラー値の取り扱いで言えば、「SUBTOTAL」関数のようにリスト内でエラー値を探さなくても、
上のように2番目の引数に「6」を指定すればリスト内にエラーがいくつあろうとそれらを除外して計算してくれるし、
仮にリスト内のエラーを発見したいのであれば、2番目の引数に「4(何も無視しない)」を選択すればいいわけです。

上のように関数を指定してみました。

=AGGREGATE(3,6,C2:C180)

集計方法には、リストの個数(空白を除く)をカウントする「3(COUNTA)」を最初の引数に指定しています。
2番目の引数に「6」を指定して、エラー値を無視しています。
リスト内(C2:C180)には、C6とC12にエラーが表示されていますが、エラーの出ているデータは無視されますのでこの2つのセルを除いた個数がカウントされます。

全部で179からエラーのある2つのデータを除いた177が結果として返されています。

「AGGREGATE」関数の集計方法

「SUBTOTAL」関数では11の集計方法がありましたが、
「AGGREGATE」関数では19の集計方法に増えました。

中でも、数値の大小で言えば最大値(MAX)と最小値(MIN)しか集計できなかった「SUBTOTAL」関数とは違い、
「AGGREGATE」関数では、メジアン(中央値)、モード(最頻値)、ラージ(最大値からX番目)、スモール(最小値からX番目)とリスト内の数値を細かく分析できるようになりました。

例えば、4番目に人口の多いデータを調べたいとしましょう。

「AGGREGATE」関数には、引数の指定方法が2つ表示されます。
リストの範囲(C2:C180)から4番目に大きいデータを調べたい時には、どちらを指定しても問題ありません。
どちらでも同じ結果が返されます。

集計方法は「14(LARGE)」になります。
リストの範囲を指定した引数(C2:C180)の次の引数に「4番目」である「4」を指定します。

4番目に多いのは、「釧路市」の172,391人だと分かりました。

集計方法に「14(LARGE)」を指定した場合、何番目のデータを求めたいのかを引数に指定しなければ、上のように結果としてエラーが返されます。
この場合、4番目の引数に求めたい順位の数字を指定する必要があったわけですが、それが指定されていないためにエラーとなっているわけですね。

リスト範囲や配列を指定する3番目の引数の後に、引数の指定が必要な集計方法は以下となります。

総括

「AGGREGATE」関数は、「SUBTOTAL」関数と比較しても引数の指定方法や使い方にそれほどの違いはありません。

ただ、細かい集計ができるようになった、つまり豊富な統計データを取得できるようになった関数の仕様は大きな違いとなるでしょう。

さらに、「SUBTOTAL」関数ではどうしてもできなかったリスト内のエラー値に対する考え方を簡単に決められるようになったのも関数が進化したと言えるのではないでしょうか。

さて、エクセル2007やエクセル2010から追加された主な関数を順番に見てきましたが、
次回は「エクセル2013」から登場した新しい関数を使ってみたいと思います。