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

panda306

公式如下，暑假有时间再作解读。

=IF(COUNTIFS(P$2:P$1000,">0",$B$2:$B$1000,$B2)=1,P2,IF(P2="","",IF(P2>=PERCENTILE(IF(($B$2:$B$1000=$B2)*(P$2:P$1000>0),P$2:P$1000),85%),IF(P2=MIN(IF(($B$2:$B$1000=$B2)*(P$2:P$1000>PERCENTILE(IF(($B$2:$B$1000=$B2)*(P$2:P$1000>0),P$2:P$1000),85%)),P$2:P$1000)),86,(100*(P2-MIN(IF(($B$2:$B$1000=$B2)*(P$2:P$1000>=PERCENTILE(IF(($B$2:$B$1000=$B2)*(P$2:P$1000>0),P$2:P$1000),85%)),P$2:P$1000)))+86*(PERCENTILE(IF(($B$2:$B$1000=$B2)*(P$2:P$1000>0),P$2:P$1000),100%)-P2))/(PERCENTILE(IF(($B$2:$B$1000=$B2)*(P$2:P$1000>0),P$2:P$1000),100%)-MIN(IF(($B$2:$B$1000=$B2)*(P$2:P$1000>=PERCENTILE(IF(($B$2:$B$1000=$B2)*(P$2:P$1000>0),P$2:P$1000),85%)),P$2:P$1000)))),IF(P2>=PERCENTILE(IF(($B$2:$B$1000=$B2)*(P$2:P$1000>0),P$2:P$1000),50%),IF(P2=MIN(IF(($B$2:$B$1000=$B2)*(P$2:P$1000>=PERCENTILE(IF(($B$2:$B$1000=$B2)*(P$2:P$1000>0),P$2:P$1000),50%)),P$2:P$1000)),71,(85*(P2-MIN(IF(($B$2:$B$1000=$B2)*(P$2:P$1000>=PERCENTILE(IF(($B$2:$B$1000=$B2)*(P$2:P$1000>0),P$2:P$1000),50%)),P$2:P$1000)))+71*(MAX(IF(($B$2:$B$1000=$B2)*(P$2:P$1000<PERCENTILE(IF(($B$2:$B$1000=$B2)*(P$2:P$1000>0),P$2:P$1000),85%)),P$2:P$1000))-P2))/(MAX(IF(($B$2:$B$1000=$B2)*(P$2:P$1000<PERCENTILE(IF(($B$2:$B$1000=$B2)*(P$2:P$1000>0),P$2:P$1000),85%)),P$2:P$1000))-MIN(IF(($B$2:$B$1000=$B2)*(P$2:P$1000>=PERCENTILE(IF(($B$2:$B$1000=$B2)*(P$2:P$1000>0),P$2:P$1000),50%)),P$2:P$1000)))),IF(P2>=PERCENTILE(IF(($B$2:$B$1000=$B2)*(P$2:P$1000>0),P$2:P$1000),15%),IF(P2=MIN(IF(($B$2:$B$1000=$B2)*(P$2:P$1000>PERCENTILE(IF(($B$2:$B$1000=$B2)*(P$2:P$1000>0),P$2:P$1000),15%)),P$2:P$1000)),56,(70*(P2-MIN(IF(($B$2:$B$1000=$B2)*(P$2:P$1000>=PERCENTILE(IF(($B$2:$B$1000=$B2)*(P$2:P$1000>0),P$2:P$1000),15%)),P$2:P$1000)))+56*(MAX(IF(($B$2:$B$1000=$B2)*(P$2:P$1000<PERCENTILE(IF(($B$2:$B$1000=$B2)*(P$2:P$1000>0),P$2:P$1000),50%)),P$2:P$1000))-P2))/(MAX(IF(($B$2:$B$1000=$B2)*(P$2:P$1000<PERCENTILE(IF(($B$2:$B$1000=$B2)*(P$2:P$1000>0),P$2:P$1000),50%)),P$2:P$1000))-MIN(IF(($B$2:$B$1000=$B2)*(P$2:P$1000>=PERCENTILE(IF(($B$2:$B$1000=$B2)*(P$2:P$1000>0),P$2:P$1000),15%)),P$2:P$1000)))),IF(P2>=PERCENTILE(IF(($B$2:$B$1000=$B2)*(P$2:P$1000>0),P$2:P$1000),2%),IF(P2=MIN(IF(($B$2:$B$1000=$B2)*(P$2:P$1000>=PERCENTILE(IF(($B$2:$B$1000=$B2)*(P$2:P$1000>0),P$2:P$1000),2%)),P$2:P$1000)),41,(55*(P2-MIN(IF(($B$2:$B$1000=$B2)*(P$2:P$1000>=PERCENTILE(IF(($B$2:$B$1000=$B2)*(P$2:P$1000>0),P$2:P$1000),2%)),P$2:P$1000)))+41*(MAX(IF(($B$2:$B$1000=$B2)*(P$2:P$1000<PERCENTILE(IF(($B$2:$B$1000=$B2)*(P$2:P$1000>0),P$2:P$1000),15%)),P$2:P$1000))-P2))/(MAX(IF(($B$2:$B$1000=$B2)*(P$2:P$1000<PERCENTILE(IF(($B$2:$B$1000=$B2)*(P$2:P$1000>0),P$2:P$1000),15%)),P$2:P$1000))-MIN(IF(($B$2:$B$1000=$B2)*(P$2:P$1000>=PERCENTILE(IF(($B$2:$B$1000=$B2)*(P$2:P$1000>0),P$2:P$1000),2%)),P$2:P$1000)))),IF(P2=PERCENTILE(IF(($B$2:$B$1000=$B2)*(P$2:P$1000>0),P$2:P$1000),0%),30,(40*(P2-PERCENTILE(IF(($B$2:$B$1000=$B2)*(P$2:P$1000>0),P$2:P$1000),0%))+30*(MAX(IF(($B$2:$B$1000=$B2)*(P$2:P$1000<PERCENTILE(IF(($B$2:$B$1000=$B2)*(P$2:P$1000>0),P$2:P$1000),2%)),P$2:P$1000))-P2))/(MAX(IF(($B$2:$B$1000=$B2)*(P$2:P$1000<PERCENTILE(IF(($B$2:$B$1000=$B2)*(P$2:P$1000>0),P$2:P$1000),2%)),P$2:P$1000))-PERCENTILE(IF(($B$2:$B$1000=$B2)*(P$2:P$1000>0),P$2:P$1000),0%)))))))))

liu2336904

可以往来地球和月亮的火车。。。。厉害，这么多都不会写错

aleaflin

要命了，这要是有几千条数据，随便改一个分数还不跑死

panda306

aleaflin 发表于 2019-7-3 12:04
要命了，这要是有几千条数据，随便改一个分数还不跑死

计算量确实大。

aleaflin

panda306 发表于 2019-7-3 12:12
计算量确实大。

中间有很多重复使用的部分，建议加辅助列（然后隐藏），可以减少计算量。没细看，但感觉至少需要2-3个辅助列，这样以后新增数据和修改数据跑的都会快很多。辅助列可以有效减少甚至不用数组公式

panda306

是的，每个等级分界点，每个等级的最大值和最小值都可以单独做成辅助列。但我有强迫症，^-^

gyx9233

太厉害了，见过最长的公式!

panda306

aleaflin 发表于 2019-7-3 12:16
中间有很多重复使用的部分，建议加辅助列（然后隐藏），可以减少计算量。没细看，但感觉至少需要2-3个辅 ...

是的，各等级分界点，各等级最高分和最低分都可以单独设置为辅助列。

闪闪小红星

真的很佩服，写这么长，逻辑思路还不会出错

panda306

不积跬步，无以至千里。