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,因为这个才是我们要查找并返回结果的有效数据区域,如果数据区域下方还有别的数据,那最好还是使用后面用具体区域的写法。
这个参数里有两点必须注意的:
可能你看不明白,这样来说吧,我们例子要找的是“NAME”,那么第二参数里指定的区域,必须以NAME那一列为第一列,虽然我们的数据库是A列到D列,但我们在参数里只能写B:D,而不能写A:D,因为A:D的第一列是“NO.”,不是我们要查找的“NAME”……
如果我们现在想根据“NO.”返回后面对应的“NAME”,那就可以以A:D作为数据区域。
❒ 这里也引申出一点,就是VLOOKUP只能从左往右查找,如果想从右往左查找,则需要另外构造数据源或者换函数,新人们使用过程中要遵循这一点,不要想当然了。。
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的模糊查找方式。
语法介绍完了,示例公式里为什么那样写也基本上解读完了,现在,你试一下自己书写H4单元格的公式吧!
有兴趣可以用你自己书写的公式回复这条博客哦……
------------------------------
错误处理:下面我们说说常见的一些错误,并按下面罗列的可能原因按顺序排查即可:
1. #N/A
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 ~~~
----------------------------------------
----------------------------------------