至急 下位６個の平均を求めるEXCEL関数について

質問

１年の成績の中で下位６回の成績の平均を求めるEXCEL関数を作成したいのですが、
６回以上の成績が有る方は、その中の下位６回を選択し、問題無く平均を求める事が出来たのですが
１年を通じて６回の成績が無い方、つまり、１回以上、６回未満しか成績が無い方の場合、「#NUM」エラーとなってしまいます。

求めたい平均値は、１年の成績の中で下位６回の成績の平均値を求めたいのですが、
１回以上６回未満の成績が出ていない方に関しては、その回数に応じた平均値を求めたのですが、どのように関数を組めば宜しいでしょうか。

作成した関数は以下になります。

=ROUND(AVERAGE(SMALL(B3:M3,{1,2,3,4,5,6})),0)

上記ですと６回以上成績がる場合、問題無く平均値が求められましたが、１回以上、６回未満しか成績が無い方の場合、「#NUM」エラーとなりました。

至急、作成しなければならない事もあり、EXCEL関数が得意な方、ご教示頂けないでしょうか。
ちなみにEXCELは、EXCEL97-2003バージョンになります。

宜しくお願い致します。

回答No.8

imogasi

お急ぎとのことで、遅れてきた回答ですが。
すでに出ているご回答でうまく行きましたか。それらの回答の結果が正しくても、私の仕事経験では、可視化して、この結果でよいという、自分なりの確信がほしいものです。
私はすでに出ている（関数の）回答で、いろんな特殊ケースもうまくゆくかテストする力や時間はありません。しかし
本当に学校や塾などの点数なら、間違うと影響が大きく、慎重にテストし、確信がないと、後で間違っていたは済まないものです。
それで関数だけ使うばかりの方法ではない（また作業列を使う）ですが、下記を記してみます。
初めは、関数では式が複雑になり、それに対処できそうなVBAを使う方法をやり始めたのですが、ソート（データの並べ替え）を使わないと、どうも処理が複雑になると思って、下記に至りました。
これでもし間違っている場合も、ソート後のデータを見て、それなりに改善点が思いつくと思います。
ーー
まず原データはA列に名前、B列-M列に１２回の点数があるとします。
これをN列ーZ列にコピー貼り付けします。
そしてO列ーZ列を「列でソート」します。
同一人内で成績の低いもの順です。
１２回に満たないものも右の方は空白セルのままで、COUNT関数で無視してくれるてんが都合よい。
普通よくやるのは「行ソート」ですが、この「列ソート」は、VBAでは、下記に当たります。
Sub test02()
lr = Range("A100").End(xlUp).Row
MsgBox lr
For i = 2 To lr
Range("O" & i & ":Z" & i).Sort Key1:=Range("O" & i), Order1:=xlAscending, Orientation:=xlSortRows
Next i
End Sub
エクセルの手の操作でもできます。
ーー
その後、O列から６列に限っての
件数として　AA列に　=COUNT(O2:T2)　空白セルは数えない
合計として　AB列に　=SUM(O2:T2)　　　空白セルは数えない
下位平均として　AC列に　=AB2/AA2　
これに「丸め」の関数ROUNDなどを（また小数点以下の位数）お好みでかぶし被せてください。
上記の式は下方向に複写する。
データ的に（受験していなくて）空白になっているセル（列）の扱いもこれでうまく行くと思いますがどうですか。
VBAで上記のことをすべてやれますが略。

回答No.7

Chiquilin

=ROUND(AVERAGE(IF(COUNT(B3:M3)<6,B3:M3,SMALL(B3:M3,{1,2,3,4,5,6}))),0)

でいいんじゃないですか？

回答No.6

bunjii

>１年を通じて６回の成績が無い方、つまり、１回以上、６回未満しか成績が無い方の場合、「#NUM」エラーとなってしまいます。
提示の数式（下記）ではSMALL関数の戻り値に#NUMが含まれるためそのままエラー値となるようです。
IF関数で条件分岐することでエラーを回避できます。
IF関数の条件式はCOUNT(B3:M3)>5で良いと思います。
数値のセル数が5より大きいときは提示の数式とし、5以下のときはROUND(AVERAGR(B3:M3))で良いはずです。
但し、1回もテストを受けていない人がいる場合は更にIF関数で分岐し、返す値を""にすれば良いでしょう。

回答No.5

mshr1962

No.4です。
単純に6回以内ってことで分ければいいのに気づきました。
=IF(COUNT(B3:M3)>6,ROUND(AVERAGE(SMALL(B3:M3,{1,2,3,4,5,6})),0),ROUND(AVERAGE(B3:M3),0))
で計算できますね。

回答No.4

mshr1962

配列数式にする必要があるけど。。。
=ROUND(AVERAGE(SMALL(B3:M3,CHOOSE(MIN(COUNT(B3:M3),6),1,{1,2},{1,2,3},{1,2,3,4},{1,2,3,4,5},{1,2,3,4,5,6}))),0)
入力後にCTRL+SHIFT+ENTERで配列数式にしてください。

回答No.3

mdmp2

スマートな方法を考えているのですが時間がかかりそうです。（時間をかけても見つからないかもしれません。）
お急ぎのようですから、とりあえず、
IF(Count(B3:M3)>=6,ROUND(AVERAGE(SMALL(B3:M3,{1,2,3,4,5,6})),0),Count(B3:M3)=5,ROUND(SUM(B3:M3)/5,0),Count(B3:M3)=4,ROUND(SUM(B3:M3)/4,0),Count(B3:M3)=3,ROUND(SUM(B3:M3)/3,0),Count(B3:M3)=2,ROUND(SUM(B3:M3)/2,0),Count(B3:M3)=1,SUM(B3:M3),0)

としてみてください。
式は、この回答の中で組み立てましたので対応する()がなかったり、式自体が間違っているかもしれません。
Count関数で入力されている数値の個数をかぞえ、IF関数と組み合わせて6個以上の場合は貴殿作成の関数、5個の場合は合計した値を5で割り、4個の場合は・・・と展開しているだけです。

回答No.2

kagakusuki

例えば、下から数えて5番目に悪い成績の人が5人いて、その次の下から数えて6番目に悪い成績の人が2人いる場合などには、下位7名の成績の平均を求めれば宜しいのでしょうか?
　もしそれで宜しければ次の様な関数となります。

=IF(COUNT(B3:M3),ROUND(SUMIF(B3:M3,"<="&SMALL(B3:M3,MIN(COUNT(B3:M3),6)))/COUNTIF(B3:M3,"<="&SMALL(B3:M3,MIN(COUNT(B3:M3),6))),0),"")

回答No.1

f272

こんな感じ？
=ROUND(AVERAGE(IF(ISERROR(SMALL(B3:M3,{1,2,3,4,5,6})),"",SMALL(B3:M3,{1,2,3,4,5,6}))),0)