求各班级总分前10，20名各科均分

故乡123

libo5563 发表于 2020-2-5 20:38
pq写确实麻烦，这种计算尽量用pp来处理吧

老师，感谢，感谢！！

故乡123

dSYWW 发表于 2020-2-6 11:08
总分一样的会排名一样，顺便弄了个动态查询的，看下是否需要的，仅供参考

感谢感谢，就是没看见公式！

故乡123

丫头的 发表于 2020-2-5 17:12
稍微长了点，将就用吧

非常感谢，公式比较好用，不太复杂，敬佩！！！！

故乡123

丫头的 发表于 2020-2-5 17:12
稍微长了点，将就用吧

非常感谢，公式比较好用，不太复杂，敬佩！！！！

故乡123

Excellover 发表于 2020-2-5 20:35
上面的计算不符合问题的要求，我建议用数据透视表来完成。这样比较容易理解。

大师就是不一样，佩服佩服！！！！非常感谢！！

故乡123

wodewan 发表于 2020-2-5 17:53
=SUM(LARGE(N($B$2:$B$187=INT((ROW(A1)-1)/3+1)&"班")*C$2:C$187,ROW(INDIRECT("1:"&MID($C191,2,2)))))/M ...

非常感谢您的解答！！

dSYWW

故乡123 发表于 2020-2-6 23:00
感谢感谢，就是没看见公式！

是用PQ做的，直接点击刷新就可以，具体步骤或者M语言可以点击数据，显示查询里点开看

youseehawk

毛利的乖乖熊 发表于 2020-2-5 21:05
D191单元格输入数组公式：
=AVERAGE(LARGE(C$2:C$187*($B$2:$B$187=T(OFFSET($B$191,3*INT((ROW(A1)-1)/3) ...

毛利的乖乖熊 解法：
=AVERAGE(LARGE(C$2:C$187*($B$2:$B$187=OFFSET($B$191,3*INT((ROW(A1)-1)/3),)),ROW(INDIRECT("1:"&MID($C191,2,2)))))

思路：
1 用Int及Row函数控制班级的选择（跳行选择参数）—— 3*INT((ROW(A3)-1)/3
2 用Offset函数选择班级（每3行跳至下一个班级）；
3 用数组相乘方式求出该班的该科目成绩（数组）；
4 用Mid函数得到需要计算的名次（前10 or 前20 or 前30）；
5 用Indirect函数转换，得到前1:X行；
6 用Row函数得到1-X数组；
7 用Large函数计算前X名，其中Large的array参数与k参数均为数组；
8 用AVERAGE函数求前X名的平均分

商榷：T函数是否可以取消？我在Excel 365上运行时无T函数也可正常计算。

谢谢

xiou1023

Excellover 发表于 2020-2-5 20:35
上面的计算不符合问题的要求，我建议用数据透视表来完成。这样比较容易理解。

要求的是以总分排名的前10 前20 前30 各科成就，而上面做的是各学科的前10 前20 前30 不符合要求

xiou1023

dSYWW 发表于 2020-2-6 11:08
总分一样的会排名一样，顺便弄了个动态查询的，看下是否需要的，仅供参考

大神，求公式