PC下载网
PC下载网 > 软件教程 > 业界资讯 > 

Excel怎么用公式来高效地处理等级评定?

Excel怎么用公式来高效地处理等级评定?

作者:秋叶PPT 来源:PC下载网资讯 原创 时间:2018-12-14 22:59:25

距离 2018 结束只剩 20 天的时间了,相信很多人正面临着年终 KPI 考核评定~

小 E 祝愿所有小伙伴,都拿到超级多的年终奖!

所以今天小 E 就来和大家说说,怎么用 Excel 公式来高效地处理等级评定的问题。

这次我们用一个具体的例子来讲解这个问题——

首先设置评定等级:

小于 60 分不合格;

大于等于 60 小于 70 为合格;

大于等于 70 小于 80 为良好;

大于等于 80 为优秀。

也就是说,如果我在考核中得了 75 分,那么就会被评定为良好。

好了,接下来我就依次介绍 4 种办法来完成等级评定,供大家根据情况自行选用哦~

u=3664447048,375956366&fm=26&gp=0.jpg

- 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 分到底是良好还是及格,等于号加在哪里要想清楚。

微信图片_20181214230504.png

- 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 要提醒大家注意两点:

❶ 评定等级的标准必须为升序排序,乱序、降序会得不到正确答案;

❷ 这里关于临界值的判断是这样的,当查找值大于等于某个等级(目标向量值)时,将返回旁边对应的返回向量值。

微信图片_20181214230744.png

- 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 时返回"良好",其他情况得到的计算结果就是"优秀"。

微信图片_20181214230819.jpg

- 4 -CHOOSE 函数法

刚刚的 TEXT 函数法,是通过自定义格式条件设置进行等级评定,适用于 4 个以内的等级评定,而 4 个以上的等级评定就不再适用了。

因此最后一种方法,小 E 将为大家介绍 CHOOSE 函数。这个函数可以根据指定的自然数序号返回与其对应的数据值、区域引用或嵌套函数结果。根据它的特性,我们可以在某些条件下用 CHOOSE 函数替代 IF 函数进行条件判断。

CHOOSE 函数的语法如下:

=CHOOSE(索引值,值 1,值 2,值 3,.....)

语法含义是索引值可以是运算结果是数值的表达式,或者直接是数值。当索引值为 1 时,函数的结果返回值 1,当索引值为 2 时,函数的结果返回值 2……以此类推。

微信图片_20181214230856.jpg

如果没看明白不要着急,我们直接看例子——

小 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,"不合格","合格","良好","优秀"),得到计算结果为"不合格"。

 

PC下载站网友:
共有14条评论
返回顶部