201401函中班课前练习题06

一杯清荼

1. =LOOKUP(,0/FIND({"[","JHJD","JHZJ","JHBP","KSKS"},B9),TEXT({"",1,4,3,2},"设计0部"))

复制代码

danny-fei

1. =IFERROR(LOOKUP(99,FIND({"JHJD";"JHZJ";"JHBP";"KSKS"},B37),{"设计1部";"设计4部";"设计3部";"设计2部"}),"")

复制代码

C.Deny

=IFERROR("设计"&LOOKUP(,0/(COUNTIF(B2,{"*JHJD*";"*JHZJ*";"*JHBP*";"*KSKS*"})),{1;4;3;2})&"部","")下拉

zac0226

1. =IFERROR(LOOKUP(199,FIND({"JHJD";"JHZJ";"JHBP";"KSKS"},B2),{"设计1部";"设计4部";"设计3部";"设计2部"}),"")

复制代码

木偶fenzi

1. =TEXT(IF(ISERR(FIND("JHJD",B2)),IF(ISERR(FIND("JHZJ",B2)),IF(ISERR(FIND("JHBP",B2)),IF(ISERR(FIND("KSKS",B2)),"",2),3),4),1),"设计0部")

复制代码

borly

1. =TEXT(SUM(IF(ISNUMBER(FIND({"JHJD","JHZJ","JHBP","KSKS"},B2)),{1,4,3,2})),"设计0部;;")

复制代码

珺珺妈

1. =IFERROR("设计"&VLOOKUP(MID(B2,MAX(IFERROR(FIND({"J";"K"},B2),"")),4),{"JHJD",1;"JHZJ",4;"JHBP",3;"KSKS",2},2,)&"部","")

复制代码

三键。
老师辛苦啦。题好难。。

滴答滴

1. =MID(C$1,60-SUM(COUNTIF(B2,"*"&MID(C$1,{6,11,16,21},4)&"*")*{6,5,4,3}*5),4)

复制代码

1. =TEXT(MATCH(,-FIND(MID(C$1,{99,6,21,16,11},4),B2))-1,"设计0部;;")

复制代码

ldxhzy

C2公式：
=IF(SUBSTITUTE(B2,"JHJD","")<>B2,"设计1部",IF(SUBSTITUTE(B2,"JHZJ","")<>B2,"设计4部",IF(SUBSTITUTE(B2,"JHBP","")<>B2,"设计3部",IF(SUBSTITUTE(B2,"KSKS","")<>B2,"设计2部",""))))

岁月静好

=IF(ISNUMBER(FIND("JHJD",B3))=TRUE,"设计1部",IF(ISNUMBER(FIND("JHZJ",B3))=TRUE,"设计4部",IF(ISNUMBER(FIND("JHBP",B3))=TRUE,"设计3部",IF(ISNUMBER(FIND("KSKS",B3))=TRUE,"设计2部",""))))
公式是不是有些太长了

本来想用自定义 来设置 并显出不同的颜色来 可惜没有成功