MATCH 函数应用非常广泛,可以在单元格区域中搜索指定项,然后返回该项在单元格区域中的相对位置。今天咱们就一起认识这个函数,领略它的魅力。
MATCH
函数的运算方式
这个函数有三个参数,其中第一个参数是查找对象,第二参数指定查找的范围或是数组,第三参数为查找的匹配方式。
第三参数有三个选项:0、1、-1,分别表示精确匹配、升序查找、降序查找模式。
例1:以下公式返回2。
=MATCH(“A”,{“C”,”A”,”B”,”A”,”D”},0)
第三参数使用0,表示在第2个参数的数组中精确字母”A”第一次出现的位置为2,不考虑第2次出现位置,且第2个参数无需排序。
例2:以下公式返回3。
=MATCH(6,{1,3,5,7},1)
第三参数使用1,(也可省略),其中第2个参数的数组要求按升序排列,并查找出
小于或等于
6
的最大值(即数组中的5)在第3个元素位置。
例3:以下公式返回2。
=MATCH(8,{11,9,6,5,3,1},-1)
其中第2个参数的数组要求按降序排列,并查找出
大于或等于
8
的最小值(即数组中的9)在第2个元素位置。
MATCH
函数与INDEX
函数逆向查询
由于实际应用中,只要求返回位置的问题不多,好像MATCH函数一时派不上用场了。其实这个函数更多的时候,是与其他引用类函数组合应用,最典型的使用是与INDEX函数组合,能够完成类似VLOOKUP函数和HLOOKUP函数的查找功能,并且可以实现逆向查询,即从左向右或是从下向上查询。
如下图所示,需要根据E列的姓名在A列查询对应的部门。
以前咱们说过,对于这种逆向查询的数据可以使用LOOKUP函数,今天再说说用INDEX+MATCH函数实现的方法。
D2单元格输入以下公式:
=INDEX(A:A,MATCH(E2,B:B,))
返回查询结果为采购部。
INDEX函数是常用的引用类函数之一,可以在一个区域引用或数组范围中,根据指定的行号和列号来返回一个值。
MATCH(E2,B:B,)部分,第三参数简写,表示使用0,即精确匹配方式查询E2单元格姓名“小美”在B列的位置,结果为4。计算结果用作INDEX函数的参数,INDEX函数再根据指定的行号返回A列中对应的值。
使用INDEX函数和MATCH函数的组合应用来查询数据,公式看似相对复杂一些,但在实际应用中,更加灵活多变。
查找首次出现的位置
除了使用特定的值作为查询参数,也可以使用逻辑值进行查询。以下图为例,是某公司的销售数据。需要查询首次超过平均销售额的月份。
D2单元格使用以下数组公式,记得要按<Shift+Ctrl+Enter>组合键:
=INDEX(A2:A13,MATCH(TRUE,B2:B13>AVERAGE(B2:B13),))
来看看公式的意思:
1、AVERAGE(B2:B13)部分,用来计算出B2:B13单元格的平均值895.33。
2、B2:B13>AVERAGE(B2:B13)部分,用B2:B13与平均值分别作比较,得到由逻辑值TRUE或是FALSE组成的内存数组:
{FALSE;FALSE;FALSE;TRUE;…;TRUE}
3、MATCH函数第一参数使用逻辑值TRUE,使用精确匹配方式查询TRUE在数组中第一次出现的位置,结果为4。本例中的第一参数也可以写成“1=1”,1=1返回逻辑值TRUE,与直接使用TRUE效果相同。
4、MATCH函数的计算结果用作INDEX函数的参数,INDEX函数再根据指定的行号返回A列中对应的月份。
查找最后一次出现的位置
除了查询首次出现的位置,MATCH函数还可以查询最后一次出现的位置。以下图为例,需要查询最后次超过平均销售额的月份。
D2单元格使用以下数组公式,按<Shift+Ctrl+Enter>组合键:
=INDEX(A2:A13,MATCH(1,0/(B2:B13>AVERAGE(B2:B13))))
来看看公式的意思:
1、先使用AVERAGE函数计算出B2:B13单元格的平均值。
2、再用B2:B13与平均值分别作比较,得到由逻辑值TRUE或是FALSE组成的内存数组。
用0除以这个内存数组,返回以下结果:
{#DIV/0!;#DIV/0!;0;0;0;…;#DIV/0!}
上一篇:excel表格制作:区分大小写的Excel汇总 下一篇: 提取最后一个星号后的数字
郑重声明:本文版权归原作者所有,转载文章仅为传播更多信息之目的,如作者信息标记有误,请第一时间联系我们修改或删除,多谢。