2019.08.11【シリーズ:一昔前のExcel(エクセル)で停滞している方へ】集計を行うAGGREGATE関数を使いこなす(その1)バージョンアップされたエクセル関数の詳細をシリーズでお送りしています。今回は、エクセル2010で追加された集計用関数の「AGGREGATE」関数について見ていきたいと思います。次回は「AGGREGATE」関数へExcek2010から追加された「AGGREGATE」関数エクセル2007から2010へとバージョンアップされた際に、これまで集計関数のメインとして使われてきた「SUBTOTAL」関数とは別に、「AGGREGATE」関数が用意されました。「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であれば「個数」となるわけですが、これを他の関数を使って表示するようにしているのです。いった補足表示があると便利なので、他の関数を使ってどのように表現しているのかを見てみましょう。OR」関数で、セル「F2」の数値を判断し、特定の数値の場合、「CHOOSE」関数で数値に対応する文字をセル「E3」に表示するようにしています。「CHOOSE」関数の引数におびただしい数の「,」が入っていますが、セル「F2」が101や102の場合に、「CHOOSE」関数の101番目の引数、102番目の引数などに対応する文字を入れているのでこのようになっています。上の図のように、「CHOOSE」関数を使う場合、「値」の引数指定が1から254まで可能となっています。今回のように、「値9」に「”加算”」と設定した後に、「値101」まで引数の指定がありません。したがって、その間は引数が空白になるので、「,」が入りまくってしまう・・となるのです。実は、CHOOSE関数の後継でエクセル2016から追加された新しい関数を使うと、こうはならないのですが、その新しい関数は今後のシリーズ記事に登場予定となりますので、しばらくお待ち下さい。「SUBTOTAL」関数の最初の引数に「8」を指定すると求められる「標準偏差」もあった方がいいな、と思えばそれも簡単に追加できます。説明は割愛しますが、是非試してみてください。「5」を指定すると、リストの最小値が求められます。最大値の市町村名、最小値の市町村名も合わせて表示されると便利だと思いませんか?ということで、セル「G3」に、最大値・最小値を求めた場合のみ、その値に該当する市町村名を表示するようにしてみました。ちなみに、この表示も「SUBTOTAL」関数ではなく、別の関数を使っています。「INDEX」関数と「MATCH」関数を使って、人口が最大・最小の場合に該当する市町村名を検索しています。「SUBTOTAL」関数でセル「F3」に最小値が表示されます。それを「MATCH」関数で、該当の行を検索し、「INDEX」関数で行列に該当する「市町村名」を取得しています。「MATCH」関数と「INDEX」関数の説明も割愛しますが、この2つの関数を組み合わせると、リスト内の検索したいデータが持っている他のデータも合わせて検索できます。今回の例で言えば、検索したいデータ(人口)が持っている他のデータ(市町村名)も一緒に検索できる、となるのです。次回は「AGGREGATE」関数へいやぁ、メインの「AGGREGATE」関数まで到達しませんでしたね。それだけ、「SUBTOTAL」関数は集計用関数としても、他の関数と組み合わせても色々と使える関数だったのです。次回は、「SUBTOTAL」関数にはなかった進化した集計用関数「AGGREGATE」の特徴を見ていきたいと思います。今回説明に使ってきたファイルは以下からダウンロードできますので、参考に色々試してみてください。【シリーズ:一昔前のExcel(エクセル)で停滞し...シリーズでお送りしている”一昔前エクセルシリーズ”でございます。...前回、OneNoteのプリンタドライバ機能を使って他アプリケーションからOneN...【シリーズ:一昔前のExcel(エクセル)で停滞し...シリーズでお送りしている”一昔前エクセルシリーズ”でございます。Windows10】表デ...OneNoteで表データを扱うときには、少し注意が必要です。2019...formrunのフォームクリエイターでメールマガジ...ホームページを閲覧していると、会員登録や物品販売などユーザーに対して何らかのアク...ピックアップ!AndroidスマホでGoogleのキーボード「Gboard」をビジネスでも上手...[OneDrive]共同作業でOfficeファイルを編集してみる!【slack】5.チャンネルの削除とアーカイブGoogleクラウドプリントでAndroidスマホから直接プリンタへ印刷する方法...[slack]18.クラウドストレージのファイルをslackで共有できるようにす...[slack]19.ファイルを削除する【Excel2016】インクで作成した手書きメモをワークシートで自由自在に操る乱立するQR決済!既存サービスの比較やこれから参入してくる注目の企業など一覧を見...[slack]20.ポストを作成する【シリーズ:人より一歩先行くエクセル技を身につける】ピボットテーブル編1↓OneDriveの便利な機能が満載!↓icon-cogマイクロソフトのクラウドストレージである「OneDrive」の便利な使い方をご紹介しています。個人契約でしか利用できない機能もあります。