年终KPI评定来了！4种公式套路帮你搞定！
不要错过，文末有福利细节
本文作者丨赵骄阳 - Excel 研究院
本文由「秋叶 Excel」原创发布
如需转载，请在公众号回复关键词「转载」查看说明
距离 2018 结束只剩 20 天的时间了，相信很多人正面临着年终 KPI 考核评定~
小 E 祝愿所有小伙伴
都拿到超级多的年终奖！
所以今天小 E 就来和大家说说，怎么用 Excel 公式来高效地处理等级评定的问题。
这次我们用一个具体的例子来讲解这个问题——
首先设置评定等级：
小于 60 分不合格；
大于等于 60 小于 70 为合格；
大于等于 70 小于 80 为良好；
大于等于 80 为优秀。
也就是说，如果我在考核中得了 75 分，那么就会被评定为良好。
好了，接下来我就依次介绍 4 种办法来完成等级评定，供大家根据情况自行选用哦~
- 1 -
IF 函数法
IF 函数的语法如下：
=IF(条件判断,条件成立返回值,条件不成立返回值)含义就是，我们先设置一个条件，看条件是否成立。
❶ 如果条件成立，返回一个值；
❷ 如果条件不成立，则返回另一个值。
根据上面说的评定规则，小 E 将判断逻辑做成下图的样子：
再根据这幅逻辑关系图，小 E 在 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,"合格","不合格")))这里小 E 特别提醒大家注意临界点的判定，70 分到底是良好还是及格，等于号加在哪里要想清楚。
- 2 -
LOOKUP 函数法
IF 函数肯定是大家最熟悉的函数之一了，但是如果评定等级比较多，公式写起来会十分冗长，下面小 E 就来介绍 LOOKUP 函数法来简化处理这个问题。
LOOKUP 函数的语法有两种，数组形式和向量形式，我们在等级评定中使用的是向量形式，语法如下：
=LOOKUP(查找值,目标向量,返回向量)看这个你可能有点懵，我们直接用案例讲：
小 E 在 C3 单元格写出了下面的公式，然后拖动向下填充。
=LOOKUP(B3,E$3:E$6,F$3:F$6)你一定注意到了，在分数列表右侧有这样两列——
这是我们在用 LOOKUP 函数法解决等级评定问题时要建立的评定标准表。
这里采用绝对引用或混合引用（本例采用混合引用，即只对行进行锚定）的方式来引用这两组向量，也就是写成E$3:E$6,F$3:F$6 的样子，否则向下填充后就有可能得不到正确的答案。
最后小 E 要提醒大家注意两点：
❶ 评定等级的标准必须为升序排序，乱序、降序会得不到正确答案；
❷ 这里关于临界值的判断是这样的，当查找值大于等于某个等级（目标向量值）时，将返回旁边对应的返回向量值。
- 3 -
TEXT 函数法
刚刚的 LOOKUP 函数法，写起公式来非常方便，但评定标准需要按升序排序，如果此条件无法满足，这个方式就无法适用。
所以第 3 种方法，小 E 要为大家再介绍一个函数——TEXT 函数。TEXT 函数是一个超级好用的格式化文本函数，可通过格式代码对数字应用格式，从而更改数字的显示方式。
TEXT 函数的语法如下：
=TEXT(数值,格式代码)具体是这样的，TEXT 函数的格式代码可分为 4 个条件区段，各区段间用半角分号间隔。这 4 个区段的定义为：
[条件 1];[条件 2];[不满足条件 1 和条件 2 的其他部分];[文本]然后根据各区段条件来判断，返回相应结果。因此小 E 在 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 个以上的等级评定就不再适用了。
因此最后一种方法，小 E 将为大家介绍 CHOOSE 函数。这个函数可以根据指定的自然数序号返回与其对应的数据值、区域引用或嵌套函数结果。根据它的特性，我们可以在某些条件下用 CHOOSE 函数替代 IF 函数进行条件判断。
CHOOSE 函数的语法如下：
=CHOOSE(索引值,值 1,值 2,值 3,.....)语法含义是索引值可以是运算结果是数值的表达式，或者直接是数值。当索引值为 1 时，函数的结果返回值 1，当索引值为 2 时，函数的结果返回值 2……以此类推。
如果没看明白不要着急，我们直接看例子——
小 E 在 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 种用于评定等级的方法，可能大家对于其中某些函数还比较陌生，不过不用着急！小 E 已经为大家安排上了练习文件，供大家在练习中掌握它们！
温馨提示，是在公众号对话框里发送关键词【等级评定】哟！
暂时先不要走开，小 E 还要告诉大家一个好消息——
「秋叶 PPT」系列课程
双 12 大促来了
一课搞定 Word、Excel、PPT 三大软件
从新手成长到 Office 高手
在年终评定中脱颖而出！
总价 509 元的课程+图书
双 12 大促期间
（12 月 10 日到 12 月 19 日）
领券下单只要 299 元
立省 210 元！
除此以外，我们还送👇
价值 198 元的锦囊课+三分钟课堂
200 元 Office 特训营优惠券
价值 40 元的优质图书一本（12 选 1）
价值 69.8 元十年典藏版说服力一本
PPT 超级大礼包
4G 专属办公资源福利包
专属学员交流群
iSlide 会员 1 个月
幕布会员 3 个月
文都教育会员 7 折卡
……
特别提示：苹果的 iPhone/iPad 上的网易云课堂 APP 购买课程，不参与实物赠礼活动！ 建议从微信公众号进入购课！
👇赶紧点击【阅读原文】
了解 2018 年最后一波福利详情！