上位３つの和を求めるエクセルの関数

上位３つの和を求めるエクセルの関数を教えてください

１００人のテスト９回分のデータがあり、９回のテストの上位３回分のテストの平均を成績としたい。

たとえば、
90,91,80, 70,75,61, 95,88,86 の生徒の場合、(90＋91＋95)/3=92
としたいです。

そのため、上位３つの和を求めるエクセルの関数が書きたいので教えてください。
関数MAX()　では最高点1つを求めることができますが2番目、3番目の得点を求める関数が見つかりません。
よろしくお願いします。

No.4 ベストアンサー 回答者： t_fumiaki 回答日時： 2016/08/09 13:25

d-q-t-p様

=SUM(LARGE(A1:A9,{1,2,3}))の方が簡単ですが、一応書いてみました。

この回答へのお礼

回答ありがとうございます。

なるほど、=SUM(LARGE(A1:A9,{1,2,3}))ですね。

同点も加算する
=SUMIF(A1:A9,">="&LARGE(A1:A9,3))

なるほど。参考になります。

お礼日時：2016/08/09 17:25

No.7 回答者： CoalTar 回答日時： 2016/08/10 09:55

=ROW(1:3) は正確には ={1;2;3} となり

縦に1,2,3と並びます。
=COLUMN(A:C) なら ={1,2,3} で横に並びます。
この質問においてはどちらでもOKですね。
{1;2;3}や{1,2,3}は配列定数といいます。
数学における行列の勉強をしているとわかりやすいと思います

ちなみに配列数式として確定するときに付く{ }は 最初の=の後ろではなく前、つまり
{=A1:C5}
といった具合です(例としては、いまいちですが)
参考まで

この回答へのお礼

解説ありがとうございます。
参考にさせていただきます。

お礼日時：2016/08/14 07:06

No.6 回答者： mike_g 回答日時： 2016/08/09 20:26

[No.2お礼]へのコメント、

》 ROW($1:$3)が理解できず、悩んでいます
何、悩むほどのことではありませぬ。{1,2,3} と同じことですよ。
ROW()関数は行番号を返します。式 =ROW(A1)、ROW(1:1) は 1 を
返すように。

では、任意の列の連続３セル、例えば、範囲 B3:B5 を選択して、数式バー上に式 =ROW(1:3) を入力して、Ctrl+Shift+Enter を「エイヤッ！」と叩き付けてみてください。
ね、セル B3 から下に配列 1、2、3 が返るでしょ？つまり、{1,2,3} という次第。

この回答へのお礼

解説ありがとうございます。

LARGE(A1:I1,ROW($1:$3))
は
LARGE(A1:I1,{1,2,3})
と同じなんですか？　勉強になりました。

=ROW($1:$3) 　ならば　1
={ROW($1:$3)} ならば　{1,2,3}
http://www.excel-list.com/row.html

と理解していたので、
LARGE(A1:I1,{ROW($1:$3)})
と書かなくてはいけないと思っていました。

この記法であれば、上位３回分でなく、５回分や7回分に変更したい場合、簡単に修正できますね。
ありがとうございます。

お礼日時：2016/08/10 06:20

No.5 回答者： CoalTar 回答日時： 2016/08/09 17:37

最終的には平均を求めたいので

=SUMIF(A1:A9,">="&LARGE(A1:A9,3))/COUNTIF(A1:A9,">="&LARGE(A1:A9,3))

XL2007以降なら
=AVERAGEIF(A1:A9,">="&LARGE(A1:A9,3))

>上位３つの和
の解釈が分かれていますが、
=SUM(LARGE(A1:A9,{1,2,3}))/3
でいいのかな

意味が分かっていないなら例題
100,95,90,90,90,80,80,80,80 の生徒の場合の計算方法は？

※ベストアンサーは他の方へ

この回答へのお礼

回答ありがとうございます。

「９回のテストの上位３回分」ですから、
100,95,90
の3回で、平均を計算します。
100,95,90,90,90,80,80,80,95 の生徒の場合
なら、
100,95,95
の3回で、平均を計算します。

つまり、
=SUM(LARGE(A1:A9,{1,2,3}))/3
が今回の希望した関数となると理解しています。

同点も加算する場合は今回は使えませんが、勉強になったのでお礼しました。

お礼日時：2016/08/09 18:10

No.3 回答者： d-q-t-p 回答日時： 2016/08/09 11:54

t_fumiakiさんへ

> =SUMPRODUCT(LARGE(A1:A9,{1,2,3}))
SUMPRODUCTにする必要がないように思います。

この回答へのお礼

回答ありがとうございます。
専門家同士の応答、拝見させてもらっています。

お礼日時：2016/08/09 17:16

No.2 回答者： mike_g 回答日時： 2016/08/09 08:14

いきなり上位３つの平均を求める式です。

J1: =ROUND(AVERAGE(LARGE(A1:I1,ROW($1:$3))),1)
【お断り】上式は必ず配列数式で入力のこと

この回答へのお礼

回答ありがとうございます。
=ROUND(AVERAGE(LARGE(A1:I1,ROW($1:$3))),1)
ROW($1:$3)が理解できず、悩んでいます

お礼日時：2016/08/09 17:14

No.1 回答者： t_fumiaki 回答日時： 2016/08/09 07:42

同点も加算する

=SUMIF(A1:A9,">="&LARGE(A1:A9,3))

同点は加算しない
=SUMPRODUCT(LARGE(A1:A9,{1,2,3}))

この回答へのお礼

回答ありがとうございます。
お礼はNo4です

お礼日時：2016/08/09 17:17