理解VLOOKUP函数首先我们来看一下它的语法结构: VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])。 用中文来理解就是VLOOKUP(查找值,查找范围,查找列数,精确匹配或者近似匹配)。 可概括为一句话:通过相关的某一列在指定范围内查找到精确或近似的目标列。 我们依次来看一下它的四个参数: 1.查找值 相关的某一列,可以理解为需要进行数据匹配的关键列,它必然是查找范围中的一列,且一般具有唯一性。 简单粗暴的解析为查找值的选取遵循以下原则: (1)属于查找范围中的列 (2)此列在查找范围中数据是唯一的 为帮助大家理解,下面我们来看个例子:
图1:通过工号V出的结果 为什么不选姓名? 因为如果查找值在查找范围中的数据不是唯一的,那么查找值对应V出的结果为重复数据中从上往下第一次出现的数据对应的值。 图2:通过姓名V出的结果 如图2所示,通过姓名V出来的结果,由于表B中有两个小明,表A中小明的薪资V出来以后均为表B中第一次出现的小明对应的6000的数值。 显然这不是我们想要的结果,大家可以自行试验一下。 2.查找范围 指定范围,可理解为包含目标列的范围,且该范围的第一列必须是与查找值对等的列。 简单粗暴的解析为查找范围的选取遵循以下原则: (1)至少包含与查找值对等的列以及目标列 (2)与查找值对等的列必须是第一列 为帮助大家理解,下面我们来看个例子:
图3:查找范围选取 为什么与查找值对等的列必须是第一列? 因为如果不是第一列,将V不出数据。 图4:查找范围的第一列不是查找值 如图4所示,假设我们以表A的姓名为查找值,以整个表B为查找范围,则表B中与查找值对等的姓名位于第二列,那么薪资的数据将无法V出。 显然这不是我们想要的结果,大家可以自行试验一下。 3.查找列数 目标列所在列数,属于查找范围从左向右数,从1开始至n中的某一列。 以上文中表A、表B、题1为例。假设查找范围为整个表B,薪资为目标列,则查找列数为3。(如图5所示) 图5:目标列薪资为第3列 4.精确匹配或者近似匹配 (1)精确匹配:0或FALSE (2)近似匹配:1或TRUE 在实际应用中,几乎所有场景下都需要精确匹配,填0或FALSE就对了。 近似匹配的情况本文不做讨论,大家有兴趣可自行试验。 VLOOKUP函数的应用1.去空格 要保证查找值和查找范围的第一列没有空格,否则可能导致V不出数据。 通常用的方法是选中查找值那一列,按“Ctrl+H”快捷键弹出替换窗口,在“查找内容”处输入一个空格,“替换为”处不输入任何东西,然后点击“全部替换”按钮即可去除空格。(查找范围的第一列用同样的方法去除空格,如图6所示) 图6:去除空格 注:WPS和Microsoft Office会有所差异,同时也有其他多种方法去除空格,本文不展开讨论,有兴趣的网友可自行搜索相关资料。 2.设置单元格格式 (1)要保证查找值和查找范围的第一列单元格格式一致,否则可能导致V不出数据。 此处主要针对纯数字,大家可能会遇到有的单元格左上角有三角形的小图标,有的则没有,这时候V出的数据就会有问题。(如图7所示) 图7:单元格格式不一致 小编的解决方法:统一为查找值和查找范围的第一列加上一个固定的字母。 图8:统一加上固定字母 如图8所示,为工号统一加上一个固定字母“A”后,以表A的“工号A”为查找值、以表B的“工号A、姓名、薪资”为查找范围,则可顺利V出薪资的值。 统一加固定字母的方法为:以图8为例,在工号(A列)后新增一列(B列),在B2输入公式:=A2&'A'(通用公式可表示为:=目标单元格&'固定字母',公式中的引号必须是双引号),按回车键;然后鼠标移到B2右下角,出现黑色十字架图标后,双击向下填充公式。 如果还有其他更好的方法欢迎大家留言交流。 (2)要保证输入VLOOKUP函数的单元格格式为常规,否则可能导致V不出数据。 此处没什么好讲的,记住即可。 3.选择查找范围 查找范围必须是某一固定的区域,不应随查找值的变化而变化。通常可通过以下两种方式固定查找范围: (1)VLOOKUP公式输入完成后,选中公式中的查找范围,按F4进行锁定。 图9:查找范围不固定和固定后的区别 如图9所示,按F4锁定后会出现$符号。 $符号的应用:加在字母前就锁定列,加在数字前就锁定行,都加上就行列都锁定!(如图10所示) 图10:$符号的应用 一般用于两个表中有数据出现在同一列的情况,行和列都要锁定! (2)直接选整列 一般用于两个表的任意数据均不在同一列的情况。(如图11所示) 图11:取值范围选取整列 在实际应用中,多数情况下数据都是分散在不同Excel表或不同工作簿内,所以查找范围可直接选取整列,快捷方便。 总结VLOOKUP函数的难点主要在于前两个参数(查找值以及查找范围)的选取和处理上,我们再来回顾几个关键点: (1)查找值与查找范围的第一列对等,且要去除空格,以及保持格式统一 (2)查找范围的第一列数据不可重复 (3)保证查找范围是固定的,不可变化 (4)保证输入公式的单元格格式为常规 (5)查找列数需要在查找范围内从1开始从左往右数 (6)最后一个参数填0或FALSE 掌握以上几个关键点,恭喜你,你再也不用为VLOOKUP函数V不出来数据而发愁了! 喜欢我的作品的话请大家关注,我将为大家带来更多的作品~ |
|