Excel VBA之函数篇-3.14数据的高级查找,快速通过区间划分等级

Excel VBA之函数篇-3.14数据的高级查找,快速通过区间划分等级

前景提要

在前面几次的学习中,我们学习了一些关于like()函数的简易的使用方法,今天我们来继续学习下相关方面的知识,今天我们更换一个场景,这个场景也是受到了小编在写月总结的时候的一个启发,主要是通过区间来区分不同的等级的,比方说我们销售人员每个月都会有一个销售任务指标,然后还会有一个实际销售额,通过完成率来评判这个销售员的工作状态和效率,这是很多商城,公司比较常用的一种KPI考核指标的方法,但是通过计算出来的数据各不相同,要快速的区分出各个档次的销售员并不是容易的事情,那么VBA是否可以实现快速帮我们区分各个不同等级的呢?

函数说明

想要实现这样的效果,也不难,我们还是继续通过like()函数来实现,学到这里你一定会觉得like()函数的功能有多强大了吧,之前我们学习了通过结构,长度,起始字符等方式的运用,今天我们来学习下like()在区间判断中的使用,判断区间的方式也很简单,“[1-10]”代表的就是1到10之前的数据,“[A-G]”代表的就是A到G之间的字母,当然还有一种情况就是逆判断,不在当前区间,“[!A-G]”你也看出来了,就是再正向判断前面加一个感叹号,就代表了不等于区间了。好了,知识点和思路有了,我们举个例子吧。

上代码

这里我们按照我们之前模拟假设的场景,还是个商城的销售人员的KPI考核指标,我们现在已经有了每个人的销售量,销售指标的完成度也是非常的简单了,用销售量/销售额度就达到了这个百分比了。

前面这一部分我们已经做了,因为这份数据是虚构的,数据也是随机输入的,可以看出来数据的差距非常的大,在实际的销售数据中,很难出现这样大的差距的,这只是我们模拟的一种场景,实际中更多每个销售员的数据还是比较的接近的,单纯肉眼识别还是非常的困难的,而且为了演示的方便,我这里仅仅是列举了20几个数据而已,好了,我们上代码

Sub TEST()Dim i&EndCol = Cells(Rows.Count, "k").End(xlUp).RowFor i = 2 To EndCol If Fix(Cells(i, "k")) >= 1 Then Cells(i, "k").Offset(0, 1) = "超额完成" ElseIf Fix(Cells(i, "k") * 10) Like "[!0-7]" Then Cells(i, "k").Offset(0, 1) = "基本完成" ElseIf Fix(Cells(i, "k") * 10) Like "[1-8]" Then Cells(i, "k").Offset(0, 1) = "未完成指标" End IfNext iEnd Sub

这里我们要求如果完成度得到80%以上就算是基本完成任务,如果低于80%就算是未完成任务,而超过100%就算是完成了销售任务了,我们来看看代码效果。

完美,正是我们想要的效果。

代码解析

EndCol = Cells(Rows.Count, "k").End(xlUp).Row

这句代码已经非常的熟悉了,获取K列最后一个非空单元格的,我们在实例中已经使用过很多次了,想必大家都非常的了解了

本次的案例中,运用到了1个我们还没有讲解过的知识点,就是函数的取整,fix(number)就是函数的取整,有一点经验的童鞋一定会说还有另外一个取整函数,int()

没错,但是这两者是有区别的,fit()取整我们可以理解为是"暴力取整",不管后面是多少,只取整数部分,不会四舍五入的,但是int()就存在四舍五入的情况,而本案例中,我们并不需要四舍五入,因为再like()中,小数区间的并不是很准确,所以更多的情况下,我们都是利用整数区间来判断的,所以本案例中,我们是将数据扩大10倍,然后取整的,这样我们就等于到了小数点的第二位数了,再通过判断这个数据的大小区间,就可以得到我们想要的结果了,如果再[1-7]区间就是没有完成,不在这个区间的就是基本完成了。

If Fix(Cells(i, "k")) >= 1 Then Cells(i, "k").Offset(0, 1) = "超额完成" ElseIf Fix(Cells(i, "k") * 10) Like "[!0-7]" Then Cells(i, "k").Offset(0, 1) = "基本完成" ElseIf Fix(Cells(i, "k") * 10) Like "[1-8]" Then Cells(i, "k").Offset(0, 1) = "未完成指标" End If

这里我们先判断区间是否大于等于1,如果属于条件成立的话,那么这个销售员的完成度一定在100%甚至以上,这是一个优秀员工,属于超额完成销售任务的,我们标记为超额完成,如果条件不成立,我们继续往下走,这里将结果扩大10倍之后再取整,如果结果再8-10之间,那就是80%以上的完成度了,我们划为基本完成,如果达不到这个要求,那就是未完成了,这里我们用了另外一个写法,[1-8]代表的就是0%~80%之间的,而[!0-7]代表的就是80%~100%之间的数据了,两种写法都展示出来,方便大家理解,可能中间扩大10倍的过程不好理解,我们这里来调试下看看

原始数据是0.998.。。。。扩大10倍就是*10,得到的结果就是9.98.。。,再取整的话,就得到了整数部分9, 这里没有四舍五入!

完整代码

Sub TEST()
Dim i&EndCol = Cells(Rows.Count, "k").End(xlUp).Row '找到最后一个非空单元格For i = 2 To EndCol If Fix(Cells(i, "k")) >= 1 Then '判断是否大于100% Cells(i, "k").Offset(0, 1) = "超额完成" ElseIf Fix(Cells(i, "k") * 10) Like "[!0-7]" Then '判断80%~100%区间的数据 'Debug.Print "完成度是" & Cells(i, "k") 'Debug.Print "扩大10倍的完成度是" & Cells(i, "k") * 10 'Debug.Print "取整之后的结果是" & Fix(Cells(i, "k") * 10) Cells(i, "k").Offset(0, 1) = "基本完成" ElseIf Fix(Cells(i, "k") * 10) Like "[1-8]" Then '判断0%~80%区间的数据 Cells(i, "k").Offset(0, 1) = "未完成指标" End IfNext iEnd Sub

==============================================

(0)

相关推荐