COUNT函数示例：单元格包含特定词语

概括

要检查单元格是否包含特定单词，可以使用基于TEXTSPLIT 函数的公式。在所示工作表中，单元格 D5 中的公式为：

=COUNT(XMATCH("green",TEXTSPLIT(B5,{".",", "," "})))>0

公式向下复制时，如果 B 列中的文本包含单词“green”，则返回 TRUE；否则返回 FALSE。有关完整说明以及此公式的变体（用于测试多个单词、至少两个单词、所有给定单词以及特定单词及其例外情况），请参见下文。

解释

在这个例子中，目标是测试单元格中的文本，如果包含一个或多个特定单词则返回 TRUE，否则返回 FALSE。请注意，这里强调的是单词，而不是子字符串。例如，如果我们要测试单词“green”，而文本包含单词“evergreen”但不包含单词“green”，则公式应返回 FALSE。传统上，这在 Excel 中一直是个难题，因为没有简单的方法将文本解析成单词。但是，随着 TEXTSPLIT 函数的引入，现在可以轻松地将文本字符串直接拆分成单词数组。一旦我们将单词放入数组中，就可以对其进行测试了。

注意：TEXTSPLIT 函数仅在最新版本的 Excel 中可用。在旧版本的 Excel 中，您可以使用类似的公式来检查子字符串。

使用 TEXTSPLIT 将文本拆分成单词

TEXTSPLIT 函数用于使用给定的分隔符将文本拆分成数组。例如，在下面的公式中，我们使用 TEXTSPLIT 函数和空格（“ "”）作为分隔符，将文本“The sea is blue”拆分成 4 个单词：

=TEXTSPLIT("The sea is blue"," ") // returns {"The","sea","is","blue"}

一旦我们将单词放入数组中，就可以对它们进行各种操作。我们可以统计单词数量、比较单词、检查特定单词等等。TEXTSPLIT 函数非常适合这项任务，但需要注意一个复杂情况：单词之间并不总是仅用空格分隔。例如，如果我们对文本“The flag is red, green, and blue”使用上述公式会发生什么情况呢？

=TEXTSPLIT("Red, green, and blue"," ")

在这种情况下，TEXTSPLIT 返回的数组如下所示：

{"Red,","green,","and","blue"}

请注意，“red”和“blue”后面的逗号是输出的一部分。这不好。我们不希望标点符号包含在单词中，因为这会在后续分析单词时造成问题。解决方法是扩展提供给 TEXTSPLIT 的分隔符列表，以便在需要时包含标点符号。在本例中，我们只需添加一个逗号 ("")，如下所示：

=TEXTSPLIT("Red,green, and blue",{","," "})

注意：我们使用数组常量{",",""}来提供逗号和空格，这是一种同时提供多个硬编码值的便捷方法。

现在我们要求 TEXTSPLIT 函数分割以逗号 (",") 和空格 (" ") 分隔的单词。这部分运行正常，但请注意，结果数组中的第三个元素多了一个空字符串：

{"Red","green","","and","blue"}

这是因为 TEXTSPLIT 现在会在两个分隔符处分割文本。这也不好，不过根据具体情况，可能无关紧要。无论如何，我们可以通过将ignore_empty设置为 1 来轻松删除空值：

=TEXTSPLIT("Red,green, and blue",{","," "},,1)

注意，我们需要跳过并省略row_delimiter参数。修改后的公式现在可以给出我们想要的结果，即一个包含四个单词且不包含任何标点符号或空值的数组：

{"Red","green","and","blue"}

注意：您需要调整提供给 TEXTSPLIT 的分隔符，以适应您正在处理的文本。

现在我们有了开始测试单元格中特定词语所需的核心流程。

测试单词

现在我们有了一个单词数组，下一步是将这些单词与我们感兴趣的单词进行比对。在所示的工作表中，我们要查找的是单词“green”。在 Excel 中有多种方法可以实现这一点。例如，我们可以统计 TEXTSPLIT 函数返回的数组中单词“green”出现的次数。然而，更具扩展性的方法是使用XMATCH 函数来检查 TEXTSPLIT 的结果。

XMATCH 是MATCH 函数的升级版，它返回查找值在数据数组中的数值位置。XMATCH 的一个优点是它会自动执行精确匹配，这正是我们在此示例中所需要的。要使用 XMATCH 在 TEXTSPLIT 的输出中查找“green”，我们使用如下公式：

XMATCH("green",TEXTSPLIT("Red, green, and blue",{".",", "," "}))

请注意，我们按照上述说明使用了 TEXTSPLIT，但我们已将 TEXTSPLIT 函数嵌入到 XMATCH 函数中作为查找数组，查找值为“green”。

XMATCH("green",TEXTSPLIT(B5,{".",","," "},,1))

TEXTSPLIT 运行后，我们得到以下结果：

=XMATCH("green",{"Red","green","and","blue"}) // returns 2

因为“green”是TEXTSPLIT返回数组中的第二个值，所以XMATCH函数返回2。如果我们检查一个不存在的值，例如单词“pink”，会发生什么情况？在这种情况下，XMATCH会返回#N/A错误：

=XMATCH("pink",{"Red","green","and","blue"}) // returns #N/A

总结一下：当 XMATCH 函数找到一个值时，它会返回一个数值位置。当 XMATCH 函数找不到值时，它会返回 #N/A 错误。至此，我们已经有了一个检查单元格中是否存在特定单词的基本机制。剩下的唯一任务就是返回 TRUE 或 FALSE。最简单的方法是使用 ISNUMBER 函数，如下所示：

=ISNUMBER(XMATCH("green",{"Red","green","and","blue"})) // returns TRUE
=ISNUMBER(XMATCH("pink",{"Red","green","and","blue"})) // returns FALSE

这种方法虽然可行，但更通用的方法（能更好地扩展以处理其他相关问题）是使用 COUNT 函数，如下所示：

=COUNT(XMATCH("green",{"Red","green","and","blue"})) // returns 1
=COUNT(XMATCH("pink",{"Red","green","and","blue"})) // returns 0

COUNT 函数只统计数值。因此，当 XMATCH 返回一个数字时，COUNT 将返回一个正数。当 XMATCH 返回错误时，COUNT 将返回零。要获得 TRUE 或 FALSE 的结果，我们可以检查计数是否大于零。

=COUNT(XMATCH("green",{"Red","green","and","blue"}))>0 // TRUE
=COUNT(XMATCH("pink",{"Red","green","and","blue"}))>0 // FALSE

现在我们已经具备了在单元格中测试特定词语所需的所有条件。

单元格包含特定词语

回到上面工作表中的示例，目标是检测给定的单词并返回 TRUE 或 FALSE。这可以通过单元格 D5 中的如下公式实现：

=COUNT(XMATCH("green",TEXTSPLIT(B5,{".",","," "},,1)))>0

从内到外，TEXTSPLIT 函数被配置为使用数组常量中提供的三个分隔符来拆分单元格 B5 中的文本，如下所示：

TEXTSPLIT(B5,{".",", "," "},,1)

文本- 单元格 B5
col_delimiter - {".",","," "}
行分隔符- 已省略
ignore_empty - 1（等同于 TRUE）

请注意，我们提供了三种不同的分隔符：句点（“.”）、逗号（“”）和空格（“”）。我们还通过提供 1 将ignore_empty设置为 TRUE。这是一个重要的细节。我们希望 TEXTSPLIT 忽略空值，因为分隔符有时会以某种方式分割文本，从而导致出现空字符串（“”）。启用忽略空值功能将移除 TEXTSPLIT 输出中出现的任何空值。

在单元格 D5 中，TEXTSPLIT 的结果是一个包含七个单词的数组，如下所示：

{"The","brown","dog","lay","on","green","grass"}

其次，由于“green”是第六个单词，XMATCH 函数返回 6，COUNT 返回 1，最终结果为 TRUE：

=COUNT(XMATCH("green",{"The","brown","dog","lay","on","green","grass"}))>0
=COUNT(2)>0
=1>0
=TRUE

注意：XMATCH 不区分大小写，因此测试“Green”或“green”将返回相同的结果。

单元格包含多个单词中的一个

另一个常见的挑战是检查单元格中是否包含多个单词中的一个，如下面的工作表所示。这里的目标是检查 B 列中的文本值是否包含 F5:F7 区域（名为“words”）中出现的三个单词中的任何一个。

测试多个特定词语中的一个

我们应该如何调整公式以处理多个查找值？实际上，我们可以使用上面相同的公式，只需将文本“green”替换为命名区域词（F5:F7）：

=COUNT(XMATCH(words,TEXTSPLIT(B5,{".",","," "},,1)))>0

区别在于，命名区域 words包含三个单词，Excel 将其表示为如下所示的数组：

{"red";"blue";"green"}

注意：命名范围是可选的，但它提供了一些便利：它会自动像绝对引用一样工作，并且使公式更易读。如果您愿意，也可以使用常规的绝对引用（$F$5:$F$7）。

因为我们现在给 XMATCH 提供了三个不同的查找值，所以它将返回 3 个结果，如下所示：

{#N/A;#N/A;6}

第一个 #N/A 错误表示未找到“red”。第二个 #N/A 错误表示未找到“blue”。最后一个结果 6 表示单词“green”出现在 TEXTSPLIT 返回的数组中的第六个单词。COUNT函数只计算数字，因此返回 1，最终结果为 TRUE。您可以增加或减少 XMATCH 函数提供的单词数量，公式仍能正常工作。

注意：无论找到多少个单词，此公式都将返回 TRUE。

单元格包含至少 2 个单词

现在基本公式已经可以运行，我们可以轻松调整逻辑以适应更具体的用例。例如，我们可以要求单元格中至少包含两个提供的单词，如下图所示：

测试至少包含多个单词中的两个

在此工作表中，单元格 D5 中的公式现在如下所示：

=COUNT(XMATCH(words,TEXTSPLIT(B5,{".",","," "},,1)))>=2

请注意，此公式几乎与上面的公式完全相同。唯一的区别在于，我们使用 >=2 来检查 COUNT 函数的结果，以确保单元格 B5 中至少有两个匹配的单词。如前所述，单元格 B5 的结果为 FALSE，因为文本中只包含单词“red”。但是，单元格 D7 的结果为 TRUE，因为单元格 B7 中同时出现了“red”和“blue”，所以 COUNT 函数返回 2。单元格 B7 中的公式计算如下：

=COUNT(XMATCH(words,TEXTSPLIT(B7,{".",","," "},,1)))>=2
=COUNT({4;7;#N/A})>=2
=2>=2
=TRUE

单元格包含所有单词

如何调整公式，使其要求所有给定的单词都出现在单元格中？只需稍作调整即可实现，如下面的工作表所示：

测试所有单词

将单元格 D5 中的公式向下复制后，现在看起来像这样：

=COUNT(XMATCH(words,TEXTSPLIT(B5,{".",","," "},,1)))=COUNTA(words)

在这个公式中，我们将 COUNT 函数返回的“命中”次数与COUNTA 函数返回的单词数进行比较。当两者相等时，表示我们在文本中找到了所有指定的单词，公式返回 TRUE。当两者不相等时，表示至少有一个单词未找到，公式返回 FALSE。我们使用 COUNTA 函数来统计单词数，因为 COUNTA 函数可以同时统计数字和文本值。

单元格包含一些单词，但不包含其他单词。

最后一个例子，我们来看看如何检测单元格中是否包含某些特定词语，同时排除其他词语。这就是我们想要的效果：

如果一个单元格包含特定的目标词，但不包含其他目标词，则结果应为真。
如果一个单元格包含特定的目标词，并且也包含其他特定的词，则结果应为 FALSE。

这使得问题更具挑战性，但我们仍然可以使用上述方法。为了使公式更高效、更易读，我们将添加LET 函数。LET 函数只需一次即可将源文本解析成单词，并将结果存储在一个变量中以便重复使用。

在下面的工作表中，我们正在测试 B 列中的文本是否包含“红色”、“蓝色”和“绿色”这三个词，同时排除包含“粉色”或“棕色”这三个词的单元格：

测试特定词语，同时排除其他词语。

请注意，我们还在工作表中添加了一个命名区域：F5:F7 区域仍然命名为“words”，F12:F13 区域现在命名为“xwords”。这样做的目的是让xwords包含我们想要排除的单词。单元格 D5 中的公式如下：

=LET(
  source,TEXTSPLIT(B5,{".",","," "},,1),
  AND(
    COUNT(XMATCH(words,source))>0,
    COUNT(XMATCH(xwords,source))=0)
)

在公式的第一行，我们使用如上所述的 TEXTSPLIT 函数，从单元格 B5 中的文本创建一个单词数组，并将该数组赋值给名为“source”的变量：

source,TEXTSPLIT(B5,{".",","," "},,1),

接下来，我们使用 AND 函数来检查两个表达式的结果。第一个表达式检查单元格中是否至少包含一个我们感兴趣的单词：

COUNT(XMATCH(words,source))>0

第二个表达式检查单元格中是否没有与目标无关的单词：

COUNT(XMATCH(xwords,source))=0)

只有当上述两个表达式都返回 TRUE 时，AND 函数才会返回 TRUE。如果其中任何一个表达式为 FALSE，则结果也为 FALSE。这样就达到了我们的目标：公式仅在找到words中至少一个单词且未找到xwords中任何单词时才返回 TRUE。

详情页