在总表中求出某一个班总分前十五个学生语文学科的人均分

范传光

在总表中求出某一个班总分前十五个学生的语文学科的人均分． 不是前十五名，而是从高分到低分前十五个．因为要排除第十六个人与第十五个人并列的情况．我做了很多天，总是出错，请各位帮助修改一下，不胜感谢．要求：
１、如果第十六个人与第十五个人并列，则舍去之．
2、不对原表排序。
3、不增加辅助列。
4、不用班名次和年级名次，因为要排除因名次并列产生的结果不准确的情况．
5、用嵌套函数一步完成．


并附上总表和我的公式说明．请指正．

summer.linn

=AVERAGE(LARGE(范围,ROW(1:15))),数组公式

范传光

范传光

求总分前十五名(从高分到低分十五个人)学生语文学科的人均分－请教表

jing716

=SUM(LARGE(IF(B3:B114="九（2）",D3:D114),ROW(1:15)))/15
这样??

范传光

summer.linn 发表于 2014-1-21 18:47
=AVERAGE(LARGE(范围,ROW(1:15))),数组公式

全年级有３个班，且分数已在同一张统计表上．
=AVERAGE(LARGE(范围,ROW(1:15))),数组公式这个公式用于单独一个班级的成绩统计表中则可，用于总表，恐怕不行

jing716

范传光 发表于 2014-1-21 19:04
全年级有３个班，且分数已在同一张统计表上．
=AVERAGE(LARGE(范围,ROW(1:15))),数组公式这个公式用于单 ...

=SUM(LARGE(IF((Y3:Y114>=LARGE(IF(B3:B114="九（2）",Y3:Y114),15))*(B3:B114="九（2）"),D3:D114),ROW(1:15)))/15  数组公式
更改班级称即可得别的班级语文,这样??

dengjq2000

思路，先把每班总分由高到低前十五位的数据提出来。

通过SQL语句来实现：

1. select top 15 班级,姓名,语文,数学,英语,物理,化学,政治,历史,总分 from [Sheet1$A2:AA200] where 班级="九（1）" order by 班级,总分 desc union all
   
2. select top 15 班级,姓名,语文,数学,英语,物理,化学,政治,历史,总分 from [Sheet1$A2:AA200] where 班级="九（2）" order by 班级,总分 desc union all
   
3. select top 15 班级,姓名,语文,数学,英语,物理,化学,政治,历史,总分 from [Sheet1$A2:AA200] where 班级="九（3）" order by 班级,总分 desc

复制代码

具体见工作表：提取有效数据源 的内容

dengjq2000

具体SQL操作可视操作为：

dengjq2000

有了有效数据，接下来就挺方便了，可以借助数据透视表来实现。

SQL语句还是原来语句，只是直接生成数据透视表而矣。
具体见8楼附件的工作表：“数据透视表”

可视操作为：