Question

如何让excel 自动变化金额大小写

经常用excel做预算，想让它自动实现数值转换为大写的金额（ 万 佰），如何实现

Answer 1

假定小写金额在a1,在需要大写金额单元格就输入下面公式即可。
="大写金额："&IF((A1-INT(A1))=0,TEXT(A1,"[DBNUM2]")&"元整",IF(INT(A1*10)-A1*10=0,TEXT(INT(A1),"[DBNUM2]")&"元"&TEXT((INT(A1*10)-INT(A1)*10),"[DBNUM2]")&"角整",TEXT(INT(A1),"[DBNUM2]")&"元"&IF(INT(A1*10)-INT(A1)*10=0,"零",TEXT(INT(A1*10)-INT(A1)*10,"[DBNUM2]")&"角")&TEXT(RIGHT(A1,1),"[DBNUM2]")&"分"))

Answer 2

=SUBSTITUTE(SUBSTITUTE(IF(A2>-0.5%,,"负")&TEXT(INT(ABS(A2)+0.5%),"[dbnum2]G/通用格式元;;")&TEXT(RIGHT(FIXED(A2),2),"[dbnum2]0角0分;;"&IF(ABS(A2)
>1%,"整",)),"零角",IF(ABS(A2)<1,,"零")),"零分","整")
这个对角分也适用

Answer 3

使用公式
=SUBSTITUTE(SUBSTITUTE(IF(A1>-0.5%,,"负")&TEXT(INT(FIXED(ABS(A1))),"[dbnum2]G/通用格式元;;")&TEXT(RIGHT(FIXED(A1),2),"[dbnum2]0角0分;;"&IF(ABS(A1)>1%,"整",)),"零角",IF(ABS(A1)<1,,"零")),"零分","整")

Answer 4

如果你在a1单元格里输入小写的数字想在b1单元格里显示大写的就用这个公式
=substitute(substitute(substitute(if(a1<0,"负",)&text(int(abs(a1)),"[dbnum2];;
")&text(mod(abs(a1)*100,100),"[>9][dbnum2]圆0角0分;[=0]圆整;[dbnum2]圆零0分"),"零分","整"),"
圆零",),"
圆",)