[已解决]关于单元格设置的问题

jwh0706

A2单元格显示为1.52元,想在B2单元格显示壹元伍角贰分,应该怎么设置啊?请高手们指点一下.

最佳答案

月排行榜 / 总排行榜

ws278106483

2012-6-8 10:49

=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]")&"分")))

公式

跳转到最佳答案楼层

ws278106483

=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]")&"分")))

公式

ws278106483

楼主记得回愎哦。呵呵，觉得好，给个最佳哦。呵呵……

jwh0706

怎么复杂?看到晕了.不过刚去试用了下,还是很好用的,谢谢啦

rmsc_kr

以下的公式是以A1为目标单元格输入对象，公式粘贴就可以使用：

=CONCATENATE(TEXT(INT(A1),"[DBNum2][$-804]G/通用格式")&"元"&IF((INT(A1*10)-INT(A1)*10)=0,"",TEXT(IF(AND(CEILING(A1*100,1)-INT(A1*10)*10=10,INT(A1*1000)-INT(A1*100)*10>=5),INT(A1*10)-INT(A1)*10+1,INT(A1*10)-INT(A1)*10),"[DBNum2][$-804]G/通用格式")&"角")&IF(OR(INT(A1*100)-INT(A1*10)*10=0,(IF(INT(A1*1000)-INT(A1*100)*10>=5,CEILING(A1*100,1)-INT(A1*10)*10=10,FALSE))),"整",(IF(INT(A1*1000)-INT(A1*100)*10>=5,(IF(CEILING(A1*100,1)-INT(A1*10)*10=10,"",(TEXT(CEILING(A1*100,1)-INT(A1*10)*10,"[DBNum2][$-804]G/通用格式")&"分"))),(TEXT(INT(A1*100)-INT(A1*10)*10,"[DBNum2][$-804]G/通用格式")&"分")))))

ligh1298

1. =SUBSTITUTE(TEXT(TRUNC(A1),"[dbnum2]")&"元"&SUBSTITUTE(TEXT(MOD(A1,1)*10,"0角[dbnum2]"),"零角","零")&SUBSTITUTE(TEXT(MOD(A1,0.1)*100,"0分[dbnum2]"),"零分","整"),"零整","整")

复制代码

这个短点。不过，在A1单元格不要带单位“元”

jwh0706

ligh1298 发表于 2012-6-8 11:07
这个短点。不过，在A1单元格不要带单位“元”

好像单元格为循环小数,显示不对,提前进位了.