admin 管理员组

文章数量: 887032


2023年12月24日发(作者:压力容器种类)

Excel查找匹配函数xmatch用法介绍

===概念===

XMATCH函数是Excel 2021版本之后新增的一个查找函数,用于在一个数组或者一个数据范围内按照一定条件进行查找匹配。该函数返回的是匹配值在数组或数据范围内的相对位置。

===功能===

XMATCH函数主要有两大功能:

查找匹配:按照给定的查找值,在数组或数据范围内进行查找匹配,返回匹配值的位置。

排序:可以按照一定的条件对数据进行排序,返回排序后的位置。

===语法===

XMATCH函数的语法如下:

XMATCH(lookup_value, lookup_array, [match_mode], [search_mode])

参数说明:

lookup_value:要查找的值。

lookup_array:要查找的数据范围或数组。

match_mode(可选):指定匹配类型,可以是0、-1、1。0表示精确匹配;-1表示按照从大到小的顺序匹配;1表示按照从小到大的顺序匹配。默认为0。

search_mode(可选):指定查找方式,可以是1、-1。1表示从前往后查找;-1表示从后往前查找。默认为1。

===案例===

下面我们通过几个案例来演示XMATCH函数的使用:

案例一:基本查找匹配

假设我们有一个数据范围A1:A5,分别是1、3、5、7、9,要在该范围内查找值3的位置,可以使用以下公式:

=XMATCH(3, A1:A5, 0)

该公式返回的结果是2,表示值3在数据范围A1:A5中的位置是第二个。

案例二:按照条件查找匹配

假设我们有一个数据范围A1:B5,A列是姓名,B列是年龄,要按照年龄从大到小的顺序查找年龄最大的姓名,可以使用以下公式:

=INDEX(A1:A5, XMATCH(MAX(B1:B5), B1:B5, -1))

该公式先使用MAX函数找到年龄最大值,然后使用XMATCH函数按照从大到小的顺序在B列中查找该年龄的位置,最后使用INDEX函数返回对应位置的姓名。

案例三:排序

假设我们有一个数据范围A1:A5,分别是3、1、4、1、5,要按照从小到大的顺序对该数据进行排序,可以使用以下公式:

=XMATCH(A1:A5, A1:A5, 1)

该公式返回的是一个数组{2;4;1;5;3},表示按照从小到大的顺序排序后,每个元素在原始数组中的位置。

===注意事项===

使用XMATCH函数时需要注意以下几点:

1. lookup_array参数必须是一个列向量或者是一个数组,而不能是一个单元格引用。

2. 如果match_mode参数为0(精确匹配),则lookup_value参数必须与lookup_array参数中的数据完全一致才能匹配成功,否则会返回#N/A错误。

3. 如果match_mode参数为-1或1(近似匹配),则lookup_value参数不必与lookup_array参数中的数据完全一致,但必须在数据范围内存在才能匹配成功。如果找不到匹配的值,则会返回#N/A错误。

4. 如果search_mode参数为-1(从后往前查找),则必须在lookup_array参数中按照从大到小的顺序进行查找。如果数据没有按照从大到小的顺序排列,则可能返回错误的结果。

5. XMATCH函数返回的是匹配值在数组或数据范围内的相对位置,如果要在实际应用中使用该位置,还需要结合INDEX函数等其他函数来实现。

6. 在使用XMATCH函数时,建议使用绝对引用或者将数据范围定义为名称,以避免在复制公式时出现错误。

7. 如果数据中有重复值,XMATCH函数只会返回第一个匹配的位置。如果需要找到所有匹配的位置,可以使用XMATCH函数的数组公式形式。

8. 如果lookup_array参数是一个数组,则lookup_value参数可以是数组中的任何一个元素,XMATCH函数会返回该元素在数组中的位置。如果lookup_value参数是一个数组,则必须保证该数组与lookup_array参数中的数据完全一致才能匹配成功。如果数据不一致,则返回#N/A错误。

===Xmatch高级用法总结===

1. 多条件查找

有时候我们需要根据多个条件进行查找,这时候就可以使用XMATCH函数结合其他函数来实现。比如,我们要在数据范围A1:B5中查找姓名为“张三”,年龄为18岁的数据,可以使用以下公式:

=INDEX(A1:B5, XMATCH(G4&H4, A1:A5&B1:B5, 0))

该公式中,XMATCH函数将姓名和年龄两个条件合并在一起进行查找,如果找到匹配项,则返回该项在数据范围中的相对位置,然后使用INDEX函数返回该位置对应的姓名和年龄数据。

2. 反向查找

XMATCH函数可以轻松实现反向查找,比如我们要查找数据范围A1:B5中姓名对应的年龄,但是已知的姓名数据是在B列,可以使用以下公式:

=INDEX(B1:B5, XMATCH(A4&"*", A1:A5&"*", 0))

该公式中,XMATCH函数将A4单元格中的姓名和“”(通配符)作为查找值,在A1:A5和“”组成的查找数组中进行查找,如果找到匹配项,则返回该项在数据范围中的相对位置,然后使用INDEX函数返回该位置对应的年龄数据。需要注意的是,这里的通配符“*”表示任意字符,因此可以使用该符号进行模糊匹配。

3. 关键字查询

关键字查询是XMATCH函数非常实用的一个功能,可以结合通配符来进行模糊匹配。比如我们要在数据范围D1:D9中查找包含关键字“数学成绩”的项,可以使用以下公式:

=INDEX(D1:D9, XMATCH(G2, A1:A9, 2))

该公式中,XMATCH函数将G2单元格中的内容作为查找值,在A1:A9中进行查找,这里的匹配模式2表示使用通配符进行匹配。因此,只要查找值中包含关键字“数学成绩”,无论其大小写、顺序如何,都可以匹配成功,并返回该项在数据范围中的相对位置,然后使用INDEX函数返回该位置对应的数据。

4. 查找多列数据

有时候我们需要同时查找多列数据,这时候就可以使用XMATCH函数结合其他函数来实现。比如我们要在数据范围A1:G10中查找姓名和年龄对应的成绩,可以使用以下公式:

=INDEX($A$1:$G$10, XMATCH($I2, $A$1:$A$10, 0), XMATCH(J$1,

$A$1:$G$1, 0))

该公式中,XMATCH函数分别将I2单元格中的姓名和J1单元格中的列标号作为查找值,在对应的查找数组中进行查找,如果找到匹配项,则返回该项在数据范围中的相对位置,然后使用INDEX函数返回该位置对应的数据。需要注意的是,这里的XMATCH函数使用了两个查找数组,分别确定数据的行标号和列标号。

5. 区间查找

XMATCH函数还可以进行区间查找,比如我们要在数据范围A1:B5中查找值在20到30之间的数据,可以使用以下公式:

=INDEX(A1:B5, XMATCH(TRUE, (A1:A5>=20)*(A1:A5<=30), 0))

该公式中,XMATCH函数将TRUE作为查找值,在(A1:A5>=20)(A1:A5<=30)

组成的查找数组中进行查找,如果找到匹配项,则返回该项在数据范围中的相对位置,然后使用INDEX函数返回该位置对应的数据。这里的(A1:A5>=20)(A1:A5<=30)表示一个逻辑数组,只有满足条件的数据才会返回TRUE。

6. 查找最后一个匹配项

默认情况下,XMATCH函数会返回第一个匹配项的位置,如果我们需要查找最后一个匹配项的位置,可以使用search_mode参数为-1来实现。比如我们要在数据范围A1:B5中查找最后一个值为“张三”的数据,可以使用以下公式:

=INDEX(A1:B5, XMATCH("张三", A1:A5, 0, -1))

该公式中,XMATCH函数将“张三”作为查找值,在A1:A5中进行查找,并将search_mode参数设置为-1,表示从后往前查找,如果找到匹配项,则返回该项在数据范围中的相对位置,然后使用INDEX函数返回该位置对应的数据。

7. 查找多个匹配项

默认情况下,XMATCH函数只会返回第一个匹配项的位置,如果我们需要查找所有匹配项的位置,可以使用XMATCH函数的数组公式形式来实现。比如我们要在数据范围A1:B5中查找所有值为“张三”的数据的位置,可以使用以下公式:

=IFERROR(INDEX(A1:B5, SMALL(IF(A1:A5="张三", ROW(A1:A5)-ROW(A1)+1), ROW(A1))), "")

该公式中,IF函数将A1:A5中值为“张三”的数据所在行的相对位置返回一个数组,SMALL函数会从该数组中选择最小的元素返回,ROW函数会返回一个行号。因此,该公式会返回所有值为“张三”的数据所在行的相对位置。如果某行没有匹配项,则IFERROR函数会返回空文本。


本文标签: 查找 函数 数据 匹配 返回