本帖最后由 panda306 于 2019-7-3 21:08 编辑
公式如下,暑假有时间再作解读。
=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%))))))))) |