LOOKUP家族就三兄弟,LOOKUP,VLOOKUP和HLOOKUP,其中最最常用的就是VLOOKUP。在面试时候如果有问到你excel的水平或者有Excel上机测试的时候,VLOOKUP是绝对绝对绕不过去的考点,还可能是唯一一个被问到的函数。 1. LOOKUP LOOKUP函数用来从单行或单列或从数组中查找一个值,在excel帮助的官方文档中,官方强烈建议在数组形式下使用VLOOKUP或者HLOOKUP,而在向量形式下可以使用LOOKUP(单行区域或单列区域被称为“向量”)。不过在我看来,VLOOKUP和HLOOKUP也完全可以做到LOOKUP在向量形式下的一切功能,所以在这里我就直接进入VLOOKUP吧。 2. VLOOKUP 学习函数,可能每个人方法不同,但是函数的作用和语法结构是绕不开的第一步。 2.1 VLOOKUP作用: VLOOKUP是一个查找和引用的函数,作用是按列查找,最终返回该列所需查询列序所对应的值。再说得仔细点,搜索表区域首列满足条件的元素,确定待检索单元格在区域中的行序号,再进一步返回选定单元格的值。这里要特别强调一点,如果你的搜索表区域内有不止一个和查找的值相同的数据,那么VLOOKUP只会找到第一个对应的数据的。 2.2 VLOOKUP语法结构 VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])
2.3 VLOOKUP精确匹配应用实例 因为精确匹配更为常用,所以先来个精确匹配的实例。为了演示的方便所以源数据量很小,有的人觉得肉眼看比公式快多了,但是如果数据源是上千行,查找的内容有几十个的情况下,函数的便捷性就很明显了。 我们来分解一下这个函数公式,先再来看一下函数的语法结构:VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])
2.4 VLOOKUP精确匹配使用通配符应用实例 还有一个小技巧是使用通配符查找,以例子讲解。下例中,查找公司名称时候,可能数据源是公司全称,而有的不是。如果不使用通配符,就很可能出现源数据中其实有,但是精确匹配就找不到的情况。例子中使用了通配符*(星号),可以看到图中查找的值,是G3&'*'而不是G3,*作为通配符,可以代表任意数量的字符,就解决了上述问题。 2.5 VLOOKUP模糊匹配应用实例 模糊匹配虽然使用的少,但是也有它自身的适用范围,在给学生的成绩打ABCDEF,或者年龄分段,或者销售员的提成百分比这些情况下,就特别适合使用模糊匹配。 下例是一个计算销售人员提成的例子。销售人员的提成比例不是固定的,而是和该销售人员销售金额(F列)有关,当你卖出去的金额越高,提成的比例也就越高。销售金额在2百万以下,提成比例就只有5%,如果销售金额超过了2百万但是不到4百万,提成比例就是10%,以此类推。这个需求显然就无法使用精确查找了,模糊查找就可以达到效果。 公式语法完全和上面一致,不再重复了,唯一的不一样就是[range_lookup]应该是TRUE或者1,作为模糊查找。 模糊查找需要注意的是,源数据一定是要进行升序排列的,如果降序排列,恭喜你,你就会得到一大堆的报错了。 2.6 VLOOKUP的报错 这里只说一个最最最容易得到的报错,#N/A。#N/A的报错原因是公式无法找到引用的值。像上面精确查找的时候,如果你将名字改成韩梅梅,在源数据中没有这个人,Excel就会果断丢给你一个#N/A。 因为这个#N/A的存在,所以VLOOKUP函数不光会被用于上面举例的这种列偏移的查找,还会更更普遍的被用于两个数据表之间的数据的互相对比,如果你用A表的数据作为查找值,用B表作为查找范围,那么出现#N/A的就说明该数据在A表中有B表中没有。 但是,当你不是在数据对比的时候,而是列偏移填数据的时候,#N/A就非常非常之碍眼了,很多时候我们会希望找不到就显示为0。所以这里穿插介绍一个小小的函数iferror。因为不是该篇主角,就介绍个语法:IFERROR(value, value_if_error)还有下图无敌简单的实例。就酱紫。 还有一种不能算报错的情况如下,公式都对啊,可是怎么不显示结果? 这时候请大家看一下自己的单元格格式是不是设置成了文本? 把文本改为常规,之后再在单元格的公式行里回个车,就会看到自己要的结果啦。 3. HLOOKUP HLOOKUP和VLOOKUP是绝对的亲兄弟or亲姐妹,语法结构也基本是一模一样的。唯一的区别只是,列偏移还是行偏移。 语法结构: HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup]) 应用实例: 通常来说,这种源数据为大家所不喜,因此建议先使用特殊粘贴的方式把数据转置再用VLOOKUP。源数据的质量排列在excel数据中真的是怎么强调都不为过。 本文来源KeepLearn,作者Vicky_Mel,版权归原作者所有。财会学堂整理发布。 每天学点财会知识,让你的职场up起来!欢迎关注微信号财会学堂(caikuaixuetang) |
|