学习
实践
活动
工具
TVP
写文章

对于Excel的“查找之王”,说点你听得懂的大白话!

小伙伴们好啊,今天和大家来说说VLOOKUP的那些事儿,深入了解一下VLOOKUP函数,看看这位大众情人还藏着多少不为人知的秘密。

VLOOKUP函数几乎成了Excel的代名词。会用VLOOKUP函数的人一定是Excel高手。

我们公司的财务人员常常说的一句话是:

我现在太忙了,我把基础表给你,你自己V一下吧。一个字母V就代表了Excel。

1

基础语法

VLOOKUP函数的基础语法为:

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

用中文翻译过来就是VLOOKUP( 目标值 , 目标区域 , 第几列 , 查找方式 )。

VLOOKUP函数中的V缩写于单词Vertical,表示垂直的,整个函数就是Vertical Look Up,垂直的查找,也就是在目标区域的第一列,纵向查找目标值,然后返回第n列的对应结果。

第4个参数的查找方式有两种:数字0或FALSE,代表精确匹配;数字1或TRUE,代表模糊匹配。查找区域必须升序,查找的方案也是“二分法”。

这两种查找方式的原理与MATCH函数完全一致,不过VLOOKUP比MATCH少了一个-1的参数。

2

根据姓名查找等级

先讲个故事。

Excel Home论坛有一位版主,曾经也是Excel的“小白”,那时他的工作中有一项是核对人员成本,大概有上千行的数据。

每月的最后几天需要赶时间做出来,最后只能发动老婆一起做,每次都是几乎3个通宵才能完成。

这样的工作效率实在太低了,于是他下决心寻找高效方法,功夫不负有心人,终于学会了用VLOOKUP函数,于是三天的工作量缩短到半天,后来这位版主慢慢升职到了高管的位置。

这个故事告诉我们,努力工作加班并不能从根本上解决问题,能促使你进步的是一颗“偷懒”的心。

我们用一个案例来展示VLOOKUP函数的最基础应用。

如上图所示,C15:E22是数据区域,要查找G15:G16单元格区域中姓名的对应分数等级,可以在H15单元格中输入以下公式并向下复制到H16单元格。

=VLOOKUP(G15,$C$15:$E$22,3,0)

G15单元格的“许褚”为查找目标值,在C15:E22单元格区域中的第一列,即在C15:C22 单元格区域中查找“许褚”,然后返回C15:E22区域中的第3列的值,即得到分数等级为“B”,最后注意第4个参数数字0,代表精确匹配。

3

案例:VLOOKUP完成各种方式的精确查找

如下图所示,A~G列是基础数据源,其中A列是部门,B列是员工号,C列是姓名,D~G列是基本工资、绩效奖、加班费及总工资。根据这个数据源进行各种方式的精确匹配查找。

常规查找

先写一个最基础的公式,如下图所示。

根据J3:J5单元格区域的员工号,查询每个员工号对应的姓名。在K3单元格中输入以下公式并向下复制到K5单元格。

=VLOOKUP(J3,B:C,2,0)

输入公式的时候,千万别忘记第4个参数数字0。在VLOOKUP函数查询的时候,如果数据源与本案例相似,是从第一行开始一直向下延伸,建议大家使用整列引用,这样会具有更好的扩展性,并且不影响计算效率。

文本数字查找

有多少人这样处理过问题:当有多列数据时,用VLOOKUP函数引用区域,在写第3个参数的时候,开始去手动数“1,2,3,…,45,46”,然后在第3个参数的位置写下46。相信很多人都这样做过,我曾经就是其中一员。接下来看看,如何学会“偷懒”技能。

如下图所示,在K8单元格中输入“=VLOOKUP(J8,”,然后用鼠标选中B:G区域,注意这时候不要松开鼠标,鼠标指针为白色空心十字的状态,仔细看看屏幕上出现了什么?在鼠标指针右侧的地方会显示“1048576R×6C”,R表示Row,即行的意思,C表示Column,即列的意思。

这说明选择了1 048 576 行×6列的区域,所以第3个参数不用数,直接写6就可以了。在不同的Excel版本中,提示行列数显示的位置和方式不同,有的是在选定区域的左上角,有的仅显示“6C”,但无论在哪儿,仔细找一找都会找到的。

最后第4个参数0,表示精确匹配,完整公式为:

=VLOOKUP(J8,B:G,6,0)

将公式向下复制到K10单元格,这时出问题了,为什么后面得到的都是错误值,如下图所示。

再仔细看看数据源,J9和J10单元格的左上角都有个小“绿帽子”,最常见的“绿帽子”一般是这几种情况:文本型的数字、错误值、单元格中的公式与周围环境不一致。

这里明显不属于后两者,所以是文本型数字。VLOOKUP在查询的时候和MATCH一样,都是会根据数据类型判断的,所以要将文本型数字转化为数值型,“减负”即可。

=VLOOKUP(--J8,B:G,6,0)

如下图所示,即可得到正确结果。

查无此人

再次根据员工号查姓名,如下图所示。

在K13单元格中输入公式“=VLOOKUP(J13, B:C,2,0)”,并向下复制到K15单元格,然后发现K15单元格返回结果为#N/A,因为原始数据中没有“209”这个员工号,所以对于错误值标注一下“查无此人”,将公式完善为:

=IFERROR(VLOOKUP(J13,B:C,2,0)," 查无此人 ")

查找一系列值

前面都是根据目标值返回一列的结果,如何使用VLOOKUP函数返回n列的信息呢?根据列的变化返回一系列数字,自然想到了COLUMN函数,如下图所示。在K18单元格中输入公式:

=VLOOKUP(J18,C:G,COLUMN(B:B),0)

公式需要复制时,别忘记使用“图钉”,将公式完善,并复制到K18:N20单元格区域。

=VLOOKUP($J18,$C:$G,COLUMN(B:B),0)

这里必须提示一点,我们根据数据源C列的姓名,返回后面D列的值时,千万不能将VLOOKUP函数的第3个参数写成COLUMN(D:D)(它的结果是数字4), 而是要写为COLUMN(B:B)(它的结果是数字2)。因为我们选择的区域是C:G,返回的结果是这个C:G区域中的第2列。写公式的时候需要知道每一步的结果是什么,公式中要的是什么。

逆向查找

如下图示,根据数据源中C列的姓名,查询A列的部门和B列的员工号,形成逆向查找,我们先把公式写出来,然后再详细讲解,在K23单元格中输入以下公式,并向下向右复制。

=VLOOKUP($J23,IF(,$C$2:$C$9,A$2:A$9),2,0)

这个公式中J23、2、0都是常见的参数,查找区域IF(,$C$2:$C$9,A$2:A$9)是此公式的关键,它具体表示什么意思呢?下面我们对它进行剖析一下。

我们之前讲IF函数的时候,已经讲过最基础的用法。这里来回忆一下。

公式“=IF(1,"a","b")”的结果为“"a"”;公式“=IF(0,"a","b")”返回的结果为“"b"”。那么数字1相当于TRUE,数字0相当于FALSE。

我们将IF函数的第1个参数变成一个数组“=IF(,"a","b")”,它的结果同样是一个数组“{"a","b"}”,与IF中的第1个参数是一一对应的关系。

所以IF(,$C$2:$C$9,A$2:A$9) 的结果就是将C2:C9放前面,A2:A9放在后面,构造成一个8行2列的数组,如下图所示。

这个区域已经构造完了,将它作为VLOOKUP函数的第2个参数。为什么第3个参数要写数字2呢?因为构造的区域只有两列。

不知道大家有没有注意一个细节,前面写公式的时候,我们都是选择整列引用,而到了这里却只限定了第2行到第9行区域?这是因为在IF函数中,经过 的数组运算,如果选择了整列,那相当于对整列的1 048 576行数据做计算,你想想计算效率能高吗?

查找指定列

如下图所示,根据J列的姓名,查找相应人员对应工资科目的明细,科目信息是根据第27行的标题而定的,我们不知道要查找的是第几列,遇到这种情况该怎么办呢?可以考虑用MATCH函数。在K28单元格中输入公式:

=VLOOKUP($J28,$C:$G,MATCH(K$27,$C$1:$G$1,0),0)

将公式向下向右复制,千万别忘记用“图钉”,另外还有几个方面需要注意。

(1)MATCH函数的第2个参数不能随手写为$1:$1。“MATCH(K$27,$1:$1,0)”的结果返回的是4,整个公式是指从C:G区域中返回第4列的值,不是我们要查找的内容。前面我们选择的区域是C列到G列,为了简单一些,我们把查找标题的范围也限制在C列到G列,所以公式就是“$C$1:$G$1”。

(2)有的读者认为“,0),0)”部分重复了,于是公式写到“VLOOKUP($J28,$C:$G,MATCH (K$27,$C$1:$G$1,0))”这里就结束了。这样的公式并不完整,前面的0是MATCH函数的,而不是VLOOKUP函数的。初学函数,务必把每一个参数写全,用来保证工作的准确性。

通配符查找

我们来查找第一个姓黄的人员的总工资,公式为:

=VLOOKUP(" 黄 *",C:G,5,0)

返回结果为“8 400”,注意,公式中第3个参数的数字5不是数出来的,而是选择区域的时候,按住鼠标不放,自动标识出来的。

查找第一个姓黄的且姓名为两个字的人员的总工资,公式为:

=VLOOKUP(" 黄 ?",C:G,5,0)

返回结果为“6 890”,对应的是“黄忠”的总工资。

好了,今天的内容就是这些,希望对你有所帮助。

本文内容摘录于

北京大学出版社新书

白话聊Excel函数应用100例

Excel Home打造的Excel函数实战指南。以降低学习曲线为宗旨,真正能看得懂、学得会、用得巧的函数实战书。

  • 发表于:
  • 原文链接https://kuaibao.qq.com/s/20200427A043EZ00?refer=cp_1026
  • 腾讯「腾讯云开发者社区」是腾讯内容开放平台帐号(企鹅号)传播渠道之一,根据《腾讯内容开放平台服务协议》转载发布内容。
  • 如有侵权,请联系 cloudcommunity@tencent.com 删除。

扫码关注腾讯云开发者

领取腾讯云代金券