Excel常用函数之MATCH函数及一些常用场景

VLOOKUP函数是Excel中最常用的函数之一,用来查找满足条件的数据。Match是另一个查找函数,它的工作方式与VLOOKUP在很大程度上是类似的,但是又有自己的特色。在很多场景中,MATCH函数提供了更多的灵活性。

01

MATCH的语法和基本例子

大部分同学都熟悉VLOOKUP的语法,我们就将MATCH函数的语法与VLOOKUP函数做个对比:

通过对比,可以看出以下几点:

  1. MATCH函数比VLOOKUP少一个参数。
    VLOOKUP的第三个参数是规定了返回查找区域的第几列。而MATCH函数没有。这是因为MATCH函数的作用是返回在查找区域中的行序号。

  2. 参数名称不同,像第二个参数,VLOOKUP的是table_array,而MATCH的则是lookup_array。

  3. 第二个参数除了名称不同外,在使用上也不同。VLOOKUP中的table_array经常是一个多行多列的矩形区域,比如:B3:F9。这是因为除了B3:B9用来匹配外,需要返回的值也在后面其他列中。但是MATCH的第二个参数一般都是单列或者单行,比如:B3:B9。这是因为MATCH返回的是行号,所以用不到其他列。

如果仔细分析会发现尽管MATCH和VLOOKUP函数的最后一个参数都是查找方式,它们也有不小的区别。

VLOOKUP函数的最后一个参数是这样的:

这里最后一个参数只能取值TRUE(或1)和FALSE(或0),并且第二个参数table-array首列必须升序排序。

而MATCH的最后一个参数是这样的:

最后一个参数可以去1,0,-1,其中0跟VLOOKUP中的0或FALSE是一样的,代表精确匹配。而1是跟VLOOKUP中的1或TRUE一样的,是近似匹配,此时,第二个参数lookup_array必须以降序排序。

如果最后一个参数取值-1,仍然是近似匹配,但是第二个参数lookup_array必须以升序排序。

下面是一些使用MATCH函数的常规例子。

假设数据如下:

公式:

=MATCH("b-2",B3:B7,0)

返回4,这是名称“b-2”在B3:B7中的行序号(第4行)

而公式:

=MATCH(13,C3:C7,1)

返回值是2。这是因为在升序的近似匹配中,需要找到所有比13小的数字中最大的那个数字,就是12,它在第2行。

而公式:

=MATCH(13,C3:C7,-1)

返回错误值#N/A。这是因为这个公式要求C列降序排序,但是当前数字是升序排序。

02

MATCH函数的几个固定用法

下面介绍MATCH函数的几个固定用法:

01

查找第一个非空单元格

使用公式:

=MATCH("*",B:B,0)

02

查找最后一个非空单元格

使用公式:

=MATCH("*",B:B,-1)

01

升序排序中得到最后一次出现的行号

使用公式:

=MATCH("b",B2:B18,1)

04

定位某一个条件所在的连续区域

如果我们希望得到某一个数值出现的连续区域:

比如,在上图中,我们希望得到"b"所在的单元格区域。可以利用前面的两个用法。(数据必须排序,这样所有的“b”才能连续排在一起。)

首先,得到首次出现“b”的行号:

=MATCH("b",B2:B18,0)

然后用下面的公式可以得到首次出现“b”的单元格:

=INDEX(B2:B18,MATCH("b",B2:B18,0))

然后得到最后一次出现“b”的行号:

=MATCH("b",B2:B18,1)

使用下面的公式得到最后一次出现“b”的单元格:

=INDEX(B2:B18,MATCH("b",B2:B18,1))

于是,下面的公式就可以返回“b”所在的连续区域:

=INDEX(B2:B18,MATCH("b",B2:B18,0)):INDEX(B2:B18,MATCH("b",B2:B18,1))

可以在公式中使用这个公式,比如:

=COUNTA(INDEX(B2:B18,MATCH("b",B2:B18,0)):INDEX(B2:B18,MATCH("b",B2:B18,1)))

返回值是6

好了,今天就分享到这里了!
(0)

相关推荐