发布网友 发布时间:2022-05-11 02:00
共5个回答
热心网友 时间:2023-11-26 05:11
b1参考:
=IF(LEFT(A1,1)="1",IF(LEN(A1)-LEN(SUBSTITUTE(A1,"a",""))>0,"麻辣"&LEN(A1)-LEN(SUBSTITUTE(A1,"a","")),"")&IF(LEN(A1)-LEN(SUBSTITUTE(A1,"b",""))>0,"微辣"&LEN(A1)-LEN(SUBSTITUTE(A1,"b","")),"")&IF(LEN(A1)-LEN(SUBSTITUTE(A1,"c",""))>0,"不辣"&LEN(A1)-LEN(SUBSTITUTE(A1,"c","")),""),"")
c1参考:
=IF(LEFT(A1,1)="1","",IF(LEN(A1)-LEN(SUBSTITUTE(A1,"a",""))>0,"麻辣"&LEN(A1)-LEN(SUBSTITUTE(A1,"a","")),"")&IF(LEN(A1)-LEN(SUBSTITUTE(A1,"b",""))>0,"微辣"&LEN(A1)-LEN(SUBSTITUTE(A1,"b","")),"")&IF(LEN(A1)-LEN(SUBSTITUTE(A1,"c",""))>0,"不辣"&LEN(A1)-LEN(SUBSTITUTE(A1,"c","")),""))
然后公式下拉。
追问非常感谢!!! 在以上公式基础上还有一点要实现,在A列同时输入1ac2bc,则同时在B列显示麻辣1不辣1 C列显示微辣1不辣1
热心网友 时间:2023-11-26 05:11
如图,用个笨办法,定义两个名称,Nr代表牛肉,Yr代表羊肉
Nr=IF(FIND("1",Sheet1!$A2&"1")<=LEN(Sheet1!$A2),MID(Sheet1!$A2,FIND("1",Sheet1!$A2)+1,FIND("2",Sheet1!$A2&"2")-2),"")
Yr=IF(FIND("2",Sheet1!$A2&"2")<=LEN(Sheet1!$A2),MID(Sheet1!$A2,FIND("2",Sheet1!$A2)+1,10),"")
B2=IF(LEN(Nr)=0,"","牛肉"&IF(ISNUMBER(FIND("a",Nr)),"麻辣"&LEN(Nr)-LEN(SUBSTITUTE(Nr,"a","")),"")&IF(ISNUMBER(FIND("b",Nr)),"微辣"&LEN(Nr)-LEN(SUBSTITUTE(Nr,"b","")),"")&IF(ISNUMBER(FIND("c",Nr)),"不辣"&LEN(Nr)-LEN(SUBSTITUTE(Nr,"c","")),""))
C2=IF(LEN(Yr)=0,"","羊肉"&IF(ISNUMBER(FIND("a",Yr)),"麻辣"&LEN(Yr)-LEN(SUBSTITUTE(Yr,"a","")),"")&IF(ISNUMBER(FIND("b",Yr)),"微辣"&LEN(Yr)-LEN(SUBSTITUTE(Yr,"b","")),"")&IF(ISNUMBER(FIND("c",Yr)),"不辣"&LEN(Yr)-LEN(SUBSTITUTE(Yr,"c","")),""))
热心网友 时间:2023-11-26 05:12
逻辑描述混乱,重新描述。热心网友 时间:2023-11-26 05:13
有文件才方便做公式,把问题作为内容(邮件主题一定要包含“excel",本人以此为依据辨别非垃圾邮件,以免误删),excel问题文件(注意:若是03后,请一定要另成为03的,把现状和目标效果表示出来,并说明如何从现状到的目标效果)作为附件发到yqch134@163.com帮你看下热心网友 时间:2023-11-26 05:13
会不会有1a2bc1a这样的记录,有的话建议用代码(自定义函数)。追问会有1ab2ac,但不会出现1ac2bc1aa的记录,用代码是什么意思?追答