EXCEL中人民币大写的代码

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

珠海XXXX报价单 人民币大写样表

单价 数量 小计
1#箱体 839.87 5 4199.35
元件 621.33 5 3106.65
辅材 533.62 5 2668.1
2#箱体 9999.3 3 29997.9
元件 18996 3 56988
辅材 99943 3 299829
3#箱体 108.28 9 974.52
元件 398.11 9 3582.99
辅材 189 9 1701
合计 403047.51

样式一:人民币大写 肆拾万叁仟零肆拾柒元伍角壹分

=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

This entry was posted in Technology and tagged , , . Bookmark the permalink. 3,752 views

Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>