Excel。手早く、上位20%の平均値を算出するには、どうすればいいの。
＜AVERAGEIF+PERCENTILE.INC関数＞
データの平均値を算出するには、AVERAGE関数をつかえば、算出することができます。
次の表をつかって確認します。
F2には、
=AVERAGE(B2:B11)
という数式を設定しました。
オートSUMボタンにある平均をつかってもいいですね。
では、上位20%の平均値を算出したい場合は、どのようにしたらいいのでしょうか。
考え方として、上位20%なのかを調べる必要があります。
20%圏内ならば、平均値の対象になります。
そこで、登場するのがPERCENTILE.INC関数です。
この関数は、データ全体の上位何％（今回は20%）のポジションの値を算出することができます。
F4に
=PERCENTILE.INC(B2:B11,80%)
と数式をつくりました。
これで、上位20%の数値が算出できました。
その結果、88.2
あとは、この結果以上の値のデータだけの平均値を算出すればいいというわけです。
ところで、
=PERCENTILE.INC(B2:B11,80%)
の2番目の引数の「率」に注意が必要です。
上位20%だから20%と設定しがちですが、上位20%というのは、80%の位置にあたりますので、100-上位率 の数値を設定します。
F2に上位20%の平均値を算出します。
F4の88.2以上が平均値算出の条件です。
条件付きの平均値ということで、使う関数は「AVERAGEIF関数」です。
=AVERAGEIF(B2:B11,">="&F4)
F2には、91と算出できました。
データも88.2以上のものは、89と93しかないので、その平均値は91ですので、合致しております。
今回は、説明の為、F4に一度、上位20%の数値を算出しましたが、一つの数式にしても、問題はありません。
=AVERAGEIF(B2:B11,">="&PERCENTILE.INC(B2:B11,80%))
