计算文科前45名和理科前140名平均分

廖月

计算文科前45名和理科前140名平均分，老师们，能用公式把其他空填满吧？附件中数据我是慢慢数出来的。

yjh_27

自定义函数可以吗

新手路上

C3单元格输入公式，三键完成输入，右拉，下拉至H4单元格

1. =ROUND(SUMPRODUCT(LARGE(IF(文科!$B:$B=$A3,文科!D:D,),ROW($1:$45)))/45,)

复制代码

C5单元格输入公式，三键完成输入，右拉

1. =ROUND(SUMPRODUCT(LARGE(文科!D:D,ROW($1:$45)))/45,)
   
2. C8单元格输入公式，三键完成输入，右拉，下拉至H13单元格[code]=ROUND(SUMPRO

复制代码

DUCT(LARGE(IF(理科!$B:$B=$A8,理科!D:D,),ROW($1:$45)))/45,)[/code]
C14单元格输入公式，三键完成输入，右拉

1. =ROUND(SUMPRODUCT(LARGE(理科!D:D,ROW($1:$45)))/45,)

复制代码

新手路上

我不知道对不对哈，随便写的，仅供参考

廖月

yjh_27 发表于 2019-1-11 21:24
自定义函数可以吗

老师好，我们学校电脑表格是简装版，没有宏功能，太遗憾了，打开全是乱码。

廖月

新手路上 发表于 2019-1-11 22:17
我不知道对不对哈，随便写的，仅供参考

老师，好像不行。

新手路上

廖月 发表于 2019-1-12 09:38
老师，好像不行。

思路就是返回数学那一列，求出最大的45个数字，求和，除以45。不知道我这个思路对不对哈。你可以参考一下我的那个公式再完善一下，都是很简单的公式。如果你实在是不会，再回复我。

xinyi_lulu

={SUM(IF(文科!D2:D134>=LARGE(IF(文科!B2:B134=5,文科!D2:D134),45),IF(文科!B2:B134=5,文科!D2:D134,0)))}
用数组公式可以解决，我只是计算了5班文科数学 ，其他模仿填上即可，经手动计算验证是正确的。
公式分析：用if函数来解决，如果该区域内数字大于前45名，就返回该区域的成绩，然后求和。
IF(文科!B2:B134=5,文科!D2:D134),这一个公式实现挑选班出5班的文科的学生成绩。
再用LARGE(IF(文科!B2:B134=5,文科!D2:D134),45),求出上一步挑选的5班的文科的前45名中第45名的学生的分数 ，即使有多个重复的也可以选对。
IF(文科!D2:D134>=LARGE(IF(文科!B2:B134=5,文科!D2:D134),45),IF(文科!B2:B134=5,文科!D2:D134,0))这一步，返回5班文科中大于45名的所有的成绩。
={SUM(IF(文科!D2:D134>=LARGE(IF(文科!B2:B134=5,文科!D2:D134),45),IF(文科!B2:B134=5,文科!D2:D134,0)))}实现对上一步返回的数学成绩求和。这是数组公式，最后需要按下ctrl+shift+enter键完成录入。
我只填个了一个公式，根据这个原理，其他的单元格可以稍加改动完成。我把原表数据复制了一个工作表，进行了手动排序计算，与我公式计算结果相同。出现相同名次的多个学生也能够完成计算。

xinyi_lulu

={SUM(IF(文科!D2:D134>=LARGE(IF(文科!B2:B134=5,文科!D2:D134),45),IF(文科!B2:B134=5,文科!D2:D134,0)))}
思路：以数学为例
先用IF(文科!B2:B134=5,文科!D2:D134),选出5班文科的学生
LARGE(IF(文科!B2:B134=5,文科!D2:D134),45),选出来5班文科学生的第45名的数学成绩
IF(文科!D2:D134>=LARGE(IF(文科!B2:B134=5,文科!D2:D134),45),IF(文科!B2:B134=5,文科!D2:D134,0))用这个公式返回文科5班的大于45分的全部数学成绩
={SUM(IF(文科!D2:D134>=LARGE(IF(文科!B2:B134=5,文科!D2:D134),45),IF(文科!B2:B134=5,文科!D2:D134,0)))}把上一步返回的成绩求和.
根据情况改造其他公式就可以完成其他统计。
注意是数组公式，公式录入后需要按ctrl+shift+回车。