Excel。一位と最下位を除いて平均を算出する数式を
考えてみよう。
体操とかフィギュアスケートなどの競技において、例えば８名の審判が出した得点のうち、
トップの点数と最下位の点数を除いて、その合計点で争うことになりますが、
このトップと最下位を除くというのは、
できるだけ差の激しいものを除外したいという所から発生した考え方だと思います。
よく企業研修や、お勤めの方から、合計ではないのですが、トップ。
すなわち1位と最下位の数値を除いた平均値を算出したいのですが…
という質問やリクエストがあったります。
いろんな方法はあるとは、思いますが、
職業訓練などの、Excel初心者さんへの複雑な四則演算の練習を兼ねて、
一位と最下位を除いて
平均を算出する数式を皆さんと一緒に考えて算出する練習をすることがあります。
それでは、早速確認していくことにしましょう。
下記の表があります。
C11にC3～C8までの、一位と最下位を除いて平均値を算出する数式を作っていきましょう。
数式を作成する前に、考えてみましょう。
まず、平均値というのは、総和した値を総和した時の件数で除算したものですよね。
なので、
まずC3:C8までの合計を算出して一位と最下位を減算した値を算出する必要がありますよね。
そして、そのあとに、総和した時の件数で除算してはいけませんので、
その件数から2を減算してあげる必要が生じますよね。
実は、それほど難しくはないのですが、なぜこれが、
職業訓練などのExcel初心者さん向けの練習になるのかというと、
オートSUMボタンの中にある、合計、最大値、最小値、数値の個数を使いつつ、
カッコを使って数値を求めていくことになりますので、非常に練習にもってこいなんです。
まず、全体の合計を求めたいので、SUM関数が登場します。
SUM(C3:C8)。
そして、最大値と最小値を求めますが、これは、MAXとMINの両関数が登場します。
MAX(C3:C8)
MIN(C3:C8)
総和のほうの数式はこれらを使いまして、
SUM(C3:C8)-MAX(C3:C8)-MIN(C3:C8)
で、総和から一位と最下位を減算した値が完成します。
続いて、除算する数値を求めます。
総和した件数を求めますので、これは、COUNT関数を使うといいですよね。
COUNT(C3:C8)
この算出した値から、一位と最下位なので、2を減算させます。
ですので、
COUNT(C3:C8)-2
ですが、四則演算は、乗算除算を和算減算よりも先に計算してしまうので、単純に、
SUM(C3:C8)-MAX(C3:C8)-MIN(C3:C8)/ COUNT(C3:C8)-2
としても、うまくいきません。カッコが必要ですよね。
なので、数式は、
=(SUM(C3:C8)-MAX(C3:C8)-MIN(C3:C8))/(COUNT(C3:C8)-2)
となるわけですね。
では、この数式を実際にC11に作ってみましょう。
数値は54.75という数値が算出されました。
平均値や中央値ともことなった差異を除外した方法をご紹介いたしました。
オートSUMボタンを使った、ちょっと複雑な数式なので、
練習するにはちょうどいい題材なので、ご紹介をしております。
