Excel中Lookup函数实现临界点归入下一个等级的方法

Excel中经常会遇到根据得分得到相应的评级的问题，例如：0≤得分<30为智障，30≤得分<60为轻障，60≤得分<70为不合格，70≤得分<80为勉强合格，80≤得分<90为合格，90≤得分<100为优秀，≥100为天才，可以据此做出如下评价表：

图1

如果给出一系列得分，得出相应的评级，如下表：

图2

如上例，K列使用什么公式？

大多数人最先想到的肯定是IF公式，如上例中在K列第一个要求等级的行（假设行号为6）中输入下面的公式然后下拉：

=IF(AND(0<=J6,J6<30),G2,IF(J6<60,H2,IF(J6<70,I2,J2)))

因为级别太多，上面的公式还有三个嵌套的IF我就偷懒没写了，本来要一直判断到J6<100才行。可见用IF公式是个笨办法。聪明的方法是使用LOOKUP公式，即在K2单元格中输入下面的公式再下拉：

=LOOKUP(J6,$G$1:$M$1,$G$2:$M$2)

其中$G$1:$M$1和$G$2:$M$2区域是图1中评价表的引用区域。这个公式看起来优雅得多，而且后续增加新的评级，只要在评价表区域增加新的列填入临界值及相关评级即可。

但是LOOKUP函数有个特点，那就是≥临界值时等级就是临界值对应的评价等级，如果评价方式变成>临界值时等级才是临界值对应的评价等级，而等于临界值时等级是前一个评价等级，例如，增加一个≤0评价等级为“傻蛋”，并且=100评价等级是优秀，只有>100评价等级才是天才，该怎么办？这个时候应该将临界值写成一个极小数，在0之前也写上一个相当于-∞的数（-1E+307），对应的等级为“傻蛋”。例如在Excel2016中，0改写成1E-14，（该版本算术运算的精度只能精确到10的-14次方），而100则写成=100+（1E-14）即可。修改后的等级评定表如下：

实际测试效果如下：

得分0对应的等级单元格公式为：

=LOOKUP(E6,$F$1:$M$1,$F$2:$M$2)

其余分数对应的等级单元格为该公式下拉得出。可见0分现在成了傻蛋，0.0001分就成了更高等级的智障，而100对应的等级已经成了比天才低一等级的优秀，100.000001对应的等级则是更高等级的天才。