澳门赌场

办公软件 > Excel > 注释

Excel VLookUp函数的利用方式,含逆向查找、一对多查找和类似婚配实例

亮术网 2018-05-11 本网首创

VLookUp函数是 Excel 中用于按行查找指定值的函数;它最少要有三个参数,第四个参数为可选项,用于肯定婚配选项(切确婚配或类似婚配),若是省略,默许为类似婚配。VLookUp函数触及的查找环境比拟多,若是设置不准确能够前往多种毛病值。

VLookUp函数的利用方式出格多,本篇将先先容它的语法、根基利用方式和经常利用方式,此中根基利用方式先容了 6 个实例,经常利用方式先容了 3 个实例,实例包罗多种前往毛病值、在查找值中利用通配符题目(?)和星号(*)、逆向查找、类似婚配和一对多查找实例。

 

一、Excel VLookUp函数语法

1、抒发式:VLOOKUP(LookUp_Value, Table_Array, Col_Index_Num, [Range_LookUp])

中文抒发式:VLOOKUP(查找值, 查找地区, 前往列号, [婚配选项])

 

2、申明

(1)、Col_Index_Num 以指定地区左侧一列为第一列,比方指定地区为 B2:D8,则以 B 列为第一列,若把 Col_Index_Num 设置为 2,则前往 C 列的值。

(2)Range_LookUp 为可选参数,它共有两个选项,一个是 True,另一个是 False,True 为类似婚配,False 为切确婚配;若是省略 Range_LookUp,默许挑选 True。

 

(3)罕见前往毛病

A、若是把 Range_LookUp 设置为 True(类似婚配),须要对第一列排序,不然能够前往毛病值;若是把 Range_LookUp 设置为 True,LookUp_Value 比 Table_Array 中第一列的最小值小,将前往毛病值 #N/A;若是把 Range_LookUp 设置为 False,若前往毛病值 #N/A,表现Table_Array 中不查找值。

B、若是 Col_Index_Num 大于 Table_Array 的列数,将前往援用毛病值 #REF!。

C、若是 Table_Array 小于 1,将前往值毛病 #VALUE!。

D、若是公式贫乏引号,凡是前往称号毛病值 #NAME?。

(4)查找数字或日期时,不能把选中地区第一列设置为文本,不然能够前往不准确的值。

(5)若是把 Range_LookUp 设置为 False(切确婚配)且 LookUp_Value 为文本,则可在 LookUp_Value 中利用通配符问号(?)和星号(*);问号表现肆意一个字符,星号表现一个或多个字符;若是要查找问号或星号,须要在它们后面加本义字符 ~,比方查找题目,应当如许表现 ~?。

(6)在 Table_Array 中不能有前后空格、单双引号和非打印字符,若是有这些字符,应当用函数或替代的方式把它们去掉,不然能够前往不测值。

 

 

二、Excel VLookUp函数的利用方式及实例

(一)省略参数 Range_LookUp 的实例

1、假设要查找“粉红衬衫”的“销量”。选中 A11 单位格,把公式 =VLOOKUP(A11,B2:E8,4,) 复制到 B11,按回车,前往查找成果 892;操纵进程步骤,如图1所示:

Excel VLookUp函数的利用方式,含逆向查找、一对多查找和类似婚配实例

图1

2、公式申明:

A、A11 是公式 =VLOOKUP(A11,B2:E8,4,) 中的查找值,B2:E8 为查找地区,4 为前往列号(即以 B 列为第一列,前往第四列,即 E 列的值),公式省略了第四个参数 Range_LookUp,默许挑选 True(类似婚配);公式的意义是:在 B2:E8 这片单位格地区中查找 A11,在 B6 找到后,前往 B6 在 E 列的对应值 E6,即前往 892。

B、若是省略第四个参数 Range_LookUp,第三个参数后既可省略逗号(,)又可加上逗号(,)。

 

(二)把参数 Range_LookUp 设置为 True 须要对第一列排序和 LookUp_Value 小于第一列最小值前往 #N/A 的实例

1、双击 B11 单位格,把公式 =VLOOKUP(A11,D2:E8,2,TRUE) 复制到 B11,按回车,前往毛病值 #N/A;框选 D2:D8,挑选“数据”选项卡,单击“升序”图标,翻开“排序提醒”窗口,挑选“扩大选定地区”,单击“肯定”,则每行按“价钱”“升序”顺次,B11 中的值随即变为 892;选中 A11 单位格,输出 36,单击一下 B11,则 B11 中的值变为 毛病值 #N/A;操纵进程步骤,如图2所示:

Excel VLookUp函数省略参数 Range_LookUp 的实例

图2

2、公式申明:

A、公式 =VLOOKUP(A11,D2:E8,2,TRUE) 把参数 Range_LookUp 设置为 True,未对“价钱”排序之前,前往毛病值 #N/A(A11 中的 38.8 是 D 列中的最小值),对“价钱”按“升序”排序后,能前往准确的查找值 892,申明把参数 Range_LookUp 设置为类似婚配时,须要对选中地区的第一列排序能力确保前往准确的查找值。

B、即便对第一列排序,若是查找值(36)小于第一列最小值(38.8),一样会前往毛病值 #N/A。

 

(三)把参数 Range_LookUp 设置为 False 前往 #N/A 的实例

1、双击 B11 单位格,把公式 =VLOOKUP(A11,D2:E8,2,FALSE) 复制到 B11,按回车,前往毛病值 #N/A;操纵进程步骤,如图3所示:

Excel VLookUp函数把参数 Range_LookUp 设置为 False 前往 #N/A 的实例

图3

2、把参数 Range_LookUp 设置为 False(切确婚配)时,不论有不对选中地区的第一列排序,若是不找到值,都前往毛病值 #N/A。

 

(四)公式贫乏引号前往称号毛病值 #NAME? 实例

1、双击 B11 单位格,把公式 =VLOOKUP(“玄色T恤”,B2:E8,4,FALSE) 复制到 B11,按回车,前往称号毛病值 #NAME?;双击 B11,把公式中的全角双引号改成半角双引号,按回车,前往查找值 982;操纵进程步骤,如图4所示:

Excel VLookUp函数公式贫乏引号前往称号毛病值 #NAME? 实例

图4

2、全角双引号不能用于把公式的文本括起来,若是查找文本中有全角双引号,里面还要加半角双引号,比方 =VLOOKUP("“玄色T恤”",B2:E8,4,FALSE)。

 

(五)在查找值 LookUp_Value 中利用通配符问号(?)或星号(*)的实例

(1)在查找值中利用通配符问号(?)

1、假设查找“产物称号”以“粉红”开首且后面只要两个字的销量和查找 ? 对应的销量。双击 B11 单位格,把公式 =VLOOKUP(A11,B2:E8,4,) 复制到 B11,按回车,前往查找成果 892;双击 A11 单位格,把内容改成 ~?,单击一下 B11,B11 中的数值变为 982;操纵进程步骤,如图5所示:

Excel VLookUp函数在查找值中利用通配符问号(?)

图5

2、申明:

A、公式 =VLOOKUP(A11,B2:E8,4,) 中 A11(粉红??)表现以“粉红”开首且后面只要两个字的文本,它刚好是“粉红衬衫”,B3 和 B6 的服装网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net称号也以“粉红”开首,但“粉红”后不止两个字,以是不知足前提。

B、把 A11 中的内容改成 ~?,表现要查找问号,刚好前往 ? 对应的销量 982。Excel 中 ~ 显现为 - 是字体缘由,换种字体(如宋体)即显现一般。

 

(2)在查找值中利用通配符星号(*)

1、假设查找“产物称号”以“粉红”开首的、以“T恤”开首和以“红色”开首且以“衬衫”开首的销量。双击 B10 单位格,把公式 =VLOOKUP("粉红*",B2:E8,4,) 复制到 B10,按回车,前往查找成果 329;双击 B10,把查找值 "粉红*" 改成 "*T恤",按回车,前往查找成果 638;双击 B10,把查找值 "*T恤" 改成 "红色*衬衫",按回车,前往查找成果 897;操纵进程步骤,如图6所示:

Excel VLookUp函数在查找值中利用通配符星号(*)

图6

2、申明:

公式 =VLOOKUP("粉红*",B2:E8,4,) 中 "粉红*" 表现查找以“粉红”开首的服装网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net、*T恤" 表现查找以肆意字符开首且以“T恤”开首的服装网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net、红色*衬衫"表现查找以“红色”开首且以“衬衫”开首的服装网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net。

 

(六)参数 Table_Array 中有前后空格的实例

1、假设要查找 A11 中的“绿色T恤”。双击 B11 单位格,把公式 =VLOOKUP(A11,B2:E8,4,) 复制到 B11,按回车,前往毛病值 #N/A;双击 B11,把公式改成 =VLOOKUP(A11,TRIM(B2:E8),4,),按 Ctrl + Shift + 回车,前往查找值 528;操纵进程步骤,如图7所示:

Excel VLookUp函数参数查找地区 Table_Array 中有前后空格的实例

图7

2、公式申明:

A、B4 中的内容便是“绿色T恤”,但公式 =VLOOKUP(A11,B2:E8,4,) 前往毛病值 #N/A,而加去掉文本前后空格的函数 Trim 后,能前往准确的值,申明 B4 中有空格。

B、=VLOOKUP(A11,TRIM(B2:E8),4,) 是数组公式,以是须要按 Ctrl + Shift + 回车,因为 B2:E8 以数组情势前往 B2:E8 中的一切值,而后用Trim函数逐一把空格去掉。

 

 

三、Excel VLookUp函数的利用方式经常利用实例

(一)类似婚配

1、假设要按照均匀分查找对应先生的评定。选中 J9 单位格,输出 =a4,按回车,前往“黄月语”;双击 K9,把公式 =VLOOKUP(H4,J3:K6,2) 复制到 K9,按回车,前往“黄月语”的评定“良”;操纵进程步骤,如图8所示:

Excel VLookUp函数类似婚配实例

图8

2、公式申明:

A、公式 =VLOOKUP(H4,J3:K6,2) 意义是在 J3:K6(评定表)中查找“黄月语”的均匀分(H4)对应的评定,因为 H4 中的数字为 86,评定表中并不这个值,是以只能用类似婚配(即省略参数 Range_LookUp 或把它设置为 True),从而选出 80 到 89 分段的评定。

B、提醒:评定表中的分数必须按“升序”排序,不然能够前往不准确的值。

 

(二)逆向查找

1、假设要按照“产物称号”查找对应的“编号”。双击 B11 单位格,把公式 =VLOOKUP(A11,CHOOSE({2,1},A2:A8,B2:B8),2) 复制到 B11,按回车,前往“编号”NS-286;操纵进程步骤,如图9所示:

Excel VLookUp函数逆向查找实例

图9

2、公式 =VLOOKUP(A11,CHOOSE({2,1},A2:A8,B2:B8),2) 申明:

A、{2,1} 是Choose函数数组情势的用于指定前往哪一个值的索引号,A2:A8 和 B2:B8 是前往值,数组中的 2 代表 A2:A8 中的值,1 代表 B2:B8 中的值;履行时,起首从数组中掏出 2,而后从 B2:B8 中掏出 B2(即“红色纯棉T恤”)和从 A2:A8 中掏出 A2(即“WS-580”),最初把这两个元素构成数组第一行的元素,即 "红色纯棉T恤","WS-580";;第二次从 B2:B8 中掏出 B3(即“粉红长袖衬衫”)和从 A2:A8 中掏出 A3(即“WS-560”),构成数组第二个元素 "粉红长袖衬衫","WS-560";;最初前往数组 {"红色纯棉T恤","WS-580";"粉红长袖衬衫","WS-560";"绿色T恤","WS-585";"粉红衬衫","WS-581";"粉红短袖衬衫","WS-561";"玄色T恤","NS-286";"红色长袖衬衫","NS-832"},如许就把“产物称号”放到了“编号”的后面。

B、则公式变为  =VLOOKUP(A11,{"红色纯棉T恤","WS-580";"粉红长袖衬衫","WS-560";"绿色T恤","WS-585";"粉红衬衫","WS-581";"粉红短袖衬衫","WS-561";玄色T恤","NS-286";"红色长袖衬衫","NS-832"},2),找到 A11(玄色T恤)后,前往第 2 列,刚好是 NS-286。

 

(三)一对多查找

1、假设要查找指定“部分”(发卖部)的一切员工姓名。双击 F2 单位格,输出部分“发卖部”,双击 A2,把公式 =(C2=$F$2)+A1 复制到 A2,按回车,前往 1,选中 A2,单击 A2 右下角的单位格添补柄,则 A3 到 A8 全用响应数字添补;双击 G2,把公式 =IFERROR(VLOOKUP(ROW(A1),A1:C$8,2,0),"") 复制到 G2,按回车,前往查找成果“林语彤”,选中 G2,把鼠标移到 G2 右下角的单位格添补柄上,按住左键,往下拖,一向拖到最初一行,则前往“发卖部”的一切员工;再双击 F2,把“发卖”改成“财政”,按回车,前往一切“财政部”的员工姓名;操纵进程步骤,如图10所示:

Excel VLookUp函数一对多查找实例

图10

2、公式申明:

(1)=(C2=$F$2)+A1

A、公式 =(C2=$F$2)+A1 用于把属于统一部分的员工停止递增编号,比方属于“发卖部”的三个员工被编号为 1、2、3,属于“财政部”的两个员工被编号为 2、3。

B、C2 表现对列和行都是相对援用,往下拖时,会变为 C3、C4、……;往右拖时,会变为 D2、D3、……。$F$2 表现对列和行都是相对援用,往下或往右拖时,F2 不会变。

C、C2=$F$2 是一个前提,即 C2 若是即是 F2,则前往 True,不然前往 False,C2 和 F2 都为“发卖部”,以是前往 True。则公式 =(C2=$F$2)+A1 变 =TRUE+A1,计较时 TRUE 被转为 1,A1 为空,默许前往 0,以是 =TRUE+A1 前往 1。

D、往下拖到 A3,C2 变为 C3,A1 变为 A2,以是 A3 的公式为 =(C3=$F$2)+A2;C3 的值为“行政部”,不即是 F2,以是前往 False,A2 的值为 1,是以公式变为 =False+1,计较时因为 False 被转为 0,以是 =False+1 前往 1。

(2)=IFERROR(VLOOKUP(ROW(A1),A1:C$8,2,0),"")

A、ROW(A1) 前往 A1 的行号 1;A1:C$8 表现 A1 到 C8 这片单位格地区,往下拖时,A1 会变为 A2、A3、……,C$8 则不会变;公式在 G2 时,A1:C$8 为 A1:C8;公式在 G3 时,A1:C$8 为 A2:C8,别的的以此类推。

B、公式在 G2 时,VLOOKUP(ROW(A1),A1:C$8,2,0) 变为 VLOOKUP(1,A1:C8,2,0),即在 A1:C8 中查找 1,找到的是 A2 中的 1,而后前往第 2 列对应的“林语彤”;第 4 个参数 0 表现类似婚配。

C、公式在 G3 时,VLOOKUP(ROW(A1),A1:C$8,2,0) 变为 VLOOKUP(ROW(A2),A2:C$8,2,0),即 VLOOKUP(2,A2:C8,2,0),查找值变为 2 且 A1 被解除查找规模,在 A4 中找到 2,前往第 2 列对应的“黄子辛”;公式在 G4 时,查找值变为 3,A2 被解除查找规模,别的的以此类推。

D、IFERROR 用于前往毛病值时的处置,若是 VLOOKUP(ROW(A1),A1:C$8,2,0) 前往毛病值,则前往空,不然前往 VLOOKUP(ROW(A1),A1:C$8,2,0) 的前往值。

除以上VLookUp函数的利用方式外,VLookUp函数还能够用If函数组合多前提查找、查找反复项、与Match函数组合查找等,这些内容将在后续文章中先容。