在每个分类下计算满足条件的数据的排名
例题描述和简单分析
有 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