Excel作为最优秀的软件之一,丰富的功能让我们的工作变得简单有效。但是这有个前提就是,我们要学好它、掌握更多的技能,遇到实际问题时才能手到擒来,干净利落地处理掉。 在这里我还要多啰嗦一句,大家如果对于分享中的知识点有任何不明白的地方,可以加入【Excel轻松学】QQ群9735376讨论,可以给我写邮件blesschao@163.com,也可以加我的微信,进入微信群讨论。 今天我们就来看看Vlookup这个函数。 Vlookup可以说是我们再熟悉不过的一个函数了。很多人在工作中接触的第一个函数可能就是Vlookup,因为它太常用了、太有用了。可是我们真的充分、合理、高效地使用Vlookup了吗?下面我们来总结一下该怎么玩转Vlookup。 语法:VLOOKUP(要查找的值, 要在其中查找值的区域, 区域中包含返回值的列号, 精确匹配或近似匹配 – 指定为 0/FALSE or 1/TRUE)。 说明:第二个参数'要在其中查找值的区域'的第一列一定要是第一个参数“要查找的值”所在的列,第三个参数表示从一个区域中返回第几列的值,第四个参数通常都用精确匹配,可以直接输入0或者false或者省略这个参数(逗号不能省略)。 有几点需要注意:
下面我们来看Vlookup的应用知识。这些都是常用的,我们推崇的是复杂的问题简单化,能用一步就不用两步,能用简单方法就不用复杂的方法。学习Excel这个工具的时候,我们还是要以简单实用为主。下面这些应用,我觉得还是非常常用的,大家有必要学会的。 1、常规使用 需要从以下数据区域中查询并返回特定产品在二月的销量 在F2单元格输入以下公式: =VLOOKUP(E2,A:C,2,0) 这里的第二个参数是A:C的整列,也可以换成一个指定行号的区域,如下; 这个是Vlookup最常见的应用,比较容易理解。 2、与Column函数组合 需要从以下数据区域中查找连续的多列,比如,我们需要查找“糖果”和“桂花糕”在Jan、Feb、Mar的数量。 在J2单元格输入以下公式,然后向右、向下拖拉公式来复制填充。 =VLOOKUP($I2,$A:$G,COLUMN()-COLUMN($J$1) 2,0) 需要注意的问题点:
千万不要小看我们举的简单例子哦,在遇到庞大的数据的时候,一个公式就搞定,是不是很爽呢! 3、与Match函数组合 上面提到的,我们需要取不连续的列中的数据,这个时候我们常用的就是用Match确定要取的数据是在第几列。 还是上面的数据,我们需要取Feb、Apr、May的数据,也就是取不连续的列中的数据。 在J2中输入以下公式: =VLOOKUP($I2,$A:$G,MATCH(J$1,$A$1:$G$1,0),0) 需要注意的问题点:
4、使用通配符 通配符有两个,*和?,*代表任意多个字符,可以是空值,可以是一个,也可以是多个;?代表任意一个字符 有以下数据A:B列。我们只有A列数据中的数字,需要根据数字来查询对应的B列的值。 我们从E2开始分别输入以下公式来说明一下。
E2单元格的公式是查找以“123456”结尾的值在B列中对应的数字。 E3单元格的公式跟E2单元格的公式类似。 E4单元格的公式为什么查不到数值呢?这是因为'?'&D4的写法表示任意一个字符加上“M2345”,字符串长度=1 5=6,大家看数据源中没有这样的数据。所以需要大家注意,?强制占了一个字符,而且它不能代表空值。 E5单元格的公式跟E4的类似,只不过'?'&D5代表任意一个字符再加上123,而数据源中有S123这个值,所以就能返回结果。 E6单元格中的公式也返回错误值,是因为数据源中有MM45678这个值,它是两个字符开头,再加上45678这个数字。这里?只占一个字符,所以公式就返回错误值。 E7单元格就纠正了E6单元格的错误,'??'&D7,使用了两个?,所以就能返回正确的结果。 E8单元格也没有返回正确的值,是因为 '*'&D8&'*' 中前后都加了*,表示查找包含2345的值,而数据区域中的第一个值S123456就包含2345,所以公式返回对应的100,而不是想要的90。 5、返回结果是错误值的处理 有时候我们公式查找没有返回结果,我们不想显示错误值,该怎么处理呢? 比如上面的示例中,E4和E6单元格都返回错误值,我们可以用If Iserror或者Iferror来处理。以下是两种方法的示例。 E4单元格公式=IF(ISERROR(VLOOKUP('?'&D4,A:B,2,0)),'未找到',VLOOKUP('?'&D4,A:B,2,0)) E6单元格公式=IFERROR(VLOOKUP('?'&D6,A:B,2,0),'未找到') 假如能查找到内容,但是返回了0值,我们可以用&''的方法将结尾显示为空白内容。 E9单元格公式=VLOOKUP(D9,A:B,2,0)&'' 6、逆向查找 通常情况下,我们都是从左到右来查找数据,但是有时我们也希望能从右往左查,并且不想更改原表格的格式。这时我们就需要用if {1,0}数组的形式来重新构造数据区域了。 我们在E2单元格中输入公式: =VLOOKUP(D2,IF({1,0},B:B,A:A),2,0) IF({1,0},B:B,A:A)这种用法就可以调换列的前后位置,重新构造数据区域。 7、多条件查找 我们需要从以下区域中查询指定产品和月份对应的数量,这里需要根据两个条件来查,分别是产品和月份。 方法1:简单地来做,我们可以添加辅助列,如上图所示,在A列添加辅助列,用&符号连接B和C列。在G2单元格输入以下公式: =VLOOKUP($F2&G$1,$A:$D,4,0) 方法2:我们也可以不用辅助列,上面我们讲到了if {1,0}可以重新构造数据区域,我们在G3单元格输入以下公式: =VLOOKUP($F3&G$1,IF({1,0},$B$2:$B$13&$C$2:$C$13,$D$2:$D$13),2,0) 这是数组公式,需要同时按Ctrl Shift Enter来结束。 8、返回多个匹配值 以下数据是产品的物料清单,我们需要将产品对应的物料横向显示出来。 我们在A列添加辅助列,在A2单元格输入以下公式: =COUNTIF(B$2:B2,B2) 这个公式的作用是根据不同的产品,将每个物料分别编号。比如产品1有三种物料,分别编号就是1、2、3。注意这个公式里面的区域范围是B$2:B2,这个表示从B2单元格开始,随着向下拖动复制公式,该区域也逐渐变大,这样就会逐步包含进来更多的物料,再按照产品计数,就得出了该产品所有物料的顺序编号。 然后这个问题就变成了一个多条件查找的问题,可以参考上面给出的公式,在F2单元格输入以下公式,并按Ctrl Shift Enter结束。 =IFERROR(VLOOKUP(F$1&$E2,IF({1,0},$A$2:$A$6&$B$2:$B$6,$C$2:$C$6),2,0),'') 9、类似函数HLookup Hlookup跟Vlookup相似,只不过Vlookup是在列里面查找与行内容对应的值,而Hlookup则是在行里面查找与列内容对应的值,这个区别主要还是看数据区域的构造。 这里的首字母H是Horizontal,即水平的意思;相应的Vlookup中的V就是Vertical,即垂直的意思。理解了意思也就容易记住了吧? 在G2单元格输入以下公式: =HLOOKUP($F8,$7:$10,COLUMN()-COLUMN($G$7) 2,0) |
|