请教对成绩进行等级赋分公式

blackieliu · 发表于 2018-7-19 15:00

浙江选考赋分规则：起点赋分40分，满分100分，共分21个等级，每个等级分差为3分。

比如某个学生如果他的成绩排名在当次考试的前1%，那么按照等级赋分方案，属于1等级，他对应赋分为100分;

如果他的成绩排名在当次考试的前4%，属于3等级，对应赋分为94分;

如果他的成绩排名在当次考试的前10%，属于4等级，对应赋分为91分。

求大侠指点该怎么写这个赋分的公式

21等赋分.rar (20.89 KB, 下载次数: 386)

fcythz · 发表于 2018-7-19 15:30

考试排名人数按多少算?实际参加考试的人数还是总人数?

fcythz · 发表于 2018-7-19 16:09

先排名次,再赋分,比如名次列在P列,计算第三行的赋分值,公式为:
=IF(B4="","",IF(100-(ROUND(P4/625,2)*100-IF(ROUND(P4/625,2)*100=0,0,1))*3<40,"",100-(ROUND(P4/625,2)*100-IF(ROUND(P4/625,2)*100=0,0,1))*3))

0星剑0 · 发表于 2018-7-19 17:16

I2单元格输入，可以右拉下拉。

=IF(B2="","",LOOKUP(RANK(B2,B$2:B$626)/COUNT(B:B),{0,1,4,7,11,16,22,29,37,44,51,58,65,72,79,85,90,94,97,99}%,{100,97,94,91,88,85,82,79,76,73,70,67,64,61,58,55,52,49,46,43,40}))

复制代码

画错了 · 发表于 2018-7-19 17:51

写好公式，要做两列辅助列，你看一下对不对，不知道我的图片上传成功了没有

我走我流 · 发表于 2018-7-19 18:09

空值算人数么？

blackieliu · 发表于 2018-7-19 18:21

我走我流发表于 2018-7-19 18:09
空值算人数么？

空值不算人数

micch · 发表于 2018-7-19 18:22

你这附件怎么排名次？有的考两门有的考三门？

我走我流 · 发表于 2018-7-19 19:10

本帖最后由我走我流于 2018-7-19 19:14 编辑

是这个意思么LOOKUP{RANK(B2,$B$2:$B$626,0)/COUNTA($B$2:$B$626),{1,3,...100}/100,{100,97...40}}思路是先求相对等级排名与总人数的比率，再与已知值比较，最后得出答案

blackieliu · 发表于 2018-7-20 11:13

fcythz 发表于 2018-7-19 16:09
先排名次,再赋分,比如名次列在P列,计算第三行的赋分值,公式为:
=IF(B4="","",IF(100-(ROUND(P4/625,2)*100 ...

用你这个公式，政治有187个人，排名后计算结果到136行就出现空值了单元格里有公式但没结果

		自动登录	找回密码
密码			免费注册

[求助] 请教对成绩进行等级赋分公式