EXCEL,数字变大写公式

-.z.近来转了转几个E*CEL相关论坛，发现有局部网友在寻求数字金额转换为人民币大写的方法。出于好奇，我在网上搜索了一番，什么VBA、加载宏，公式函数各式方法争相亮相，应有尽有。但我的习惯是，只要能用公式解决的问题，坚决不使用VBA。所以我就特别地关注使用公式来生成的方法，结果是用公式的方法可谓是更加地灿烂缤纷，使人眼花缭乱。通过分析我收集到的二十几个公式，发现比拟牛，适合我口味的公式有三个，在此我将其列出。公式一：

SUBSTITUTE(SUBSTITUTE(IF(A1<0,"负","")&TE*T(TRUNC(ABS(ROUND(A1,2))),"[DBNum2]")&"元"&IF(ISERR(FIND(".",ROUND(A1,2))),"",TE*T(RIGHT(TRUNC(ROUND(A1,2)*10)),"[DBNum2]"))&IF(ISERR(FIND(".0",TE*T(A1,"0.00"))),"角","")&IF(LEFT(RIGHT(ROUND(A1,2),3))=".",TE*T(RIGHT(ROUND(A1,2)),"[DBNum2]")&"分",IF(ROUND(A1,2)=0,"","整")),"零元零",""),"零元","")公式二：

CONCATENATE(IF(A1<0,"负",""),TE*T(IF(TRUNC(A1)=0,"",TRUNC(ABS(A1))),"[DBNum2]"),IF(INT(TRUNC(A1))=0,"","元"),TE*T(IF(OR(ABS(A1)<0.1,TRUNC(A1)=A1),"",RIGHT(TRUNC(A1*10),1)),"[DBNum2]"),IF(RIGHT(TRUNC(A1*10),1)="0","","角"),TE*T(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,TE*T(INT(ROUND(ABS(A1),2)),"[DBNum2]")&"元","")&IF(RIGHT(TE*T(A1,".00"),2)*1=0,"整",IF(RIGHT(TE*T(A1,".00"),4)*1>=1,IF(RIGHT(TE*T(A1,".00"),2)*1>9,"","零"),IF(ROUND(ABS(A1),2)>=1,"零",""))&IF(RIGHT(TE*T(A1,".00"),2)*1>9,TE*T(LEFT(RIGHT(TE*T(A1,".00"),2)),"[DBNum2]")&"角","")&IF(RIGHT(TE*T(A1,".00"))*1>0,TE*T(RIGHT(TE*T(A1,".00")),"[DBNum2]")&"分","整")))我用不同的数值对这三个公式进展了一番测试，都到达了我的要求，至少到目前为止还没有发现上述公式存在着什么错误。上述公式对负数的处理采取了不同的方法。公式一和公式二对负数是在其前部加上一个"负〞字，而公式三则是按正数进展处理。我比拟推崇公式三的方式，不是还有条件格式可以设置吗？假设是负数就用条件格式来变为红色。我不准备在此对上述公式进展分析讲解。撰写本文的目的是我也想自己来"组装〞一个所谓的通用公式。我只所以说是"组装〞而不是叫什么"开发〞或者是"拼凑〞，是因为E*CEL的函数现成地摆在了那里，使用时就是为了到达*种目的，按照其固有的规则，将其进展有机的组合，可谓"组装〞。但是在这个过程中，也是要开动脑筋的，并非是随随便便"拼凑〞就能成功的。本文的目的就是想将我在"组装〞公式时的思路展现出来。假设用程序设计语言〔比方用C语言〕来写一个人民币数字金额转大写金额会怎样考虑呢？我想每一位数字都要考虑到。但在E*CEL中，单元格格式中有一个"特殊〞格式，里面就有将小写数字转换为大写数字的格式。但要注意的是，这里转换的是数字而不是金额。所以转换出来后并不存在"元〞、"角〞、"分〞、"整〞等在大写金额中出现的字。但是，"元〞只出现在整数局部的后面，而小数局部只有角分两位。这样只要利用E*CEL的"特殊〞格式将数据分为整数局部、角位和分位三个局部来处理就行了。处理的顺序是先整数，再角位、再分位。由于要分三个局部分别处理，然后根据情况分别与"元〞，"角〞，"分〞及"整〞相连接。连接的方式可以使用CONCATENATE()函数，如公式二，也可以使用连接符＆。而用＆来连接，公式构造似乎会相对简单清晰一些，所以我选择了用＆来连接。虽然对问题进展了分解，但需要考虑的细节相当多。假设单元格为0或者空，输出空，防止出现"零〞、"零元〞等字样。对非数字字符不进展处理，使用E*CEL自身的错误提示。对负数按正数处理，再用条件格式将其转换为红色，不拟采用"负******〞的形式。整数加"元〞，假设是纯整数，就只处理整数且加"整〞，不再处理小数局部。假设是纯小数，放在小数局部处理，不单独列出处理。角为0且分不为0，应输出"零〞，只有角的就加"整〞如果有尾数，即有3位以上小数〔这种情况也许很少发生，但作为一个"通用〞公式，却不能不考虑这种情况〕。则有两种处理方式，一是截尾，另一则是四舍五入。由于两种方式的排它性。决定做两个公式。如果数值小于分，截尾公式输出空，而四舍五入公式则要考虑舍入问题。假设有舍入输出分，没有则输出空。根据这个思路，我"组装〞了下面两个公式。公式四：

IF(A1=0,"",IF(ABS(A1)<0.995,"",TE*T(INT(ROUND(ABS(A1),2)),"[DBNum2]")&"元")&IF(RIGHT(TE*T(A1,".00"),2)*1=0,IF(ABS(A1)<0.005,"","整"),TE*T(IF(ABS(A1)<0.095,"",LEFT(RIGHT(TE*T(A1,".00"),2))),"[dbnum2]")&IF(LEFT(RIGHT(TE*T(A1,".00"),2))*1=0,"","角")&IF(RIGHT(TE*T(A1,".00"))*1=0,"整",TE*T(RIGHT(TE*T(A1,".00")),"[dbnum2]")&"分")))公式五：

IF(A1=0,"",IF(ABS(A1)<1,"",TE*T(TRUNC(ABS(A1)),"[DBNum2]")&"元")&IF(RIGHT(TRUNC(A1*100),2)*1=0,IF(ABS(A1)<0.01,"","整"),IF(ABS(A1)<0.1,"",TE*T(RIGHT(TRUNC(A1*10)),"[dbnum2]"))&IF(RIGHT(TRUNC(A1*10))*1=0,"","角")&IF(RIGHT(TRUNC(A1*100))*1=0,"整",TE*T(RIGHT(TRUNC(A1*100)),"[dbnum2]")&"分")))公式四是四舍五入公式，公式五则是截尾公式。两个公式的构造其实是一样的，只是使用了不同的函数。下面用公式四来说明。TE*T(INT(ROUND(ABS(A1),2)),"[DBNum2]")&"元"用来处理整数局部，考虑到纯小数及舍入问题，增加一个判断ABS(A1)<0.995，用0.995，即保证了正常的舍入，又防止了出现0.9945也进展舍入的错误。用RIGHTB(TE*T(A1,".00"),2)*1=0来判断是不是纯整数，是就输出"整〞，后面就不用处理了。因为TE*T函数输出的是文本值，所以在这里有两种处理方法，一是就是本式，二是RIGHTB(TE*T(A1,".00"),2)="00"。LEFT(RIGHT(TE*T(A1,".00"),2))是取出角位数，增加一个判断ABS(A1)<0.095是为了在只有分票的情况下角位防止出现"零〞的字样，0.095也是考虑了尾数的舍入问题。IF(LEFT(RIGHT(TE*T(A1,".00"),2))*1=0,"","角")，角位是0输出空，否则输出"角〞。同样，这里的判断表达式也可改为LEFT(RIGHT(TE*T(A1,".00"),2))="0

"

用RIGHT(TE*T(A1,".00"))取出分位数，分位为零输出"整〞，否则输出分位数。对于负数，如果一定要用"负******〞的形式，则直接在第二个判断前加上"IF(A1<0,"负","")&〞。还有网友提出来说万位为零时不输出零不符合标准，为此我专门去查了一下相关规定。结果如下：大写金额中间有"0〞时，大写金额要写"零〞字；小写金额中间连续有几个"0〞时，大写金额中间可以只写一个"零〞字；小写金额万位或元位是"0〞，或者数字中间连续有几个"0〞，万位、元位也是"0〞，但千位、角位不是"0〞时，大写金额中可以只写一个"零〞字，也可以不写"零〞字。所以上述公式在当万位为零而千位不为零时，以不输出零的方式处理，应该是符合标准的。也许正是E*CEL采取这种样式的原因。当然，如果遵照习惯非要加上这个零字也不是不可能。只不过要增加一点公式复杂度罢了。在一个数中，万会出现几次？不管是万、十万、百万、千万，这个万就出现一次，如果是万万则是亿，如果是万亿又是兆，万兆呢？已经超出E*CEL的处理能力了。所以答案是万字在大写金额中只可能出现一次，且只出现在整数局部。即然是这样，用SUBSTITUTE()函数就能解决问题。即判断当万位为零而千位不为零时，用"万零〞来替换"万〞，其它情况下不进展替换。因为E*CEL只有在这种情况下不会输出"零〞。在四舍五入公式中，因为还要考虑尾数舍入问题，判断使用了四舍五入函数ROUND()，公式四的输出整数的局部公式改为：IF(AND(RIGHT(INT(ROUND(ABS(A1),2)/10000))="0",RIGHT(INT(ROUND(ABS(A1),2)/1000))<>"0"),SUBSTITUTE(TE*T(INT(ROUND(ABS(A1),2)),"[DBNum2]"),"万","万零"),TE*T(INT(ROUND(ABS(A1),2)),"[DBNum2]"))而截断公式不用考虑尾数舍入问题，直接用TRUNC()取万位和千位数来判断。相应地公式五的整数局部亦改为：IF(AND(RIGHT(TRUNC(ABS(A1)/1000