ExcelのAGGREGATE関数の使い方について
ExcelのAGGREGATE関数は、複数の値を集計する際に非常に便利な関数です。この関数には、セル範囲形式と配列形式の2種類の書式があります。それぞれの書式について、引数の解説とサンプルを交えながら解説していきます。
1. セル範囲形式
まずは、セル範囲形式のAGGREGATE関数について解説します。書式は以下の通りです。
AGGREGATE(集計方法, オプション, 参照1, [参照2], …)
各引数の説明は以下の通りです。
- 集計方法：集計方法には1〜19の整数を指定します。それぞれの数値が表す集計方法は後述します。
- オプション：計算時に無視するデータの種類を指定します。0〜7の整数を指定します。
- 参照1, 参照2, …：集計対象となるセル範囲を指定します。複数の範囲を指定することができます。
集計方法に指定できる数値とそれぞれの意味は以下の通りです。
1: AVERAGE - 平均 2: COUNT - 個数 3: COUNTA - 非空白セルの個数 4: MAX - 最大値 5: MIN - 最小値 6: PRODUCT - 積 7: STDEV - 標準偏差 8: STDEVP - 母集団標準偏差 9: SUM - 合計 10: VAR - 分散 11: VARP - 母集団分散 12: MEDIAN - 中央値 13: MODE - 最頻値 14: LARGE - 第n大の値 15: SMALL - 第n小の値 16: PERCENTILE - nパーセンタイル値 17: QUARTILE - 四分位数 18: PERCENTILE.EXC - 排他的nパーセンタイル値 19: QUARTILE.EXC - 排他的四分位数
オプションに指定できる数値とそれぞれの意味は以下の通りです。
0: すべての値を計算に使用 1: 隠れた行を無視 2: エラー値を無視 3: 隠れた行とエラー値を無視 4: 隠れた行、エラー値、サブトータルを無視 5: 隠れた行、エラー値、集計関数を無視 6: エラー値とサブトータルを無視 7: エラー値と集計関数を無視
それでは、セル範囲形式のAGGREGATE関数のサンプルを見ていきましょう。
以下の表を使用します。
| ＼ | A | B | C |
|---|---|---|---|
| 1 | 名前 | 性別 | 年齢 |
| 2 | 山田 | 男 | 34 |
| 3 | 佐々岡 | 女 | 25 |
| 4 | 岸川 | 男 | 28 |
例1: 年齢の平均を計算します。
=AGGREGATE(1, 0, C2:C4)
この計算式では、集計方法に「1」を指定して平均を計算し、オプションに「0」を指定してすべての値を使用します。参照範囲としてC2:C4を指定しています。この場合、結果は29となります。
2. 配列形式
次に、配列形式のAGGREGATE関数について解説します。書式は以下の通りです。
AGGREGATE(集計方法, オプション, 配列, [順位])
各引数の説明は以下の通りです。
- 集計方法：集計方法には14〜19の整数を指定します。それぞれの数値が表す集計方法は後述します。
- オプション：計算時に無視するデータの種類を指定します。0〜7の整数を指定します。
- 配列：集計対象となる配列を指定します。
- [順位]：集計方法が14〜15の場合は第n大の値、集計方法が16〜19の場合はパーセンタイルや四分位数の位置を指定します。この引数はオプションですが、集計方法によっては必要です。集計方法に指定できる数値とそれぞれの意味は以下の通りです。
14: LARGE - 第n大の値 15: SMALL - 第n小の値 16: PERCENTILE - nパーセンタイル値 17: QUARTILE - 四分位数 18: PERCENTILE.EXC - 排他的nパーセンタイル値 19: QUARTILE.EXC - 排他的四分位数
それでは、配列形式のAGGREGATE関数のサンプルを見ていきましょう。前述の表を引き続き使用します。
例2: 年齢の中央値を計算します。
=AGGREGATE(16, 6, C2:C4, 0.5)
この計算式では、集計方法に「16」を指定してnパーセンタイル値を計算し、オプションに「6」を指定してエラー値とサブトータルを無視します。配列としてC2:C4を指定し、順位に「0.5」を指定して中央値を求めます。この場合、結果は28となります。
例3: 年齢の第2小さい値を計算します。
=AGGREGATE(15, 6, C2:C4, 2)
この計算式では、集計方法に「15」を指定して第n小の値を計算し、オプションに「6」を指定してエラー値とサブトータルを無視します。配列としてC2:C4を指定し、順位に「2」を指定して第2小さい値を求めます。この場合、結果は28となります。
まとめ
ExcelのAGGREGATE関数は、複数の値を集計する際に非常に便利な関数で、セル範囲形式と配列形式の2種類の書式があります。それぞれの書式には、さまざまな集計方法とオプションが用意されており、データの種類や状況に応じて適切な集計を行うことができます。この解説記事を参考に、Excelでのデータ集計を効率的に行ってください。