首页 EXCEL中金额小写转大写

EXCEL中金额小写转大写

举报
开通vip

EXCEL中金额小写转大写EXCEL中金额小写转大写 网络收集 2007-10-06 EXCEL中金额小写转大写 网络收集 2007-10-06 到现在为止EXCEL中自带的金额小写转大写功能都不能如我们中国人的习惯,曾有朋友问过我如何做到人性化点,还真惭愧,我的EXCEL办公方面的软件使用很差,我相信会有公式可以完成,于是上面到处找,但当时都没有找到,只好作罢!今天又再有朋友问起,我决定要找到一个为止.没有想到竟真的被我找了,以下就是我本人从网络上收集到的,因为不知作者为何人,在此只能为这些默默奉献的朋友说声谢谢! 在网上找到有二...

EXCEL中金额小写转大写
EXCEL中金额小写转大写 网络收集 2007-10-06 EXCEL中金额小写转大写 网络收集 2007-10-06 到现在为止EXCEL中自带的金额小写转大写功能都不能如我们中国人的习惯,曾有朋友问过我如何做到人性化点,还真惭愧,我的EXCEL办公方面的软件使用很差,我相信会有 公式 小学单位换算公式大全免费下载公式下载行测公式大全下载excel公式下载逻辑回归公式下载 可以完成,于是上面到处找,但当时都没有找到,只好作罢!今天又再有朋友问起,我决定要找到一个为止.没有想到竟真的被我找了,以下就是我本人从网络上收集到的,因为不知作者为何人,在此只能为这些默默奉献的朋友说声谢谢! 在网上找到有二篇,功能相对完善的是第二篇.现在一一转贴过来! ===============================第一篇================================== 要将人民币小写金额转换成大写 格式 pdf格式笔记格式下载页码格式下载公文格式下载简报格式下载 ,用Excel提供的格式,将自定义格式类型中的“[dbnum2]G/通用格式”改为“[dbnum2] G/通用格式“元””来实现。但在转换小数时却出现了问题,比如¥6,050.09只能转换为“陆仟零伍拾.零玖圆”。那么用Excel能不能解决这一先天不足呢?其方法有以下两种(以下均对B1转换,假设B1是通过函数ROUND( )四舍五入取得二位小数的小写金额数,其值为¥6,050.09)。 一、在三个连续的同行单元格中转换 1. B2中输入公式 “=IF(B1〈0,"金额为负无效",INT(B1))”,计算结果为¥6,050.00,然后点击“格式→单元格→数字→特殊→中文大写数字→确定”,B2显示“陆仟零伍拾”,再点击“格式→单元格→数字→自定义”,将“类型”编辑框中的“[dbnum2]G/通用格式”修改为:[dbnum2](“人民币”)G/通用格式“元”,此时B2显示:“(人民币)陆仟零伍拾元”。 2. C2中输入“=IF(B1〈0,"",INT(B1 10)-INT(B1) 10)”,同样C2改为:[DBNum2]G/通用格式“角”,C2显示:“零角”。 3. D2中输入“=IF(B1〈0,"",INT(B1 100)-INT(B1 10) 10)”,同样D2改为:[DBNum2]G/通用格式“分”, D2显示:“玖分”。 这样在连续的三个同行单元格中实现了大写金额格式的转换,如图1所示。为了使转换符合财务格式,B2应右对齐,C2列宽无间隔,且不设左右边框线,D2要左对齐。 HYPERLINK "http://img.blog.163.com/photo/3du9Bfn3JK1Jqpj388ZC9Q==/2052797005151118454.jpg" \t "_blank" 图1 二、用Excel提供的函数转换 个位为0,则不显示;不为0,显示“XXX元”;不为0、且角位和分位均为0,显示“XXX元整”;元角分位均为0,则显示“零元整”。 小数点后面第一位为0、且元位或分位有一为0,则不显示;为0、且元位和分位均不为0,则显示“零”;不为0,显示“X角”。 小数点后面第二位为0,则显示“整”;不为0,显示“X分”。 根据以上思路在B2中输入公式: “IF(B1〈0,"金额为负无效",IF(OR(B1=0,B1=""),"(人民币)零元整")); IF(B1〈1,"(人民币)",TEXT(INT(B1),"[dbnum2](人民币)G/通用格式")&&"元")&&IF(INT(B1 10)-INT(B1) 10=0); IF(INT(B1) (INT(B1 100)-INT(B1 10) 10)=0,"","零"),TEXT(INT(B1 10)-INT(B1) 10,"[dbnum2]")&&"角")&&IF((INT(B1 100)-INT(B1 10) 10)=0,"整",TEXT((INT(B1 100)-INT(B1*10)*10),"[dbnum2]")&&"分")))”。 确认后B2显示:“(人民币)陆仟零伍拾元零玖分”。以后使用时将公式复制到有关单元格中,若转换的对象B1发生了变化,可点击“编辑→替换”将B1替换掉。两种转换测试结果如图2所示。 图2 ===============================第一篇(完)=============================== 第一篇我照做时,我发现第二种方法,我弄不出来,可能是我弄错,不过不理它,因为我采用了第二篇的相当方便 ===============================第二篇(推荐)============================= 近来转了转几个EXCEL相关论坛,发现有部分网友在寻求数字金额转换为人民币大写的方法。出于好奇,我在网上搜索了一番,什么VBA、加载宏,公式函数各式方法争相亮相,应有尽有。但我的习惯是,只要能用公式解决的问题,坚决不使用VBA。所以我就特别地关注使用公式来生成的方法,结果是用公式的方法可谓是更加地灿烂缤纷,使人眼花缭乱。 通过分析我收集到的二十几个公式,发现比较牛,适合我口味的公式有三个,在此我将其列出。 公式一: 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,"","整")),"零元零",""),"零元","") 公式二: CONCATENATE(IF(A1<0,"负",""),TEXT(IF(TRUNC(A1)=0,"",TRUNC(ABS(A1))),"[DBNum2]"),IF(INT(TRUNC(A1))=0,"","元"),TEXT(IF(OR(ABS(A1)<0.1,TRUNC(A1)=A1),"",RIGHT(TRUNC(A1*10),1)),"[DBNum2]"),IF(RIGHT(TRUNC(A1*10),1)="0","","角"),TEXT(IF(RIGHT(TRUNC(A1*100),1)="0","",RIGHT(TRUNC(A1*100),1)),"[DBNum2]"),IF(RIGHT(TRUNC(A1*100),1)="0","","分")) 公式三: IF(ROUND(A1,2)=0,"",IF(ROUND(ABS(A1),2)>=1,TEXT(INT(ROUND(ABS(A1),2)),"[DBNum2]")&"元","")&IF(RIGHT(TEXT(A1,".00"),2)*1=0,"整",IF(RIGHT(TEXT(A1,".00"),4)*1>=1,IF(RIGHT(TEXT(A1,".00"),2)*1>9,"","零"),IF(ROUND(ABS(A1),2)>=1,"零",""))&IF(RIGHT(TEXT(A1,".00"),2)*1>9,TEXT(LEFT(RIGHT(TEXT(A1,".00"),2)),"[DBNum2]")&"角","")&IF(RIGHT(TEXT(A1,".00"))*1>0,TEXT(RIGHT(TEXT(A1,".00")),"[DBNum2]")&"分","整"))) 我用不同的数值对这三个公式进行了一番测试,都达到了我的要求,至少到目前为止还没有发现上述公式存在着什么错误。上述公式对负数的处理采取了不同的方法。公式一和公式二对负数是在其前部加上一个“负”字,而公式三则是按正数进行处理。我比较推崇公式三的方式,不是还有条件格式可以设置吗?若是负数就用条件格式来变为红色。 我不准备在此对上述公式进行分析解说。撰写本文的目的是我也想自己来“组装”一个所谓的通用公式。我只所以说是“组装”而不是叫什么“开发”或者是“拼凑”,是因为EXCEL的函数现成地摆在了那里,使用时就是为了达到某种目的,按照其固有的规则,将其进行有机的组合,可谓“组装”。但是在这个过程中,也是要开动脑筋的,并非是随随便便“拼凑”就能成功的。本文的目的就是想将我在“组装”公式时的思路展现出来。 若用程序 设计 领导形象设计圆作业设计ao工艺污水处理厂设计附属工程施工组织设计清扫机器人结构设计 语言(比如用C语言)来写一个人民币数字金额转大写金额会怎样考虑呢?我想每一位数字都要考虑到。但在EXCEL中,单元格格式中有一个“特殊”格式,里面就有将小写数字转换为大写数字的格式。但要注意的是,这里转换的是数字而不是金额。所以转换出来后并不存在“元”、“角”、“分”、“整”等在大写金额中出现的字。但是,“元”只出现在整数部分的后面,而小数部分只有角分两位。这样只要利用EXCEL的“特殊”格式将数据分为整数部分、角位和分位三个部分来处理就行了。处理的顺序是先整数,再角位、再分位。 由于要分三个部分分别处理,然后根据情况分别与“元”,“角”,“分”及“整”相连接。连接的方式可以使用CONCATENATE()函数,如公式二,也可以使用连接符&。而用&来连接,公式结构似乎会相对简单清晰一些,所以我选择了用&来连接。 虽然对问题进行了分解,但需要考虑的细节相当多。 若单元格为0或者空,输出空,避免出现“零”、“零元”等字样。对非数字字符不进行处理,使用EXCEL自身的错误提示。 对负数按正数处理,再用条件格式将其转换为红色,不拟采用“负XXXXXX”的形式。 整数加“元”,若是纯整数,就只处理整数且加“整”,不再处理小数部分。若是纯小数,放在小数部分处理,不单独列出处理。角为0且分不为0,应输出“零”,只有角的就加“整” 如果有尾数,即有3位以上小数(这种情况也许很少发生,但作为一个“通用”公式,却不能不考虑这种情况)。则有两种处理方式,一是截尾,另一则是四舍五入。由于两种方式的排它性。决定做两个公式。 如果数值小于分,截尾公式输出空,而四舍五入公式则要考虑舍入问题。若有舍入输出分,没有则输出空。 根据这个思路,我“组装”了下面两个公式。 公式四: IF(A1=0,"",IF(ABS(A1)<0.995,"",TEXT(INT(ROUND(ABS(A1),2)),"[DBNum2]")&"元")&IF(RIGHT(TEXT(A1,".00"),2)*1=0,IF(ABS(A1)<0.005,"","整"),TEXT(IF(ABS(A1)<0.095,"",LEFT(RIGHT(TEXT(A1,".00"),2))),"[dbnum2]")&IF(LEFT(RIGHT(TEXT(A1,".00"),2))*1=0,"","角")&IF(RIGHT(TEXT(A1,".00"))*1=0,"整",TEXT(RIGHT(TEXT(A1,".00")),"[dbnum2]")&"分"))) 公式五: IF(A1=0,"",IF(ABS(A1)<1,"",TEXT(TRUNC(ABS(A1)),"[DBNum2]")&"元")&IF(RIGHT(TRUNC(A1*100),2)*1=0,IF(ABS(A1)<0.01,"","整"),IF(ABS(A1)<0.1,"",TEXT(RIGHT(TRUNC(A1*10)),"[dbnum2]"))&IF(RIGHT(TRUNC(A1*10))*1=0,"","角")&IF(RIGHT(TRUNC(A1*100))*1=0,"整",TEXT(RIGHT(TRUNC(A1*100)),"[dbnum2]")&"分"))) 公式四是四舍五入公式,公式五则是截尾公式。两个公式的结构其实是一样的,只是使用了不同的函数。下面用公式四来说明。 TEXT(INT(ROUND(ABS(A1),2)),"[DBNum2]")&"元"用来处理整数部分,考虑到纯小数及舍入问题,增加一个判断ABS(A1)<0.995,用0.995,即保证了正常的舍入,又避免了出现0.9945也进行舍入的错误。 用RIGHTB(TEXT(A1,".00"),2)*1=0来判断是不是纯整数,是就输出“整”,后面就不用处理了。因为TEXT函数输出的是文本值,所以在这里有两种处理方法,一是就是本式,二是RIGHTB(TEXT(A1,".00"),2)="00"。 LEFT(RIGHT(TEXT(A1,".00"),2))是取出角位数,增加一个判断ABS(A1)<0.095是为了在只有分票的情况下角位避免出现“零”的字样,0.095也是考虑了尾数的舍入问题。 IF(LEFT(RIGHT(TEXT(A1,".00"),2))*1=0,"","角"),角位是0输出空,否则输出“角”。同样,这里的判断表达式也可改为LEFT(RIGHT(TEXT(A1,".00"),2))="0 " 用RIGHT(TEXT(A1,".00"))取出分位数,分位为零输出“整”,否则输出分位数。 对于负数,如果一定要用“负XXXXXX”的形式,则直接在第二个判断前加上“IF(A1<0,"负","")&”。 还有网友提出来说万位为零时不输出零不符合规范,为此我专门去查了一下相关规定。结果如下: 大写金额中间有“0”时,大写金额要写“零”字;小写金额中间连续有几个“0”时,大写金额中间可以只写一个“零”字;小写金额万位或元位是“ 0”,或者数字中间连续有几个“0”,万位、元位也是“0”,但千位、角位不是“0”时,大写金额中可以只写一个“零”字,也可以不写“零”字。 所以上述公式在当万位为零而千位不为零时,以不输出零的方式处理,应该是符合规范的。也许正是EXCEL采取这种样式的原因。当然,如果遵照习惯非要加上这个零字也不是不可能。只不过要增加一点公式复杂度罢了。 在一个数中,万会出现几次?不管是万、十万、百万、千万,这个万就出现一次,如果是万万则是亿,如果是万亿又是兆,万兆呢?已经超出EXCEL的处理能力了。所以答案是万字在大写金额中只可能出现一次,且只出现在整数部分。即然是这样,用SUBSTITUTE()函数就能解决问题。即判断当万位为零而千位不为零时,用“万零”来替换“万”,其它情况下不进行替换。因为EXCEL只有在这种情况下不会输出“零”。 在四舍五入公式中,因为还要考虑尾数舍入问题,判断使用了四舍五入函数ROUND(),公式四的输出整数的部分公式改为: IF(AND(RIGHT(INT(ROUND(ABS(A1),2)/10000))="0",RIGHT(INT(ROUND(ABS(A1),2)/1000))<>"0"),SUBSTITUTE(TEXT(INT(ROUND(ABS(A1),2)),"[DBNum2]"),"万","万零"),TEXT(INT(ROUND(ABS(A1),2)),"[DBNum2]")) 而截断公式不用考虑尾数舍入问题,直接用TRUNC()取万位和千位数来判断。相应地公式五的整数部分亦改为: IF(AND(RIGHT(TRUNC(ABS(A1)/10000))="0",RIGHT(TRUNC(ABS(A1)/1000))<>"0"),SUBSTITUTE(TEXT(TRUNC(ABS(A1)),"[DBNum2]"),"万","万零"),TEXT(TRUNC(ABS(A1)),"[DBNum2]")) 为了一个我认为不是很必要的“零”字使公式臃肿了许多,而我喜欢简单清晰的公式,所以不愿在正式公式中采用。 自认为这两个公式已经考虑了许多的细节,但我也不敢保证没有挂一漏万。现在暂时告一段落,若以后发现问题再来修改了。 ===============================第二篇(完)=============================== 这就是我弄出来的,当然第二篇公式有点长,对于我来说过于复杂,希望能有人创新更实用,更简单的方法!
本文档为【EXCEL中金额小写转大写】,请使用软件OFFICE或WPS软件打开。作品中的文字与图均可以修改和编辑, 图片更改请在作品中右键图片并更换,文字修改请直接点击文字进行修改,也可以新增和删除文档中的内容。
该文档来自用户分享,如有侵权行为请发邮件ishare@vip.sina.com联系网站客服,我们会及时删除。
[版权声明] 本站所有资料为用户分享产生,若发现您的权利被侵害,请联系客服邮件isharekefu@iask.cn,我们尽快处理。
本作品所展示的图片、画像、字体、音乐的版权可能需版权方额外授权,请谨慎使用。
网站提供的党政主题相关内容(国旗、国徽、党徽..)目的在于配合国家政策宣传,仅限个人学习分享使用,禁止用于任何广告和商用目的。
下载需要: 免费 已有0 人下载
最新资料
资料动态
专题动态
is_910942
暂无简介~
格式:doc
大小:156KB
软件:Word
页数:5
分类:
上传时间:2010-11-20
浏览量:51