1. Home » 
2. エクセル関数一覧表 » 
3. Excel関数の目次 » 
4. Excel関数の目次 » 
5. AGGREGATE関数の使い方

集計値を求める関数・AGGREGATE関数の使い方:Excel関数

• AGGREGATE関数はExcel2010で追加された関数です。
  Excel for Microsoft365,Excel2010以降(Excel2013,Excel2016,Excel2019,Excel2021,Excel2024)で使用可能な関数です。

更新:2023/10/14;作成:2011/12/10

リストまたはデータベースの集計値を返す　　　topへ
　アグリゲイト
セル範囲形式　=AGGREGATE(集計方法, オプション, 参照 1, [参照 2], …)
配列形式　　　=AGGREGATE(集計方法, オプション, 配列, [k(順位)])

• AGGREGATE関数はExcel2010で追加された関数です。
  Excel for Microsoft365,Excel2010,Excel2013,Excel2016,Excel2019,Excel2021,Excel2024で使用可能です。
• AGGREGATE関数はエラーを無視して、集計方法に指定した計算が可能になります。
• AGGREGATE関数では、非表示の行やエラー値を無視するオプションを利用して、集計方法に指定した計算が可能になります。 SUBTOTAL関数の強化版の関数となっています。
  • 機能が似ている関数には SUBTOTAL関数があります。
• 集計方法で指定する番号と対応する関数
  赤文字はよく使われる関数名です。
  

  集計方法	関数	機能
  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.SNGL	最頻値を求める
  14	LARGE	大きい方からの順位を求める
  15	SMALL	小さい方からの順位を求める
  16	PERCENTILE.INC	百分位数を求める
  17	QUARTILE.INC	四分位数を求める
  18	PERCENTILE.EXC	百分位数を求める（0%と100%を除く）
  19	QUARTILE.EXC	四分位数を求める（0%と100%を除く）

• オプションで指定できるもの
  

  オプション	動作
  0 または省略	ネストされた SUBTOTAL 関数と AGGREGATE 関数を無視します。
  1	非表示の行、ネストされた SUBTOTAL 関数と AGGREGATE 関数を無視します。
  2	エラー値、ネストされた SUBTOTAL 関数と AGGREGATE 関数を無視します。
  3	非表示の行、エラー値、ネストされた SUBTOTAL 関数と AGGREGATE 関数を無視します。
  4	何も無視しません。
  5	非表示の行を無視します。
  6	エラー値を無視します。
  7	非表示の行とエラー値を無視します。

• 下表の得点表が作成されています。D16セルに平均点を求めなさい。
  ただし、D4セルにはエラー値「#N/A」が返されています。
  • 下表をExcelへコピーすると「#N/A」は文字列となるかもしれません。ですので、D4セルには　=NA()　や　=VLOOKUP(B4,Sheet2!A1:B20,2,FALSE)　などエラーが返る数式を入力してください。

  • 	B	C	D
    1	名前	クラス	得点
    2	相沢	1	36
    3	井上	2	92
    4	市田	1	=NA()
    5	飯田	1	60
    6	上野	1	38
    7	上村	2	71
    8	江藤	1	56
    9	枝野	2	78
    10	江頭	1	84
    11	遠藤	2	32
    12	大野	2	62
    13	大木	1	40
    14	大川	2	98
    15
    16	平均点

• 平均を求めるAVERAGE関数やSUBTOTAL関数では参照セル範囲にエラー値があるとエラー値を返して計算できませんが、AGGREGATE関数を使うと計算ができます。

1. 数式を入力する D17セルを選択します。
   =ag　と入力すると、数式オートコンプリートの機能でag　を含む関数がリストで表示されます。
   AGGREGATE関数のみなので、[Tab]キーを押して入力します。
   
2. =AGGREGATE(　と入力され、引数1のリストが表示されます。
   ここでは 平均を計算したいので、1-AVERAGE　を入力したいので、[Tab]キーを押します。
   
3. =AGGREGATE(1　と入力されるので、,(カンマ)を入力すると、引数2のリストが表示されます。
   ここでは　6-エラー値を無視します。　を[↓]キーを6回押して選択したら、[Tab]キーを押して入力します。
   
4. =AGGREGATE(1,6,　と入力したら、計算するセル範囲 D3:D15 を入力します。
   マウスを利用する場合は D3セルを選択して、「Shift]+[Ctrl]+[↓]キーを押してセル範囲を入力することもできます。
   =AGGREGATE(1,6,D3:D15)　とします。
   
5. D17セルの平均値は　62.25　となります。
   

【問題2】

• 問題1の表を使って、得点が一番高い人〜3番目に高い人を表示しなさい。

【問題2の解答例】

• 得点が一番高い人（H3セル）に
  =INDEX($B$3:$B$15,MATCH(AGGREGATE(14, 6, $D$3:$D$15,ROW(A1)),$D$3:$D$15,0))
  と、入力して下方向へ（H5セルまで）コピーします。で求めます。
  
• 考え方は、最も大きい値を求めて、MATCH関数でこの最大値が何行目かにあるかを計算します。
  そして、INDEX関数で 名前を求めます。
• 最も大きい値はMAXやLARGE関数で求めよう・・・となるのですが、エラー値が途中にあるので AGGREGATE関数を使うことになります。
  AGGREGATE(14, 6, $D$3:$D$15,ROW(A1))　となります。
  配列形式　=AGGREGATE(集計方法, オプション, 配列, [k])　の各引数は
  集計方法には　14、
  範囲にエラー値があるので無視するのでオプションは　6　を指定します。
  配列には得点のセル範囲　D3:D5 を絶対参照とし、
  順位は　1を指定するのですが、数式を下方向にコピーするときに　1、2、3　としたいので 　ROW(A1) としました。
• 得点から行位置を求めるためにMATCH関数を使います。
  MATCH(AGGREGATE(14, 6, $D$3:$D$15,ROW(A1)),$D$3:$D$15,0)
  検査値に AGRGREGATE関数で求める得点を指定します。
  検査範囲は　D3:D15 で絶対参照とします。
  照合の種類は 0　とします。
• 名前はINDEX関数で求めています。
  =INDEX($B$3:$B$15,MATCH(AGGREGATE(14, 6, $D$3:$D$15,ROW(A1)),$D$3:$D$15,0))
  配列には　名前のセル範囲　B3:B15 を絶対参照で指定します。
  行番号には、下のMATCH関数で求める値を指定します。

【問題3】

• 問題1の表を使って、B列のクラスが「1」のデータを抽出しなさい。抽出はフィルターを利用することとします。
  抽出したクラス「1」の平均値を求めなさい。

【問題3の解答例】

• 非表示の行とエラーの行を除いた平均値を求めるために、AGGREGATE関数の数式を組み立てます。
  次に、クラスが2のデータを非表示にして計算から除外します。（クラスが1のデータだけを表示します）

1. D17セルに　=AGGREGATE(1,7,D3:D15)　と入力します。
   引数の集計方法：1 = AVERAGE　
   引数のオプション：7 = 非表示の行とエラー値を無視します
   引数の参照範囲：D3:D15
   非表示行の平均値を求めるにはSUBTOTAL関数が使えますが、エラー値があると計算できません。
   よって、ここではAGGREGATE関数を利用しました。
   
2. データリストをフィルターで絞り込みます。
   データの範囲　B2:D15　を選択します。
   
3. [データ]タブの[フィルター]を実行します。
   
4. 列見出し「クラス」のフィルターボタンをクリックします。
   表示されたメニューのリストで「1」にチェックを入れます。（「2」のチェックを外します。)
   
5. クラス「1」のデータが抽出できました。
   クラス1の平均が計算されました。
   

スポンサードリンク

PageViewCounter

Since2006/2/27