Question

excel中如何用一个公式计算某班总分前20名的平均分,已经有班级排名，而且有同名次的情况

40个班级所有学生各科目成绩都在一张表上，需要另外建立一个表，统计各个班级前5名、前10名、前15名等各个科目及总分平均分，这其中有排名的相同的学生都要计算进去，例如前20名一共有23个人，这23人都要算进去。 以前尝试了这个办法：假定“语文”在B列，“班级”在I列，第一行为表头，求“一班”“语文”前10名的平均分，公式如下：=AVERAGE(LARGE(IF(I2:I1000="一班",B2:B1000,),ROW(1:10)))公式以CTRL+SHIFT+ENTER三键结束。但无法解决同名次人数超过设定值的情况，而且每次计算都要按三键回车，很麻烦，希望能固定公式，以后只要修改数据原表就能得到第二标结果表格。求高手指教
另外还需要求出总分前5名的学生各个单科的平均分，而不是单科前5名的平均分





Answer 1

1. 加排名列：

   =SUMPRODUCT((C$2:C$50>=C2)/COUNTIF(C$2:C$50,C$2:C$50))

   图示数据到50行，根据自己的数据自行调整。

   

2. 条件平均公式：

   =ROUND(AVERAGEIFS(C2:C1000,D2:D1000,"<=20"),1)

   此公式未加入语文，一般等条件，可自行添加（因为是多条件函数）

   

Answer 2

本来很简单的事情，可以直接给你公式的，偏偏上的图没有行列标号的，重新上个有行列标号的图吧

追问

这样可以吗，这次强调一下，表格中有多个班级，每个班级都要统计前几名的平均分及他们的单科平均分，而且有同名次的情况，公式最好就是统计每个班级中班级名次小于等于某个数字的学生的总分及单科平均分

追答

还是没有完全清楚你的数据表结构，现假设你的总成绩表的工作表名称为：“总表”，在第二个工作表中应该是C3单元格吧，输入公式：
=AVERAGEIFS(总表!$C:$C,总表!$A:$A,A3,总表!$C:$C,">="&LARGE(总表!$C:$C,5))
即可得到“本01”班的前5名总成绩平均数了，不管有多少前5名都统计在内了。
思路上，先算出班级的第5名成绩，然后把该班级大于等于该成绩的分数求平均，应该就是你要的平均数吧。
复制并下拉，即可

追问

是要一个一个班级算？不能设置函数，假设班级班名小于等于5，就是前5名的平均值，但如何选择班级啦，我最先的问题中提到一个方法，是对班级有条件选择，那就是我想要的

追答

那个公式粘贴到D3单元格，复制并下拉即可得到A列对应班级的前5名平均数