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

有同学在微信平台提问人民币大写公式怎么写,兰色就从网上搜集了一些,公式很多,也很复杂,有兴趣的同学可以研究一下,不想研究的就收藏起来备用。使用方法很简单,把下面公式中的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,,”零”)),”零分”,”整”)

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

19 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(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分;;整”)

28 TEXT(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]#分;;整;”))


长按下面二维码图片,点上面识别图中二维码然后再点关注,每天可以收到一篇兰色最新写的excel教程。


本篇文章来源于微信公众号: Excel精英培训

为您推荐

发表评论

电子邮件地址不会被公开。 必填项已用*标注