テーブルやデータベースを集計する際、非表示の行やエラー値を無視して集計する関数です。集計には19種類の関数を使用することができます。SUBTOTAL関数 との違いについても解説しています。
AGGREGATE関数の使い方
集計で使用する関数によって構文が違います。関数の引数が範囲のみの場合はセル範囲形式、範囲以外に引数が必要な関数は配列形式の構文を使用します。
セル範囲形式
=AGGREGATE(引数1:集計方法, 引数2:オプション, 引数3:参照 1, [引数4:参照 2], …)
- 引数1:集計方法
省略不可。使用する関数を番号で指定します。
- …AVERAGE
- …COUNT
- …COUNTA
- …MAX
- …MIN
- …PRODUCT
- …STDEV.S
- …STDEV.P
- …SUM（この後サンプルあり）
- …VAR.S
- …VAR.P
- …MEDIAN
- …MODE.SNGL
- 引数2:オプション
省略可。引数3で指定した範囲内で無視する値を番号で指定します。
- または省略…ネストされたSUBTOTAL関数 とAGGREGATE関数 を無視します。
- …非表示の行、ネストされたSUBTOTAL関数 とAGGREGATE関数 を無視します。
- …エラー値、ネストされたSUBTOTAL関数 とAGGREGATE関数 を無視します。
- …非表示の行、エラー値、ネストされたSUBTOTAL関数 とAGGREGATE関数 を無視します。
- …何も無視しません。
- …非表示の行を無視します（この後サンプルあり）。
- …エラー値を無視します。
- …非表示の行とエラー値を無視します（この後サンプルあり）。
- 引数3:参照1
省略不可。
- 引数4:参照2
省略可。最大253個まで指定できます。
配列形式
=AGGREGATE(引数1:集計方法, 引数2:オプション, 引数3:配列, 引数4:値)
- 引数1:集計方法
省略不可。使用する関数を番号で指定します。
- …LARGE（引数4:順位 この後サンプルあり）
- …SMALL（引数4:順位）
- …PERCENTILE.INC（引数4:率）
- …QUARTILE.INC（引数4:戻り値）
- …PERCENTILE.EXC（引数4:率）
- …QUARTILE.EXC（引数4:戻り値）
- 引数2:オプション
省略可。引数3で指定した範囲内で無視する値を番号で指定します（セル範囲形式と同じ）。
- 引数3:配列
省略不可。
- 引数4:値
省略不可。各関数に必要な引数を指定します。
集計で使用する関数によって構文は若干変わりますが、関数による引数の違いがあるだけで構文の基本は変わりません。
- 引数1…集計に使用する関数を指定する
- 引数2…集計の条件（何を無視するか）を指定する
- 引数3…集計の範囲を指定する
- 引数4…関数によっては追加の引数を指定する（配列形式のみ）
AGGREGATE関数でフィルタ抽出した行を集計する
テーブル内の年棒を合計するサンプルです。
=AGGREGATE(9,5,N2:N76)
集計方法はSUM、オプションは非表示行を無視、参照1は年棒列（N列）です。
- フィルタで抽出した値で年棒の合計が算出されます（非表示行は集計されない）。
AGGREGATE関数でエラー値を無視して集計する
年棒を背番号で割り、集計方法LARGEで一位の値を抽出するサンプルです（集計に意味はありません）。2行目と3行目は背番号により#DIV/0!が返されますが、条件で非表示の行とエラー値を無視しているので値 が返ります。
=AGGREGATE(14,7,O2:O76,1)
集計方法はLARGE、オプションは非表示の行とエラー値を無視する、配列は年棒/背番号列（O列）、順位は1です。
- O2とO3のエラー値が無視され、範囲内の最大値である75,000,000を返します。
- もちろんですが、この計算を普通にLARGE関数 のみで行うと#DIV/0!が返ります。
- 加算であれば、=SUMIF(範囲,“<>#DIV/0”,合計範囲)でもエラー値を除いて集計できます。
SUBTOTAL関数との違い
AGGREGATE関数 はExcel2010で実装された関数です。それまではSUBTOTAL関数 を使用していました。違いは4つです。
- AGGREGATE関数 はエラー値を無視できる
- SUBTOTAL関数 はフィルタによる非表示行しか無視できない（AGGREGATE関数 は右クリックメニューの非表示も無視する）
- 集計方法（使用できる関数）がSUBTOTAL関数 は11種類、AGGREGATE関数 は19種類
- 引数の指定方法が若干違う
後発のAGGREGATE関数 は当然、SUBTOTAL関数 の上位互換です。
非表示の行やエラー値を無視して集計するときはAGGREGATE関数を使おう
- エラー値を無視したいときや非表示行を集計したくないときはAGGREGATE関数 を使用する。
- 引数1:集計方法（使用する関数）によって構文が変わってくるので注意する。
- SUBTOTAL関数 は旧バージョンの関数なので積極的に使用する必要はない。
集計方法やオプションは入力補助でリストが出るので覚える必要はありません。構文間違いさえ気を付ければ大丈夫です。
