最后,从分析人民币金额构成的数值三部分(即①、符号,②、整数部分,③小数部分)的既相对独立又存在关联制约的特征出发,终于得出一个堪称完美的三段式公式(165字):- =TEXT(RMB(A2,2),";负;零元")&TEXT(INT(ABS(A2)+0.5%),"[dbnum2]G/通用格式元;;")&SUBSTITUTE(SUBSTITUTE(TEXT(RIGHT(RMB(A2,2),2),"[dbnum2]0角0分;;整"),"零分","整"),"零角",IF(A2^2<1,,"零"))
复制代码 这个公式不仅完全符合票据填写的有关规定,而且也完全符合人们的语言习惯,与国产软件WPS提供的内置人民币大写结果完全一致,而且在符合前面这两个条件的同时,其字符数也达到了最省。
补充内容 (2014-7-13 11:27):
再精简掉1个字(仅164字):
=TEXT(A2+0.5%,";负;负")&TEXT(INT(ABS(A2)+0.5%),"[dbnum2]G/通用格式元;;零元")&SUBSTITUTE(SUBSTITUTE(TEXT(RIGHT(RMB(A2,2),2),"[dbnum2]0角0分;;整"),"零分","整"),"零角",IF(A2^2
补充内容 (2014-9-26 09:41):
对补充的更正:TEXT(RMB(E3,2),";负")&TEXT(INT(ABS(E3)+0.5%),"[dbnum2]G/通用格式元;;零元")&SUBSTITUTE(SUBSTITUTE(TEXT(RIGHT(RMB(E3,2),2),"[dbnum2]0角0分;;整"),"零分","整"),"零角",IF(E3^2<1,,"零"
补充内容 (2015-4-23 12:42):
再精简:
=IF(A2>-0.5%,"","负")&TEXT(INT(ABS(A2)+0.5%),"[dbnum2]G/通用格式元;;零元")&SUBSTITUTE(SUBSTITUTE(TEXT(RIGHT(RMB(A2,2),2),"[dbnum2]0角0分;;整"),"零分","整"),"零角",IF(A2^2<1,,"零"))
补充内容 (2015-4-23 13:02):
再减少2字符:
=IF(A2>-0.5%,,"负")&TEXT(INT(ABS(A2)+0.5%),"[dbnum2]G/通用格式元;;零元")&SUBSTITUTE(SUBSTITUTE(TEXT(RIGHT(RMB(A2,2),2),"[dbnum2]0角0分;;整"),"零分","整"),"零角",IF(A2^2<1,,"零")) |