if多重嵌套精简

gooodok

=IF(COUNTIF(B10,"*产业化*")<>0,"本列不能含’产业化",if(COUNTIF(B10,"*示范*")<>0,"本列不能含’示范’",if(COUNTIF(B10,"*集成*")<>0,"本列不能含’集成’",if(COUNTIF(B10,"*应用*")<>0,"本列不能含’应用’",if(COUNTIF(B10,"*规模化*")<>0,"本列不能含’规模化’",if(COUNTIF(B10,"*咨询*")<>0,"本列不能含’咨询’",if(COUNTIF(B10,"*搭建实施*")<>0,"本列不能含’搭建实施’",if(COUNTIF(B10,"*安装*")<>0,"本列不能含’安装’",if(COUNTIF(B10,"*加工*")<>0,"本列不能含’加工’",if(COUNTIF(B10,"*规范化*")<>0,"本列不能含’规范化’",if(COUNTIF(B10,"*优化*")<>0,"本列不能含’优化’",if(COUNTIF(B10,"*工艺*")<>0,"本列不能含’工艺’",if(COUNTIF(B10,"*方法研究*")<>0,"本列不能含’方法研究’",if(COUNTIF(B10,"*工业化*")<>0,"本列不能含’工业化’",if(COUNTIF(B10,"*生产技术*")<>0,"本列不能含’生产技术’",if(COUNTIF(B10,"*升级*")<>0,"本列不能含’升级’",if(COUNTIF(B10,"*工程*")<>0,"本列不能含’工程’",if(COUNTIF(B10,"*升级*")<>0,"本列不能含’升级’",if(COUNTIF(B10,"*改进*")<>0,"本列不能含’改进’",if(COUNTIF(B10,"*管理*")<>0,"本列不能含’管理’",if(COUNTIF(B10,"*教学*")<>0,"本列不能含’教学’",if(COUNTIF(B10,"*评价*")<>0,"本列不能含’评价’",if(COUNTIF(B10,"*计划*")<>0,"本列不能含’计划’",if(COUNTIF(B10,"*饲料*")<>0,"本列不能含’饲料’",if(COUNTIF(B10,"*成果*")<>0,"本列不能含’成果’",if(COUNTIF(B10,"*转化*")<>0,"本列不能含’转化’",if(COUNTIF(B10,"*中心*")<>0,"本列不能含’中心’",if(COUNTIF(B10,"*建设*")<>0,"本列不能含’建设’",if(COUNTIF(B10,"*基地*")<>0,"本列不能含’基地’",if(COUNTIF(B10,"*运营*")<>0,"本列不能含’运营’",if(COUNTIF(B10,"*提高*")<>0,"本列不能含’提高’",if(COUNTIF(B10,"*作业*")<>0,"本列不能含’作业’",if(COUNTIF(B10,"*推广*")<>0,"本列不能含’推广’",if(COUNTIF(B10,"*改善*")<>0,"本列不能含’改善’",if(COUNTIF(B10,"*生产*")<>0,"本列不能含’生产’",“”))))))))))))))))))))))))))))))))))))))))))

表达是如果B10单元格输入的句子中出现：”产业化、示范、集成、应用、规模化、咨询、搭建实施、安装、加工、规范化、优化、工艺、改造、方法研究、工业化、生产技术、升级、工程、改进、管理、教学、评价、计划、饲料、成果、转化、中心、建设、基地、运营、提高、作业、推广、改善、生产”就在单元格中显示：“本列不能含产业化”相对应值。怎么精简。

leepoison

建议用辅助列，以后可以方便调整。或者VBA

theking_wsz

=“本列不能含“&concat(if(isnumber(find({"产业化","示范","集成","应用","规模化","咨询","搭建实施","安装","加工","规范化","优化","工艺","改造","方法研究","工业化","生产技术","升级","工程","改进","管理","教学","评价","计划","饲料","成果","转化","中心","建设","基地","运营","提高","作业","推广","改善","生产"},b10)),{"产业化","示范","集成","应用","规模化","咨询","搭建实施","安装","加工","规范化","优化","工艺","改造","方法研究","工业化","生产技术","升级","工程","改进","管理","教学","评价","计划","饲料","成果","转化","中心","建设","基地","运营","提高","作业","推广","改善","生产"},""))

miaojohn45

1. =LET(x,{"产业化";"示范";"集成";"应用";"规模化";"咨询";"搭建实施";"安装";"加工";"规范化";"优化";"工艺";"改造";"方法研究";"工业化";"生产技术";"升级";"工程";"改进";"管理";"教学";"评价";"计划";"饲料";"成果";"转化";"中心";"建设";"基地";"运营";"提高";"作业";"推广";"改善";"生产"},"本列不能含产业化"&TEXTJOIN(",",,IF(COUNTIF(B10,"*"&x&"*"),""""&x&"""","")))

复制代码

使用辅助列的简化公式如下图所示：

世界客都

朋友，请上传附件，你这个问题显然可以通过列表来简化公式。

gooodok

miaojohn45 发表于 2024-2-28 16:37

有点问题，如果没有这些关键字，还是提示“本列不能含产业化”

gooodok

miaojohn45 发表于 2024-2-28 16:37

如果没有这些关键字，最好显示空

miaojohn45

gooodok 发表于 2024-2-28 16:48
如果没有这些关键字，最好显示空

关键字只能唯一出现，还是会同时出现两个或两个以上？

=LET(x,{"产业化";"示范";"集成";"应用";"规模化";"咨询";"搭建实施";"安装";"加工";"规范化";"优化";"工艺";"改造";"方法研究";"工业化";"生产技术";"升级";"工程";"改进";"管理";"教学";"评价";"计划";"饲料";"成果";"转化";"中心";"建设";"基地";"运营";"提高";"作业";"推广";"改善";"生产"},CONCAT(IF(COUNTIF(B10,"*"&x&"*"),"本列不能含产业化"""&x&"""。","")))


=LET(x,{"产业化";"示范";"集成";"应用";"规模化";"咨询";"搭建实施";"安装";"加工";"规范化";"优化";"工艺";"改造";"方法研究";"工业化";"生产技术";"升级";"工程";"改进";"管理";"教学";"评价";"计划";"饲料";"成果";"转化";"中心";"建设";"基地";"运营";"提高";"作业";"推广";"改善";"生产"},y,COUNTIF(B10,"*"&x&"*"),IF(SUM(y),"本列不能含产业化：","")&TEXTJOIN(",",,IF(y,""""&x&"""","")))

gooodok

miaojohn45 发表于 2024-2-28 16:55
关键字只能唯一出现，还是会同时出现两个或两个以上？

有多少出现多少，但实际最多出现三个。您前面那个公式我修改下就可以了，谢谢

everg

在第二个表A:A建立一个不能出现词的字典表,在B:B用公式:=SEARCHB(A2,输内容表!$B$10),这样的形式来做维护和写公式就简单多了