バージョンアップされたエクセル関数の詳細をシリーズでお送りしています。
今回は、エクセル2010で追加された集計用関数の「AGGREGATE」関数について見ていきたいと思います。
Excel2010から追加された「AGGREGATE」関数
エクセル2007から2010へとバージョンアップされた際に、これまで集計関数のメインとして使われてきた「SUBTOTAL」関数とは別に、「AGGREGATE」関数が用意されました。
「SUBTOTAL」関数の引数指定
「AGGREGATE」関数の引数指定
まずは、「SUBTOTAL」関数の使い方から見ていきましょう。
「SUBTOTAL」関数を使う
人口統計データのリストがあるとします。
そのリスト内のデータから目的の集計方法で素早く集計値を取りたい時に、「SUBTOTAL」関数を使います。
よく使われる「足し算」や「全体の個数」などを簡単に切り替えながら表示できるので、個別に「SUM」関数や「COUNT」関数を書くよりも便利に使えるのです。
今回は、以前、3Dマップで使用した北海道の人口統計表を使います。
※ファイルのダウンロードは、このページの最後で用意しています
北海道の各市町村の人口が一覧表になっています。
表の右横には、数値を入力すると各集計値を表示するようにいくつかのセルに関数を埋め込んであります。
全体では何人いるのか(足し算)、市町村はいくつあるのか(個数)、最大の人口はどこか(最大)、最小の人口はどこか(最小)などを「SUBTOTAL」関数で表示できるようにし、集計内容によっては補足データも表示するようにしました。
それでは、順番に見ていきましょう。
入力させる数値はSUBTOTAL関数の引数
セル「F2」に入力してもらう数値は、「SUBTOTAL」関数の最初の引数になります。
「1から11」であれば、リストで非表示となっているデータも含めて集計されます。
また「101から111」であれば、リストで非表示となっているデータは含まれず集計されます。
上の画面では、セル「F2」に「5」と入力されています。
「SUBTOTAL」関数の最初の引数が「5」の場合、「最小(MIN)を計算する」という意味になります。
それらを踏まえて、セル「F3」に集計値を表示するわけですが、セル「F2」に上記の数値以外が入力された場合には、「数値が正しくありません」と表示するようにしてみます。
入力された数値の整合性をIF関数で判断する
入力された数値が正しいかの分岐を以下のように関数を使って処理しています。
IF関数で「1から11もしくは101から111までの場合」を表現すると以下のようになります。
OR(AND(F2>=1,F2<=11),AND(F2>=101,F2<=111))
OR関数とAND関数を使った条件式です。
その条件に合う場合は、「SUBTOTAL」関数で集計します。
SUBTOTAL(F2,C2:C180)
先程も少し触れましたが、セル「F2」に入力された集計方法でリストのデータ(「C2からC180」)までの数値を調べます。
条件に合わない場合は、「数値が正しくありません」と表示されます。
実際に動作を確認してみる
それでは、実際に数値を入力してみましょう。
セル「F2」に「1」と入力してみました。
「SUBTOTAL」関数の最初の引数が「1」の場合、「平均」を算出してくれます。
セル「F3」には「29,830」という平均値が表示されました。
今度は、セル「F2」に「101」と入力してみました。
同じく「平均」が算出されますが、リストで「非表示となっている行」があれば集計対象とはならず無視されます。
今回のリストは非表示となっている行がないので、セル「F3」には「1」と入力された場合と同じ平均値である「29,830」が表示されます。
さらに、セル「F2」に「15」と入力してみました。
「SUBTOTAL」関数の最初の引数指定に「15」は存在しません。
したがって、IF関数で分岐させたように、「1から11もしくは101から111」以外の数値が入力された場合は、「数値が正しくありません」と表示されました。
どの集計かを補足データとして表示させる
平均値を算出した際に、セル「E3」に、「平均」と表示された動作に気づいた方もいるでしょう。
このセル「E3」の表示は、「SUBTOTAL」関数で表示させたわけではありません。
セル「E2」に入力された数値は「SUBTOTAL」関数の最初の引数として”どの集計方法であるか”を表していますよね。
この数値が1であれば「平均」、2であれば「個数」となるわけですが、これを他の関数を使って表示するようにしているのです。
こういった補足表示があると便利なので、他の関数を使ってどのように表現しているのかを見てみましょう。
以下に挙げるよく使われる集計値の場合だけ、セル「E3」に表示させるようにしてみました。
- 平均
- 個数
- 最小
- 最大
- 加算
「OR」関数で、セル「F2」の数値を判断し、特定の数値の場合、「CHOOSE」関数で数値に対応する文字をセル「E3」に表示するようにしています。
「CHOOSE」関数の引数におびただしい数の「,」が入っていますが、セル「F2」が101や102の場合に、「CHOOSE」関数の101番目の引数、102番目の引数などに対応する文字を入れているのでこのようになっています。
上の図のように、「CHOOSE」関数を使う場合、「値」の引数指定が1から254まで可能となっています。
今回のように、「値9」に「”加算”」と設定した後に、「値101」まで引数の指定がありません。
したがって、その間は引数が空白になるので、「,」が入りまくってしまう・・となるのです。
実は、CHOOSE関数の後継でエクセル2016から追加された新しい関数を使うと、こうはならないのですが、その新しい関数は今後のシリーズ記事に登場予定となりますので、しばらくお待ち下さい。
※画像をクリックすると拡大します
5つの集計値だけに限定してみましたが、「SUBTOTAL」関数の最初の引数に「8」を指定すると求められる「標準偏差」もあった方がいいな、と思えばそれも簡単に追加できます。
説明は割愛しますが、是非試してみてください。
スポンサーリンク
スポンサーリンク
最大値と最小値の場合の市町村名を補足データとして表示させる
「SUBTOTAL」関数の最初の引数に「4」を指定すると、リストの最大値が求められ、
「5」を指定すると、リストの最小値が求められます。
ただし、今回使っている人口データの統計表で最大値・最小値を求める場合、最大値の市町村名、最小値の市町村名も合わせて表示されると便利だと思いませんか？
ということで、セル「G3」に、最大値・最小値を求めた場合のみ、その値に該当する市町村名を表示するようにしてみました。
ちなみに、この表示も「SUBTOTAL」関数ではなく、別の関数を使っています。
セル「F2」に入力された数値が4(最大)か5(最小)の場合、
「INDEX」関数と「MATCH」関数を使って、人口が最大・最小の場合に該当する市町村名を検索しています。
「SUBTOTAL」関数でセル「F3」に最小値が表示されます。
それを「MATCH」関数で、該当の行を検索し、「INDEX」関数で行列に該当する「市町村名」を取得しています。
「MATCH」関数と「INDEX」関数の説明も割愛しますが、この2つの関数を組み合わせると、リスト内の検索したいデータが持っている他のデータも合わせて検索できます。
今回の例で言えば、検索したいデータ(人口)が持っている他のデータ(市町村名)も一緒に検索できる、となるのです。
次回は「AGGREGATE」まとめ
いやぁ、メインの「AGGREGATE」関数まで到達しませんでしたね。
それだけ、「SUBTOTAL」関数は集計用関数としても、他の関数と組み合わせても色々と使える関数だったのです。
次回は、「SUBTOTAL」関数にはなかった進化した集計用関数「AGGREGATE」の特徴を見ていきたいと思います。
なお、今回説明に使ってきたファイルは以下からダウンロードできますので、参考に色々試してみてください。