EXCEL：查找与引用函数
EXCEL：查找与引用函数
Excel中的查找与引用函数是数据处理中非常重要的工具，它们可以帮助用户快速定位和获取所需的数据。本文将详细介绍VLOOKUP、MATCH、INDEX、OFFSET、INDIRECT和CHOOSE等函数的功能、参数和使用方法，帮助读者掌握这些函数的使用技巧。
一、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: 定义为引用的名称或对作为文本字符串的单元格的引用; 如果是对另一个工作簿的引用(外部引用)，则工作簿必须被打开
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))
1、常规查找
2、 案例：根据员工级别及销量，计算提成
3、案例：配合VLOOKUP进行查找
七、HYPERLINK（超链接）
HYPERLINK(link_location, [friendly_name])
创建快捷方式或跳转，用以打开存储在 Internet 中的文档。
参数：
link_location 要打开的文档的路径和文件名
friendly_name 单元格中显示的跳转文本或数字值.显示为蓝色并带有下划线。如果省略 Friendly_name，单元格会将 link_location 显示为跳转文本。