excel包含不包含函数填写?

发布网友发布时间：2022-04-23 02:51

共3个回答

热心网友时间：2023-10-12 09:39

如果不是很高级的EXCEL，公式这么写：

="包含"&REPT("A",NOT(ISERR(FIND("A",B1))))&REPT("B",NOT(ISERR(FIND("B",B1))))&REPT("C",NOT(ISERR(FIND("C",B1))))&REPT("D",NOT(ISERR(FIND("D",B1))))

&"，不包含"&REPT("A",ISERR(FIND("A",B1)))&REPT("B",ISERR(FIND("B",B1)))&REPT("C",ISERR(FIND("C",B1)))&REPT("D",ISERR(FIND("D",B1)))

效果示例：

热心网友时间：2023-10-12 09:40

请看看这是否是你需要的结果？

="包含"&B1&"不包含"&IF(ISERR(FIND("A",B1)),"A,","")&IF(ISERR(FIND("B",B1)),"B,","")&IF(ISERR(FIND("C",B1)),"C,","")&IF(ISERR(FIND("D",B1)),"D","")

追问如果ABCD，文字"不包含“不显示，如果"",文字"包含"不显示，这样函数还得要怎么修改？

追答那最简单当然是直接用if判断。
=IF(B1="","","包含"&B1)&IF(B1="A,B,C,D","","不包含"&IF(ISERR(FIND("A",B1)),"A,","")&IF(ISERR(FIND("B",B1)),"B,","")&IF(ISERR(FIND("C",B1)),"C,","")&IF(ISERR(FIND("D",B1)),"D",""))

热心网友时间：2023-10-12 09:40

在C1中输入或复制粘贴下列公式之一
=IF(ISNUMBER(FIND("A",B1)),"","不")&"包含A "&IF(ISNUMBER(FIND("B",B1)),"","不")&"包含B "&IF(ISNUMBER(FIND("C",B1)),"","不")&"包含C "&IF(ISNUMBER(FIND("D",B1)),"","不")&"包含D"
=IF(COUNT(FIND("A",B1)),"","不")&"包含A "&IF(COUNT(FIND("B",B1)),"","不")&"包含B "&IF(COUNT(FIND("C",B1)),"","不")&"包含C "&IF(COUNT(FIND("D",B1)),"","不")&"包含D" 下拉填充