关键词 | 弦乐样本 | 预期结果 |
---|---|---|
汽车、自行车、火车、狗、鸟巢 | 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&" ")))}
感谢 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