个人随笔和一些网文摘录
http://www.dlxp.cn

EXCEL中转换人民币大写代码

Pop:合同等正式文书中,人民币是必须要大写的,在EXCEL中人民币大写主要是用代码来搞定的,具体实例如下:

XXXX报价单 人民币大写XXXXXX

样式一: 肆拾万叁仟零肆拾柒元伍角壹分
=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,"","整")),"零元零",""),"零元","")
样式二:人民币大写 :肆拾万叁仟零肆拾柒元伍角壹分
=IF(ROUND(A1,2)<0,"金额不能为负",IF(ROUND(A1,2)=0,"零",IF(ROUND(A1,2)<1,"人民币大写:",TEXT(INT(ROUND(A1,2)),"[dbnum2]人民币大写:G/通用格式")&"元")&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]")&"分")))

//注:人民币大写都是直接读取表格的数据。 两种代码都可以用,随便选一种即可。 如果有变动的话,直接把代码复制在记事本中全部替换,如把A1替换成D13

原文地址:http://blog.sina.com.cn/s/blog_521a5c7d0100a9pb.html

    http://429006.com/article/technology/3276.htm

未经允许不得转载:Leejoa's 生活随笔 » EXCEL中转换人民币大写代码
分享到: 更多 (0)

评论 抢沙发

评论前必须登录!