Question

用excel如何实现如下功能，请大侠们详细指教下，谢谢！！请看下面问题补充。

学月考试成绩出来了，现在我想把我班上的成绩统计一下：对每科成绩计算前40名同学的平均分。我试着用简单的排序做了一下，效果不理想，有什么语句可以实现的，请高手指教。

Answer 1

设你的学生成绩在A列从A2开始，班级人数小于101人，在A102输入
=SUMPRODUCT((A2:A101>=LARGE(A2:A101,40))*A2:A101)/SUMPRODUCT((A2:A101>=LARGE(A2:A101,40))*COLUMN($A$2:$A$101))
公式可以向右填充至需要的科目，注意有40名并列的情况，不是40个学生！

追问

不行，也计算了一下，计算的结果是原始结果的三分之一。

追答

注意根据人数需要修改公式中的A101和$A$101
看看例子，右边是用排序得到的，左边使用上述公式计算得到。
语文 数学 英语 语文 数学 英语
54 70 30 99 99 98
88 58 43 98 99 97
44 52 71 94 98 97
40 94 38 93 98 97
36 60 65 93 96 96
29 37 54 92 95 95
79 45 56 90 94 94
42 30 68 88 94 94
49 32 58 88 93 93
87 59 49 88 92 92
78 99 32 87 91 92
64 82 43 87 91 90
63 96 29 85 90 89
38 99 48 84 90 86
20 46 28 83 89 84
68 48 97 83 88 84
67 33 69 83 85 84
49 89 92 82 83 83
77 40 62 82 83 81
38 51 40 81 82 81
39 83 95 80 82 81
85 80 52 79 80 80
54 70 96 79 79 80
43 98 35 79 78 79
27 66 84 78 77 78
30 26 51 78 77 77
37 95 65 77 76 77
79 50 21 77 74 77
76 73 60 77 74 75
67 42 45 77 73 71
50 51 75 76 73 71
45 29 81 76 72 71
74 48 97 74 70 71
72 52 39 74 70 70
72 29 20 72 66 69
82 33 71 72 66 69
51 45 92 72 65 69
55 50 33 71 64 69
77 57 71 71 62 68
71 32 69 71 60 68
60 74 81 60
60 26 77 81.75 81.17073171 81.925
88 44 83
35 73 79
39 34 37
56 60 67
65 65 38
81 44 32
32 27 56
83 44 65
93 49 69
51 92 48
52 94 77
74 77 43
90 21 24
22 50 55
77 24 49
65 39 93
84 22 84
82 43 27
80 21 71
66 31 70
71 64 89
83 91 67
29 90 26
30 83 80
93 39 48
43 57 26
25 72 32
94 88 52
71 76 33
37 82 20
25 39 77
72 93 81
49 77 49
40 90 42
30 23 84
54 78 98
99 98 40
40 85 69
35 23 39
30 37 86
42 62 94
77 32 46
41 55 78
65 25 68
68 66 62
98 57 47
42 74 21
68 29 97
78 35 56
83 48 90
32 59 48
88 79 80
49 26 41
41 58 38
79 52 34
87 21 40
76 31 21
92 91 94
81.75 81.17073171 81.925

Answer 2

=SUMPRODUCT((A1:A100>=LARGE(A1:A100,40))*A1:A100)/COUNTIF(A1:A100,">="&LARGE(A1:A100,40))
可以处理并列成绩的！并且不需要按三键的！
去试一试吧！！！祝你工作顺利！！！

Answer 3

=SUM(IF(A1:A100>=LARGE(A1:A100,40),A1:A100,0)/40 数组公式，三键！

追问

计算了一下，但是发现一个问题，就是如果第41名的分数跟40名一样的话，第41名的分数同样也会计算在其中，这样也就影响了整体的平均分了，这怎么办？

Answer 4

前40名不一定是40人。
=AVERAGE(IF(RANK(A1:A100,A1:A100)<41,A1:A100))
CTRL+SHIFT+Enter

追问

计算了一下，但是发现一个问题，就是如果第41名的分数跟40名一样的话，第41名的分数同样也会计算在其中，这样也就影响了整体的平均分了，这怎么办？