1列おきのワースト平均3を計算する関数

質問

よろしくお願いします。
下記の様な表でＬ列の求め方は分かりますがＭ列の求め方は次の様な関数を使うとAVERAGE(SMALL(B3:J3,{1,2,3}))とするとC,E,G,I,Kの人数まで計算されます。点数だけのワースト3を求める方法をどうか教えて下さい。
　 A　 　　B　 C　　D　 E　　 F　 G　　　 H　 I　　　J　 K　　　L　　　 M　 　
1　　 　　国語　 　英語　　　数学　　 　社会　　　物理　 　平均　ワースト3
2 クラス 点　人　 点　人　 点　人　 点　人　 　点　人 　　 点 　　平均
3 a組　　70　33　 75　31　 74　32　 73　32　 70　30 　
4 b組　　80　30　 79　28　 76　33　 75　31　 72　32
5 c組　　85　31　 82　30　 75　32　 77　30　 74　31
6 d組　　90　32　 80　30　 77　31　 79　29　 73　31

回答No.2

MackyNo1 · ベストアンサー

例示の各クラスの点というのはクラスごとの平均点なのでしょうか？

その場合、正確には総平均点（加重平均）はクラスごとの平均点の平均とは違うのですがOKでしょうか？

クラスごとの点数の平均やワースト３の平均点を出すなら、以下のような数式のほうがわかりやすいかもしれません。

平均：=AVERAGE(IF($B$2:$K$2="点",B3:K3,""))
配列数式を使わないなら　=SUMIF($B$2:$K$2,"点",B4:K4)/5

ワースト3平均：=AVERAGE(SMALL((IF($B$2:$K$2="点",B3:K3,"")),{1,2,3}))

いずれも配列数式になりますので入力後Ctrl+Shift+Enterで確定してください。

補足

質問が足りなかったのでね。例示してある点数は科目ごとのクラスの平均で示してあります。またＬ列の平均は5科目の平均です。
科目数はまだ多くあり、クラスもまだ多いのですが質問上省略し簡単に例を書きました。解答例 ワースト3平均 AVERAGE(SMALL((IF($B$2:$K$2="点",B3:K3,"")),{1,2,3}))
を書いて試してみたのですが#VALUE！となり目的の答えが出ません。
もし宜しければ再度お願い致します。

回答No.3

noname#204879

A　　 B　 C　 …　…　J　 K　　 L　　　　　 M
1　　　　　 国語　　……　　物理
2　クラス　点　人　…　…　点　人　平均点　ワースト3平均
3　a組　　 70　33　…　…　70　30　　72.4　　　　　 72.4
4　b組　　 80　30　…　…　72　32　　76.4　　　　　 76.4
5　c組　　 85　31　…　…　74　31　　78.6　　　　　 78.6
6　d組　　 90　32　…　…　73　31　　79.8

L3: =SUMPRODUCT((B$2:K$2="点")*(B3:K3))/5
M3: =SMALL(L$3:L$6,ROW(A1))

お礼

私の説明不足もありましたが貴重なご指導ありがとうございました。

回答No.1

mshr1962

平均点=SUMPRODUCT(($B$2:$K$2="点")*($B3:$K3))/5
W3平均=SUMPRODUCT(SMALL(100^($B$2:$K$2="人")*($B3:$K3),{1,2,3}))/3

お礼

質問の仕方が悪かったのかワースト3の平均が出ませんでした。
手を煩わしまして申し訳ございません。
ありがとうございました。