[原创]人民币大写数字相互转换

wshcw

公式经过刀砍斧削,终于打破了170字符.
168个字符
=SUBSTITUTE(IF(-RMB(A2),IF(A2>0,,"负")&TEXT(INT(ABS(A2)+0.5%),"[dbnum2]G/通用格式元;;")&TEXT(RIGHT(RMB(A2),2),"[>9][dbnum2]0角0分;"&IF(A2^2<1,,0)&"[>][dbnum2]0分;整"),),"零分","整")
167个字符
=SUBSTITUTE(SUBSTITUTE(IF(-RMB(A2),IF(A2>0,,"负")&TEXT(INT(ABS(A2)+0.5%),"[dbnum2]G/通用格式元;;")&TEXT(RIGHT(RMB(A2),2),"[dbnum2]0角0分;;整"),),"零角",IF(A2^2<1,,"零")),"零分","整")

W3pZL9HF.rar (153.08 KB, 下载次数: 1352)

2008-8-11 12:21 上传

点击文件名下载附件
[原创]人民币大写数字相互转换

金额分列公式:

=IF($A2,LEFT(RIGHT(" ￥"&$A2/1%,13-COLUMN())),"")

这个是通用于负值和0值,公式也是最短的,只有48字.

注:17楼公式更完善请查看.

http://club.excelhome.net/viewthread.php?tid=337492&replyID=1495284&skin=1

VBA法参考:

http://club.excelhome.net/viewthread.php?tid=337509&page=6#pid2210353
Function dx(M)
    dx = Replace(Replace(Replace(Join(Application.Text(Split(Format(M, " 0. 0 0;负 0. 0 0;   ")), ["[DBnum2]"&{0,"","圆0角;;圆零","0分;;整"}]), a), "零圆零", a), "零圆", a), "零整", "整")
   '163字符A 无缺陷 负数显示 “负”   负伍圆陆角伍分 
End Function

＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝

与用友一致的大写公式:
=SUBSTITUTE(SUBSTITUTE(IF(-RMB(A2),IF(A2>0,,"（负）")&TEXT(INT(ABS(A2)+0.5%),"[dbnum2]G/通用格式元;;")&TEXT(RIGHT(RMB(A2),2),"[>9][dbnum2]"&IF(MOD(ABS(A2),10)<1,0,)&"0角0分;"&IF(A2^2<1,,0)&"[>][dbnum2]0分;整"),),"万",IF((MOD(ABS(A2%%),10)<1)*(MOD(ABS(A2%%),10)>=0.1),"万零","万")),"零分","整")

LangQueS

谢谢wshcw先生，希望多来ExcelHome论坛。

有感论坛中不少帖子没得到解答

gouweicao78

谢谢黄兄分享。不过EH人气怎能说冷漠呢？基本上，描述清楚的问题都得能到解决的。

关于“金额分列”，请参考开放式竞赛版第32期函数题：

http://club.excelhome.net/dispbbs.asp?boardid=102&replyid=920555&id=135231&page=1&skin=0&Star=1

可谓条条大路通罗马。

比如：B2=LEFT(RIGHT(TEXT($A2/1%," ￥0; ￥-0"),COLUMNS(B:$L)))可得到1楼附件中的分列效果

但类似-0.01或者0.01得到的是￥-1、￥1，与日常工作中用的-￥001、￥001表示方法不一样，其实用负数的情况好像不多。这些问题在上面链接中已得到解决。

[此贴子已经被作者于2008-7-13 12:17:21编辑过]

wshcw

忘了链接了旧附件,重新从硬盘上传.

和平饭店

精彩啊，谢谢楼主了

sgcsq

谢谢高手！

wshcw

又有牛人打破论坛记录了,参考:
http://tieba.baidu.com/f?kz=337413440
请港澳台同胞用繁体版测试,看看繁体版是否适用172个字符的以下公式(也就是看看RMB函数是否可用于繁体版):
=IF(-RMB(A1),SUBSTITUTE(SUBSTITUTE(IF(A1<0,"负",)&TEXT(INT(ABS(A1)+0.5%),"[dbnum2]G/通用格式元;;")&TEXT(RIGHT(RMB(A1),2),"[dbnum2]0角0分;;整"),"零角",IF(ABS(A1)<1,,"零")),"零分","整"),"")
=IF(-RMB(A1),SUBSTITUTE(SUBSTITUTE(IF(A1<0,"负",)&TEXT(INT(ABS(RMB(A1))),"[dbnum2]G/通用格式元;;")&TEXT(RIGHT(RMB(A1),2),"[dbnum2]0角0分;;整"),"零角",IF(ABS(A1)<1,,"零")),"零分","整"),"")
=IF(-RMB(A1),SUBSTITUTE(SUBSTITUTE(IF(A1<0,"负",)&TEXT(INT(ABS(A1)+0.5%),"[dbnum2]G/通用格式元;;")&TEXT(RIGHT(RMB(A1),2),"[dbnum2]0角0分;;整"),"零角",IF(MOD(A1,1),,"零")),"零分","整"),"")
176个字符(应该通用于繁体版):
=IF(-FIXED(A2),SUBSTITUTE(SUBSTITUTE(IF(A2>0,,"负")&TEXT(INT(ABS(A2)+0.5%),"[dbnum2]G/通用格式元;;")&TEXT(RIGHT(FIXED(A2),2),"[dbnum2]0角0分;;整"),"零角",IF(ABS(A2)<1,,"零")),"零分","整"),"")

[此贴子已经被作者于2008-8-8 22:18:49编辑过]

wshcw

今天改了改,这个公式虽短(153字符),但有缺陷,帮改改(有元有分,无角时,元后面应该有零,而该公式结果没能达到,不是严格要求可以过得去).
=SUBSTITUTE(IF(-RMB(A2),IF(A2<0,"负",)&TEXT(INT(ABS(A2)+0.5%),"[dbnum2]G/通用格式元;;")&TEXT(RIGHT(RMB(A2),2),"[>9][dbnum2]0角0分;[>][dbnum2]0分;整"),""),"零分","整")

170个字符的测试无缺陷:

=SUBSTITUTE(SUBSTITUTE(IF(-RMB(A1),IF(A1<0,"負",)&TEXT(INT(ABS(A1)+0.5%),"[dbnum2]G/通用格式元;;")&TEXT(RIGHT(RMB(A1),2),"[dbnum2]0角0分;;整"),),"零角",IF(ABS(A1)<1,,"零")),"零分","整")
 

[此贴子已经被作者于2008-8-9 23:41:40编辑过]

linchangh

学习了

wshcw

公式经过刀砍斧削,终于打破了170字符.
168个字符
=SUBSTITUTE(IF(-RMB(A2),IF(A2>0,,"负")&TEXT(INT(ABS(A2)+0.5%),"[dbnum2]G/通用格式元;;")&TEXT(RIGHT(RMB(A2),2),"[>9][dbnum2]0角0分;"&IF(A2^2<1,,0)&"[>][dbnum2]0分;整"),),"零分","整")
167个字符
=SUBSTITUTE(SUBSTITUTE(IF(-RMB(A2),IF(A2>0,,"负")&TEXT(INT(ABS(A2)+0.5%),"[dbnum2]G/通用格式元;;")&TEXT(RIGHT(RMB(A2),2),"[dbnum2]0角0分;;整"),),"零角",IF(A2^2<1,,"零")),"零分","整")

[此贴子已经被作者于2008-8-11 12:46:58编辑过]