Excel操作技巧之查找和引用函数.pptVIP

在高级筛选的条件区域中，可以将公式的计算结果作为条件使用。例如，找出成绩表中高于平均分的学生记录，找出工资表中高于或低于平均收入的职工档案，找出人员档案表中超过平均年龄10岁以上的人员的信息等。此时，因为平均成绩、平均收入和平均年龄都不是一个常数条件，它们全部都是需要根据工作表计算的结果。 在Excel中，可以创建计算条件的条件区域，用计算条件进行高级筛选。 当筛选的条件不是一个常数，而是随数据清单中数据变化的计算结果，此时无法直接利用高级筛选进行数据筛选。通过计算条件可以解决此种情况。 计算条件的条件区域设置规则： 字段名行为空 条件行中输入包含有公式或函数的条件 查找3月份出生的人员记录? E1单元格无内容 在E2单元格输入公式：=month(d10)=3 条件区域为E1:E2 查找笔试成绩大于平均笔试成绩的人员信息? E1单元格不输入内容 E2单元格输入公式：=K10average($k$10:$k$39) 条件区域为: E1:E2 认识工作表区域 单元格引用方式 相对引用: A1 绝对引用: $A$1 混合引用: $A1, A$1 单元格引用的分类 一维引用：A1:A10、A2:G2 二维引用：A1:C5 三维引用：Sheet1:sheet3!A1 数组的概念 由文本、数值、日期、逻辑、错误值等元素组成的集合 ，行方向 ；列方向 常量数组和内存数组 数组的分类 一维数组：行数组（垂直数组）和列数组(水平数组） {1，2，3，4，5}或{”A”; ”B”;”C”;”D”;”E”} 二维数组：多行多列数组 {0，”差”；60，“中”；80,”良”；90，”优”} Offset函数 将源引用根据指定的行，列偏移来产生新的单元格引用 Offset函数语法 Offset(基点，行数，列数，[高度],[宽度]) 函数特征 “引用基点”只能是单元格引用，而不能使数组 [高度],[宽度]用于指定目标的单元格区域大小，如果不指定，则目标引用尺寸与源引用相同 [高度],[宽度]允许用户使用负数作为参数 将A5区域进行偏移后产生的新的C7:E11 公式： = Offset(A5,2,2,5,3) = C7:E11 Match函数：返回查找值在范围中的位置序号 Match函数的灵活性比LOOKUP（包括VLOOKUP、HLOOKUP）更强，它可以在工作表的一行（或一列）中进行数据查询，并返回数据在行（或列）中的位置。如果需要找出数据在某行（或某列）的位置而不是数据本身，则应该使用 Match 函数。 Match函数的语法格式如下： Match（lookup_value，lookup_array，match_type） lookup_value 为需要在 Look_array 中查找的数值。 lookup_array 为可能包含所要查找数值的区域 match_type指明了查找方式，其取值可以为数字-1，0或 1 当 match_type 为 1，函数 MATCH 查找小于或等于 lookup_value 的最大数值。Lookup_array 必须按升序排列：...、-2、-1、0、1、2、...、A-Z、FALSE、TRUE。 若 match_type为0，函数MATCH查找等于 lookup_value的第一个数值。Lookup_array 可以按任何顺序排列。 如果 match_type 为 -1，函数 MATCH 查找大于或等于 lookup_value 的最小数值。Lookup_array 必须按降序排列：TRUE、FALSE、Z-A、...、2、1、0、-1、-2、...，等等。 如果省略 match_type，则假设为 1。 查看“BO1”在数据序列中A4:A9的位置 公式为：=Match(F3,A4:A9,0) Index函数 返回指定的行与列交叉处的单元格引用。 语法： Index（reference，row_num，column_num，area_num） reference 是对一个或多个单元格区域的引用。 row_num 为引用中某行的行序号，函数从该行返回一个引用。 column_num 为引用中某列的列序号，函数从该列返回一个引用。 area_num 选择引用中的一个区域，并返回该区域中 row_num 和 column_num 的交叉区域。 注意事项 通常情况下，行号、列号参数不支持使用数组 返回指定的行与列交叉处的单元格引用 公式：=INDEX((A4:C9,E4:F8),2,3,1) 返回值 Choose函数的功能是从值的列表中选择一个值，它有点类似于计算机程序语言中的分情况选择语句。使用该函数可以返回多达 29 个基于给定待选数值中的任一数值。 Choose函数的语法格式如下： Choose