command lab
  • TOP
  • BLOG
  • Excel

  • 【エクセル】AGGREGATE関数の使い方(集計値や順位などを求める)

【エクセル】AGGREGATE関数の使い方(集計値や順位などを求める)

集計方法を指定し、様々な集計ができる多機能なAGGREGATE関数。ここではAGGREGATE関数の基本的な使い方と使用例について解説していきます。

AGGREGATE関数とは

ExcelのAGGREGATE関数は、SUBTOTAL関数の機能を強化した関数です。集計方法を指定し様々な集計ができる多機能な関数で、エラーや非表示行を無視して集計することができます。平均値やカウント、最大値などの集計計算を返し、オプションで非表示の行とエラーを無視するように設定できます。合計19個の機能が使用可能です。(オプションについては表を参照してください)。

AGGREGATE関数の構文1(集計方法1〜13)

=AGGREGATE(集計方法,オプション,範囲1,【範囲2】…)
  • 集計方法:集計方法を1 ~ 19の番号で指定します。各番号の機能は下部に対応表を記載
  • オプション:無視する値をどのように指定するかを数値で設定します。対応表は下部に記載。省略されると0を設定します。
  • 範囲1:集計値を求めるために範囲を指定します。範囲は複数指定することができ、最大で253個設定することが可能です。
  • 範囲2:2つ目の範囲から省略可能です。集計値を求めるために範囲を指定します。範囲は複数指定することができ、最大で253個設定することが可能です。

AGGREGATE関数の構文2(集計方法14〜19)

=AGGREGATE(集計方法,オプション,配列,集計方法の設定値
  • 集計方法:集計方法を1 ~ 19の番号で指定します。各番号の機能は下部に対応表を記載
  • オプション:無視する値をどのように指定するかを数値で設定します。対応表は下部に記載。省略されると0を設定します。
  • 配列:集計値を求めるために範囲を指定します。
  • 集計方法の設定値:集計方法ごとの設定値を指定します。例えば「14:降順の順位を出力」の場合は順位が何番目の値を取得するかを設定します。

AGGREGATE関数の集計方法対応表

1つ目の設定値(引数)の集計方法の対応表は下記です。

設定数値同じ機能の関数説明
1AVERAGE平均値を出力
2COUNT数値の個数を出力
3COUNTAデータの個数を出力
4MAX最大値を出力
5MIN最小値を出力
6PRODUCT積を出力
7STDEV.S不偏標準偏差を出力
8STDEV.P標本標準偏差を出力
9SUM合計値を出力
10VAR.S普遍分散を出力
11VAR.P標本分散を出力
12MEDIAN中央値を出力
13MODE.SNGL最頻値を出力
14LARGE降順の順位を出力
15SMALL昇順の順位を出力
16PERCENTILE.INC百分位数を出力
17QUARTILE.INC四分位数を出力
18PERCENTILE.EXC百分位数を出力
19QUARTILE.EXC四分位数を出力

オプション対応表

2つ目の設定値(引数)のオプションの対応表は下記です。

設定数値説明
0指定する範囲内にSUBTOTAL関数やAGGREGATE関数 がある場合は、これらの集計値を無視します。省略した場合こちらが設定されます。
1「0」の内容に加えて、非表示行を無視します
2「0」の内容に加えて、エラー値を無視します
3「0」の内容に加えて、非表示行とエラー値を無視します
4何も無視しません
5非表示の行だけを無視します
6エラー値を無視します
7非表示の行とエラー値を無視します

AGGREGATE関数の使用例

例1:エラーを無視して最大値を出力

集計方法を「4:最大値を出力」に設定してリストの最大値を出力します。リストにエラーが混ざっているため、オプションを「2:SUBTOTALやAGGREGATEの内容に加えてエラー値を無視」に設定し、エラーを無視します。

=AGGREGATE(4,2,C3:C10)  

例2:エラーを無視してカウントする

リストのデータ数をカウントします。集計方法を「2:数値の個数を出力」に設定し、エラーは除外するため、オプションを「2:SUBTOTALやAGGREGATEの内容に加えてエラー値を無視」に設定します。

=AGGREGATE(2,2,C3:C10)

例3:n番目に大きい値を抽出

n番目に大きい値を抽出したい場合は「14:降順の順位を出力」に設定します。このケースの場合AGGREGATE関数の書式は変わり、AGGREGATE(集計方法、オプション、配列、[順位])となり、配列の後に取得したい順位を設定します。

=AGGREGATE(14,2,C3:C10,3)

AGGREGATE関数の注意点

  • 3D参照はAGGREGATEでは機能しません。
  • AGGREGATE関数は、水平範囲ではなく、垂直範囲を集計するように設計されています。

ショートカットキーの練習できます

当サイトはショートカットキーの練習を実際の動きを確認しながら練習できる機能がついています。繰り返し指を動かして練習ができるので、ゲーム感覚で遊んでいくうちに自然とショートカットキーが使えるようになれます。ショートカットキーは暗記するよりも実際に手を動かして練習していったほうが習得が早くなるので、是非当サイトで定期的に練習してみてください。下記のSTARTキーを押下すると練習を始められます。

下のスタートボタンを押して練習モードに入ります。
STARTSTOP
※入力モード切り替え ctrl + shift + alt + space
PRE
NEXT
ガイドモード
リピートモード

※推奨ブラウザ Google Chrome

PUSH ENTER

関連記事