在 Excel 中,有一类函数称为查找函数和引用函数。 今天给大家分享6个查询函数(、、、、Match、Index)的应用技巧,大家可以收藏起来,方便以后使用!
一,。
功能:根据序号从列表中选择对应的内容。
语法结构:=(序列号,值1,[值2]...[值254])。
防范措施:
1. 如果参数“序列号”小于1或大于列表中最后一个值的序列号,函数将返回错误值“#VALUE!”。
2、当参数“序列号”的值为1时,返回“值1”,当“序列号”的值为2时,返回“值2”……以此类推。
3. 如果参数“序列号”为小数,则在使用前将其截断并四舍五入。
4.参数“值X”可以是数字、文本、参考、名称、公式或函数。
目的:评价员工的绩效,≤1000为“一般”,≥3500为“优秀”,中间部分为“良好”。
方法:
在目标单元格中输入公式:=(IF(,3,2))、“一般”、“良好”、“优秀”)。
解释:
公式中,IF函数的作用是根据G3单元格的值返回对应的序号1、2、3,作为函数的“序号”,返回“一般”、“好”根据序列号,“优秀”。
二,。
功能:返回指定行或列中指定位置的值。
有向量和数组两种应用形式。
(1)向量形式。
语法结构:=(查询值,查询值所在区域或数组,[返回值所在区域或数组])。
防范措施:
1、对于参数“查询值”,如果在“查询值所在的区域或数组”中找不到修改,则返回小于等于查询值的最大值对应的结果。
2、为了得到正确的查询结果,在查询开始前必须将“查询值所在的区域或数组”按【升序】排列,规则为:数字<字母<FALSE<TRUE。
3、“查询值”和“返回值”所在的区域和数组必须是同一方向,即如果查询区域是行方向,则返回结果区域不能是列方向。
4、当“查询值所在的区域或数组”与“返回值所在的区域或数组”相同时,“返回值所在的区域或数组”可以省略。
目的:查询“员工”的“销售额”。
方法:
1. 以“员工姓名”为主关键字,对数据源进行升序排序。
2. 在目标单元格中输入公式:=(J3,B3:B12,G3:G12)。
解释:
使用函数查询数据时,前提是将“查询值所在的区域或数组”按【升序】排序,否则无法得到正确的结果。
(2)数组形式。
语法结构:=(查询值,数据区)。
防范措施:
1、参数“数据区”必须包含“查询值”和“返回值”,“查询值”为第一列(行),“返回值”为最后一列(行)。
2、查询前,必须将“数据区”中的值以“查询值”所在列为主键升序排序,规则为:数字<字母<FALSE<TRUE。
目的:查询“员工”的“销售额”。
方法:
1. 以“员工姓名”为主关键字,对数据源进行升序排序。
2. 在目标单元格中输入公式:=(J3,B3:G12)。
(3)经典用法。
目的:查询“员工”的“销售额”。
方法:
在目标单元格中输入公式:=(1,0/(B3:B12=J3),G3:G12)。
解释:
使用这种方法时,不需要对数据源进行升序排序,其本质仍然是向量形式。
三,。
功能:查找范围或数组的第一行中的数据,并返回与指定值同一列的范围或数组中的行的值。
语法结构:=(查询值、数据区域、返回值相对行数、[匹配类型])。
防范措施:
1.“数据区”必须包含列标题。
2、参数“匹配类型”分为两种,TRUE或省略为模糊查询,FALSE为精确查询。
3.对于模糊查询,返回最大值≤“查询值”,且“查询区域”必须按升序排序; 精确查询时,返回“查询区域”中第一个等于“查询值”的值,“查询区域”不需要排序。
目的:查询“员工”的“销售额”。
方法:
在目标单元格中输入公式:=(J3,B2:G12,MATCH(K2,B2:B12,0),0)。
解释:
Match函数的功能和使用方法请参考本文后续内容。
四、.
功能:在范围或数组的第一列中查找指定值,并返回范围或数组中与指定值同一行的其他列的值。
语法结构:=(查询值、数据区域、返回值所在列数、[匹配类型])。
防范措施:
1、参数“匹配类型”分为两种,TRUE或省略为模糊查询,FALSE为精确查询。
2、模糊查询时,返回最大值≤“查询值”,且“查询区域”必须按升序排序; 精确查询时,返回“查询区域”中第一个等于“查询值”的值,“查询区域”不需要排序。
目的:查询“员工”的“销售额”。
方法:
在目标单元格中输入公式:=(J3,B3:G12,6,0)。
解释:
参数“6”表示返回值“Sales”在范围B3:G12 中的相对列号。
五、比赛。
功能:返回指定方法(精确查询或模糊查询)下要查找的值在范围或数组中的位置。
语法结构:=Match(查询值,数据区,[匹配类型])。
防范措施:
1、参数“匹配类型”分为3种,“1或省略”,模糊查询,返回小于等于“查询值”最大值的位置,“数据区”必须排序按[升序]; “0”为精确查询,返回数据区中第一个等于“查询值”的值,“数据区”不需要排序; “-1”为模糊查询,返回大于等于“查询值”最小值的位置,“数据区域”必须按降序排序。
2. 如果参数为文本,Match 函数将不区分大小写字母。 如果要严格匹配“查询值”,则需要使用Exact函数。
用途:返回“业务员”在指定范围内的相对位置。
方法:
在目标单元格中输入公式:=MATCH(J3,B3:B12,0)。
解释:
“数据区域”不同,相对位置也不同。
六、索引。
功能:返回单元格区域或数组中行和列的交集处的值。
语法结构:=Index(数据范围,行数,[列数])。
防范措施:
1、“行数”和“列数”参数只能省略其中之一,且不能同时省略。
2. 参数“行数”和“列数”所指示的引用必须在“数据范围”内,否则 Index 函数将返回错误值“#REF!”。
目的:查询“员工”的“销售额”。
方法:
在目标单元格中输入公式:=INDEX(G3:G12,MATCH(J3,B3:B12,0))。
解释:
使用Match函数定位员工姓名在相应列中的相对位置,并将其作为Index函数的参数,返回该位置的销售额。