首页
学习
活动
专区
工具
TVP
发布

EXCEL分享·VLOOKUP专题

讲了那么多VLOOKUP的应用

是时候整个专题啦!

欢迎转发收藏

嘻嘻

VLOOKUP 专题

基础篇

这边还是以查找理综成绩为例,如图:

=VLOOKUP(A2,成绩!B2:F5,5,0)

=VLOOKUP(要查找的值, 要在其中查找值的区域, 区域中包含返回值的列号,精确匹配或近似匹配 – 指定为 0/FALSE 或 1/TRUE)

第一个参数:确定我们要通过什么已知的参数进行查找;

第二个参数:告诉EXCEL在哪里可以找到并返回我要的值;

第三个参数:我要返回的值在第二个参数规定的区域中左起第几列;

第四个参数:0或FALSE表示精确查找,1或TRUE表示近似查找。

第三个参数多说几句:

在选择区域的时候,EXCEL会提示已经选择了多少行,多少列表示为“4R×5C”,因此可以很清楚的知道,理综成绩在第5列。如果区域中间有隐藏的列,要判断第几列就有点麻烦,但是用上述这个方法判断就很简单!

上面这种方法是手动输入公式的,简单一点的也可以调出参数设置的弹窗,如下图:

我的EXCEL因为经常用,第一个提示的函数就是VLOOKUP,所以可以直接点击使用,如果是第一次用,可以点击函数直接调出弹窗,如图:

在同一张表格里进行查找也是类似的操作,如图:

使用通配符

有时候也会遇到已知信息不完整等情况,比如我们只知道姓氏,那我们就可以先用通配符,找出这个姓氏对应的名字,如图:

=VLOOKUP(I3&"*",B2:G6,1,0)

=VLOOKUP(J3,B2:G6,6,0)

嵌套一起就是

=VLOOKUP(VLOOKUP(I3&"*",B2:G6,1,0),B2:G6,6,0)

"*" 表示任意多个字符,I3&"*"则表示以I3开头的内容;

然后再给定的B2:G6区域内,精确查找出第一列的值,也就是找到以I3单元格开头的姓名;

然后再通过查找到的姓名去查找对应的总分。

近似匹配

接下来说下近似匹配,也就是第四个参数等于1或TRUE时,如图:

=VLOOKUP(B2,F:G,2,1)

按顺序给出各个分数对应的评价,如对照表;

然后填入VLOOKUP公式,最后一个参数填1,EXCEL会自动匹配出近似的值;

第四个参数也可以省略不填,表示返回精确值,如果没有精确值则返回近似值;

Tips:要注意的是,给出的对照表,必须按照升序排序,否则会出现错误,如下图

=VLOOKUP(B2,I:J,2,1)

输入类似的公式;

但是对照表非升序的情况下,得到的结果就不对了。

看下图,给对照表加个筛选,就能解决问题

给对照表加筛选按钮;

调整为降序,可以看到,对应的评价还是显示错误;

调整为升序,返回的评价就对了。

逆向查询

VLOOKUP要求查询的已知信息,必须是位于查询区域的第一列。那如果已知信息不在第一列的时候,我们就要用一个内存数组来帮忙了。具体操作如下:

=VLOOKUP(I3,CHOOSE(,B:B,A:A),2,0)

VLOOKUP参数如下:

第一个参数:I3

第二个参数:CHOOSE(,B:B,A:A)

第三个参数:2

第四个参数:0

在讲VLOOKUP基础的时候,就应该知道,现在这个公式,表示的是,根据I3的值,在“CHOOSE(,B:B,A:A)”这个区域内查找,精确匹配出第二列的值。

在区域的选择上,我们用了CHOOSE函数,规定了一个两列多行的内存数组,第一列是 [姓名] B:B,第二列是 [学号] A:A。

这样转换一下,逆向查询就完成了~

CHOOSE函数

这边补充说明下CHOOSE函数,其实很简单,看下图:

返回A3的值:CHOOSE(2,A2,A3,A4)

返回B5的值:CHOOSE(3,B2,B3,B5,B4)

返回G2:G4的总和:SUM(G2:CHOOSE(2,G3,G4))

统一格式是这样的:CHOOSE(index_num, value1, [value2], ...)

CHOOSE函数可以根据索引号从最多 254 个数值中选择一个

index_num:是要返回参数的索引

value1, [value2], ...:即一系列的参数值

这样再回过去看逆向查询,就很好理解了,CHOOSE函数就是把AB列的数值对调了下位置,形成一个内存数值,以供VLOOKUP查询!

多条件查询

,B2:B6&C2:C6,A2:A6),2,0)}

VLOOKUP参数如下:

第一个参数:J3&K3,是用&把两个条件串联起来,得到“Ankh四班”

第二个参数:IF(,B2:B6&C2:C6,A2:A6)

第三个参数:2

第四个参数:0

其他的不多说,我们来看下第二个参数,把函数复制进去可以看到,我们会得到一个两列多行的数组:

第一列:姓名班级串连的值

第二列:对应的学号

这样VLOOKUP的多条件查找就基本完成了。

大家有没有注意到函数外边多了一个中括号 {}

这是因为函数涉及到数组运算,直接按ENTER键会提示错误,必须按CTRL+SHIFT+ENTER才可以,记得按CTRL+SHIFT+ENTER,按CTRL+SHIFT+ENTER之后,函数外边会自动加上中括号哒。(我已经说三遍了,别说我没提醒你~)

数组运算&连接符

这边简单的介绍下,数组运算和&连接符

是一个水平数组,它有两个值分别为1和0;{"乐竟","音十","Ankh"} 也是一个水平数组,有三个值分别为"乐竟","音十","Ankh"。

&连接符可以把两个字符串连到一起,如:

"乐竟"&"音十"&"Ankh" = "乐竟音十Ankh"

字符串可以连接,数组也可以连接,如:

{{"欢迎","乐竟"} & {"关注","音十"}} = {"欢迎关注","乐竟音十"}

假设A1="欢迎",A2="乐竟",B1="关注",B2="音十",那么:

= {"欢迎关注";"乐竟音十"}

哈哈~

数组运算&IF函数

=IF(内容为 True,则执行某些操作,否则就执行其他操作)

=IF(条件,返回值1,返回值2)

IF(1,"乐竟","音十") = "乐竟"

IF(0,"乐竟","音十") = "音十"

1表示TRUE,返回值1

0表示FALSE,返回值2

对于数组而言:

{=IF(,,) }

返回的结果如图:

{=IF(,,) }

返回的结果如图:

VLOOKUP&MATCH

=VLOOKUP(J3,B:H,MATCH(K2,B1:H1,0),0)

MATCH函数是用在VLOOKUP函数的第三个参数里,也就是要返回,需要查找值为选定区域的第几列。

MATCH函数

我们来看下MATCH函数:可以在 范围 单元格中搜索特定的项,然后返回该项在此区域中的相对位置,在这里就是我们需要的第几列。

MATCH(lookup_value, lookup_array, [match_type])

第一个参数:lookup_value,要查找的已知值;

第二个参数:lookup_array,可以为值(数字、文本或逻辑值)或对数字、文本或逻辑值的单元格引用;

第三个参数:[match_type]

若为1或省略,表示查找小于或等于 lookup_value 的最大值

若为0,查找完全等于 lookup_value 的第一个值

若为-1,查找大于或等于 lookup_value 的最小值。

=MATCH(K2,B1:H1,0)

表示K2的值在表头B1:H1中的第几个,刚好是LOOKUP第三个参数所需要的值。

两者搭配起来使用就可以随意变换查找了~

下拉列表

可以看到,J3,K2两个单元格都做了下拉列表,这样变换查询的时候会更方便。 具体操作见上图,路径如下:

【数据】—>【数据验证】—>【设置】—>【允许:序列】—>【来源:数据区域】—>【勾选忽略空值和下拉箭头】—>【确定】

VLOOKUP专题~晚安~

往期分享

请给二狗子多一点关爱

  • 发表于:
  • 原文链接http://kuaibao.qq.com/s/20180209G16UM100?refer=cp_1026
  • 腾讯「腾讯云开发者社区」是腾讯内容开放平台帐号(企鹅号)传播渠道之一,根据《腾讯内容开放平台服务协议》转载发布内容。
  • 如有侵权,请联系 cloudcommunity@tencent.com 删除。

扫码

添加站长 进交流群

领取专属 10元无门槛券

私享最新 技术干货

扫码加入开发者社群
领券