我是做报表的，需要用到人民币小写转换成大写，但是转换之后百万位是0的常常大写没零。
以下是函数式，请高手帮忙看看
=SUBSTITUTE(SUBSTITUTE(IF(I24<0,"负","")&TEXT(TRUNC(ABS(ROUND(I24,2))),"[DBNum2]")&"圆"&IF(ISERR(FIND(".",ROUND(I24,2))),"",TEXT(RIGHT(TRUNC(ROUND(I24,2)*10)),"[DBNum2]"))&IF(ISERR(FIND(".0",TEXT(I24,"0.00"))),"角","")&IF(LEFT(RIGHT(ROUND(I24,2),3))=".",TEXT(RIGHT(ROUND(I24,2)),"[DBNum2]")&"分",IF(ROUND(I24,2)=0,"","整")),"零元零",""),"零元","")
[分享]一个微软未公开的小写转大写函数,会计们有福了
NumberString(1234567890,1) ＝ 一十二亿三千四百五十六万七千八百九
NumberString(1234567890,2) ＝ 壹拾贰亿叁仟肆佰伍拾陆万柒仟捌佰玖拾
NumberString(1234567890,3) ＝ 一二三四五六七八九
TEXT(1234567890,"[DBNum1]")=一十二亿三千四百五十六万七千八百九十
TEXT(1234567890,"[DBNum2]")=壹拾贰亿叁仟肆佰伍拾陆万柒仟捌佰玖拾
TEXT(1234567890,"[DBNum1]0")=一二三四五六七八九○
TEXT(1234567890,"[DBNum2]0")=壹贰叁肆伍陆柒捌玖零
NumberString(1234567890,1) ＝ 一十二亿三千四百五十六万七千八百九
NumberString(1234567890,2) ＝ 壹拾贰亿叁仟肆佰伍拾陆万柒仟捌佰玖拾
NumberString(1234567890,3) ＝ 一二三四五六七八九
TEXT(1234567890,"[DBNum1]")=一十二亿三千四百五十六万七千八百九十
TEXT(1234567890,"[DBNum2]")=壹拾贰亿叁仟肆佰伍拾陆万柒仟捌佰玖拾
TEXT(1234567890,"[DBNum1]0")=一二三四五六七八九○
TEXT(1234567890,"[DBNum2]0")=壹贰叁肆伍陆柒捌玖零
温馨提示：答案为网友推荐，仅供参考
第1个回答 2009-02-05
比如:
120056789
你觉得这样的大写不正确吗？
壹亿贰仟零伍万陆仟柒佰捌拾玖元整
你要的是：
壹亿贰仟零佰零拾伍万陆仟柒佰捌拾玖元整
其实上面那个已经是正确的了呀，支票上也用这公式的没什么问题
120056789
你觉得这样的大写不正确吗？
壹亿贰仟零伍万陆仟柒佰捌拾玖元整
你要的是：
壹亿贰仟零佰零拾伍万陆仟柒佰捌拾玖元整
其实上面那个已经是正确的了呀，支票上也用这公式的没什么问题
第2个回答 2009-02-05
好像并不能达到你的要求
不过这个函数比你的那个要短些 ，供你参考。
=SUBSTITUTE(SUBSTITUTE(TEXT(TRUNC(FIXED(A24)),"[dbnum2]G/通用格式元;负[dbnum2]G/通用格式元;"&IF(A24>-0.5%,,"负"))&TEXT(RIGHT(FIXED(A24),2),"[dbnum2]0角0分;;"&IF(ABS(A24)>1%,"整",)),"零角",IF(ABS(A24)<1,,"零")),"零分","整")
不过这个函数比你的那个要短些 ，供你参考。
=SUBSTITUTE(SUBSTITUTE(TEXT(TRUNC(FIXED(A24)),"[dbnum2]G/通用格式元;负[dbnum2]G/通用格式元;"&IF(A24>-0.5%,,"负"))&TEXT(RIGHT(FIXED(A24),2),"[dbnum2]0角0分;;"&IF(ABS(A24)>1%,"整",)),"零角",IF(ABS(A24)<1,,"零")),"零分","整")
第3个回答 2009-02-06
用这个看看
=IF(INT(A1)*100=A1*100,TEXT(INT(A1),"[DBNum2]")&"元整",IF(INT(A1*10)=A1*10,TEXT(INT(A1),"[DBNum2]")&"元"&TEXT(A1*10-INT(A1)*10,"[DBNum2]")&"角整",TEXT(INT(A1),"[DBNum2]")&"元"&IF(INT(A1*10)=INT(A1)*10,"零",TEXT(RIGHT(INT(A1*10)),"[DBNum2]")&"角")&TEXT(RIGHT(A1*100),"[DBNum2]")&"分"))
=IF(INT(A1)*100=A1*100,TEXT(INT(A1),"[DBNum2]")&"元整",IF(INT(A1*10)=A1*10,TEXT(INT(A1),"[DBNum2]")&"元"&TEXT(A1*10-INT(A1)*10,"[DBNum2]")&"角整",TEXT(INT(A1),"[DBNum2]")&"元"&IF(INT(A1*10)=INT(A1)*10,"零",TEXT(RIGHT(INT(A1*10)),"[DBNum2]")&"角")&TEXT(RIGHT(A1*100),"[DBNum2]")&"分"))
