祝!100記事達成!!平日毎日更新中!!テーマにしてほしい内容等ありましたらお問合せフォームからメッセージください(^^)

Excel関数大全!~AGGREGATE関数~

どうもasです。

 

本日はExcelのAGGREGATE関数について解説していきます。

 

それではいってみましょう~。

 

 

AGGREGATE関数

用途

数える、合計する、平均する等の19種類の集計を行います。

集計方法『0~13』はSUBTOTAL関数の機能を拡張した関数で、オプションの集計条件に従い、指定した集計方法で集計します。

集計方法『14~19』は配列に指定したセル範囲のデータをオプションの集計条件に従い、指定した集計方法で値を求めます。集計方法に応じた集計方法の第2引数を指定します。

 

基本形

集計方法0~13

=AGGREGATE(集計方法,オプション,参照1,[,…参照N])※N=1~253

集計方法14~19

=AGGREGATE(集計方法,オプション,配列,集計方法の第2引数)

 

ワンポイント

参照値は最大253個の数値を指定できます。

 

SUBTOTALとAGGREGATEの違い

 

AGGREGATE関数ではオプションにより集計条件を指定できます。

上図のように参照値の指定範囲内にエラーが含まれている場合、SUM関数やSUBTOTAL関数では集計結果はエラーとなってしまいます。

しかしAGGREGATE関数ではオプション6(エラー値を無視)を集計条件として指定すれば、参照値の指定範囲内にエラーが含まれている場合も、エラーを除外して集計することができます。

 

引数解説

集計方法

1~19を指定します。

 

集計方法参照N・配列に対する
集計内容
対応関数と第2引数
( )内は2007の表記
1平均AVERAGE
2数値の個数COUNT
3空白以外の個数COUNTA
4最大値MAX
5最小値MIN
6掛け算(積)PRODUCT
7標本標準偏差STDEV.S(STDEV)
8標準偏差STDEV.P(STDEVP)
9合計SUM
10不偏分散VAR.S(VAR)
11分散VAR.P(VARP)
12中央値MEDIAN
13最頻値MODE.SNGL(MODE)
14降順の順位の値LARGE(配列,順位)
15昇順の順位の値SMALL(配列,順位)
16百分位数PERCENTILE.INC(配列,順位)(PERCENTILE)
17四分位数QUARTILE.INC(配列,戻り値)(QUARTILE)
1810~90の百分位数PERCENTILE.EXC(配列,率)
19第2、第3の四分位数QUARTILE.EXC(配列,戻り値)

 

オプション

オプションでは集計条件を指定できます。『0』は省略可能です。

オプションは0~7まであります。

 

オプション内容
0
(省略可)
参照値に指定する範囲内にSUBTOTAL関数やAGGREGATE関数が存在する場合は、これらの集計値を無視します。
1オプション『0』のほか、非表示の行を無視します。
2オプション『0』のほか、エラー値を無視します。
3オプション『0』『1』『2』の全てを含みます。
4全てを集計対象にします。
5非表示行を無視します。
6エラー値を無視します。
7非表示行とエラー値を無視します。

 

さまざまな集計値を求める

  1. 目的の集計方法に合わせて集計方法を入力します。
  2. オプションは『2』を指定し、SUBTOTAL関数・AGGREGATE関数の集計値、エラー値を無視します。
  3. 参照Nに金額列を範囲指定して、セル範囲『C3:C14』を絶対参照にして、G3セルの入力内容をG7セルまでコピーできるようにします。

 

このように、一つの関数で様々な目的に合わせた数値を集計する事ができます。

 

 

成長曲線の値を作成する

  1. 100人の体重データを小さい方から数えて
  2. 指定した百分位の位置に

該当した体重を求めています。

パーセンタイル値とは

パーセンタイル値とは全体を100とした時に、小さい方から数えて何番目になるかを示しています

上図の場合では『39』は体重データの数値を小さい方から数えて3番目の数値、つまり『3パーセントタイルの体重』が『39』という事になります。

 

指定した順位の値を求める

  1. 集計方法は14を指定し、指定した範囲の数値が大きい方から(成績上位者)成績を求められます。逆に集計方法を15に指定すれば、指定した範囲の数値の小さい方から(成績下位者)成績を求めれれます。
  2. 第2引数です。ここに入力した順位の点数を求めます。

 

 

 

まとめ記事

Excel関数のまとめ記事です。

 

是非参考にしてください(^-^)

 

     

asblogの最新情報をチェックしよう!