在EXCEL中怎样实现1,2,3,5,7等于1或者质，0,4,6,8,9等于2或者合.

Question

如：61508需要转换成21122或者合质质合合。

75932需要转化成11211或质质合质质。

Answer 1

B1=SUMPRODUCT(((MMULT(1-ISERR(-(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)/COLUMN(A:J)&".")),ROW($1:$10)^0)>2)+1)*10^(LEN(A1)-ROW(INDIRECT("1:"&LEN(A1)))))

还有个问题。1是非质非合的。如果你的位数不固定，要显示合质这样的最后用2个substitute替换一下就行。

追问

谢谢

Answer 2

前面的数字确定是5位数吗？
原理应该是逐位去判定然后合并就是了
如果是5位数的话，公式是：
=IF(MOD(MID(A1,1,1),2)=0,2,1)&IF(MOD(MID(A1,2,1),2)=0,2,1)&IF(MOD(MID(A1,3,1),2)=0,2,1)&IF(MOD(MID(A1,4,1),2)=0,2,1)&IF(MOD(MID(A1,5,1),2)=0,2,1)
或是：
=IF(MOD(MID(A1,1,1),2)=0,"合","质")&IF(MOD(MID(A1,2,1),2)=0,"合","质")&IF(MOD(MID(A1,3,1),2)=0,"合","质")&IF(MOD(MID(A1,4,1),2)=0,"合","质")&IF(MOD(MID(A1,5,1),2)=0,"合","质")本回答被提问者和网友采纳

Answer 3

=IF(OR(MID(A1,1,1)={0,4,6,8,9}&""),"合","质")&IF(OR(MID(A1,2,1)={0,4,6,8,9}&""),"合","质")&IF(OR(MID(A1,3,1)={0,4,6,8,9}&""),"合","质")&IF(OR(MID(A1,4,1)={0,4,6,8,9}&""),"合","质")&IF(OR(MID(A1,5,1)={0,4,6,8,9}&""),"合","质")追问

谢谢