|
|
《偷懒的技术》读者群有朋友问：
如何判断单元格中包含a、b、c、d中的任意一个。如果包含，则标记为“通过”，否则标记为“不通过”。
这个判断是否包含的需求在工作中比较常见，故将解决思路写成本文分享给大家。
我们先从简单的开始：
一、如何判断单元格是否判断包含某个字符串
我们使用Excel时遇到问题，先别去考虑用什么函数，先要有解决思路，有了思路再来思考“用什么函数、如何编公式”。
Excel中的函数大多数都很简单，稍加练习就可掌握，关键的是要有思路(或者说是套路)。我们学习Excel书上或网上看到的一些公式，最需要学习的，不是学习所用到的函数，而是公式的思路。
在Power Query的M语言中有个List.ContainsAny函数，可轻松解决本文的问题但Excel工作表函数中，并没有“判断是否包含”的函数。因而，我们只有用其他方法代替，可用以下思路来解决：
- 用查找函数来查找字符串的位置。如果找到了，返回的值就是大于0小于等于长符字度的一个数字。如果没找到，就会出错。
- 用替换函数来删除要查找的字符，再用删除后的字符数和原字符数进行对比。如果长度不一样，那肯定是包含了的。
- 用条件统计个数的函数（用通配符来构建"包含"这个条件），如果统计的个数等于1，那就是包含的。
下面分别举例说明：
思路1：用查找函数查找字符串的位置
查找函数有二个：FIND、SEARCH，二者语法结构差不多，
=FIND(找什么, 在哪找, 从哪个位置开始找)
=SEARCH(找什么, 在哪找, 从哪个位置开始找)
区别是：
FIND函数要区分大小写，不能使用通配符；
SEARCH函数不区分大小写，能使用通配符。
比如公式
=FIND("excel",A2,1)
就是从第一个字符开始查找A2单元格中是否有“excel”字符，如果找到了，返回其位置数。
然后在外面套一个IF函数，其公式就是：
=IF(FIND("excel",A2,1)>0,"包含","不包含")
=IF(SEARCH("excel",A2,1)>0,"包含","不包含")
如果公式中没有要找的字符串，公式就会出错，为避免显示错误，可以完善一下：
=IF(COUNT(FIND("excel",A2,1))>0,"包含","不包含")
=IF(COUNT(SEARCH("excel",A2,1))>0,"包含","不包含")
实际上，这个公式可以简化一下，
由于Excel用IF逻辑判断时，非零值都视同true，零值为false，比如
=IF(-1,"结果1","结果2")，公式返回结果1；
=IF(2,"结果1","结果2")，公式返回结果1；
=IF(0,"结果1","结果2")，公式返回结果2。
而COUNT函数是统计数字的个数，其结果不是0，就是大于0的其他数。因而前面的公式可将“>0”的判断去掉，直接简化为：
=IF(COUNT(FIND("excel",A2,1)),"包含","不包含")
=IF(COUNT(SEARCH("excel",A2,1)),"包含","不包含")
思路2：用替换函数来删除要查找的字符，然后比较字符长度的变化
公式：
=IF(LEN(A2)<>LEN(SUBSTITUTE(A2,&#34;excel&#34;,&#34;&#34;)),&#34;包含&#34;,&#34;不包含&#34;)
此公式会区分大小写。
思路3：利用COUNTIF可以使用通配符的特点，构造“包含”条件
公式：
=IF(COUNTIF(A2,&#34;*excel*&#34;),&#34;包含&#34;,&#34;不包含&#34;)
此公式不区分大小写。
上面二个公式就不解释了。
二、如何判断单元格是否判断包含多个字符串中的任意一个
下面我们回答本文开篇时的问题
如何判断单元格中包含a、b、c、d中的任意一个。如果包含，则标记为“通过”，否则标记为“不通过”。
1、用查找函数查找字符串的位置
如果要查找多个字符串可以用大括号括起来就是了。
=IF(COUNT(FIND({&#34;A&#34;,&#34;B&#34;,&#34;C&#34;},A13,1)),&#34;通过&#34;,&#34;不通过&#34;)
解释：
{&#34;A&#34;,&#34;B&#34;,&#34;C&#34;}是常量数组，在计算时，公式会将常量数组逐个传递到公式中计算：
先传递&#34;A&#34;，FIND(&#34;A&#34;,A13,1)
然后传递&#34;B&#34;，FIND(&#34;B&#34;,A13,1)
最后传递&#34;C&#34;，FIND(&#34;A&#34;,A13,1)
都计算完后，再用count函数统计上面计算结果中数字的个数，然后将COUNT计算结果作为IF函数的第一参数，再根据此参数的结果是否等于0，决定返回&#34;通过&#34;还是&#34;不通过&#34;。
其余二种思路的公式与上面类似，请大家参照前文的解释对照理解，就不详细解释了。
=IF(SUM(--(LEN(A10)<>LEN(SUBSTITUTE(A10,{&#34;A&#34;,&#34;B&#34;,&#34;C&#34;},&#34;&#34;)))),&#34;通过&#34;,&#34;不通过&#34;)
=IF(SUM(COUNTIF(A10,&#34;*&#34;&{&#34;a&#34;,&#34;b&#34;,&#34;c&#34;}&&#34;*&#34;)),&#34;通过&#34;,&#34;不通过&#34;)
保持其他条件不变，添加一个条件：如果含有CF，就标记不通过，那公式该怎么写呢？
“CF”中含有“C”，我们在设计公式时，可以先用IF函数判断，是否含有CF，如果有，则标记为“不通过”，如果没有，再利用前面的公式判断是否含有其他字符。完整的公式为：
=IF(COUNTIF(A23,&#34;*CF*&#34;),&#34;不通过&#34;,IF(COUNT(FIND({&#34;A&#34;,&#34;B&#34;,&#34;C&#34;},A23,1)),&#34;通过&#34;,&#34;不通过&#34;))
=IF(LEN(A20)<>LEN(SUBSTITUTE(A20,&#34;CF&#34;,&#34;&#34;)),&#34;不通过&#34;,IF(SUM(--(LEN(A20)<>LEN(SUBSTITUTE(A20,{&#34;A&#34;,&#34;B&#34;,&#34;C&#34;},&#34;&#34;)))),&#34;通过&#34;,&#34;不通过&#34;))
=IF(COUNTIF(A20,&#34;*cf*&#34;),&#34;不通过&#34;,IF(SUM(COUNTIF(A20,&#34;*&#34;&{&#34;a&#34;,&#34;b&#34;,&#34;c&#34;}&&#34;*&#34;)),&#34;通过&#34;,&#34;不通过&#34;))
大家如果想学习更多的函数知识和Excel实战经验，欢迎购买《“偷懒”的技术：打造财务Excel达人》
《“偷懒”的技术》稳踞当当网办公类畅销榜前五名，
好评率99.7%的Excel畅销书，你值得拥有！
购买地址：
http://product.dangdang.com/23626444.html
－－－－－－－－－－－－－－－－－－－－
本文首发于微信公众号“Excel偷懒的技术“，
本公众号坚持分享原创Excel文章，求实用、接地气、不炫技。欢迎大家关注！
如果本文对你有帮助，欢迎点赞、转发分享 |
|
回复
发帖
发表于 2023-1-13 18:04:32