在每个分类下计算满足条件的数据的排名

例题描述和简单分析

有 Excel 文件 book1.xlsx,数据如下所示:

学号 姓名 班级 是否有缺考 总成绩 班级排名
201800001 AA1 8(1) 590
201800002 AA2 8(1) 583
201800003 AA3 8(1) 599
201800004 AA4 8(1) 500
201800005 AA6 8(1) 580
201800006 AA7 8(2) 598
201800007 AA8 8(2) 580
201800008 AA9 8(2) 570
201800009 AA10 8(2) 490
201800010 AA11 8(2) 593
201800013 AA14 8(2) 585
201800015 AA16 8(3) 595
201800016 AA17 8(3) 582
201800017 AA18 8(3) 563
201800018 AA19 8(3) 575
201800019 AA20 8(4) 587
201800020 AA21 8(4) 571
201800021 AA22 8(4) 590

现需要对无缺考的学生按班级将成绩进行排名,结果如下:

学号 姓名 班级 是否有缺考 总成绩 班级排名
201800001 AA1 8(1) 590 2
201800002 AA2 8(1) 583 3
201800003 AA3 8(1) 599 1
201800004 AA4 8(1) 500
201800005 AA6 8(1) 580 4
201800006 AA7 8(2) 598 1
201800007 AA8 8(2) 580 4
201800008 AA9 8(2) 570 5
201800009 AA10 8(2) 490
201800010 AA11 8(2) 593 2
201800013 AA14 8(2) 585 3
201800015 AA16 8(3) 595 1
201800016 AA17 8(3) 582 2
201800017 AA18 8(3) 563 4
201800018 AA19 8(3) 575 3
201800019 AA20 8(4) 587 2
201800020 AA21 8(4) 571 3
201800021 AA22 8(4) 590 1

解法及简要说明

Excel中加载插件 ExcelRaq.xll 后。

在 Excel 中,选中 F2:F19,输入表达式:

解法1

=esproc("=(a=?.group(~(3)).run(~=~.(if(~(4)!=""是 "",~(5)))),a.conj(~.(if(~==null,null,a.~.select(~!=null).rank@z(~)))))",A2:E19),按下组合键 ctrl+shift+enter,即可获得计算结果。

简要说明:变量 a 为按班级分组后的总成绩列(序列的序列),其中缺考的总成绩值为 null。对每个班级(第一层序列)下的未缺考总成绩(第二层序列中的非空数据)排名。

解法2

=esproc("=(a=?.new(~(3):clz,if(~(4)==""是 "",-1,~(5)):grade),b=a.psort(clz,-grade),c=a(b).(if(grade>0,rank(grade;clz))),c(b.psort()))",A2:E19) ,按下组合键 ctrl+shift+enter,即可获得计算结果。

简要说明:变量 a 为序表,clz 字段是班级,grade 字段是总成绩(缺考的话值为 -1);变量 b 为变量 a 按 clz 升序,grade 降序排序后的结果在变量 a 中的序号位置序列;变量 c 为变量 a 按 clz 升序,grade 降序排序后,对大于 0 的 grade 按 clz 进行组内排名的结果;变量 c 按排序前位置重排。

问答搜集

http://club.excelhome.net/thread-1572848-1-1.html