Excel。最大値と最小値を除いた平均値をLET関数で算出してみる
＜SUM+MAX+MIN+COUNT関数・LET関数＞
最大値と最小値を除いた平均値は、AVERAGE関数では算出することができません。
AVERAGE関数をつかわないで、平均値を算出するには、範囲の総和を範囲の総和した件数で除算すれば、算出することができます。
C2:C6まで、売上金額が入力されています。
この範囲の最大値と最小値を除いた平均値を算出するために、D8に数式を設定しました。
=(SUM(C2:C6)-MAX(C2:C6)-MIN(C2:C6))/(COUNT(C2:C6)-2)
長いですが、シンプルな関数しかつかっていません。
だけど、C2:C6という範囲選択が何度も登場してきます。
数式を説明すると、
SUM(C2:C6)-MAX(C2:C6)-MIN(C2:C6)
SUM関数で、合計を算出します。
その値から、MAX関数で最大値をMIN関数で最小値を減算します。
この値を
COUNT(C2:C6)-2 の算出結果で除算します。
COUNT関数で数値の件数を算出することができます。
「-2」するのは、最大値と最小値のデータを除いた件数にする必要があるからです。
この数式で算出して、何ら問題はありません。
今回は、この数式をLET関数で、算出してみます。
LET関数は、数式内に「名前の定義」というか、「変数」を設定することができる関数です。
ただ、どのように使うのか、わかりにくい関数といわれています。
そこで、比較的わかりやすい 範囲選択 を名前に設定してみます。
D9にLET関数をつかって数式をつくります。
=LET(rg,C2:C6,(SUM(rg)-MAX(rg)-MIN(rg))/(COUNT(rg)-2))
数式の文字数としては、差はあまりありませんが、C2:C6を何度もつかわないので、可読性は改善されています。
では、この数式の説明をします。
LET関数の最初の引数は、名前。
プログラムでいうところに変数。あるいは、名前の定義です。
今回は、範囲選択なので、Excel VBAでお馴染みのRangeの「rg」としました。
2つ目の引数は、式。
式となっていますが、数式でもいいし、範囲でも指定できます。
繰り返し設定するところを設定するといいので、「C2:C6」としました。
この名前と式を複数設定することも出来ます。
3つ目は、計算式。
SUM(rg)-MAX(rg)-MIN(rg))/(COUNT(rg)-2
C2:C6だったところが、rgという名前を設定しましたので、rgにすることで、C2:C6と入力しなくてもいいわけです。
このように、LET関数をつかうと、複雑な数式の時に、可読性の改善と、数式もコンパクトにすることができるかもしれません。