office 365 在有并列名次的情况下，班级成绩按人头求平均值

冰皮小饼干

情况是这样：
这是整个年级的成绩单，需要在这份成绩单里，按每个班的任务人数，计算出各科的均分。——比如说103班的任务数是9，那就只算这个班总分前9名同学的各科均分。

• 在有并列名次的前提下，也只算前9个人的各科均分，哪怕第10名的总分和第9名相同也不在均分计算的数据里
• A和B并列且刚好卡在任务数的这个名次上，那么按A的单科计算还是按B的单科计算都行，但需要保证各科均分都是同一位同学。
  

————这是总需求。我说一下我现在遇到的问题。


这张图里L3列的公式就是我目前所使用的公式。但这个公式有个问题就是，如果遇到任务名次有并列的情况下，后面的row函数是自动按降序选择前N名的成绩计算均分的。这就导致前面同学的单科成绩没有被参与计算均分，反而并列同学的单科成绩都被参与到均分计算中。
如图所示，我把“公式求值‘步骤中，参与语文均分计算的成绩按黄颜色标了出来，发现第4名同学的语文成绩没有参与计算。

所以求助大家，我现在用的这个公式能不能改进？如果有其他更好的公式的话，求赐教！！！！！我把简易版的成绩单附在帖子里。

part98

K3=AVERAGE(TAKE(SORT(C2:C36,,-1),9))

冰皮小饼干

part98 发表于 2024-6-23 22:49
K3=AVERAGE(TAKE(SORT(C2:C36,,-1),9))

非常感谢你这个思路，但单科的结果不对……那……是不是可以调整一下take函数的内容？我试试

冰皮小饼干

part98 发表于 2024-6-23 22:49
K3=AVERAGE(TAKE(SORT(C2:C36,,-1),9))

妈呀，我把里面的sor函数换成filter后，居然奇迹般的解决了我的问题，而且各个单科的均分全都保证是同一个人的，结果完全正确啊！！！！太感谢了！！！！！

mjm04

思路与2楼一致；
只是加了 班级 与  前几位 的限制条件；在更改班级与前几位后 可以自动计算

K3单元格：
=SUM(TAKE(SORT(FILTER(FILTER($C$2:$G$36,COUNTIF(K$2,$C$1:$G$1)),$A$2:$A$36=$J$3),1,-1),$P$3))/$P$3
回车，右拉至O3单元格

1786024734

抄2楼公式

1. =AVERAGE(INDEX(TAKE(SORT(FILTER($C:$H,$A:$A=$J$3),,-1),$P$3),,COLUMN(A1)))

复制代码

冰皮小饼干

一楼大神的公式里，单科的结果就不正确了，所以我把里面的sort改成了filter，=AVERAGE(TAKE(FILTER(C:C,($A:$A=103)*($H:$H<=$P3)),9))。但这个公式有一个问题就是，只能在”班级升序排列且总分降序排列“前提下使用。但凡把成绩单按其他顺序排列的话，结果就变了。有没有其他大神有其他的解决办法吖？

part98

冰皮小饼干 发表于 2024-6-24 01:34
一楼大神的公式里，单科的结果就不正确了，所以我把里面的sort改成了filter，=AVERAGE(TAKE(FILTER(C:C,($A ...

=BYCOL(TAKE(SORT(FILTER(C2:G36,A2:A36=J3),1,-1),P3),AVERAGE)
这是103的，如果有多个班级，去重一下做reduce+lamuda就行了

yfan17

1. =AVERAGE(INDEX(TAKE(SORT(FILTER($C:$H,($A:$A=$J$3)),,-1),$P$3),,COLUMN(A$1)))

复制代码

yfan17

语文平均分=AVERAGE({107;102;104;92;94;101;107;96;96})