EXCEL：查找与引用函数

努力学习数据分析的小江

已于 2024-03-19 23:56:18 修改

阅读量1.4k

点赞数 13

分类专栏： EXCEL学习文章标签： excel

于 2024-03-19 23:55:30 首次发布

本文链接：https://blog.csdn.net/2301_76815178/article/details/136424875

版权

EXCEL学习专栏收录该内容

1 篇文章 0 订阅

订阅专栏

一、VLOOKUP

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

根据线索查找目标值

参数：
   lookup_value：线索(值或者单元格引用)
   table_array：目标区域(两列或多列数据)
   col_index_num：目标在目标区域的第几列(数值)
   range_lookup：匹配方式(TRUE/FALSE)

注意事项：
1）线索所在列必须在目标区域的第一列
2）匹配方式：
   0/FALSE: 返回精确匹配值
   1/TURE/省略：返回精确匹配值或近似匹配值,如果找不到精确匹配值，则返回小于lookup_value 的最大数值，目标区域的第一列必须以升序排序
3）  如果 table_array 第一列中有两个或多个值与 lookup_value 匹配，则使用第一个找到的值

1、常规查找

2、将返回的错误值，替换成文字

3、查找一系列的值

（这种也只能按照一定的顺序查找，如果需要查找指定列，详见方法5）

4、逆向查找

推荐使用INDEX+MATCH的方法，但VLOOKUP也是可以进行逆向查找的，适用于线索列不在第一列，示例函数如下：

5、查找指定列

需要结合MATCH进行使用

6、通配符查找

姓黄的人="黄*"

姓黄的人q且姓名为2个字的人="黄?"

7、模糊匹配

（1）首先需要进行排序、升序

（2）在VLOOKUP中，查找方式=1/TURE/省略：返回精确匹配值或近似匹配值,如果找不到精确匹配值，则返回小于 lookup_value 的最大数值，目标区域的第一列必须以升序排序。

二、MATCH

MATCH(lookup_value, lookup_array, [match_type])

返回查找值在查找区域中的相对位置（返回是一个数值)

参数：
   lookup_value:   查找的值
   lookup_array：   查找的区域
   match_type：   查找方式

注意事项：
1）   行号和列号都是针对区域而言
2）   查找文本值时，不区分大小写字母
3）   查找方式：
1或省略   查找小于或等于 lookup_value 的最大值。lookup_array 参数中的值必须按升序排列. 0   查找等于 lookup_value 的第一个值。lookup_array 参数中的值可以按任何顺序排列.               -1   查找大于或等于 lookup_value 的最小值。lookup_array 参数中的值必须按降序排列.

1、MATCH可以进行多条件查找

三、INDEX

INDEX(array,row_num,column_num)

返回行列交叉处的值，一般和MATCH配合使用

参数：
   array：   区域
   row_num：   行号
   column_num：   列号

注意事项：
1）   行号和列号都是针对区域而言
2）   如果将 row_num 或 column_num 设置为 0，函数 INDEX 分别返回对整列或整行的引用，（可以认为返回区域的第几个值）

1、常规查找

2、文本数字查找

Tips：为了不改变原始数据，可以在公式中运用加减乘除运算将文本型数字变成数字

3、查无此人

4、查找一系列值

5、逆向查找

可以直接写，因为数据源的排列顺序并不影响查找，因为INDEX函数所需的参数是数据在所在数据源的行和列的位置信息

6、查找指定列

只要用两个MATCH知道两个条件所在行列即可

7、多条件查找

（1）使用VLOOKUP

①建立辅助列，辅助列一般放在第一列

②根据辅助列进行查找

注意：VLOOKUP的数据源区域不可以进行拼接，但是查找值可以进行拼接

（2）使用INDEX

使用INDEX可以不需要制作辅助列

8、案例：员工信息卡的制作

结果：

数据源：

（1）首先使用数据验证，来进行名字的选择

（2）使用INDEX+MATCH查找信息

主要逻辑是根据姓名和条件对数据源进行行列查找

（3）使用图片超链接和名称进行照片的选择

①如果直接用INDEX查找的话，出来的结果是0

②应该先新建立一个名称，命名为图片，其对象为使用INDEX+MATCH查找得出的图片

③将任意一张图片复制粘贴到员工信息表中，选中图片，并使其=图片

④回车之后，图片会根据姓名的变化而变化

四、OFFSET

OFFSET(reference,rows,cols,height,width)

以指定的引用为参照系，通过给定偏移量返回新的引用

可以返回一个单元格，也可以返回一个区域

参数：
Reference：偏移量参照系的引用区域：单元格或相连单元格区域的引用
rows：相对于偏移量参照系的左上角单元格，上（下）偏移的行数---- 正（下）负（上）
cols：相对于偏移量参照系的左上角单元格，左（右）偏移的列数----正（右）负（左）
height：   高度，即所要返回的引用区域的行数。Height 必须为正数
width：   宽度，即所要返回的引用区域的列数。Width 必须为正数

注意事项：
1）   如果行数和列数偏移量超出工作表边缘，函数 OFFSET 返回错误值 #REF!。
2）   如果省略 height 或 width，则假设其高度或宽度与 reference 相同。
3）   函数 OFFSET 实际上并不移动任何单元格或更改选定区域，它只是返回一个引用。
函数 OFFSET 可用于任何需要将引用作为参数的函数。
例如，公式 SUM(OFFSET(C2,1,2,3,1)) 将计算比单元格 C2 靠下 1 行并靠右 2 列的 3 行 1 列的区域的总值。

1、特定区域总值计算

2、OFFSET+COUNTA实现动态的数据验证

如何让新增的数据自动添加到下拉菜单？（提示：使用到名称的功能）

（1）首先理解如何引用某一区域的非空单元格的所有内容

（2）然后使用名称工具，定义名称，引用位置为OFFSET+COUNTA的组合函数

（3）使用数据验证实现动态下拉列表

五、INDERECT

INDIRECT(ref_text,[a1])

返回由文本字符串指定的引用

参数：
ref_text: 定义为引用的名称或对作为文本字符串的单元格的引用; 如果是对另一个工作簿的引用(外部引用)，则工作簿必须被打开
[a1]: TRUE（1）或省略，第一参数为A1样式的引用
       FALSE（0），第一参数为R1C1样式的引用

一种加引号，一种不加引号。
=INDIRECT("A1")——加引号，文本引用，即引用A1单元格所在的文本，即返回单元格本身
=INDIRECT(A1)——不加引号，地址引用，引用的是A1单元格地址，即引用单元格所在地址的值

1、实现下拉菜单的二级联动

（1）新建名称，可以批量新建名称

注意：名称是不能以数字开头的，所以根据所选内容创建名称的时候，系统会自动在数字前面添加下划线_，因此使用SUM时应该写=SUM(INDIRECT("_"&I6))

（2）数据验证

（3）使用INDERECT配合第一步中创建的名称进行引用

（4）配合SUM求和

六、CHOOSE

CHOOSE(index_num, value1, [value2], ...)

根据给定的索引值，从参数串中选出相应值或操作

参数：
index_num （索引值）：如果 index_num 为小数，则在使用前将被截尾取整
value1、2、3 （参数串）：参数可以为数字、单元格引用、已定义名称、公式、函数或文本
函数 CHOOSE 的数值参数不仅可以为单个数值，也可以为区域引用。
例如，公式：=SUM(CHOOSE(2,A1:A10,B1:B10,C1:C10))