超全的金额大写excel公式28个,收藏学习 图文

2020年9月2日07:08:33超全的金额大写excel公式28个,收藏学习 图文已关闭评论 38 views
表格 定制  数据 合并 处理 分析 VBA 编程 开发 网页
超全的金额大写excel公式,可以收藏起来备用哦

人民币大写公式怎么写,公式很多,也很复杂,有兴趣的朋友可以研究一下,不想研究的就收藏起来备用。

使用方法很简单,把下面公式中的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,,"零")),"零分","整"))

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]#分;;整;"))

  • 郑广学老师微信号
  • EXCEL880B
  • weinxin
  • 我的微信公众号
  • EXCEL880
  • weinxin