Excel:检查字符串是否包含多个逗号分隔的关键字之一

Excel:检查字符串是否包含多个逗号分隔的关键字之一
关键词 弦乐样本 预期结果
汽车、自行车、火车、狗、鸟巢 C 错误的
错误的
真的
火车、汽车 真的
小狗 错误的
错误的
汽车 自行车 错误的
错误的
燕窝 真的
鸟巢2 真的
鸟巢2 错误的

要求

  • 解决方案不应局限于特定的 Microsoft Excel 版本。
  • 不应使用辅助列
  • 不应使用任何宏。

我尝试使用带有 SUMPRODUCT 的 SEARCH 来解决这个问题,但遇到了误报问题。为了改进公式,我的第一个挑战是将以逗号分隔的排除词列表转换为数组,这可以作为使用 SEARCH 作为数组公式的基础。


样品表中测试的解决方案:

答案1

使用:

=AND(ISNUMBER(SEARCH(", "&TRIM(MID(SUBSTITUTE(B2,",",REPT(" ",999)),(ROW($ZZ$1:INDEX($ZZ:$ZZ,LEN(B2)-LEN(SUBSTITUTE(B2,",",""))+1))-1)*999+1,999))&", ",", "&$A$2&", ")))

ROW($ZZ$1:INDEX($ZZ:$ZZ,LEN(B2)-LEN(SUBSTITUTE(B2,",",""))+1)) 创建一个从 1 到查找字符串样本中的实体数的数字数组。因此,对于除 B5 之外的所有数组,对于{1}B5,它将是{1;2}

然后我们减去 1 使其0基于。然后我们将其乘以999并得到类似数组,{0;999}然后我们再加回 1 {1,1000},。

这就是成为MID的第二个标准。第三个是999

第一个是字符串,我们用 999 个空格代替任何逗号。这样可以留出很大的空白区域。我们在这些大区域的中间某处进行分割,然后进行修剪,返回搜索项的数组,即:{"Train","Car"}

我们将其传递给搜索的第一个条件。我们将 附加", "到两个条件的开头和结尾,以便我们查找完整的单词。

AND 将确保找到搜索条件中的所有单词。

在此处输入图片描述

答案2

在我自己对问题有了更好的理解之后,我不得不添加两个示例字符串:1.Bird nest 2其结果应为TRUE。搜索不应区分大小写,并且部分匹配关键字(如果分隔符是空格字符)就足够了。2.bird nest2其结果为,FALSE因为 2 没有被空格分隔。

我最终使用的解决这个问题的公式是:

{=AND(COUNT(SEARCH(TRIM(MID(SUBSTITUTE($A$2,",",REPT(" ",999)),(ROW($ZA$1:INDEX($ZA:$ZA,LEN($A$2)-LEN(SUBSTITUTE($A$2,",",""))+1))-1)*999+1,999))&" ", B2&" ")))}

参见 Google 表格扩展或者消耗臭氧层物质


感谢 Scott Craner 提供的核心部分TRIM(MID(SUBSTITUTE($A$2,",",REPT(" ",999)),(ROW($ZA$1:INDEX($ZA:$ZA,LEN($A$2)-LEN(SUBSTITUTE($A$2,",",""))+1))-1)*999+1,999))&" ",基本上就是将逗号分隔的关键字列表转换为数组。在他的帖子中,他将字符串样本转换为数组,而我则改为将关键字转换为数组。

使用SEARCH数组公式搜索样本字符串以查找数组中的每个关键字。

COUNT返回字符串中找到的关键字数量,如果数量不为 0,则AND返回。TRUE

相关内容