[数据分析: Excel] vlookup的精确匹配和模糊匹配

vlookup是工作中最常见的公式之一,可以快速得出相匹配的值。那么,你真的会使用嘛?


先来看一下vlookup的公式:

        VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

看上去有点复杂……好吧,说人话:

        VLOOKUP(要匹配的值,匹配的列表,返回第几列,[精确 / 模糊匹配])

公式里面有4个参数,这回看清楚了,是4个参数,那接下来就是搞清楚这4位老兄是干嘛滴了,用一个例子说明一下比较好理解:


张大牛的分数是多少?

刚刚考完试,查询成绩就是vlookup大显身手的时候啦。上图左边是班级的成绩表,右图是张大牛同学的个人成绩单,用vlookup可以快速匹配出分数。别嘲笑这个图哈,一个班肯定不止这三个大宝贝,这里就是一个小栗子。

我们回到公式上来,还记得前面说到有4个参数嘛,我们一个一个来看。

        1. 要匹配的值:“张大牛”, 这个肯定是张大牛同学啦

        2. 匹配的列表:“成绩表”,4*2的表格区域

        3. 返回第几列:“2”,我们要返回的“分数”在表格的第2列

        4. [精确 / 模糊匹配]:“0”,0表示精确匹配,1表示模糊匹配,不填默认是0,先选择0吧,后面再详细解释原因。

于是,这张图的下场应该是这样的:

手术后的成绩单

在黄色区域填入公式,4位参数兄台对号入座,张大牛,你就是80分啦!

如果要重复使用这个公式,比如遇到下面这种情况:


这时候记得把“成绩表”区域固定起来,像这样:“$B$2:$C$5”,默认是“Ctrl + F4”,也有的系统是直接“F4”的。其他参数不用固定,这样张大牛的公式可以直接向右拖拉复制到其他同学的分数格里。

说到这里,最后一个参数也应该自我介绍一下了,为什么这里是0而不是其他数呢……

其实vlookup支持2种匹配方式,体现在第4个参数,即精确匹配和模糊匹配。该用布尔值表示的,0(或者False)表示的是精确匹配,1(或者True)表示的是模糊匹配。

上面的例子中的成绩表因为是精确的1对1,所以用精确匹配。

那么模糊匹配,或者叫近似匹配什么时候用呢?请看下面的例子:

模糊匹配

如果我们想对学生的分数进行等级评价,如右图,此时如果继续用精确匹配,那么赵花花同学的“等级”会报错,因为在左边的“分数-等级表”中匹配不到“75分”的等级。


在模糊匹配中,第2个参数选中的表格就变成了一个“范围”概念的表格,即起点为自身的数值,终点为下面一格的数值,不包含下面一格,可以参看右边的匹配范围。注意,这里只能从小向大进行无缝隙排列。

回到例子中来,如果将第4个参数填入“1”,或者“True”,那么匹配条件为“模糊匹配”或者“近似匹配”,可以看到赵花花的等级匹配到的是“中等”,因为赵花花是75分,属于“中等”,即 [70,80) 的取值范围。

好啦,是不是很简单,是不是哈哈。

顺便说一下,vlookup公式有4个参数,只要是符合参数要求,并且可以识别,你填什么进去都可以,比如像这样:

        VLOOKUP(“张“&”大牛“, B2:C5, 2, 0)..........把”张“和”大牛“拼接起来

        VLOOKUP(F3, B2:C5, 2, 0+1)..........注意看第4个参数,是0+1,其实我想进行的是 1 模糊匹配

最后说一个vlookup的小技巧吧,因为vlookup的第3个参数,即选择返回第几列,只能从左至右数,那么遇到下面这中情况怎么办呢?

        还是用最开始的精确匹配的例子:

谁是100分?

现在问题反过来了,给出右侧的分数,匹配左侧的姓名,那该怎么做……

如果这样填入公式:

                =VLOOKUP(F3, B2:C5, 2, 0)  那结果就是报错。

原因出在左边的分数表,我们要匹配的是“100”,而在左边分数表中第一列没有任何一个值是“100”,自然也就无法返回相对应的第2列的值了……

那么怎么办呢……?以前工作中经常看到同事这样做:

改变左边的分数表:

添加辅助列

的确,这样确实可以解决问题,但是很...难...看...,而且在一些有完善数据表制作流程的公司,比如微软啦,所有的表都是和后台数据库实时连接的动态表,是没有机会添加辅助列的哦,比如power pivot自动生成的表,添加的辅助列会在刷新的时候被重置掉。

没关系,肯定有办法咯,可以这样做:

把公式改成这样:

                =VLOOKUP(M8,IF({1,0},C2:C5,B2:B5),2,0)

解释一下:

                其他的参数不变,把第2个参数改成:

                        IF({1,0},C2:C5,B2:B5)

                        首先创造一个IF函数,有3个参数:

                                数组{1,0},表示顺序,然后输入“第2列”(识别列),最后输入“第1列”(返回列)。

简单理解为:VLOOKUP(要匹配的值,IF( {1,0},第2列,第1列 ),返回第几列,[精确 / 模糊匹配])

这样,就可以在不添加辅助列的情况下,实现自右至左的匹配了。


先写到这吧,每周更新,欢迎交流。

推荐阅读更多精彩内容