深度解读VLOOKUP四大参数以及最新用法
作者:逍遥来源:部落窝教育发布时间:2023-05-09 11:16:27点击:2244
编按:
从VLOOKUP的四个参数来深度解读VLOOKUP的使用规则和用法。包括最新用法:不用下拉填充公式即可完成所有查找值的查找。
一说起查找函数,大部分人都会想到VLOOKUP函数。
作为一个有34年历史的老牌函数,VLOOKUP也算是声名远扬,无愧 “查找之王”的美称。但在我看来,他既有光鲜的一面,也有一堆糗事,与他的笨拙和固执分不开。
怎么说?
今天就从VLOOKUP函数的4个参数来深度解读VLOOKUP,同时介绍最新用法:在第一和第三参数引用数据区域或者数组,不用下拉填充公式即可完成所有查找值的查找。
分别是:查找值,查找区域,返回列,匹配类型。
第一参数查找值:表里如一的坚守者;灵活变通和与时俱进者!
在这里我们可以看到VLOOKUP成为之所以成为明星函数的原因:坚守、灵活、与时俱进。
1.表里如一
表里必须如一,来不得半点虚假。这是VLOOKUP的笨拙之处也是最可贵的地方!
如下图,我们要查找编号为714848的销售金额,Excel却给我们返回了一个错误值,这是咋回事呢?
取消A列和G列居中对齐,G2单元格的数字靠左,而A9单元格的数字靠右,原来G2和A9属性不一样!
Vlookup函数有自己的规则,查找的时候严格遵守【表里如一】的第一定律:数据属性一致。
如果查找值是文本,那么查找区域里对应的值也应该是文本;
如果查找值是数字,那么查找区域里对应的值也应该是数字。
再往下查,懊恼继续,怎么又被Vlookup发了一个好人“NA”卡?
通过取消对齐,排除了数据属性不一致的原因。
考虑是数据违反了第一参数【表里如一】的第二定律:字符数相等!
查找值与查找区域中的比对值必须字符数相等。
表面看到的字符是一样的,但因为有空格或者不可见字符的存在,两者实际字符数可能不等,是不一样的。
用LEN函数检查字符数:
A2单元格的字符数=LEN(A2)=8,G2单元格的字符数=LEN(G2)=7。字符数不相等,两者肯定不相等啦,也就查不到了。
TIPS:
几个常见的Excel不可见字符,水平制表符char(9)、换行符char(10)、空格符char(32)。
在单元格输入公式=char(9)即可得到一个不可见的水平制表符,其虽然不可见,但字符数为1。
彩蛋:如何纠正Vlookup看着有却查找错误的问题?
对于数据属性不一样的:网上有很多方式可以在文本数字和数字之间转换,这里就不说了。
不可见字符造成字符数不一样的:用公式=CLEAN(TRIM(SUBSTITUTE(要清洗的某单元格,CHAR(32),)))可以清除空格和常见的不可见字符。清洗后再复制并选择性粘贴覆盖原来数据即可。
2.灵活变通——支持通配符查找和支持多条件连接查找
表里如一的坚守并不等于死板和不知变通!
相反,第一参数非常灵活。
如果查找值本身不完整,如是简称,我们可以通过添加通配符“*”或者“?”来进行包含查找,如下图,查找鞋,即为查找包含鞋字的:
VLOOKUP的第一参数还支持用&连接多个单元格的内容,用于多条件查询:
3.与时俱进——引用数据区域或数组进行查找
这是Vlookup最新用法!
在低版本的Excel中,Vlookup的第一参数通常是一个数据,但在最新的office365、office2021版本中,VLOOKUP函数的第一参数可以直接引用数据区域或数组。有了这项支持,不需要下拉填充公式即可完成所有查找值的查询。如下,直接在I2中输入公式,第一参数引用数据区域G2:G7,即可完成所有订单查找。
第二参数查找区域:圈地爱好者与固执的向右查找者!
1.圈地爱好者
要查找先圈地。把包含查找值和返回值所在的整片区域都圈起来作为自己的领地,然后只在领地里进行查找,其他地方恕不接待。
比如下图中的B到E列就是此次查找的领地(查找区域)。
这点与它的兄长Lookup迥然不同,Lookup可以分别指定查找区域和返回区域,而不需要把两者圈在一期。
2. 固执的向右查找者!
这块地从哪里开始圈起?往哪个方向圈?往哪个方向查?
VLOOKUP要求领地中左起的第一列必须是查找值所在列,然后向右圈地;圈地后,查找也是从领地左起的第一列开始往右查。
譬如:查找值为订单编号,那么我们就要以订单编号这一列为第一列,向右进行圈地,直到圈到要查询的结果值——销售金额这一列为止。然后在B列中查找订单“762145”,找到后水平往右走,直到返回列。
一个小问题,能向左进行圈地吗?
比如下图,我们需要根据订单编号来查找销售部门,我们可以选中B列再向左边A列开始圈吗?
答案是可以!但选择的区域如B1:A15在公式输入完毕后自动会更正为A1:B15。
那能否向左查找呢?
答案是不可以!
TIPS
如果必须从右往左查找,可以请来IF这个热心肠的函数将这两列内容颠倒一下顺序,即用IF({1,0}把B列和A列组合在一起,并把 B列放在A列前面。
TIPS:
如果有向右或者向下复制公式的需求,我们还需要给这块查找区域上个保险。
通俗一点就是说,不想你千辛万苦圈起来的地到处乱跑,最好给它修个栅栏。修栅栏,就得花钱对不对?
所以我们用这个$符号,将它放在你需要固定的数据前面,这样,拖拽公式的时候,查找区域就不会再变化了。
TIPS:
VLOOKUP的查找区域是只能圈一块地,还是可以圈多块地呢?
比如现在我们要找西红柿的产量、销量、利润,而这些数据分别放在三个Excel表里,又该如何写公式?
借助INDIRECT函数可以实现多表查询数据,如下图所示:
第三参数返回列:笨拙的数数者
1.默认数数
返回第几列呢?VLOOKUP是边走边数数来确定返回列数的。从领地的第一列开始,一步一列,走到返回列有几步,就写几列。
尤其是同样的条件需要查找多列返回值的时候,每次都去数一次然后手动修改,特麻烦。
2.找朋友相助自动给出返回数
在查找多列返回值时,为避免数来数去以及改来改去出错,那就只有求人相助了。
VLOOKUP:各位大哥大姐,小弟能力有限,实在是活不下去了。俗话说在家靠父母,出门靠朋友……
众函数觉得VLOOKUP不摆明星架子,还算真诚,于是纷纷效犬马之劳。
(1)COLUMN,搞定有序变化的返回列
如图所示,要找出A、B、D产品在1、2、3月的销量,如果单单是靠VLOOKUP的话,只能频繁地去修改第三参数,于是COLUMN雪中来送碳。
=VLOOKUP($A14,$A$1:$G$10,COLUMN(B2),FALSE)
(2)MATCH——自动识别返回列
如果不是1月、2月、3月这样的有序排列,而是1月、3月、5月的序列,还有MATCH函数绝渡逢舟。
=VLOOKUP($A14,$A$2:$G$10,MATCH(B$13,$A$1:$G$1,0),FALSE)
3.支持数组一次返回多个值
这也是VLOOKUP的最新用法!
在低版本中,虽然Vlookup的第三参数可以输入数组,但需要提前选中多个单元格后再来输入公式并三键回车。现在不一样,直接选中第一个单元格输入数组,直接回车,结果会返回一组数。如下图求不同产品一二季度的销量。
将Vlookup的第三参数用花括号括起来,2、 3 、 4,这三个数据分别对应着 “产品A” 在1 、2、3月份的销售数量。然后在外面套一个SUM函数就得到了A产品第一季度的销量。
数组公式,低版本(office365、2019、2021等以下)的,需按Ctrl+Shift+Enter三键结束。
第四参数匹配类型:失误的反人类设计
1.反人类设计
第4参数是个可选参数,用于设置匹配类型。匹配类型有近似匹配(TRUE或1)和精确匹配(FALSE或0)两种。
默认为近似匹配,可以省略不写,而精确匹配则须给出参数,这和我们日常主要查询需求——追求精确——截然不同。
如下图所示,我们要查找编号为“786029”的销售数量,保持默认不填写第4参数,那对不起,只能得到一个错误的答案。
这就意味着,每次输完前三个参数时,你都需要谨慎地写出第4参数(FALSE或0),或者你至少要在第三参数后加一个逗号,才能精确查找。
2.也并非一无是处——做区间查找很合适
默认近似匹配也并非一无是处,当我们做区间查找,如根据数据查等级时,就可以少写一个参数,很省事。
譬如查A列的销售等级,只用三个参数即可。
注:利用近似匹配做区间查找时,查找区域首列必须是升序排列。Vlookup近似匹配时,其查找方法与Lookup函数一样,都采用二分法进行。需要了解的可以看《一文讲透LOOKUP二分法原理》。
VLOOKUP作为查找明星,很多Excel人都认他、用他。我们从四个参数入手,去掉光环,看到他的本真,有坚守,有笨拙,有固执,有失误。同时,Excel高版本中Vlookup有最新用法,可以直接用数组做参数,不用下拉填充公式即可完成所有查找。
有了这些理解,相信大家再使用VLOOKUP可以提前规避很多错误并提高效率。
本文配套的练习课件请加入QQ群:902294808下载。
做Excel高手,快速提升工作效率,部落窝教育《一周Excel直通车》视频和《Excel极速贯通班》直播课全心为你!
扫下方二维码关注公众号,可随时随地学习Excel:
相关推荐:
版权申明:
本文作者逍遥;同时部落窝教育享有专有使用权。若需转载请联系部落窝教育。