情報整理術の数々! PICK UP!

【シリーズ:一昔前のExcel(エクセル)で停滞している方へ】集計を行うAGGREGATE関数を使いこなす(その2)

集計を行う「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」から登場した新しい関数を使ってみたいと思います。

関連記事

  1. 【シリーズ:一昔前のExcel(エクセル)で停滞している方へ】条件分岐を楽にできるSWITCH関数と…

  2. Excelのスキルを図る企業の入社テストを実際にやってみた【7】

  3. 【Excel2016】インクで作成した手書きメモをワークシートで自由自在に操る

  4. エクセルに標準搭載された3Dマップを活用する![その1]―地図上へのデータ表示・表示方法の編集を操作…

  5. ExcelやWordファイルに保存される個人情報を削除するには

  6. Excelのスキルを図る企業の入社テストを実際にやってみた【4】

  7. 【シリーズ:一昔前のExcel(エクセル)で停滞している方へ】進化し続けるエクセルのIF関数を紐解く…

  8. 【シリーズ:一昔前のExcel(エクセル)で停滞している方へ】文字列を連結する新しい関数CONCAT…

  9. エクセルに標準搭載された3Dマップを活用する![その2]―ツアーを作成してみる

特集記事!

  1. 高機能は必要ない!初心者にも簡単な動画の編集を可能にしたEaseUS Video…
  2. Office365のエクセルにもとうとう登場したスピル!オートフィルや配列数式と…
  3. iPadOSでできるようになったテキスト操作とファイル操作いろいろ
  4. iPadOS13で強化された「Slide Over」と「Split View」!…
  5. 【連載3】Lucidchartで作成した図面をGoogleの文書やシートに挿入す…
  6. 【連載2】Lucidchartで図面作成からプレゼンテーションまですべて完結!プ…
  7. 【連載1】Lucidchartの図をワードやエクセルに挿入する―マイクロソフトイ…
  8. 【連載序章】よい図面作成アプリが見つからない現場担当者必見!Lucidchart…
  9. 簡単設定と分かりやすい料金プランで国内・国外”どんなときも”Wi-Fiが繋がるな…
  10. マイクロソフト発StickyNotesで覚えておきたい設定・使い方8選

人気記事 PickUp!

ー文字だけで語る(あつもじ)ー

おすすめ記事

ピックアップ!

  1. AndroidスマホでGoogleのキーボード「Gboard」をビジネスでも上手…
  2. [OneDrive]共同作業でOfficeファイルを編集してみる!
  3. 【slack】5.チャンネルの削除とアーカイブ
  4. GoogleクラウドプリントでAndroidスマホから直接プリンタへ印刷する方法…
  5. [slack]18.クラウドストレージのファイルをslackで共有できるようにす…
  6. [slack]19.ファイルを削除する
  7. 【Excel2016】インクで作成した手書きメモをワークシートで自由自在に操る
  8. 乱立するQR決済!既存サービスの比較やこれから参入してくる注目の企業など一覧を見…
  9. [slack]20.ポストを作成する
  10. 【シリーズ:人より一歩先行くエクセル技を身につける】ピボットテーブル編①

↓OneDriveの便利な機能が満載!↓

icon-cogマイクロソフトのクラウドストレージである「OneDrive」の便利な使い方をご紹介しています。個人契約でしか利用できない機能もあります。

PAGE TOP