Question

如何在Excel中自动生成人民币大写

Answer 1

我们的计划是在表格A列填入阿拉伯小写金额数值，点击回车，B列自动生成中文大写数额。
我们以A2为目标单元格，在B2单元格输入公式：=IF(A2=0,"",IF(A2<0,"负","")&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXT(INT(ABS(A2)),"[DBNum2]")&"元"&TEXT(RIGHT(TEXT(A2,".00"),2),"[DBNum2]0角0分"),"零角零分","整"),"零分","整"),"零角","零"),"零元零",""))

点击回车，就会在B2单元格生成A2单元格的中文大写数额。

Answer 2

比如在A1格是：123
在B1格输入：=
"大写："&IF(AND(INT(ABS(A1)*10)=INT(ABS(A1))*10,RIGHT(DOLLAR(A1),1)<>"0"),TEXT(INT(ABS(A1)),"[dbnum2]")&"元"&TEXT(INT(ABS(A1*10))-INT(ABS(A1))*10,"[dbnum2]")&TEXT(INT(ABS(A1*100))-INT(ABS(A1*10))*10,"[dbnum2]")&"分",IF(AND(INT(ABS(A1)*10)<>INT(ABS(A1))*10,RIGHT(DOLLAR(A1),1)="0"),TEXT(INT(ABS(A1)),"[dbnum2]")&"元"&TEXT(INT(ABS(A1*10))-INT(ABS(A1))*10,"[dbnum2]")&"角整",IF(AND(INT(ABS(A1)*10)=INT(ABS(A1))*10,RIGHT(DOLLAR(A1),1)="0"),TEXT(INT(ABS(A1)),"[dbnum2]")&"元整",TEXT(INT(ABS(A1)),"[dbnum2]")&"元"&TEXT(INT(ABS(A1*10))-INT(ABS(A1))*10,"[dbnum2]")&"角"&TEXT(INT(ABS(A1*100))-INT(ABS(A1*10))*10,"[dbnum2]")&"分")))

那么在B1格自动显示 ：大写：壹佰贰拾叁元整

Answer 3

如果将公式修改一下更好。
修改后：=IF(A1="","",IF(A1<0,"负","")&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXT(INT(ABS(A1)),"[DBNum2]")&"元"&TEXT(RIGHT(TEXT(A1,".00"),2),"[DBNum2]0角0分"),"零角零分","整"),"零分","整"),"零角","零"),"零元零",""))
即A1为空时，不显示。因为是显示金额大写，即使金额为0时，也显示零元整。

Answer 4

假设数据在A1单元格，公式：
=TEXT(INT(A1),"[dbnum2]")&"元"&IF(INT(A1*10)-INT(A1)*10=0,"",TEXT(INT(A1*10)-INT(A1)*10,"[dbnum2]")&"角")&IF(INT(A1*100)-INT(A1*10)*10=0,"整",IF(INT(A1*10)-INT(A1)*10=0,"零"&TEXT(INT(A1*100)-INT(A1*10)*10,"[dbnum2]")&"分",TEXT(INT(A1*100)-INT(A1*10)*10,"[dbnum2]")&"分"))

Answer 5

如果只是大写EXCEL单元格格式中就有提供，但人民币大写要用函数公式或VBA。
发票或支票等人民币大写公式如下：（J15为小写数字所在单元格，自己根据需要替换）
=SUBSTITUTE(SUBSTITUTE(IF(J15>-0.5%,,"负")&TEXT(INT(FIXED(ABS(J15))),"[dbnum2]G/通用格式元;;")&IF(AND(INT(J15)<>0,INT(J15/10)=INT(J15)/10,INT(J15*10)<>INT(J15)*10),"零","")&TEXT(RIGHT(FIXED(J15),2),"[dbnum2]0角0分;;"&IF(ABS(J15)>1%,"整",)),"零角",IF(ABS(J15)<1,,"零")),"零分","整")