Excel教程:一起认识MATCH函数

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单元格使用以下数组公式,记得要按组合键:

=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单元格使用以下数组公式,按组合键:

=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!}

3、MATCH函数以1作为查找值,在这个数组中查找小于或等于1的最大值。

在开始部分的例2中咱们说过,MATCH函数第三参数使用1或是省略时,要求第2个参数的数组按升序排列。但在这个数组中,实际是由特别多个0和错误值#DIV/0!组成的,并不是升序排列。MATCH函数在处理时,只要将第三参数设置为1或是省略,就会默认第二参数是已经按升序排列过的数据,所以会返回最后一个小于或等于1的最大值(也就是0)的位置。

4、最后使用INDEX函数,根据MATCH函数指定的行号返回A列中对应的月份。

与VLOOKUP

函数配合实现动态查询

以下图为例,是某单位职工工资表的部分内容。咱们要做的,是要根据姓名和项目,来实现一个动态的查询效果。

步骤1    单击A9单元格,依次点击【数据】【数据验证】(07 10版本中叫做数据有效性),设置序列来源为A2:A6。

步骤2    单击B8单元格,以同样的方法设置数据验证,序列来源选择项目所在单元格:

=$B$1:$H$1

这时候,只要单击A9或是B8单元格,就可以在下拉列表中选择不同的姓名或是项目了:

步骤3    B9单元格输入以下公式:

=VLOOKUP(A9,A:H,MATCH(B8,A1:H1,),)

简单说说公式的含义:

MATCH(B8,A1:H1,)部分,在B8单元格选择不同的项目,MATCH函数即计算出该项目在A1:H1单元格中的位置,计算结果用作vlookup函数的第三参数。

vlookup函数使用A9作为查询值,查询的区域为A:H列,由MACHT函数计算出要返回查询区域的第几列。

只要在A9单元格的下拉列表中选择不同的姓名,或是在B8单元格的下拉列表中选择不同的项目,公式就会动态返回不同姓名、不同项目的查询结果。

与OFFSET

函数配合实现动态汇总

在实际工作中,特别多时候需要汇总某个时间段的数据,比如说一至三季度的销售额,4-6月份的利润等等。以下图为例,需要根据A9单元格的业务员姓名和D8单元格指定的截止月份,汇总指定业务员从一月份至该月份的销售业绩完成情况。

C7单元格使用以下公式:

=SUM(OFFSET(B1,MATCH(A9,A2:A6,),,,MATCH(D8,B1:J1,)))

1、MATCH(A9,A2:A6,)部分,精确查找A9单元格姓名在A2:A6单元格区域中的位置。

2、MATCH(D8,B1:J1,) 部分,精确查找D8单元格月份在B1:J1单元格区域中的位置。

3、OFFSET函数以B1单元格为基点,向下偏移的行数为MATCH(A9,A2:A6,)的计算结果。向右偏移的列数为0列,新引用的列数为MATCH(D8,B1:J1,) 的计算结果。实际引用的范围即B5:F5单元格区域。

OFFSET函数的引用过程如下图所示:

4、最后使用SUM函数计算该区域的和,完成销售业绩汇总。

课后练习

今天的内容是入门篇,列举的例子是比较简单的,实际工作中,往往会有特别多奇葩的数据源表,看看在下面这个图中,如何根据E2单元格的姓名查询A列对应的部门呢?

AI助手