betway必威官网手机版-betway必威官网下载
VLOOKUP函数是Excel中的一个纵向查找函数,它与LOOKUP函数和HLOOKUP函数属于一类函数,在工作中都有广泛应用,下面小编就来详细的说一下vlookup函数的使用方法

△用VLOOKUP函数制作的生产日报
函数的学习,大家一直以来都觉得特别复杂、难懂。然而,Excel的高效办公中,肯定是离不开函数的熟练应用。在函数的学习中,推荐大家,“由难到易”,先从复杂的学起,这样大家学习其他简单的函数,也便变得容易起来。得其一,而知百,比如:先学习复杂的4参数函数。
今天我们就来聊一聊,函数界“人见人爱、花见花开”的查找神器:VLOOKUP函数。 【1】基本语法
△
VLOOKUP查找和引用函数
公式的写法是:
=VLOOKUP(Lookup_value,Table_array,Col_index_number,Range_lookup)
参数①Lookup_value:要查找的值
参数②Table_array:要在其中查找值的区域
参数③Col_index_number:区域中包含返回值的列号
参数④Range_lookup:精确匹配或近似匹配 – 指定为 0/FALSE or 1/TRUE
【函数功能】:根据被查找值,在查找的数据源区域,按列查询,并返回指定列数下,所对应的值。
要求:
函数的第2参数(在选定数据源时),将被查找的值必须位于选定数据源区域的最左侧。

在B2单元格中,需要根据A2的值(联系人:表姐),在数据源区域(B5:E11)中,查找其对应的第3列的值(身份证号码),是多少,并且要求精确查找。
将上述汉语的逻辑转化为公式:
=VLOOKUP(A2,B5:E11,3,0)
注意:
在选定数据源的时候,要求“联系人”列,必须位于,因此所选择的区域并非从A5开始选择,而应当从B5开始,往后往下开始选定数据源区域。
小技巧:
公式不用背,在单元格内,输入=VL,然后按住【Tab】键,公式自动填充。

效果图:

单元格自动补齐了函数,并贴心的带上了左括号(。
对于初学者,可以在输入函数以后,按fx调取函数参数对话框,进行学习。

【2】查询实例
有小伙伴留言提问:
我要核对两张几千行的Excel表中,哪些数据是已经出现过的?哪些是没有出现过的值?现在我是用“Ctrl+F”去查找,但特别慢。

答:只需要分别针对两列数据,用VLOOKUP函数去对方的区域中查找,是否包含该值。如果查找的结果是错误值,则证明该数据没有在对方区域中出现过。

B2的公式=VLOOKUP(A2,D:D,1,0)
根据A2的值,在D:D的数据源区域中进行查找,并且返回该数据源区域中的第1列的值,进行精确匹配。
E2的公式=VLOOKUP(D2,A:A,1,0)
根据D2的值,在A:A列中精确查找第1列的值,并将查找结果,返回在E2单元格中。
#N/A
此时,如果单元格计算的结果,显示为,则表示:。 【3】错误分析
①错误值:#N/A(查无此值)
1.空白符
当单元格中包含空白符时,我们往往无法通过肉眼判断,两个单元格的值一致,但在Excel的语言当中,表姐≠表 姐

解决方案:
a.用查找替换的方式,将所有空格符,替换为空:

b.用SUBSTITUTE函数,将单元格中的空白,替换为空值""(即连续输入两个英文状态下的双引号)

c.用TRIM函数,删除字符串中多余的空格。
但如上图所示,在C4单元格中,表 姐中间包含2个空格时,函数的计算结果,并不能完全有效的清除所有的空白符。

2.文本型数字
很多后台导出的数据中,产品id、编号等,看似是数字格式的,但其单元格格式还是文本。造成VLOOKUP的查询结果,显示错误。

A列,系统导出编号:文本格式
D列,产品编号:常规格式(数值)
解决方案:
a.文本转化为数字:选择性粘贴*1

b.数字转化为文本:分列为文本

3.制表符char(9)
从很多信息化管理系统中导出的表格数据,会包含制表符,它会把数字前,空白单元格中,默认输入制表符。
比如在下图中的G3单元格,看似空白的单元格中,实际上并非完全空白。
如果将G3单元格,复制粘贴到查找对话框中,您会发现有一个" ",并且它不能够通过查找替换来消除。

解决方案:
用分列的方法,去除制表符
注意:在分列向导第三步中,选择【常规】

②错误值:#REF!(区域有误)
当VLOOKUP函数的第二函数选定区域错误时,或第三参数要求返回的列数(3)>选定区域(2列)时,会出现查询结果为#REF!的错误提示:

解决方案:
修订函数的参数。
③屏蔽错误值:IFERROR
在公式编写时,有时为了版面的整洁,通常会用错误值判定函数,将单元格内的错误值屏蔽,显示为空白等。

公式:=IFERROR(VLOOKUP(A2,$D$2:$E$9,3,0),"")
含义:
如果VLOOKUP(A2,$D$2:$E$9,3,0)查找的结果为错误值,则显示为"",否则显示为VLOOKUP(A2,$D$2:$E$9,3,0)的查找结果 【4】逆向查询
逆向查询
在工作实践中,往往我们的数据表格格式是固定的,被查询的值,不一定会放在最左侧列,这就要求我们通过VLOOKUP函数实现的功能。

如上图所示,根据物料编码,查询产品类别、名称等信息。
要打破数据源布局上的约束,我们需要在Excel当中用数组,构建一个从左向右的列表区域,供VLOOKUP函数来调用:

IF({1,0},F:F,B:B)
公式:=VLOOKUP(I2,,2,0)
重点解析:
通过IF({1,0},F:F,B:B)在Excel中,构建虚拟数组列表,且F列在第1列,B列在第2列。 【5】一对多查询
众所周知,Excel只能针对被查找数据,一对一的进行查询,当数据源中出现多个相同的被查找值时,默认按照从上到下,第一个被查询到的结果,进行返回。

如果需要根据所选部门,将对应该部门的所有报销明细,进行查找呈现,则需要VLOOKUP函数,能够实现一对多的查询。
解决方案:
为每个部门的出现次数,进行编号,即:从上至下,将销售部,分别标识为:
销售部1,销售部2,销售部3,销售部4......然后再分别查找销售部1~4对应的报金额、报销日期

构建辅助列A列,A6的公式
=C6&COUNTIFS($C$1:C6,C6)
A列辅助列,表示:
C列的每一行,都从$C$1单元格开始,向下至当前行,判断当前行C列(C6)的值,出现的次数。
再用&连接符,将C列(C6)的值和出现的次数,进行连接。
这样就在原来的数据表格基础上,构建了一个唯一值序列。
然后只需要将新构建的辅助列,用作VLOOKUP函数中,进行查询就好:

F5单元格的公式:
=IFERROR(VLOOKUP($F$2&ROW(A1),$A$1:$D$11,2,0),"")
其中ROW(A1),表示返回A1单元格的行数,即为1。
这样方便公式向下填充时,序号可以随行数的增加而自动递增。 【6】企业级应用案例
【数据基础】建立全面的完工日报流水记录

【报表查询】根据完工流水记录,根据所选的日期或月份,实时生成产品完工日报、月报;
使用统计求和函数,可以在月报表的顶部,构建统计汇总表

最重要的是,这份查询表,可以根据明细数据源的增减变化而自动更新。
是的,您只需要把明细表,更换成您公司的产品、业绩、人员招聘、成本支出、费用预算、工程结算等等具体的内容,就可以像本文开头的动图一样,实时查询具体的数据明细了。
|