|
本帖最后由 lunkuo 于 2011-8-19 20:25 编辑
EXCEL表格的数字转换成人民币金额大写,只有五个汉字"负元角整分"且只出现一次的最短公式只有225个字符,只有六个汉字"负元角零整分"且只出现一次的最短公式只有215个字符,“负元角零整分”只出现一次的最短公式只有195个字符:
第一个公式(基础版,只有五个汉字,且"负元角整分"只出现一次)[2011.8.16](278字符)
=IF(ROUND(B3,2),TEXT(B3,";负")&IF(ABS(B3)<0.995,,NUMBERSTRING(INT(ABS(B3)+0.5%),2)&"元")&IF(OR(ROUND(B3,0)=ROUND(B3,2),ABS(B3)<9.5%),,TEXT(LEFT(RIGHT(RMB(B3),2)),"[dbnum2]")&IF(TRUNC(B3)=TRUNC(ROUND(B3,2),1),,"角"))&IF(RIGHT(RMB(B3))="0","整",NUMBERSTRING(RIGHT(RMB(B3)),2)&"分"),"")
第二个公式(简洁版,"负元角零整分"只出现一次)[2011.8.17](195字符)
=IF(-RMB(B3),TEXT(B3,";负")&TEXT(INT(ABS(B3)+0.5%),"[dbnum2]G/通用格式元;;")&TEXT(LEFT(RIGHT(RMB(B3),2)),"[dbnum2]0角;;"&IF((-RMB(B3,0)=-RMB(B3))+(B3^2<1),,"零"))&TEXT(RIGHT(RMB(B3)),"[dbnum2]0分;;整"),"")
第三个公式(百炼版1,非常整洁)[2011.8.16](205字符)
=IF(ROUND(B3,2),TEXT(B3,";负")&TEXT(INT(ABS(B3)+0.5%),"[dbnum2]G/通用格式元;;")&IF((ROUND(B3,0)=ROUND(B3,2))+(ABS(B3)<9.5%),,TEXT(LEFT(RIGHT(RMB(B3),2)),"[dbnum2]0角;;零"))&TEXT(RIGHT(RMB(B3)),"[dbnum2]0分;;整"),"")
第四个公式(百炼版2,另一种写法,且用RMB(B3,2))[2011.8.17](206字符)
=IF(ROUND(B3,2),TEXT(B3,";负")&TEXT(INT(ABS(B3)+0.5%),"[dbnum2]G/通用格式元;;")&TEXT(LEFT(RIGHT(RMB(B3,2),2)),"[dbnum2]0角;;"&IF((ROUND(B3,0)=ROUND(B3,2))+(B3^2<1),,"零"))&TEXT(RIGHT(RMB(B3,2)),"[dbnum2]0分;;整"),"")
第五个公式(特别版1,只有五个汉字,且"负元角整分"只出现一次)[2011.8.18](225字符)
=IF(ROUND(B3,2),TEXT(B3,";负")&IF(ABS(B3)<0.995,,NUMBERSTRING(INT(ABS(B3)+0.5%),2)&"元")&IF((ROUND(B3,0)=ROUND(B3,2))+(ABS(B3)<9.5%),,TEXT(LEFT(RIGHT(RMB(B3),2)),"[dbnum2]0角;;[dbnum2]"))&TEXT(RIGHT(RMB(B3)),"[dbnum2]0分;;整"),"")
第六个公式(特别版2,只有六个汉字,且"负元角零整分"只出现一次)[2011.8.17](215字符)
=IF(ROUND(B3,2),TEXT(B3,";负")&IF(ABS(B3)<0.995,,NUMBERSTRING(INT(ABS(B3)+0.5%),2)&"元")&TEXT(LEFT(RIGHT(RMB(B3),2)),"[dbnum2]0角;;"&IF((ROUND(B3,0)=ROUND(B3,2))+(B3^2<1),,"零"))&TEXT(RIGHT(RMB(B3)),"[dbnum2]0分;;整"),"")
第七个公式(融合版,非SUBSTITUTE函数的简短公式)[2011.8.18](214字符)
=IF(ROUND(B3,2),TEXT(B3,";负")&TEXT(INT(ABS(B3)+0.5%),"[dbnum2]G/通用格式元;;")&IF(RIGHT(RMB(B3))="0",TEXT(LEFT(RIGHT(RMB(B3),2)),"[dbnum2]0角整;;整"),TEXT(RIGHT(RMB(B3),2),IF(B3^2<1,,0)&"[<10][dbnum2]0分;[dbnum2]0角0分")),"")
20110818人民币大写
第一个基础版公式是按照原来思路编写的,看了wshcw的两个公式后,了解了TEXT的(,";;")用法,把基础版公式修改为简洁版公式、百炼版公式和特别版公式,另外写出融合版公式。
这七个转换公式完美通过八大测试数字(1E+15,-1.01,1.995,0.995,0.55,0.095,0.01,0.001),可处理文本型数字,可处理千兆元,可正确显示负数,并可正确显示元角分的各种情况,公式非常严谨而且完善,运行非常快速。
七个公式其实只有两种思路,基础版、简洁版、百炼版和特别版属于同一种思路,融合版是另一种思路,力求最短的公式,考虑全方面情况。
|
评分
-
1
查看全部评分
-
|