AGGREGATE（アグリゲート）関数とは？

AGGREGATEは「集計・合計」を意味する英語です。Excel2010から装備された関数で、降順・昇順の順位や中央値を求めたり、エラー値のセルを除外した集計もできます。

AGGREGATE関数とSUBTOTAL関数の違い

簡単に言えば、SUBTOTALは進化前で、AGGREGATEは進化後です。AGGREGATE関数はSUBTOTALの集計方法を拡張し、より柔軟な指定を可能にしました。

両者とも「集計方法」を対応する関数ごとに決まった番号で指定しますが、AGGREGATEは対応する関数が８種類増えています。

また、SUBTOTALでは非表示にした行データを除外して集計するのに３桁の番号を指定する必要がありましたが、AGGREGATEでは集計で除外する条件を引数オプションに0～7までの数字で指定する仕様に変わりました。このオプションには非表示・エラー値・小計の除外に関する様々な選択肢が含まれます。

SUBTOTAL関数の詳しい使い方です。

https://www.tschoolbank.com/tb-articles/excel-function-subtotal/

www.tschoolbank.com

AGGREGATE関数の引数と書式

「数式」タブ「関数ライブラリ」の「数学/三角」からAGGREGATEをクリックします。

AGGREGATEのダイアログを開く際に２タイプの書式を選択する画面が現れます。

最初の書式の引数は「集計方法」「オプション」「配列」「順位」で、これはAGGREGATE関数で新たに加わった集計方法の内、14～19を対象にした書式です。

２番目の書式の引数は「集計方法」「オプション」「参照1」が必須で、「参照2」以下は任意です。これは1～13までの集計方法を対象にした書式です。

書式の構成はこうなります。

AGGREGATE関数の使い方｜集計方法1～13までの書式

集計方法「1」から「13」までは引数「集計方法・オプション・参照1・参照2・・・」の書式を選択して計算します。
この中で「最大値」と「最小値」を求める集計をしてみましょう。

会員名簿のデータ表です。会員資格をクリアしていない数名の行が非表示になっており、入力ミスによるエラー値もあります。この中から「最年長」と「最年少」の会員の年齢をAGGREGATE関数で取得してみます。

• 「集計方法」には最大値を求める「4」を指定します。
  
• 「オプション」には非表示の行とエラー値を無視する「7」を指定します。
  
• 「参照1」には「年齢」配下のデータ範囲を指定します。
  

ダイアログでの指定は以下のようになります。

「最年長」の数式を「最年少」のセルにコピーして、「集計方法」の数値を最大値を求める「4」から最小値を求める「5」に修正します。

「最年長」と「最年少」の年齢が取得できました。

最年長の数式：　=AGGREGATE(4,7,$D$2:$D$21)
最年少の数式：　=AGGREGATE(5,7,$D$2:$D$21)

AGGREGATE関数の使い方｜集計方法14～19までの書式

集計方法「14」から「19」までは引数「集計方法・オプション・配列・順位」の書式を選択して計算します。
この中から「降順の順位」を求める集計をしてみましょう。

ランダムに並べた成績表があり、これを点数の高い順に並べ替えます。欠席者の行が非表示になっており、「氏名」の欄には点数が並べ替えられたら氏名も自動で表示されるように「=IFERROR(VLOOKUP(E2,$A$2:$B$16,2,0),””)」の数式が入っています。

• 「集計方法」には降順の順位を求める「14」を指定します。
  
• 「オプション」には非表示の行を無視する「5」を指定します。
  
• 「配列」には「総合点」配下のデータ範囲を指定します。
  
• 「順位」には「1」をセル参照で指定します。
  

ダイアログでの指定は以下のようになります。

この数式を下までオートフィルコピーして完了です。成績順位で表が並べ替えられました。

数式：　=AGGREGATE(14,5,$A$2:$A$16,D2)