简单很好 lunkuo
收音机.单车
关注数: 13 粉丝数: 39 发帖数: 1,603 关注贴吧数: 44
[原创] EXCEL人民币金额大写转换,"负元角零整分"不重复出现的最短 EXCEL表格的数字转换成人民币金额大写,只有五个汉字"负元角整分"且只出现一次的最短公式只有240字符,只有六个汉字"负元角零整分"且只出现一次的最短公式只有210字符,“负元角零整分”只出现一次的最短公式只有199个字符: 第一个公式(基础版,只有五个汉字,且"负元角整分"只出现一次)[2011.8.7](282字符)=IF(-FIXED(B3),IF(B3<0,"负",)&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(FIXED(B3),2)),"[dbnum2]")&IF(TRUNC(B3)=TRUNC(ROUND(B3,2),1),,"角"))&IF(-RIGHT(FIXED(B3))=0,"整",NUMBERSTRING(RIGHT(FIXED(B3)),2)&"分"),"") 第二个公式(简洁版1,"负元角零整分"只出现一次)[2011.8.9](199字符)=IF(-RMB(B3),TEXT(B3,";负")&TEXT(INT(ABS(B3)+0.5%),"[dbnum2]G/通用格式元;;")&IF(OR(MOD(ROUND(B3,2),1)=0,ABS(B3)<9.5%),,TEXT(LEFT(RIGHT(RMB(B3),2)),"[dbnum2]0角;;零"))&TEXT(RIGHT(RMB(B3)),"[dbnum2]0分;;整"),"") 第三个公式(简洁版2,把RMB(B3)替换为RMB(B3,2)或FIXED(B3))[2011.8.9](205字符)=IF(-RMB(B3,2),TEXT(B3,";负")&TEXT(INT(ABS(B3)+0.5%),"[dbnum2]G/通用格式元;;")&IF(OR(MOD(ROUND(B3,2),1)=0,ABS(B3)<9.5%),,TEXT(LEFT(RIGHT(RMB(B3,2),2)),"[dbnum2]0角;;零"))&TEXT(RIGHT(RMB(B3,2)),"[dbnum2]0分;;整"),"") 第四个公式(加强版1,只有五个汉字,且"负元角整分"只出现一次)[2011.8.10)(240字符)=IF(-RMB(B3),TEXT(B3,";负")&IF(RMB(B3)^2<1,,NUMBERSTRING(INT(ABS(B3)+0.5%),2)&"元")&IF(OR(MOD(ROUND(B3,2),1)=0,ABS(B3)<9.5%),,TEXT(LEFT(RIGHT(RMB(B3),2)),"[dbnum2]")&IF(-LEFT(RIGHT(RMB(B3),2))=0,,"角"))&TEXT(RIGHT(RMB(B3)),"[dbnum2]0分;;整"),"") 第五个公式(加强版2,只有六个汉字,且"负元角零整分"只出现一次)[2011.8.10](210字符)=IF(-RMB(B3),TEXT(B3,";负")&IF(RMB(B3)^2<1,,NUMBERSTRING(INT(ABS(B3)+0.5%),2)&"元")&IF(OR(MOD(ROUND(B3,2),1)=0,ABS(B3)<9.5%),,TEXT(LEFT(RIGHT(RMB(B3),2)),"[dbnum2]0角;;零"))&TEXT(RIGHT(RMB(B3)),"[dbnum2]0分;;整"),"") 第六个公式(融合版,非SUBSTITUTE函数的简短公式)[2011.8.10](213字符)=IF(-RMB(B3),TEXT(B3,";负")&TEXT(INT(RMB(ABS(B3))),"[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分")),"")在EXCELhome论坛见到wshcw写的“[原创]人民币大写数字相互转换”的两个公式,太强了,我平时只用些简单的函数,第一次认识SUBSTITUTE函数,但感觉SUBSTITUTE有些取巧。第一个基础版公式是按照我原来思路编写的,看了wshcw的公式后,初步了解了TEXT的(,";;")用法,把基础版公式修改为简洁版公式和加强版公式,另外写出融合版公式。 这六个转换公式完美通过六大测试数字(-1.01,11.995,0.55,0.1,0.01,0.001),可处理文本型数字,可正确显示负数,并可正确显示元角分的各种情况,其中简洁版是"负元角零整分"六个汉字不重复的最短公式。六个公式其实只有两种思路,基础版、简洁版和加强版属于同一种思路,融合版是另一种思路,而且都不使用SUBSTITUTE函数,力求最短的公式,考虑全方面情况。
1 下一页