スポンサーリンク

2016年6月23日木曜日

さまざまな集計ができるAGGREGATE関数とは

さまざまな集計ができるAGGREGATE(アグリゲート)関数の解説です。

AGGREGATE関数は、SUBTOTAL関数の機能を強化したExcel2010から新しく追加された関数です。

セル範囲形式の場合 : =AGGREGATE(集計方法, オプション, 参照1[, 参照2, ...,])
配列形式の場合 : =AGGREGATE(集計方法, オプション, 配列 , 順位)

●集計方法
集計番号 意味 同等の関数
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 百分位数(0%と100%を除く) PERCENTILE.EXC
19 四分位数(0%と100%を除く) QUARTILE.EXC

●オプション
0または省略 ネストされたSUBTOTAL関数とAGGREGATE関数を無視
1 0の機能に加えて非表示の行を無視
2 0の機能に加えてエラー値を無視
3 0の機能に加えて非表示の行とエラー値を無視
4 何も無視しない
5 非表示の行を無視
6 エラー値を無視
7 非表示の行とエラー値を無視

●使用例

下の表でAGGREGATE関数を使って「合計値」「中央値」「最頻値」を求めます。
AGGREGATE関数を使って「合計値」「中央値」「最頻値」を求める

リボンの[数式]~[数学/三角]~[AGGREGATE]をクリックします。
[数学/三角]~[AGGREGATE]をクリックする

引数の選択ダイアログボックスが開きます。
ここではセル範囲を使うので「集計方法、オプション、参照1、…」を選択します。
引数の選択ダイアログボックス

関数の引数ダイアログボックスが開きます。

集計方法 : 合計するので9を入力します。
オプション : デフォルトの0を入力します。
参照1 : 合計するセル範囲を指定します。
関数の引数ダイアログボックス

これでAGGREGATE関数で合計が計算できました。

=AGGREGATE(9,0,C3:D14)
AGGREGATE関数で合計が計算できた

中央値 : =AGGREGATE(12,0,C3:D14)
最頻値 : =AGGREGATE(13,0,C3:D14)
中央値と最頻値を求める

集計番号14の[降順の順位]と15の[昇順の順位]を求めるには、LARGE関数とSMALL関数と同様に何番目の値を求めるかを指定する必要があります。
指定しない場合は、#VALUE!エラーになります。

降順の順位(2番目を求める場合) : =AGGREGATE(14,0,C3:D14,2)
[降順の順位][昇順の順位]何番目を指定しない場合は#VALUE!エラー

関連する記事
関連する記事
SUBTOTAL関数を使い、複数の表から合計・平均・最大・最小を求める
SUBTOTAL関数を使い、小計を集計する方法
オートフィルターの抽出結果を反映した合計を求める
LARGE関数を使い、上位3位を求める
○番目に小さい数値を求めるSMALL関数を使ってみる