广东省新高考3+1+2政治、化学、地理、生物等级赋分公式

panda306

panda306 发表于 2019-7-21 13:32

1. =ROUND(TREND(INDEX({100,86;85,71;70,56;55,41;40,30},MATCH(RANK(A2,A:A)/COUNT(A:A),{0,15,50,85,98}%),),INDEX(IF({1,0},LARGE(A:A,COUNTIF(A:A,">="&LARGE(A:A,INT(COUNT(A:A)*{0;0.15;0.5;0.85;0.98;1})))+1),LARGE(A:A,INT(COUNT(A:A)*{15;50;85;98;100}%))),MATCH(RANK(A2,A:A)/COUNT(A:A),{0,15,50,85,98}%),),A2),)

复制代码

panda306

panda306 发表于 2019-7-21 13:33

以上是参考各路大神的公式后重新编写的公式。

panda306

panda306 发表于 2019-7-21 13:33

以上是参考各路大神的公式后重新编写的公式。

panda306

LARGE(A:A,COUNTIF(A:A,">="&LARGE(A:A,INT(COUNT(A:A)*{0;0.15;0.5;0.85;0.98;1})))+1)求出原始分各等级区间的最高分，LARGE(A:A,INT(COUNT(A:A)*{15;50;85;98;100}%))原始分各等级区间的最低分，再用IF({1,0},构造数组。MATCH(RANK(A2,A:A)/COUNT(A:A),{0,15,50,85,98}%)则用于确定A2处于哪个区间。最后将A2在所属的区间进行赋分。

panda306

用这条公式和用percentile做出来时的结果是完全一致的。与51楼公式不同的是分界点的划分。

1. =IF(A2="","",ROUND(TREND(INDEX({100,86;85,71;70,56;55,41;40,30},MATCH((RANK(A2,A:A)-1)/COUNT(A:A),{0,15,50,85,98}%),),INDEX(IF({1,0},LARGE(A:A,COUNTIF(A:A,">="&LARGE(A:A,COUNT(A:A)*{0;15;50;85;98;100}%))+1),LARGE(A:A,COUNT(A:A)*{15;50;85;98;100}%)),MATCH((RANK(A2,A:A)-1)/COUNT(A:A),{0,15,50,85,98}%),),A2),))

复制代码

panda306

用percentile重做的数组公式，纯属娱乐。

1. =IF(A2="","",ROUND(IF(A2>=PERCENTILE(A:A,85%),IF(MIN(IF(A:A>=PERCENTILE(A:A,85%),A:A))=PERCENTILE(A:A,100%),(100+86)/2,IF(A2=PERCENTILE(A:A,100%),100,IF(A2=MIN(IF(A:A>=PERCENTILE(A:A,85%),A:A)),86,(100*(A2-MIN(IF(A:A>=PERCENTILE(A:A,85%),A:A)))+86*(PERCENTILE(A:A,100%)-A2))/(PERCENTILE(A:A,100%)-MIN(IF(A:A>=PERCENTILE(A:A,85%),A:A)))))),IF(A2>=PERCENTILE(A:A,50%),IF(MIN(IF(A:A>=PERCENTILE(A:A,50%),A:A))=MAX(IF(A:A<PERCENTILE(A:A,85%),A:A)),(71+85)/2,IF(A2=MAX(IF(A:A<PERCENTILE(A:A,85%),A:A)),85,IF(A2=MIN(IF(A:A>=PERCENTILE(A:A,50%),A:A)),71,(85*(A2-MIN(IF(A:A>=PERCENTILE(A:A,50%),A:A)))+71*(MAX(IF(A:A<PERCENTILE(A:A,85%),A:A))-A2))/(MAX(IF(A:A<PERCENTILE(A:A,85%),A:A))-MIN(IF(A:A>=PERCENTILE(A:A,50%),A:A)))))),IF(A2>=PERCENTILE(A:A,15%),IF(MIN(IF(A:A>=PERCENTILE(A:A,15%),A:A))=MAX(IF(A:A<PERCENTILE(A:A,50%),A:A)),(56+70)/2,IF(A2=MAX(IF(A:A<PERCENTILE(A:A,50%),A:A)),70,IF(A2=MIN(IF(A:A>=PERCENTILE(A:A,15%),A:A)),56,(70*(A2-MIN(IF(A:A>=PERCENTILE(A:A,15%),A:A)))+56*(MAX(IF(A:A<PERCENTILE(A:A,50%),A:A))-A2))/(MAX(IF(A:A<PERCENTILE(A:A,50%),A:A))-MIN(IF(A:A>=PERCENTILE(A:A,15%),A:A)))))),IF(A2>=PERCENTILE(A:A,2%),IF(MIN(IF(A:A>=PERCENTILE(A:A,2%),A:A))=MAX(IF(A:A<PERCENTILE(A:A,15%),A:A)),(41+55)/2,IF(A2=MAX(IF(A:A<PERCENTILE(A:A,15%),A:A)),55,IF(A2=MIN(IF(A:A>=PERCENTILE(A:A,2%),A:A)),41,(55*(A2-MIN(IF(A:A>=PERCENTILE(A:A,2%),A:A)))+41*(MAX(IF(A:A<PERCENTILE(A:A,15%),A:A))-A2))/(MAX(IF(A:A<PERCENTILE(A:A,15%),A:A))-MIN(IF(A:A>=PERCENTILE(A:A,2%),A:A)))))),IF(PERCENTILE(A:A,0%)=MAX(IF(A:A<PERCENTILE(A:A,2%),A:A)),(30+40)/2,IF(A2=MAX(IF(A:A<PERCENTILE(A:A,2%),A:A)),40,IF(A2=PERCENTILE(A:A,0%),30,(40*(A2-PERCENTILE(A:A,0%))+30*(MAX(IF(A:A<PERCENTILE(A:A,2%),A:A))-A2))/(MAX(IF(A:A<PERCENTILE(A:A,2%),A:A))-PERCENTILE(A:A,0%))))))))),))

复制代码

panda306

了解更多教务方面的解决方案，请查看另一个主题：Excel在教务中的应用

luoqiu123456

panda306 发表于 2019-7-12 08:27
综合了各位朋友的建议，对赋值公式进行了调整，区间的最高分，最低分和等级都做成辅助列形式，大大简化了公 ...

非常感谢  刚刚好成绩统计要用到  我是福建的   

geryjun

厉害，正需要，谢谢分享。

zeyaots

能不能把浙江赋分公式也贡献一下？