Excel表格：1代表牛肉（B列），2代表羊肉（C列），a代表麻辣，b代表微辣，c代表不辣

发布网友发布时间：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

逻辑描述混乱，重新描述。
按你的描述，1代表牛肉，a代表麻辣，那么1aaa就代表“牛肉麻辣麻辣麻辣”
还有后面括号的B列，C列什么的，逻辑意义不明。

热心网友时间：2023-11-26 05:13

有文件才方便做公式，把问题作为内容（邮件主题一定要包含“excel",本人以此为依据辨别非垃圾邮件，以免误删），excel问题文件（注意：若是03后，请一定要另成为03的，把现状和目标效果表示出来，并说明如何从现状到的目标效果）作为附件发到yqch134@163.com帮你看下

热心网友时间：2023-11-26 05:13

会不会有1a2bc1a这样的记录，有的话建议用代码（自定义函数）。追问会有1ab2ac，但不会出现1ac2bc1aa的记录，用代码是什么意思？

追答