本文作者丨赵骄阳 - Excel 研究院
本文由「秋叶 Excel」原创发布
表姐碎碎念
幸福的日子时间总是过的飞快，转眼间距离 2018 结束只剩 20 天的时间了，相信很多人正面临着年终 KPI 考核评定，又一波加班即将来临。
所以今天我就来和大家说说，怎么用 Excel 公式来高效地处理等级评定的问题。
这次我们用一个具体的例子来讲解这个问题——
首先设置评定等级
小于 60 分不合格；
大于等于 60 小于 70 为合格；
大于等于 70 小于 80 为良好；
大于等于 80 为优秀。
也就是说，如果我在考核中得了 75 分，那么就会被评定为良好。
好了，接下来我就依次介绍 4 种办法来完成等级评定，供大家根据情况自行选用哦~
1
IF 函数法
IF 函数的语法如下：
=IF(条件判断,条件成立返回值,条件不成立返回值)
含义就是，我们先设置一个条件，看条件是否成立。
❶ 如果条件成立，返回一个值；
❷ 如果条件不成立，则返回另一个值。
根据上面说的评定规则，我将判断逻辑做成下图的样子：
再根据这幅逻辑关系图，我在 C3 单元格中写下了多重嵌套的 IF 函数公式：
=IF(B3<60,'不合格',IF(B3<70,'合格',IF(B3<80,'良好','优秀')))
而这串公式的具体含义是：
当 B3<60 时，返回值为'不合格'，这时在'不合格'逗号的右侧默认就是>=60 的情况，接下来只需要满足<70 的条件即可判断为'合格'，以此类推，第一个 IF 函数的第 3 个参数就被安排上了第二个 IF 函数，之后再作判断……从而形成 IF 函数的多层嵌套公式。
当然，我们也可以换另一个思路，将得分由大至小判断，逻辑图就变成了下面的样子：
此时 C3 单元格的公式就可以写成：
=IF(B3>=80,'优秀',IF(B3>=70,'良好',IF(B3>=60,'合格','不合格')))
这里特别提醒大家注意临界点的判定，70 分到底是良好还是及格，等于号加在哪里要想清楚。
2
LOOKUP 函数法
IF 函数肯定是大家最熟悉的函数之一了，但是如果评定等级比较多，公式写起来会十分冗长，下面我就来介绍 LOOKUP 函数法来简化处理这个问题。
LOOKUP 函数的语法有两种，数组形式和向量形式，我们在等级评定中使用的是向量形式，语法如下：
=LOOKUP(查找值,目标向量,返回向量)
看这个你可能有点懵，我们直接用案例讲：
我在 C3 单元格写出了下面的公式，然后拖动向下填充。
=LOOKUP(B3,E$3:E$6,F$3:F$6)
你一定注意到了，在分数列表右侧有这样两列——
这是我们在用 LOOKUP 函数法解决等级评定问题时要建立的评定标准表。
这里采用绝对引用或混合引用，本例采用混合引用，即只对行进行锚定的方式来引用这两组向量，也就是写成E$3:E$6,F$3:F$6 的样子，否则向下填充后就有可能得不到正确的答案。
最后要提醒大家注意两点：
❶ 评定等级的标准必须为升序排序，乱序、降序会得不到正确答案；
❷ 这里关于临界值的判断是这样的，当查找值大于等于某个等级（目标向量值）时，将返回旁边对应的返回向量值。
3
TEXT 函数法
刚刚的 LOOKUP 函数法，写起公式来非常方便，但评定标准需要按升序排序，如果此条件无法满足，这个方式就无法适用。
所以第 3 种方法，我要为大家再介绍一个函数——TEXT 函数。TEXT 函数是一个超级好用的格式化文本函数，可通过格式代码对数字应用格式，从而更改数字的显示方式。
TEXT 函数的语法如下：
=TEXT(数值,格式代码)
具体是这样的，TEXT 函数的格式代码可分为 4 个条件区段，各区段间用半角分号间隔。这 4 个区段的定义为：
[条件 1];[条件 2];[不满足条件 1 和条件 2 的其他部分];[文本]
然后根据各区段条件来判断，返回相应结果。因此我在C3 单元格写下了下面的公式：
=TEXT(0&B3-60,'[<10]合格;[<20]良好;优秀;不合格')
这串公式的含义是：
❶ '0&B3-60'部分就是首先计算 42-60 得到-18，然后与前面的 0 连接，最终结果为'0-18'，这时 TEXT 函数会把它当文本处理，文本落在第 4 个区间，返回结果'不合格'。我们可以看出，分数在 60 分以下。表达式的结果都会是'0-数字'这种样式，这种样式会被 TEXT 函数以文本对待处理；
❷ 当 B3-60 小于 10 时 ，公式返回的结果是'合格'；当<20 时返回'良好'，其他情况得到的计算结果就是'优秀'。
4
CHOOSE 函数法
刚刚的 TEXT 函数法，是通过自定义格式条件设置进行等级评定，适用于 4 个以内的等级评定，而 4 个以上的等级评定就不再适用了。
因此最后一种方法，将为大家介绍 CHOOSE 函数。这个函数可以根据指定的自然数序号返回与其对应的数据值、区域引用或嵌套函数结果。根据它的特性，我们可以在某些条件下用CHOOSE 函数替代 IF 函数进行条件判断。
CHOOSE 函数的语法如下：
=CHOOSE(索引值,值 1,值 2,值 3,.....)
语法含义是索引值可以是运算结果是数值的表达式，或者直接是数值。当索引值为 1 时，函数的结果返回值 1，当索引值为 2 时，函数的结果返回值 2……以此类推。
如果没看明白不要着急，我们直接看例子——
我在 C3 单元格输入了下面的公式：
=CHOOSE(SUM(--(B3>={0;60;70;80})),'不合格','合格','良好','优秀')
这串公式的含义是：
❶ B3>={0;60;70;80}用来判断 B3（这里是 42）是否大于等于考评值{0;60;70;80}，判断完将返回逻辑值{TRUE;FALSE;FALSE;FALSE}；
❷ --(B3>={0;60;70;80})也就是在前面放置两个-的目的是将逻辑值转换为数值，结果为{1,0,0,0}，再用 SUM 求和，计算结果为 1，也就是说作为这个 CHOOSE 函数第一个参数的索引值是 1；
❸ 最后， CHOOSE(1,'不合格','合格','良好','优秀')，得到计算结果为'不合格'。
▼▼▼
以上就是 4 种用于评定等级的方法，不知道大家掌握的怎么样？EXCEL技巧再多，也要熟练运用才好，今天的科普就到这里，如果大家有什么疑问，欢迎到后台进行咨询哟～
联系客服
