lookup公式中的区域不对应吧:
$A$18:$F$18应该是$B$18:$F$18
追问不是哦,把$A$18:$F$18换成$B$18:$F$18以后比例就不对了。100分的都变成了#N/A
追答查找范围与结果范围不一致,肯定有问题。
直接按百分比返回结果怎么样?
=HLOOKUP(1-PERCENTRANK($Y$3:$Y$253,Y3),{0,0.05,0.2,0.6,0.95;"S","A","B","C","D"},2)
追问![](https://video.ask-data.xyz/img.php?b=https://iknow-pic.cdn.bcebos.com/eac4b74543a982263007e1ca8b82b9014b90eb1e?x-bce-process=image%2Fresize%2Cm_lfit%2Cw_600%2Ch_800%2Climit_1%2Fquality%2Cq_85%2Fformat%2Cf_auto)
![](https://video.ask-data.xyz/img.php?b=https://iknow-pic.cdn.bcebos.com/6159252dd42a28346766fce65ab5c9ea14cebf1f?x-bce-process=image%2Fresize%2Cm_lfit%2Cw_600%2Ch_800%2Climit_1%2Fquality%2Cq_85%2Fformat%2Cf_auto)
我把您给的公式代入后就是这样了。。。100分的都是C~应该不行吧
追答上面的公式要复制到Z3单元格,下位复制