Excel。一定の上限と下限の数値を除外して
平均値を求める方法をご紹介
TRIMMEAN関数
Excel。前回は、一位と最下位の数値を除いて平均値を算出する方法をご紹介しましたが、今回も企業研修の際に、データのブレがある場合に、一位と最下位ではなくて、
一程度の割合を除いて平均値を算出することは出来ませんか？とご質問があったので、
その方法をご紹介させていただきました。
たしかに、極端に大きな値や小さい値がを除きたいのに、
一位と最下位だけの2アイテムを除いても、データの件数によっては、役に立ちませんので、
別の方法が必要になりますね。
例えば、上限10%と下限10％に該当するものを除外するとした場合、
上限10%と下限10％を算出するだけでも大変ですよね。
そこで、登場するが、ほとんどなじみのない、
TRIMMEAN関数
をご紹介します。このTRIMMEAN関数は、
データ全体の上限と下限から一定の割合のデータを除いた平均値を算出してくれる
関数なのです。
ただ、普通は使うことはないと思いますが、
データ分析業務などでは使うことがあると思われますので、
こんな関数もあるんだぁ～と知っているだけでも、プラスになるでしょう。
数式の作り方自体は、シンプルなので、早速やってみましょう。
下記のような表があります。
内藤さんから大久保さんまでの数値が10件ありますので、上限と下限。
それぞれ10%を除外した平均値をC14に算出したいと思います。
なお10件で10%なので、上限と下限。それぞれ1件ずつをカットすることになりますよ。
では、C14にTRIMMEAN関数のダイアログボックスを表示しましょう。
配列には、
この配列には、データが入力されているセル範囲を選択しますので
C3:C12
と入力します。
さて、次の割合なのですが、これがちょっと厄介なんですね。
日頃あまり使わないものなので、アレレということも結構あるんですが、
今回は、上限と下限のそれぞれ10%を除きたいので、この割合には、
10%の0.1を入力しそうになるのですが、上限と下限なので、20%ということになりますので、
今回の割合には、
0.2
と入力します。
ですので、仮に、0.1と割合に入力すると、
上限と下限がそれぞれ5%のデータを除外することになりますので、注意が必要です。
あとは、OKボタンをクリックして、完成ですね。
C14には、56.25という数値が算出されましたね。
さて、よくここで、【本当にあっているの？】と疑問を持たれる方もいらっしゃいますので、
C3の100とC9の20を除いた値の平均値を算出してみるといいでしょう。
このように、56.25になります。
TRIMMEAN関数
このような関数があることを知っていると、Excel力が益々アップすることが出来ますね。
本当にExcelって奥が深い。
