Question

怎么用Excel将小写金额转换成人民币的大写金额?

怎么用Excel将小写金额转换成人民币的大写金额?
比如我在AV框中写123.45 AF框,就会出现:壹佰贰拾叁元肆角伍分!!!
财务上用的,很急.谢谢!!

Answer 1

假设A1中为数值123.45, 在B1中输入公式=SUBSTITUTE(SUBSTITUTE(IF(A1<0,"负","")&TEXT(TRUNC(ABS(ROUND(A1,2))),"[DBNum2]")&"元"&IF(ISERR(FIND(".",ROUND(A1,2))),"",TEXT(RIGHT(TRUNC(ROUND(A1,2)*10)),"[DBNum2]"))&IF(ISERR(FIND(".0",TEXT(A1,"0.00"))),"角","")&IF(LEFT(RIGHT(ROUND(A1,2),3))=".",TEXT(RIGHT(ROUND(A1,2)),"[DBNum2]")&"分",IF(ROUND(A1,2)=0,"","整")),"零元零",""),"零元","")

Answer 2

假如要想在F3栏得到F2栏的大写金额只要在F3栏的编辑框内输入下面的函数公式就可以了
=IF(F2<0,"金额为负无效",IF(OR(F2=0,F2=""),"零元整",IF(F2<1,"",TEXT(INT(F2),"[DBNum2]G/通用格式")&"元")&IF(INT(F2*10)-INT(F2)*10=0,IF(INT(F2)*(INT(F2*100)-INT(F2*10)*10)=0,"","零"),TEXT(INT(F2*10)-INT(F2)*10,"[DBNum2]G/通用格式")&"角")&IF((INT(F2*100)-INT(F2*10)*10)=0,"零分",TEXT((INT(F2*100)-INT(F2*10)*10),"[DBNum2]G/通用格式")&"分")))

Answer 3

单元格格式--》数字-->特殊--》人民币大写