AGGREGATE(アグリゲート)関数の使い方:さまざまな集計値や順位を求める関数:Excel関数

AGGREGATE(アグリゲート)関数の使い方:さまざまな集計値や順位を求める関数:Excel関数

AGGREGATE関数は、さまざまな集計値や順位を求める関数で、「数学と三角関数」に分類される関数の一つです。

以前にご紹介した「SUBTOTAL関数」の機能を強化した関数で、引数〔集計方法〕と〔オプション〕を組み合わせ、さまざまなニーズに対応できるよう進化しています。

AGGREGATE関数の使い方

対応バージョン:Excel365 2019 2016 2013 2010

AGGREGATE関数の書式です。

1.=AGGREGATE(集計方法,オプション,参照1,参照2,…,参照253)
2.=AGGREGATE(集計方法,オプション,配列,値)
※1.の書式は、引数〔集計方法〕に1~13を指定した場合、2.の書式は、引数〔集計方法〕に14~19を指定した場合に使います。

引数の説明

  • 〔集計方法〕:どういう集計値を求めるか「集計方法を1~19までの番号(詳細は後述)」で指定
  • 〔オプション〕:集計する際の「オプションを1~7までの番号(詳細は後述)」で指定
  • 〔参照〕:集計の対象範囲を指定
  • 〔配列〕:順位や分位数を求める範囲を指定
  • 〔値〕:求める値の順位や位置を指定

集計方法の番号の種類

集計方法 意味と同等の関数
1 平均(AVERAGE)
2 数値の個数(COUNT)
3 空白を除くデータの個数(COUNTA)
4 最大値(MAX)
5 最小値(MIN)
6 積を求める(PRODUCT)
7 標本標準偏差(STDEV.S)
8 標準偏差(STDEV.P)
9 合計(SUM)
10 不偏分散(VAR.S)
11 標本分散(VAR.P)
12 中央値(MEDIAN)
13 最頻値(MODE.SNG)
14 順位「降順」(LARGE)
15 順位「昇順」(SMALL)
16 百分位数(PERCENTILE.INC)
17 四分位数(QUARTILE.INC)
18 0%と100%を除外した百分位数(PERCENTILE.EXC)
19 0%と100%を除外した四分位数(QUARTILE.EXC)

オプション番号の種類

オプション 意味
0(省略可) ネストされているAGGREGATE関数およびSUBTOTAL関数を無視
1 0に加え、非表示の行を無視
2 0に加え、エラー値を無視
3 0に加え、非表示の行、エラー値を無視
4 すべてを検索対象とする
5 非表示の行を無視
6 エラー値を無視
7 非表示の行とエラー値を無視

AGGREGATE関数の基本的な使い方

それでは、実際のサンプルでAGGREGATE関数の使い方をイメージしましょう。

以下のサンプルは、果物の売上額を月別に集計した表です。月が替わるごとにAGGREGATE関数(SUBTOTAL関数も一部で使用)で「小計」を算出し、最後にAGGREGATE関数で「合計」を算出しています。

AGGREGATE関数の使用例

まず、4月の小計を求めるので、C6セルに「AGGREGATE関数」を入力し「小計」を求めます。

関数式は以下の通り。

=AGGREGATE(9,4,C2:C5)

関数式の説明

引数〔集計方法〕には、合計(小計)を求めるので「9」を指定。

引数〔オプション〕には、「すべてを検索対象とする」の「4」を指定。集計値を算出するセル範囲にエラー値がある場合は、このオプションに「エラー値を無視」する「2」を指定します。非表示の行を無視するなら「1」というように、必要に応じて指定してください。

引数〔参照〕には、小計を求めるセル範囲を指定。

以降、5月、6月も同様の方法で「小計」を算出します(なお、5月の小計は「SBTOTAL関数」で求めています)。

最後に、表の最終行に「合計」を「AGGREGATE関数のオプションを「1」として」算出しています。

オプションをこういう指定にしておかなければ、最終行の合計を算出するときに、途中のAGGREGATE関数およびSUBTOTAL関数で求めた「小計値」まで集計の対象になって正しい計算結果が求められませんので要注意です。

途中に小計があるような表の「合計」を求める場合、引数〔オプション〕は0~3のどれかを指定します。なお、小計を「SUM関数」で求めている場合は無視してくれませんので注意しましょう。

AGGREGATE関数と同じ分類の関数

AGGREGATE関数と同じ「合計/平均」系の関数は以下のページで探せます。

Excel関数カテゴリの最新記事