关于数值金额大写在EXCEL 表中不随金额小写变化的公式编写

来源：百度知道 编辑：UC知道 时间：2024/05/17 19:17:21
=IF(P17=0,""&IF(P17=0,"",IF((P17-ROUND(P17,0))=0,(TEXT(INT(P17),"[DBnum2]")&"元整"),(TEXT(INT(P17),"[DBnum2]")&"元")&IF((RIGHT(P17,2)-RIGHT(P17,1))=0,"零",TEXT(ROUND((INT(((P17-INT(P17))*100)-RIGHT(P17,1))/10),0),"[dbnum2]")&"角")&IF((P17*10-INT(P17*10))=0,"",TEXT(ROUND(((P17*10-INT(P17*10))*10),0),"[dbnum2]")&"分")&"整")),""&IF(P17=0,"",IF((P17-ROUND(P17,0))=0,(TEXT(INT(P17),"[DBnum2]")&"元整"),(TEXT(INT(P17),"[DBnum2]")&"元")&IF((RIGHT(P17,2)-RIGHT(P17,1))=0,"零",TEXT(ROUND((INT(((P17-INT(P17))*100)-RIGHT(P17,1))/10),0),"[dbnum2]")&"角")&IF((P17*10-INT(P17*10))=0,"",TEXT(ROUND(((P17*10-INT(P17*10))*10),0),"[dbnum2]")&"分"))))

以上是在excel表中，把小写金额，变成大写金额的公式，但是在使用时出现错误，如小写金额是307.60元，大写金额显示叁佰零柒元伍角，显然不对。
又如：52.07元，显示大写确是：伍拾贰元壹角柒分。

=IF(ISNUMBER(A1)=FALSE,"输入非数字值",IF(A1>=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(ABS(A1),2)<1,"","负"&TEXT(INT(ROUND(ABS(A1),2)),"[dbnum2]")&"元")&IF(INT(ROUND(ABS(A1),2)*10)-INT(ROUND(ABS(A1),2))*10=0,IF(INT(ROUND(ABS(A1),2))*(INT(ROUND(ABS(A1),2)*100)-INT(ROUND(ABS(A1),2)*10)*10)=0,"","零"),TEXT(INT(ROUND(ABS(A1),2)*10)-INT(ROUND(ABS(A1),2))*10,"[dbnum2]")&"角")&IF((INT(ROUND(ABS(A1),2)*100)-INT(ROUND(ABS(A1),2)*10)*10)=0,"整&