Excel-查找和引用函数

查找与引用函数

• vlookup(lookup_value,table_array,col_index_num,range_lookup) - 搜索表区域首例满足条件的元素,确定待检索单元格在区域中的行序号,再进一步返回选定单元格的值。默认情况下，表是以升序排序的 <–> 垂直方向查找

• offset(reference，rows，columns,height，width) - 以指定的引用为参照系，通过给定偏移量返回新的引用

步骤:

1. 先选中 与要查询的单元格/单元格区域 具有相同区域大小的 空单元格区域

   ​ 在此例子中,因为要查询的区域 C5~E9 有5行3列 , 所以我们需要先在空白单元格里也选择 5行3列 的单元格(H5~J9)

2. 在 公式 栏中 输入 =OFFSET(A5,2,2,5,3)

3. 同时按下 Ctrl+Shift+Enter , 使得公示栏中的公式变为={OFFSET(A5,2,2,5,3)}样式 , 即查询成功

   ​ 注意, 千万不要直接回车 !!!

注意事项:

1. offset(基点 , 行偏移 , 列偏移 , 找多少行数据 , 找多少列数据)

2. reference必须为引用(包括函数产生的三维引用) , 不能为常量或数组

3. rows , columns 为必须项 , 如要省略须使用","逗号进行占位 , 缺省值为0(即不偏移) ;

   heigh , width 为可选项 , 可省略 , 缺省视为与基点相同

4. rows-行偏移

   • 向下偏移 为 正数
   • 向上偏移 为 负数

   columns-列偏移

   • 向右偏移 为 正数
   • 向左偏移 为 负数

5. 公式写完后 , 按下 Ctrl+Shift+Enter

• match(lookup_value,lookup_array,match_type) - 返回符合特定值特定顺序的项在数组中的相对位置

注意事项:

1. lookup_value为必须项 , 该值是需要在 lookup_array 中查找的值

   • lookup_value 可以为值(数字、文本或逻辑值) ，也可以为数字、文本或逻辑值的单元格的引用

2. lookup_array为必须项 , 表示要搜索的单元格区域

3. match_type为可选项 , 指定查找方式 (-1、0、1）, 默认值为1

   • 1 或 省略 :

     • 表示match函数会查找＜或＝lookup_value的最大值

     • lookup_array中的值必须按照升序排列

       ​ 例如： … -2, -1, 0, 1, 2, … , A-Z, FALSE, TRUE。

   • 0

     • 表示match函数会查找＝lookup_value的第一个值
     • lookup_array中的值可以按照任何顺序排列

   • -1

     • 表示match函数会查找＞或＝lookup_value的最小值
     • lookup_array中的值必须按照降序排列

• index(array,row_num,column_num) - 在给定的单元格区域中,返回特定行列交叉处单元格的值或引用

  index(reference,row_num,column_num,area_num)

• indirect(ref_text,[a1]) - 返回文本字符串所指定的引用

其中,

​ ref_text: 为必须项,表示对指定单元格的引用,如果ref_text不是合法的单元格引用,则返回错误值

​ al: 为可选项, 用来指定包含在单元格ref_text中的引用类型

主要应用

1. 生成二级下拉菜单(样图如下:)

方法一

​ 第一步: 选择范围(B2~B6) -> 开始 -> 公式 -> 名称管理器-> 新建 ;

​ 第二步: 在 名称 栏 输入 序列1-> 确定 ,

​ 不要关闭名称管理器的页面,继续点击 新建 -> 在 名称 栏 输入 序列2 -> 引用位置 栏 点击 右边的小标识 -> 选择范围(C2~C6) -> 再次点击 右边的标识 -> 确定

​ 第三步: 选中 E3 单元格 -> 数据 -> 数据验证 -> 数据验证

​ -> 在 验证条件 允许 栏 选择 序列

​ -> 在 来源 栏内 输入 序列1,序列2-> 确定

​ 第四步: 选中 F3 单元格 -> 数据-> 数据验证 -> 数据验证

​ -> 在 验证条件 允许 栏 选择 序列

​ -> 在 来源 栏内 输入 =INDIRECT($E$3) -> 确定

方法二(推荐使用)

​ 第一步: 选中 H5 单元格 -> 数据 -> 数据验证 -> 数据验证

​ -> 在 验证条件 允许 栏 选择 序列

​ -> 在 来源 栏内 点击 右边的小标识 -> 选择 A2~A5 , 再次点击 右边的小标识 -> 确定 , 这样 一级下拉菜单(省) 就创建好了

​ 第二步: 选中 C2~F14 单元格 -> 开始 -> 查找和选择 -> 定位条件 -> 常量 -> 确定

​ 这样做的目的是剔除 C2~F14 中空白单元格

​ 第三步: 公式 -> 根据所选内容创建-> 首行 -> 确定

​ 第四步: 选中 I5 单元格 -> 数据 -> 数据验证 -> 数据验证

​ -> 在 验证条件 允许 栏 选择 序列

​ -> 在 来源 栏内 输入 =INDIRECT($H$5) -> 确定

​ 以上四步完成后,便可实现 二级下拉菜单(市) 与 一级下拉菜单(省) 的关联

2. 多表合并

• row([reference]) - 返回一个引用的行号

• column([reference]) - 返回一个引用的列号

• hlookup(lookup_value,table_array,row_index_num,range_lookup) - 搜索数组区域首行满足条件的元素,确定待检索单元格在区域中的序列号,再进一步返回选定单元格的值 <–> 水平方向查找

• choose(index_num,value1,value2, … ) - 根据给定的索引值,从参数串中选出相应值或操作

• find(find_text,within_text,start_num) - 返回一个字符串在另一个字符串中出现的起始位置(区分大小写)