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专题~晚安~

往期分享

请给二狗子多一点关爱