超全的人民币大写金额转换公式

2017-05-05 07:56 来源: 注会更easy

有同学在微信平台提问人民币大写公式怎么写，小编就从网上搜集了一些，公式很多，也很复杂，有兴趣的同学可以研究一下，不想研究的就收藏起来备用。使用方法很简单，把下面公式中的A2换成你表中数字所在单元格地址即可。

1=SUBSTITUTE(SUBSTITUTE(TEXT(TRUNC(FIXED(A2)),"[>0][dbnum2]G/通用格式元;[<0]负[dbnum2]G/通用格式元;;")&TEXT(RIGHT(FIXED(A2),2),"[dbnum2]0角0分;;"&IF(ABS(A2)>1%,"整",)),"零角",IF(ABS(A2)<1,,"零")),"零分","整")

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

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

4=SUBSTITUTE(SUBSTITUTE(IF(A2>-0.5%,,"负")&TEXT(INT(FIXED(ABS(A2))),"[dbnum2]G/通用格式元;;")&TEXT(RIGHT(FIXED(A2),2),"[dbnum2]0角0分;;"&IF(ABS(A2)>1%,"整",)),"零角",IF(ABS(A2)<1,,"零")),"零分","整")

5=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(IF(A2>-0.5%,,"负")&TEXT(INT(FIXED(ABS(A2))),"[dbnum2]")&TEXT(RIGHT(FIXED(A2),2),"[dbnum2]元0角0分;;元"&IF(ABS(A2)>1%,"整",)),"零角",IF(ABS(A2)<1,,"零")),"零元",),"零分","整")

展开全文

6=SUBSTITUTE(SUBSTITUTE(IF(A2>-0.5%,,"负")&IF(ABS(A2)+0.5%<1,,TEXT(INT(ABS(A2)+0.5%),"[dbnum2]")&"元")&TEXT(RIGHT(FIXED(A2),2),"[dbnum2]0角0分;;"&IF(ABS(A2)>1%,"整",)),"零角",IF(ABS(A2)<1,,"零")),"零分","整")

7 =IF(A2=0,"零",IF(A2>-0.5%,,"负")&TEXT(INT(ABS(A2)),"[dbnum2]G/通用格式元;;")&SUBSTITUTE(SUBSTITUTE(TEXT(RIGHT(FIXED(A2),2),"[dbnum2]0角0分;;"),"零角",IF(ABS(A2)<1,,"零")),"零分","整"))

8=SUBSTITUTE(SUBSTITUTE(TEXT(TRUNC(FIXED(A2)),"[dbnum2]G/通用格式元;负[dbnum2]G/通用格式元;"&IF(A2>-0.5%,,"负"))&TEXT(RIGHT(FIXED(A2),2),"[dbnum2]0角0分;;"&IF(ABS(A2)>1%,"整",)),"零角",IF(ABS(A2)<1,,"零")),"零分","整")

9=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(IF(B8<0,"负",)&TEXT(INT(ABS(B8)),"[dbnum2];; ")&TEXT(MOD(ABS(B8)*100,100),"[>9][dbnum2]圆0角0分;[=0]圆整;[dbnum2]圆零0分"),"零分","整")," 圆零",)," 圆",)

10=SUBSTITUTE(SUBSTITUTE(TEXT(INT(A1),"[dbnum2]G/通用格式元;;")&TEXT(RIGHT(A1/1%,2),"[dbnum2]0角0分;;"&IF(A1,"整",)),"零角","零"),"零分","整")

"大写（人民币）："&IF(A1-INT(A1)<0.005,TEXT(INT(A1),"[dbnum2]")&"元整",IF(A1*10-INT(A1*10)<0.05,TEXT(INT(A1),"[dbnum2]")&"元"&TEXT(INT(A1*10-INT(A1)*10),"[dbnum2]")&"角整",TEXT(INT(A1),"[dbnum2]")&"元"&TEXT(INT(A1*10-INT(A1)*10),"[dbnum2]")&"角"&TEXT((FIXED(A1*100,0)-INT(A1*10)*10),"[dbnum2]")&"分"))

11=IF(ABS(A2)<0.5%,"",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,,"零")),"零分","整"))

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

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

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

15=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分;整"),""),"零分","整")

16 =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分;整"),""),"零分","整")

17=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,,"零")),"零分","整")

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

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

20=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分;整"),),"零分","整")

21=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,,"零")),"零分","整")

22 =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分;整"),),"零分",)

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

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

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

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

27=TEXT(LEFT(RMB(A1),LEN(RMB(A1))-3),"[>0][dbnum2]G/通用格式元;[<0]负[dbnum2]G/通用格式元;;") & TEXT(RIGHT(RMB(A1),2),"[dbnum2]0角0分;;整")

28TEXT(INT(A3),"[dbnum2]")&"元"&IF(INT(A3*10)-INT(A3)*10=0,"",TEXT(INT(A3*10)-INT(A3)*10,"[dbnum2]")&"角")&IF(INT(A3*100)-INT(A3*10)*10=0,"整",TEXT(INT(A3*100)-INT(A3*10)*10,"[dbnum2]")&"分")

29=IF(OR(B1="",B1=0),"",TEXT(INT(B1),"[dbnum2]G/通用格式元;[dbnum2]G/通用格式元;;")&TEXT(--RIGHT(INT(B1*10)),"[dbnum2]#角;;;")&TEXT(--RIGHT(INT(B1*100)),"[dbnum2]#分;;整;")) 返回搜狐，查看更多

责任编辑：

平台声明：该文观点仅代表作者本人，搜狐号系信息发布平台，搜狐仅提供信息存储空间服务。

阅读 ()