在现代办公环境中，数据无处不在。作为数据处理的强大工具，Microsoft Excel提供了众多函数，用以处理各种复杂的数据任务。今天，我们将深入探讨如何利用Excel中的FILTER、XMATCH和COUNTIF等函数来比较两个列表，并提升数据处理的效率与准确性。本文将通过详细讲解和案例分析，带您走进Excel函数的世界，掌握其强大功能。

 

FILTER函数可以帮助我们在一列或一行中筛选出满足特定条件的值；

XMATCH函数则可以返回一个项在数组中的相对位置；

COUNTIF函数则可以统计符合特定条件的单元格数量。

FILTER函数详解

FILTER是一个非常实用的函数，它可以基于指定的条件筛选出一个范围或数组中的数据。

语法：FILTER(array, include, [if_empty])

• array：需要过滤的数据范围或数组。

• include：一个布尔数组，用于指定哪些行/列满足过滤条件。

• if_empty（可选）：当没有满足条件的条目时返回的值。

XMATCH函数详解

XMATCH函数用于在一个数组中查找一个项的相对位置。如果找到多个匹配项，它还会返回第一个以外的其他匹配项的位置。

语法：XMATCH(lookup_value, lookup_array, [match_mode], [search_mode])

• lookup_value：要查找的值。

• lookup_array：要搜索的数组或范围。

• match_mode（可选）：匹配模式，确定应返回哪个位置。

• search_mode（可选）：搜索模式，确定搜索的方向。

COUNTIF函数详解

COUNTIF是一个统计函数，用来计数符合特定条件的单元格数量。

语法：COUNTIF(range, criteria)

• range：要应用条件的单元格范围。

• criteria：定义哪些单元格将被计数的条件。

 

 

综合案例分析

现有两个列表，包含不同的水果名称。

要求1：用FILTER+XMATCH函数，提取两列共同出现的水果名称

在F5单元格输入公式：

=FILTER(B5:B16,ISNUMBER(XMATCH(B5:B16,D5:D14)))

XMATCH函数标识两个列表中哪些值是公共的。在C5单元格中输入公式：

=XMATCH(B5:B16,D5:D14)

可以看到列表1中的”葡萄柚“在列表2中排第5，”橙子“不包含在列表2中。

FILTER函数筛选XMATCH函数查找的数字，就返回出现在两个列表中的值。这7个常用值分布在F5:F11范围内。ISNUMBER函数判断是否为数字。

要求2：提取两列没有共同出现的水果名称

=FILTER(B5:B16,ISNA(XMATCH(B5:B16,D5:D14)))

ISNA函数判断是否为错误值。

要求3：用FILTER+COUNTIF函数，提取两列共同出现的水果名称

=FILTER(B5:B16,COUNTIF(D5:D14,B5:B16))

在C5单元格输入公式：

=COUNTIF(D5:D14,B5:B16)

获取列表1中各水果在列表2中出现的次数。用FILTER函数将不为0的筛选出来。

要求4：用FILTER+COUNTIF函数，提取两列没有共同出现的水果名称

=FILTER(B5:B16,COUNTIF(D5:D14,B5:B16)=0)

上述是用函数来进行提取比较的，还可以用条件格式显示出来。

将两列中共有的水果标记为红色

结果如下。

通过上述步骤，我们不仅学会了如何使用FILTER、XMATCH和COUNTIF这三个函数，而且还了解了如何将它们结合起来解决实际问题。这些函数的组合使用，可以帮助我们更加高效地处理和分析数据。

在实际工作中，我们还会遇到更多的数据处理问题，但只要我们掌握了Excel函数的基本用法，就能够灵活应对。希望本文能够帮助您提升Excel技能，如果您有任何疑问或需要进一步的帮助，欢迎在评论区留言。