登录
首页精彩阅读VLOOKUP很难理解?或许你就差这一个神器
VLOOKUP很难理解?或许你就差这一个神器
2021-07-13
收藏

来源:数据STUDIO

作者:云朵君

一说到Excel查找函数,你一定会想到VLOOKUP函数,虽然它是最基础实用的函数,但每次一看就会,一用就忘。接下来给大家分享一个VLOOKUP函数动态图解 ,记得收藏它哦,在每次使用VLOOKUP函数时,把它拿出来一看就会用,不用再去花精力搜其它资料了。

VLOOKUP很难理解?或许你就差这一个神器

看完这篇VLOOKUP函数动态图解制作步骤,不仅能够轻松掌握VLOOKUP函数,还会掌握一些附加高阶技能。

VLOOKUP查找函数

INDEX索引查找函数

开发工具-数值控制钮应用

Excel自动填充颜色

数据验证-下拉选项框应用


制图准备

为方便演示,先将制图所需的文字准备好,并勾选网格线,让背景更加清晰。按个人习惯,也可以在做完图后再取消勾选。

VLOOKUP很难理解?或许你就差这一个神器

调整格式

根据自己的需求,调整好版面格式,并设置动态变化的公式解释语句。

="公式解释:在C14:I19范围内查找首列等于 "&D8&" 对应第 "&F7&" 列的值。结果为:"&I8

'&' 是本文字符链接符,将几个文本字段连接成一句话。

VLOOKUP很难理解?或许你就差这一个神器

VLOOKUP查找函数

接下来是我们主要功能,运用VLOOKUP查找函数查找出对应匹配的内容。

VLOOKUP (lookup_valuetable_arraycol_index_num[range_lookup])

=VLOOKUP (要查找的项、要查找位置、包含要返回的值的单元格区域中的列号、返回近似或精确匹配 - 指示为 1/TRUE 或 0/FALSE) 。

参数名称说明lookup_value(必需)要查找的值。要查找的值必须列于在 table_array 参数中指定的单元格区域的第一列中。例如,如果 表数组 跨越单元格 B2:D7,则lookup_value必须列 B。Lookup_value 可以是值,也可以是单元格引用。table_array(必需)VLOOKUP 在其中搜索lookup_value 和返回值的单元格区域。可以使用命名区域或表,并且可以使用参数中的名称而不是单元格引用。单元格区域的第一列必须包含lookup_value。单元格区域还需要包含要查找的返回值。col_index_num(必需)对于包含 (的列,列 table_array) 从 1 开始。range_lookup(可选)一个逻辑值,该值指定希望 VLOOKUP查找近似匹配还是精确匹配:近似匹配 - 1/TRUE假定表中的第一列按数字或字母顺序排序,然后搜索最接近的值。这是未指定值时的默认方法。例如,=VLOOKUP (90,A1:B100,2,TRUE)。完全匹配 - 0/FALSE 搜索第一列中的确切值。例如,=VLOOKUP ("Smith",A1:B100,2,FALSE)。

看到上表中的参数说明,似乎有点不太明白,接下来通过一个具体的案例来直观感受VLOOKUP查找函数如何工作的。

本例中需要在部门表中找出 玉玉所在的部门。需要对应填写函数的四个参数:

  • 要查找的项:即找啥?找E5单元格的内容玉玉 。
  • 要查找位置:即在哪块儿找?在部门表所在的区域B4:C9 中查找。
  • 包含要返回的值的单元格区域中的列号:即找到后,要它身上哪个地方的东西?需要部门表 第二列的部门。
  • 返回近似或精确匹配 - 指示为 1/TRUE 或 0/FALSE:即是准确找,还是近似找?0或者FALSE需要精确匹配。
VLOOKUP很难理解?或许你就差这一个神器

查找结果是的玉玉所在的部门法务部

查找浙江省的景点是什么

首先以静态查找值为例,编写VLOOKUP查找函数:从C14:I19 表区域中查找D8单元格中浙江省的景点所在的列值4,并且是精确查找。

= VLOOKUP(D8,C14:I19,F7,0) # =VLOOKUP(查找的内容, 查找区域, 返回查找区域内第几列的数据, 匹配(精确或模糊)) 
VLOOKUP很难理解?或许你就差这一个神器

利用数值控制钮实现可控选项

第一步 开启开发工具(已经开启的不需要重复操作)。在【开始】--【选项】--【自定义功能区】--【开发工具】勾选并确定。

第二步 插入数值控制钮,并调整大小及合适的位置。

VLOOKUP很难理解?或许你就差这一个神器

第三步 设置控制参数:选中,在编辑状态下右击 -- 【设置控件格式】,设置最小值、最大值、步长以及单元格链接。其中单元格链接是将所要控制的数值放置在目标单元格内,以方便显示或运用其数值以作他用。

这里有两个数值控制钮,一个是窗体控件,一个是Active X控件,后者需要在【设计模式】下调整【属性】,以设置最小值、最大值、步长以及单元格链接。

VLOOKUP很难理解?或许你就差这一个神器

INDEX索引函数动态显示查找目标

运用数值控制按钮控制输出行号和列号,接下来是需要通过行号和列号查找出对应的单元格内容,以实现动态显示查找目标值。

首先看下INDEX索引查找函数说明。

数组形式

INDEX(array, row_num, [column_num])

返回由行号和列号索引选中的表或数组中元素的值。

当函数 INDEX 的第一个参数为数组常量时,使用数组形式。

参数说明

array 必需。单元格区域或数组常量。

如果数组仅包含一行或一列,则相应的row_num 或column_num 参数是可选的。

如果数组具有多行和多列,并且row_num 或 column_num ,INDEX 返回数组中整个行或列的数组。

row_num 必需,除非column_num 存在。选择数组中的某行,函数从该行返回数值。如果row_num ,column_num 参数。

column_num 可选。选择数组中的某列,函数从该列返回数值。如果column_num ,row_num 参数。

如果同时使用 row_num 和 column_num 参数,INDEX 将返回单元格中两row_num column_num

引用形式

INDEX(referencerow_num[column_num][area_num])

返回指定的行与列交叉处的单元格引用。如果引用由非相邻选定区域所决定,您可以选择要查找的选定区域。

参数说明

reference 必需。对一个或多个单元格区域的引用。

如果要为引用输入非相邻区域,请用括号括住引用。

如果引用的每个区域仅包含一行或一列,则row_num或column_num参数是可选的。例如,对于单行的引用,可以使用函数 INDEX(reference, column_num)。

row_num 必需。引用中某行的行号,函数从该行返回一个引用。

column_num 可选。引用中某列的列标,函数从该列返回一个引用。

area_num 可选。在引用中选择一个区域,从该范围返回row_num column_num。选定或输入的第一个区域编号为 1,第二个为 2,以此类比。

引用area_num选择特定区域后,row_num 和 column_num 选择特定单元格:row_num=1 是区域的第一行,column_num=1 是第一列,以此类比。INDEX 返回的引用是索引row_num column_num

如果将row_numcolumn_num设置为 0 ,INDEX 将分别返回整个列或行的引用。

row_numcolumn_numarea_num必须指向引用中的单元格;否则,INDEX 返回#REF!错误。如果row_numcolumn_num,INDEX 将返回引用中 area_num

函数 INDEX 的结果为一个引用,且在其他公式中也被解释为引用。根据公式的需要,函数 INDEX 的返回值可以作为引用或是数值。

例如,公式 CELL("width",INDEX(A1:B2,1,2)) 等价于公式 CELL("width",B1)。CELL 函数将函数 INDEX 的返回值作为单元格引用。而在另一方面,公式 2*INDEX(A1:B2,1,2) 将函数 INDEX 的返回值解释为 B1 单元格中的数字。

下面根据由数值控制钮输出的数值查找对应内容:

C14:C19区域所在的数组--省份,按照C8的数值,查找出目标省份得到查找值

=INDEX(C14:C19,7-C8) 

C13:I13区域中的数组--名称,按照F7的数值,查找出目标名称得到需要的列数据

=INDEX(C13:I13,F7) 
VLOOKUP很难理解?或许你就差这一个神器

这样就可以通过数值控制按钮动态演示VLOOKUP查找函数查找原理了。

Excel自动填充颜色

以上已经完成了本次动态图解的主体内容了,最后再加上颜色的突出演示,那就是锦上添花,一目了然了。

【开始】--【条件格式】--【新建规则】--选择【使用公式确定要使用格式的单元格】,并在【为符合此公式的值设置格式】中填写公式。

下面演示突出显示D13:I13区域内的格式为例。输入公式=D13=$F$8,并应用于=$D$13:$I$13区域内。

这里输入公式中的D13是相对引用,而$F$8是绝对引用,意思是在应用区域内任意值等于绝对地址$F$8内的内容,就是符合条件,并使用此格式。

具体操作如下动画演示。其余格式设置也是按照此原理逐一设置。

VLOOKUP很难理解?或许你就差这一个神器

利用数据验证设置下拉选项

除了使用数值控制钮选择目标查找值,还可以通过设置下拉选框选择目标查找值。

以区号为例,在【数据】--【数据验证】下【数据验证】--【设置】中【允许值】为序列,来源是区号所在区域$I$14:$I$19,确定即可。

在运用VLOOKUP函数,查找区号所对应的省份。函数如下:

=VLOOKUP(M1912,IF({1,0},I14:I19,C14:C19),2,FALSE) 
VLOOKUP很难理解?或许你就差这一个神器

其中使用IF({1,0},I14:I19,C14:C19)可以实现反向查找。

VLOOKUP进行数据查找,查找值必须在查找区域的第一列,如果查找值不在查找区域的第一列,遇到这种问题时,但靠VLOOKUP函数并不能查找出所需要的数据。此时可以通过 INDEX+MATCH函数。

另外还有一种方法,配合使用IF函数。即VLOOKUP的反向查找。它的大致思路是,将查找值使用if函数加上{0,1}数组,构建一个二维的表格,来进行查找,下面就让我们来具体分析下

第二个参数使用IF({1,0},I14:I19,C14:C19)构建二维列表。

在Excel中0=FALSE,1=TRUE,我们把{1,0}放在IF函数的第一参数中,它实际上代表对和错的条件结果,又因为,{1,0}在大括号中,所以它是一个数组,它会跟每一个元素都发生运算,比如在IF的第二参数中它的单元格个数是6个,所以,当IF的条件为1时候,他就会得到6个结果,第三个参数也是这个道理以此类推,它的运算结果可以显示为下图。

VLOOKUP很难理解?或许你就差这一个神器

这样就将原来两列数据前后颠倒过来,这样就符合了VLOOKUP函数查找方向的需求了。

VLOOKUP很难理解?或许你就差这一个神器

数据分析咨询请扫描二维码

客服在线
立即咨询