Question

excel求最大n个数值的平均数问题

一所学校所有班级的各科成绩在一个工作表上，每个班级都有不同的编号，问题1：怎样用一个公式或函数求出一个班前20名同学的平均成绩。问题2：能否根据这一个工作表，把各个班级的成绩分到不同的工作表内，即一班一个工作表。求指点！在线等。

Answer 1

以下实现了第一步，求得每个班的任何科目的前20名平均成绩。第二步分到各个工作表建议用VBA语句实现，或者用已有网友建议的作法。

步骤如下：先用LARGE函数得到前20名的成绩点(A)；再求得所有大于等于这个成绩点的总成绩（B）,B用了SUM数组函数；再求得大于等于这个成绩点的学生总数（C)，C也是用SUM数组函数求得的，则(B-(C-20)*A)/20。

求得数值区域是用OFFSET+MATCH, 所以必须要求每个班的记录不得间断，现在OFFSET设的行数的限制是30人，如果超过30人，则需要修改公式，将常量30改成变量，变量值可以用COUNTIF求得。好想被悬赏啊！

如下图H2的公式为：=(SUM((OFFSET($A$1,MATCH($F2,A:A,0)-1,MATCH($G2,$A$1:$D$1,0)-1,30)>=LARGE(OFFSET($A$1,MATCH($F2,A:A,0)-1,MATCH($G2,$A$1:$D$1,0)-1,30),20))*(OFFSET($A$1,MATCH($F2,A:A,0)-1,MATCH($G2,$A$1:$D$1,0)-1,30)))-(SUM((OFFSET($A$1,MATCH($F2,A:A,0)-1,MATCH($G2,$A$1:$D$1,0)-1,30)>=LARGE(OFFSET($A$1,MATCH($F2,A:A,0)-1,MATCH($G2,$A$1:$D$1,0)-1,30),20))*1)-20)*LARGE(OFFSET($A$1,MATCH($F2,A:A,0)-1,MATCH($G2,$A$1:$D$1,0)-1,30),20))/20 ， 数组公式，别忘了最后加上大括号（CTRL+SHIFT+ENTER)

Answer 2

1、假设成绩在A列：
=AVERAGEIF(A:A,">="&LARGE(A:A,20))
2、如果表格设计规范的话，用数据透视表可以很快按要求的分出多个表格。

追问

问题1的意思为在30个学生成绩中筛选出前20个最好成绩求平均值。你的公式不管用啊。
不管如何谢谢你。

追答

开始没看到图。既然有排名列，直接使用即可：
=AVERAGEIF(H:H,"<=20",G:G)
分班那个，你表格中并没有班级，无法提供详细步骤。

Answer 3

步骤如下：1、计算每个学生的总分；2、以班编号为第一关键字，总分为第二关键字排倒序；3、假设班编号在A列，总分在J列，平均分在K列，在K2写入公式：=IF(A1<>A2,AVERAGE(J2:J21),0)，复制至全列；4、筛选出K列非0的行就是各班前20名平均分；5、按班数量插入新表；6、以班编号为关键字筛选出各班成绩，分别复制到新表。

Answer 4

怎么知道哪些人是同一个班的呢?
考号有规则吗?