满足以下要求：1000.00 壹千圆整
1000.01 壹千圆零角壹分
1001.01 壹千零壹圆零角壹分
1010.01 壹千零壹拾圆零角壹分
满足此要求建的函数公式给高分！ 最高金额能到百万以下
1001.01 壹千零壹圆零壹分
这种效果的公式我有很多，我想要个壹千零壹圆零角壹分的函数公式，麻烦各位了
在线等
下面这个公式可以实现：
=IF(ROUND(A1,2)<0,"无效数值",IF(ROUND(A1,2)=0,"零",IF(ROUND(A1,2)<1,"",TEXT(INT(ROUND(A1,2)),"[dbnum2]")&"元")&IF(INT(ROUND(A1,2)*10)-INT(ROUND(A1,2))*10=0,IF(INT(ROUND(A1,2))*(INT(ROUND(A1,2)*100)-INT(ROUND(A1,2)*10)*10)=0,"","零角"),TEXT(INT(ROUND(A1,2)*10)-INT(ROUND(A1,2))*10,"[dbnum2]")&"角")&IF((INT(ROUND(A1,2)*100)-INT(ROUND(A1,2)*10)*10)=0,"整",TEXT((INT(ROUND(A1,2)*100)-INT(ROUND(A1,2)*10)*10),"[dbnum2]")&"分")))
=IF(ROUND(A1,2)<0,"无效数值",IF(ROUND(A1,2)=0,"零",IF(ROUND(A1,2)<1,"",TEXT(INT(ROUND(A1,2)),"[dbnum2]")&"元")&IF(INT(ROUND(A1,2)*10)-INT(ROUND(A1,2))*10=0,IF(INT(ROUND(A1,2))*(INT(ROUND(A1,2)*100)-INT(ROUND(A1,2)*10)*10)=0,"","零角"),TEXT(INT(ROUND(A1,2)*10)-INT(ROUND(A1,2))*10,"[dbnum2]")&"角")&IF((INT(ROUND(A1,2)*100)-INT(ROUND(A1,2)*10)*10)=0,"整",TEXT((INT(ROUND(A1,2)*100)-INT(ROUND(A1,2)*10)*10),"[dbnum2]")&"分")))
温馨提示：答案为网友推荐，仅供参考
第1个回答 2007-11-08
"壹千零壹圆零角壹分"好象不用怎么讲吧.
应该是壹仟零壹元零壹分.
=IF(ISNUMBER(A9)=FALSE,"输入非数字值",IF(A9>=0,IF(ROUND(A9,2)=0,"零",IF(ROUND(A9,2)<1,"",TEXT(INT(ROUND(A9,2)),"[dbnum2]")&"元")&IF(INT(ROUND(A9,2)*10)-INT(ROUND(A9,2))*10=0,IF(INT(ROUND(A9,2))*(INT(ROUND(A9,2)*100)-INT(ROUND(A9,2)*10)*10)=0,"","零"),TEXT(INT(ROUND(A9,2)*10)-INT(ROUND(A9,2))*10,"[dbnum2]")&"角")&IF((INT(ROUND(A9,2)*100)-INT(ROUND(A9,2)*10)*10)=0,"整",TEXT((INT(ROUND(A9,2)*100)-INT(ROUND(A9,2)*10)*10),"[dbnum2]")&"分")),IF(ROUND(ABS(A9),2)<1,"","负"&TEXT(INT(ROUND(ABS(A9),2)),"[dbnum2]")&"元")&IF(INT(ROUND(ABS(A9),2)*10)-INT(ROUND(ABS(A9),2))*10=0,IF(INT(ROUND(ABS(A9),2))*(INT(ROUND(ABS(A9),2)*100)-INT(ROUND(ABS(A9),2)*10)*10)=0,"","零"),TEXT(INT(ROUND(ABS(A9),2)*10)-INT(ROUND(ABS(A9),2))*10,"[dbnum2]")&"角")&IF((INT(ROUND(ABS(A9),2)*100)-INT(ROUND(ABS(A9),2)*10)*10)=0,"整",TEXT((INT(ROUND(ABS(A9),2)*100)-INT(ROUND(ABS(A9),2)*10)*10),"[dbnum2]")&"分")))
应该是壹仟零壹元零壹分.
=IF(ISNUMBER(A9)=FALSE,"输入非数字值",IF(A9>=0,IF(ROUND(A9,2)=0,"零",IF(ROUND(A9,2)<1,"",TEXT(INT(ROUND(A9,2)),"[dbnum2]")&"元")&IF(INT(ROUND(A9,2)*10)-INT(ROUND(A9,2))*10=0,IF(INT(ROUND(A9,2))*(INT(ROUND(A9,2)*100)-INT(ROUND(A9,2)*10)*10)=0,"","零"),TEXT(INT(ROUND(A9,2)*10)-INT(ROUND(A9,2))*10,"[dbnum2]")&"角")&IF((INT(ROUND(A9,2)*100)-INT(ROUND(A9,2)*10)*10)=0,"整",TEXT((INT(ROUND(A9,2)*100)-INT(ROUND(A9,2)*10)*10),"[dbnum2]")&"分")),IF(ROUND(ABS(A9),2)<1,"","负"&TEXT(INT(ROUND(ABS(A9),2)),"[dbnum2]")&"元")&IF(INT(ROUND(ABS(A9),2)*10)-INT(ROUND(ABS(A9),2))*10=0,IF(INT(ROUND(ABS(A9),2))*(INT(ROUND(ABS(A9),2)*100)-INT(ROUND(ABS(A9),2)*10)*10)=0,"","零"),TEXT(INT(ROUND(ABS(A9),2)*10)-INT(ROUND(ABS(A9),2))*10,"[dbnum2]")&"角")&IF((INT(ROUND(ABS(A9),2)*100)-INT(ROUND(ABS(A9),2)*10)*10)=0,"整",TEXT((INT(ROUND(ABS(A9),2)*100)-INT(ROUND(ABS(A9),2)*10)*10),"[dbnum2]")&"分")))
第2个回答 2012-11-23
只要把相应的A1改成你要的格式栏就OK了
=SUBSTITUTE(SUBSTITUTE(TEXT(TRUNC(FIXED(A1)),"[>0][dbnum2];[<0]负[dbnum2];;")&TEXT(RIGHT(FIXED(A1),2),"元[dbnum2]0角0分;;"&IF(ABS(A1)>1%,"元整",)),"零角",IF(ABS(A1)<1,,"零")),"零分","整")
=SUBSTITUTE(SUBSTITUTE(TEXT(TRUNC(FIXED(A1)),"[>0][dbnum2];[<0]负[dbnum2];;")&TEXT(RIGHT(FIXED(A1),2),"元[dbnum2]0角0分;;"&IF(ABS(A1)>1%,"元整",)),"零角",IF(ABS(A1)<1,,"零")),"零分","整")
