正文共:1855 字 10 图
预计阅读时间: 5 分钟
VLOOKUP函数是工作中非常实用的查找函数,能帮助我们很方便地处理数据查找和匹配,比如根据员工编号查找姓名,根据城市名称查找销售数据等。如果是人工查找,一般先从城市列表中找到“成都”,然后去找成都的“2月”数据,找到后填入H1单元格中。
用VLOOKUP函数查找速度更快更准确,那么VLOOKUP函数怎么用呢?
我们还是先来看下这个函数的语法结构:
· VLOOKUP(查找值,查找范围,返回第几列的值,精确/模糊参数)
函数的这4个参数图解如下。
VLOOKUP函数对这4个参数也有自身的要求,下面逐一介绍。
参数1:查找值,又称查找依据,本例是根据城市查找2月份销售额。VLOOKUP函数要求查找值在查找范围列表中是唯一值。如果不是唯一值,返回的永远是第一次出现的数据。同时要求查找值和查找范围中的值要完全一致,比如“成都”和“成 都”就不一致,后者中间多了空格。
参数2:查找范围,要包含查找值和返回值。VLOOKUP函数要求范围要以查找值作为第1列,返回值在查找值的右侧。所以上面案例中选取范围就不能选取A列的序号。另外,如果公式要向下填充,要保证查找范围不变,常用$符号固定或者整列选取范围。
参数3:返回第几列值,直接输入数字就可以,不需要点击范围中的单元格。要从查找范围的首列开始数,不是从A列开始数。
参数4:精确/模糊,精确查找比较常见,用0/FALSE表示,如果找不到会报错;模糊查找常用于数据区间段查找,用1/TRUE表示。
工作中精确查找比较常见,上述案例就是精确查找。在I2单元格中输入公式:
=VLOOKUP(G2,B2:D10,3,0)
在用VLOOKUP做精确查找时,因为数据原因常常遇到报错的情形,导致表格看起来不美观。
这时可以用IFEORROR+VLOOKUP来进行美化处理。在H2单元格中输入公式:
=IFERROR(VLOOKUP(G2,$B$2:$D$10,3,0),'/')
表示如果VLOOKUP函数结果有错误,就用“/”填充,否则就显示VLOOKUP函数的计算结果。
关于数据区间模糊查找常用于计算个人所得税、销售佣金和绩效等级评价等场合。就拿销售佣金计算为例,销售政策上的表格如下表。
销售额区间 | 佣金比例 |
大于等于0,小于20000 | 2% |
大于等于20001 小于50000 | 2.5% |
大于等于50001 小于100000 | 3% |
大于等于100000 | 4% |
那如何将员工的销售额和佣金比例相匹配?需要在Excel中设计一个佣金规则表。
销售额区间 | 佣金比例 |
0 | 2% |
20000 | 2.5% |
50000 | 3% |
100000 | 4% |
这样,可以用VLOOKUP函数的模糊查找功能,实现根据销售额自动查找对应的佣金比例。在下图C2单元格中输入公式:
=VLOOKUP(B2,$F$2:$G$5,2,1)
在使用VLOOKUP函数的模糊查找时,要注意以下两点:
· 查找规则中的区间数据要升序排列;
· 区间数据是前闭后开,比如2%对应区间是[0,20000),表示大于等于0,小于20000。
下图根据城市和月份来查找销售额数据,可以看到A列的城市不是唯一值,B列的月份也不是唯一值,所以单纯查询城市和月份都不满足VLOOKUP的要求。
仔细看你会发现,城市和月份连接起来,如“北京1月”是唯一的。所以,考虑用添加辅助列的方法。在C列添加辅助列,C2单元格输入公式:
=A2&B2
并向下填充,在H列也添加辅助列,H2单元格输入公式:
=F2&G2
这样,在I2单元格的查询公式如下:
=VLOOKUP(H2,C2:D9,2,0)
就可以把成都2月份的销售额400查找出来了。
如果觉得添加辅助列会改变原表的布局结构,可以用数组公式的方法来实现,在G2单元格输入公式:
=VLOOKUP(E2&F2,IF({1,0},A2:A9&B2:B9,C2:C9),2,0)
该公式为数组公式,需要按Ctrl+Shift+Enter完成输入,成功后公式两边会自动添加大括号。用IF({1,0})重组区域为新的数组,A和B列连接到一起,再和销售额组成新的数据区域。
本文节选自营长的《Excel数据管理:不加班的秘密》。
营长简介:王忠超,北大纵横管理咨询公司合伙人,16年Office培训与11年管理咨询经验,畅销书《商务PPT的说服之道》和《Excel数据管理:不加班的秘密》作者。
联系客服