TL;DR
次の式で計算できます。
=ARRAYFORMULA(AVERAGE(LARGE(<計算範囲>, {1, 2, 3, 4, 5})))
例
やっていること
素朴な方法
手間を惜しまずに書くなら以下のようになります。
AVERAGE(LARGE(<計算範囲>, 1), LARGE(<計算範囲>, 2), LARGE(<計算範囲>, 3), LARGE(<計算範囲>, 4), LARGE(<計算範囲>, 5))
しかし、非常に式が長いので見づらいですし、修正も大変です。そこで配列リテラルと ARRAYFORMULA 関数の出番になります。
配列リテラル
スプレッドシートでは {a, b, c, ...} という表記で配列リテラルを使うことができます。
配列は、数の並びです。
例えばA1セルからA3セルにかけて 1,2,3 という数が並んでいたら、セル名で A1:A3 とするのと、直接値を使って {1,2,3} とするので同じ配列が得られます。
LARGE 関数
LARGE(<計算範囲>, n) という表記で、計算範囲内で上位 n 番目に大きい値を取得することができます。
通常は n には単体の数値が来ます。
ARRAYFORMULA 関数
ARRAYFORMULA 関数では、配列を引数として取らない関数を配列の各要素に適用させたい場合に役に立ちます。
イメージとしては次のようになります。
- 配列の要素ごとに関数を適用する
LARGE(<計算範囲>, {1,2,3,4,5})
->{LARGE(<計算範囲>, 1), LARGE(<計算範囲>, 2), LARGE(<計算範囲>, 3), LARGE(<計算範囲>, 4), LARGE(<計算範囲>, 5)} - 計算結果の配列を集約して1つの数値を返す
ARRAYFORMULA(AVERAGE({LARGE(<計算範囲>, 1), LARGE(<計算範囲>, 2), LARGE(<計算範囲>, 3), LARGE(<計算範囲>, 4), LARGE(<計算範囲>, 5)}))
-> 上位5つの平均値
map reduce の知識がある人は、1番が map 関数、2番が reduce 関数に相当すると理解できると思います。
終わりに
間違っていたらご指摘いただけると幸いです。