打开APP
userphoto
未登录

开通VIP,畅享免费电子书等14项超值服

开通VIP
VLOOKUP函数基本使用方法及常见问题解析
userphoto

2015.07.22

关注

VLOOKUP函数基本使用方法及常见问题解析

(2013-11-19 21:04:51)

VLOOKUP,应该是Excel里使用频率仅次于SUM函数的吧……

所以,这里优先介绍一下VLOOKUP函数的基本使用方法,以及常见错误的简单解析。

 

VLOOKUP主要的功能是什么呢,什么情况下用这个传说中的函数?

VLOOKUP在Excel里,属于查找/索引函数,就是根据你提供的条件,去数据列表/数据库里把相对应的东西查找并返回来,这也说明一点,那就是使用这个函数的前提是必须有可以能用来查找的数据列表或者数据库

 

深刻了解了这一点,以后有需要查找的问题,首先就要想到VLOOKUP函数,同类的查找函数还是LOOKUP、HLOOKUP,而VLOOKUP是Vertical_LOOKUP的简写,HLOOKUP是Horizontal_LOOKUP的简写,以区分他们不同的使用环境和情况,常用还是VLOOKUP。

 

看图说话,如下图,A、B、C、D列是我们预先做好的数据列表(数据库),现在我们希望在F4单元格里输入名称,然后右边的G4、H4单元格就能自动返回该名称相对应的CODE、PART。

 



很多Excel新手虽然不知道有查找函数这一回事,但都有这个设想,至于用什么方法完全没有概念。

如下,G4单元格只需要将下面的公式复制并粘贴进去,G4单元格就可以显示“AT002”了:

=VLOOKUP(F4,B:D,2,FALSE)

------------------- 

语法介绍:下面我们详细分析一下VLOOKUP函数的语法和各个参数应该注意的内容。

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

如上为VLOOKUP函数的语法规则,我们用什么函数,都必须以其语法规则为准则书写对应的参数,而不是按自己想当然的方式把自己认为可以的东西写到参数里去。

 

如上语法,以逗号为分隔,VLOOKUP函数总共有4个参数:

 

lookup_value,这是第一参数,表示需要查找的内容。

就像我们前面讲的例子,F4单元格的“NAME”,就是我们需要在数据列表或数据库中查找的内容。

 

table_array,这是第二参数,表示的是你要从中匹配查找并返回结果的数据区域。

上例中我们使用的是B:D,表示B列到D列的全部区域,这是比较简单快捷的写法,而负责任一点的写法,应该是$B$2:$D$13,因为这个才是我们要查找并返回结果的有效数据区域,如果数据区域下方还有别的数据,那最好还是使用后面用具体区域的写法。

 

这个参数里有两点必须注意的:

  1>.数据区域的第一列,必须是你第一参数中指定要查找的内容的那个字段。

可能你看不明白,这样来说吧,我们例子要找的是“NAME”,那么第二参数里指定的区域,必须以NAME那一列为第一列,虽然我们的数据库是A列到D列,但我们在参数里只能写B:D,而不能写A:D,因为A:D的第一列是“NO.”,不是我们要查找的“NAME”……

如果我们现在想根据“NO.”返回后面对应的“NAME”,那就可以以A:D作为数据区域。

 

这里也引申出一点,就是VLOOKUP只能从左往右查找,如果想从右往左查找,则需要另外构造数据源或者换函数,新人们使用过程中要遵循这一点,不要想当然了。。

 

  2>. 数据区域里,必须包含你要返回的字段。

  例子中要返回的是“CODE”,而B:D列则包括了C列,所以是成立的,如果只写B:B,就没办法返回我们要的值了。

 

col_index_num,这是第三参数,表示你要返回结果的值(的字段),在你第二参数里指定的数据区域中的第几列。

这个很好理解,例子里我们写的是“2”,因为以NAME为第一列,CODE则是B:D区域中的第2列,那么后面H4单元格的公式,我们要返回的是PART,你就应该知道这个公式应该怎么修改了吧?

有时候可能你会看到别人用COLUMN()放在这个参数里,这又表示什么意思呢?(作为思考题吧)

 

[range_lookup]这是第四参数,表示的是要在数据库中查找数据的匹配方式,有两种参数可以选择,TRUE和FALSE,其中True表示模糊查找,False表示精确查找。

 

1>.这个参数使用了中括号,表示该参数是可省略的,而省略的时候,默认使用TRUE,也就是模糊查找方式;

 

2>.True和False我们简单描述是模糊与精确的关系,但实际上使用True时,要求你要匹配的列,必须是按“升序”排列好的,比如说上例中如果我们最后的参数用的是True,那么B列的NAME就首先要按从小到大排序好,不然就匹配不到结果,可能结果正确但那也只是碰巧。

False则没有排序的这个要求,你的数据可以是乱序的,因此通常情况下,我们使用的都是False。

 

-------------------------

加个小插曲,如下图,先看看下面这5个公式,其区别在于第四参数,你能辨识她们各自代表的查找方式是哪一种吗?她们又有什么区别呢?

 

 



首先,先科普一个小知识,在Excel表格中(与VBA的定义有区别),False能转化为数值0,而True能转化为数值1所以你可以用0表示False,用非0的数值表示True。以此作为背景,我们再来解释上面这5个公式函数的含义和区别,这个也经常应用在其他很多公式参数的书写上。

 

首先,公式1、2、3这三个公式代表的含义和查找方式是一样的,也就是FALSE的精确查找方式;

后面,公式4、5两个公式,使用的则是TRUE的模糊查找方式。

 

   有上面的小知识作为解读的依据,其实就不难明白了,更多疑惑的应该是公式3和公式4,为什么差距这么大呢?

   首先,公式4已经完全省略了第四个参数,参考语法规则,省略第四参数的情况下,按True进行模糊匹配,这样说你应该容易理解也没有异议吧?

   那么第3条公式呢?我们可以注意到,公式3的最后面有一个逗号,这个逗号有跟没有是有本质上的区别的,有的话就表示我们需要使用第四参数,而不是省略,而后面没有数值,Excel就按“空值”来处理,而空值转化为具体的数值,就是0,所以又回到公式2的情况。

   这是Excel的智能,也是我们的苦恼,因为有时候当你用VLOOKUP要返回的那一列的值是空的时候,VLOOKUP公式给你提供的结果会是“0”,而不是空白单元格。

 

语法介绍完了,示例公式里为什么那样写也基本上解读完了,现在,你试一下自己书写H4单元格的公式吧!

有兴趣可以用你自己书写的公式回复这条博客哦……

 

 

------------------------------

错误处理:下面我们说说常见的一些错误,并按下面罗列的可能原因按顺序排查即可:

 

 



1.#N/A  - “数据缺失错误”,最直接的解读就是告诉你,在你指定的数据区域的第一列里,找不到你第一参数说的那个值

 

 -看一下你第二参数指定的区域的第一列,是不是你第一参数要查找的值所在的那一列,有时候可能你选错区域了;

 -第四参数有没有使用错误?如果是True的话,数据匹配区域第一列要按“升序”排列哦;

 -第二参数引用的区域,所使用的单元格引用方式是否正确,如果未用绝对引用,下拉填充过程中,查找区域会逐渐变小哦;

 -用Excel的“查找”功能,手动在数据列表里查找一下你第一参数要找的值,看是不是存在;

 -确认一下查找值与数据列表的值的数据格式是否一致,有可能你提供的是文本,而数据库里是数值,或者相反;

 -确认一下查找值与数据列表里的值是否完全一致,有时候一方的值的后面会有多余的空格或者其他不可见字符;

 

2. #REF! -“引用无效错误”,表示你第三参数指定要返回的第几列,压根就没包括在你第二参数指定的区域里

 -怎么修改就应该一目了然了吧?

 

 

--------------------------

 

错误屏蔽:某些情况下,我们写的公式检查没有错,但数据列表里确实不存在要查找的值,那就铁定会返回#N/A错误,毕竟我们都喜欢整洁,不想表格里零零落落分布着#N/A错误,那可怎么办呢,有没有办法在公式里屏蔽这样的错误呢?

答案是肯定的,一般人会直接教你用=IF(ISERROR(原公式),"",原公式),或者=IF(ISNA(原公式),"",原公式),这什么意思呢?就是用ISERROR函数,判断你“原公式”返回的结果是不是错误值,如果是就显示""(空值),也就是什么都不显示,如果没有错误,就显示原公式的结果。

拿上面的例子来说,我们处理后的结果应该是:

=IF(ISNA(VLOOKUP(F4,B:D,2,FALSE)),"",VLOOKUP(F4,B:D,2,FALSE))

当然,我会觉得这样的公式太长,所以一般会教人使用=IF(COUNTIF(),原公式,""),这种方式样写出来的公式就是:

=IF(COUNTIF(B:B,F4),VLOOKUP(F4,B:D,2,FALSE),"")

 

而自从07版新增IFERROR函数之后,我们在xlsx格式的文档中写容错公式就可以更简洁了,直接写为:

=IFERROR(原公式,"")

如:=IFERROR(VLOOKUP(F4,B:D,2,FALSE),"")

 

----------------------------

另外说一下,源数据里NO.5和NO.12的NAME是一样的,如果我们同样使用“Michel”来查询后面的值,返回的只会是排在前面的NO.5的记录哦,这一对一查找也算是VLOOKUP的一个特性,因此用VLOOKUP查找时有多种查询关键值时最好是使用如号码、证件号等有唯一性的关键词,而姓名、地区等可能有重名的则不能为首选,不然会徒劳无功哦。

至于一对多的查找,那需要用复合函数嵌套,如典型的INDEX+IF+SMALL+MATCH+ROW~~~

 

 

   语法和使用其实很简单,但要注意的小细节也非常多,这里也没办法一一列举,比如说如何确认查找值与被查找值的数据类型是否一致,当我们希望从右往左查询时怎么变换?这都需要其他方面的综合经验,因此需要多用、甚至多犯错才能吸收,才能运用自如。

   VLOOKUP的姐妹函数HLOOKUP语法和使用均是一样的,是当你的源数据列表的形式是“转置”的形式时使用,骚年,加油吧……VLOOKUP是把利器啊,灵活运用可以大大提高工作效率,比如说对比两列数据是否一致

本站仅提供存储服务,所有内容均由用户发布,如发现有害或侵权内容,请点击举报
打开APP,阅读全文并永久保存 查看更多类似文章
猜你喜欢
类似文章
【热】打开小程序,算一算2024你的财运
让你从菜鸟成为玩转Excel的高手
如何将符合条件的多行数据中的值放到一个单元格里面
你还不会逆向查找?这三个小技巧分享给你!
如何快速提升EXCEL水平?
Excel函数应用篇:函数Vlookup
excel两列对比的四个实例
更多类似文章 >>
生活服务
热点新闻
分享 收藏 导长图 关注 下载文章
绑定账号成功
后续可登录账号畅享VIP特权!
如果VIP功能使用有故障,
可点击这里联系客服!

联系客服