按班级总分的前90%计算各门学科的平均分

hhjjpp

相信运算速度有一比！

CheryBTL

寒舍痴人 发表于 2012-3-3 22:46
这公式觉得还有点小问题，我算的另外三科的平均分加起来不等于总的平均分，请看：

呵呵 是的 这个公式是计算每科前90%的平均分，
15楼的公式才是最终的公式：
=AVERAGE(IF(($B$2:$B$2650=$G2)*($F$2:$F$2650>=LARGE(IF($B$2:$B$2650=$G2,$F$2:$F$2650),INT(COUNT(0/($B$2:$B$2650=$G2))*90%))),C$2:C$2650))

ex121

hhjjpp 发表于 2012-3-3 23:34
相信运算速度有一比！

大侠能讲解一下原理吗？

hhjjpp

ex121 发表于 2012-3-4 10:43
大侠能讲解一下原理吗？

首先按要求进行筛选，标准是每班总分排名前90%
为便于识别班次，在总分前加上班次得到h列分值。
前面为101的就是101班，在i列的全校大排名中，自然101班都排在一起了，比101班次大的自然就排在前面，那101班前面有多少排名（同学）呢，我们在m列计算（l-r列的数据与k列的班次行行相对应）。为避免重复运算，我们先在l列算出每班的人数，以此为依据累计班次大于101班的同学，以此类推

hhjjpp

接着在o列算出班上90%的人数（也是为了他处便于引用中间计算结果），那101班截止的排名就可在n列算出，那101班排名就在m2与n2之间，就可直接用相减的sumif算出90%的各班学科总分，p-r汇总的目标是一样只是求和的科目不同而已
为了解决并列rank排名的问题，在j列对i列的排名进行调整。简单地说，如果有3个114名，最上面行的保持排名114，第2个则人为排成115，第3个则为116，这样就消除了rank函数排名结果从114直接跳到名次117了

hhjjpp

总之一步一个脚印，决不把同一计算结果反复运算，运算有组织，讲究效率，拒绝重复劳动，保证运算速度，数以千计的数据就很容易看到明显效果
请注意我已更新了附件o列公式，少了一群不必要的countif运算，相信效率更高

ex121

感谢hhjjpp老师的热情讲解，您的思路待我慢慢琢磨。{:soso_e181:}

丹山曾宁

这是我用的：=IF(G4="","",AVERAGE(IF((成绩!$A$4:$A$1002=B4)*(成绩!$M$4:$M$1002>=IF(ISERROR(LARGE(IF(成绩!$A$4:$A$1002=B4,成绩!$M$4:$M$1002,),G4)),"",LARGE(IF(成绩!$A$4:$A$1002=B4,成绩!$M$4:$M$1002,),G4)))*(成绩!$D$4:$D$1002>0),成绩!$D$4:$D$1002)))

丹山曾宁

=IF(G4="","",AVERAGE(IF((成绩!$A$4:$A$1002=B4)*(成绩!$M$4:$M$1002>=IF(ISERROR(LARGE(IF(成绩!$A$4:$A$1002=B4,成绩!$M$4:$M$1002,),G4)),"",LARGE(IF(成绩!$A$4:$A$1002=B4,成绩!$M$4:$M$1002,),G4)))*(成绩!$D$4:$D$1002>0),成绩!$D$4:$D$1002))) 数组公式

Michael.邓

=AVERAGEIFS(C:C,F:F,">="&LARGE(F:F,90)) 这是语文，其余同理