您现在的位置: 首页 > 网站导航收录 > 百科知识百科知识
excel人民币大写自动生成公式(excel自动生成人民币大写金额)
零分,格式,公式excel人民币大写自动生成公式(excel自动生成人民币大写金额)
发布时间：2020-12-06加入收藏来源：互联网点击：
很多朋友想了解关于excel人民币大写自动生成公式的一些资料信息，下面是小编整理的与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(B80,"负",)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(A20,"负",)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(A20,"负",)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(A20,"负",)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(A20,"负",)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(A20,"负",)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(A10,"负",)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(A20,"负",)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(A20,,"负")TEXT(INT(ABS(A2)+0.5%),"[dbnum2]G/通用格式元")TEXT(RIGHT(RMB(A2),2),"[9][dbnum2]0角0分;"IF(A2^21,,0)"[][dbnum2]0分;整"),),"零分","整")
20 =SUBSTITUTE(SUBSTITUTE(IF(-RMB(A2),IF(A20,,"负")TEXT(INT(ABS(A2)+0.5%),"[dbnum2]G/通用格式元")TEXT(RIGHT(RMB(A2),2),"[dbnum2]0角0分整"),),"零角",IF(A2^21,,"零")),"零分","整")22 =SUBSTITUTE(IF(-RMB(A2),IF(A20,"负",)TEXT(INT(ABS(A2)+0.5%),"[dbnum2]G/通用格式元")TEXT(RIGHT(RMB(A2),2),"[9][dbnum2]0角0分整;"IF(A2^21,,0)"[][dbnum2]0分;整"),),"零分",)
21 =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^21,,0)"[][dbnum2]0分;整"),),"零分",)
22 =TEXT(RMB(A1),"[=]g;"TEXT(INT(ABS(A1)+0.5%),"[dbnum2]G/通用格式元")SUBSTITUTE(SUBSTITUTE(TEXT(RIGHT(RMB(A1),2),"[dbnum2]0角0分整"),"零角",IF(A1^21,,"零")),"零分","整"))
23 =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^21,,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^21,,"零")),"零分","整")
24 =TEXT(LEFT(RMB(A1),LEN(RMB(A1))-3),"[0][dbnum2]G/通用格式元;[0]负[dbnum2]G/通用格式元") TEXT(RIGHT(RMB(A1),2),"[dbnum2]0角0分整")
25 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]")"分")
26 =IF(OR(B1="",B1=0),"",TEXT(INT(B1),"[dbnum2]G/通用格式元;[dbnum2]G/通用格式元")TEXT(--RIGHT(INT(B1*10)),"[dbnum2]#角;")TEXT(--RIGHT(INT(B1*100)),"[dbnum2]#分整;"))
680个带公式的Excel模板（工资绩效社保考勤可视图表，全自动生成）
①HR高颜值实用Excel图表模板（考勤、工资条、数据分析、排班自动生成）
②饼图圆环图模板（99个）
③采购模板（59个）
……
本文到此结束，希望对大家有所帮助呢。
下一篇：返回列表
相关链接 |
||
| 网友回复（共有 0 条回复） |