情報整理術の数々! 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. 【シリーズ:人より一歩先行くエクセル技を身につける】ピボットテーブル編①

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

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

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

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

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

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

  8. 【シリーズ:人より一歩先行くエクセル技を身につける】ピボットテーブル編④

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

特集記事!

  1. iPadOS13で強化された「Slide Over」と「Split View」!…
  2. 【連載3】Lucidchartで作成した図面をGoogleの文書やシートに挿入す…
  3. 【連載2】Lucidchartで図面作成からプレゼンテーションまですべて完結!プ…
  4. 【連載1】Lucidchartの図をワードやエクセルに挿入する―マイクロソフトイ…
  5. 【連載序章】よい図面作成アプリが見つからない現場担当者必見!Lucidchart…
  6. 簡単設定と分かりやすい料金プランで国内・国外”どんなときも”Wi-Fiが繋がるな…
  7. マイクロソフト発StickyNotesで覚えておきたい設定・使い方8選
  8. 【OneNote for Windows10】StickyNotesが使用可能に…
  9. iPadで手書きのメモを取る!ApplePencilが使える無料のメモアプリ5選…
  10. 楽天銀行の法人ビジネス口座を持っているなら無料で使えるクラウドアプリを使ってみよ…

人気記事 PickUp!

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

おすすめ記事

ピックアップ!

  1. 【OneNoteへGo】Office製品のOutlook連絡帳をOneNoteの…
  2. 【ちょっと休憩】検索したいわけじゃないけどつい弾きたくなる「弾いちゃお検索」とは…
  3. 【マイクロソフト To-Do】パソコンとスマホで”やること”をシンプルに管理する…
  4. 【Google ToDo リスト】Gmailからも起動OK!Googleの予定管…
  5. ワールドカップには嘘のような法則が存在する?!
  6. 【リメンバーザミルク】海外製アプリながら使いやすいインターフェースで人気のタスク…
  7. これは使ってみたい!3つのTo-doリストを比較してみた
  8. 絵心がないからデザインは苦手っ・・・そんなあなたは「Canva」があればもう大丈…
  9. 経県値―自分がどれだけの都道府県に足を踏み入れたのかが簡単に分かるスマホアプリ
  10. とうとう正式リリース!マイクロソフトの「Whiteboard」アプリの使い心地は…

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

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

PAGE TOP