求excel大小写金额转换函数公式在线等急急急急！！！！

Question

求excel大小写金额转换函数公式在线等急急急急！！！！="大写："&SUBSTITUTE(SUBSTITUTE(IF(ROUND(A2,2),TEXT(A2,";负")&TEXT(INT(ABS(A2)+0.5%),"[dbnum2]G/通用格式元;;")&TEXT(RIGHT(TEXT(A2,".00"),2),"[dbnum2]0角0分;;整"),),"零角",IF(A2^2<1,,"零")),"零分","整")
元后为零的话怎么不显示“整”
说错了应该是元后不为零的是时候怎么去掉整

Answer 1

公式为：

="金额大写:"&IF($D$12=0,"   拾   万   仟   佰   拾   元   角   分",TEXT($D$12/1%,"[dbnum2]_ 0拾_ 0万_ 0仟_ 0佰_ 0拾_ 0元_ 0角_ 0分"))


如图所示：

追问

如果元后是整数时候需要显示整，怎么弄

追答

=TEXT(C1,"[dbnum2]0仟0佰0拾0元整")

=IF(C1=INT(C1),TEXT(C1,"[dbnum2]0仟0佰0拾0元整"),TEXT(C1/1%,"[dbnum2]0仟0佰0拾0元0角0分"))





Answer 2

改好了，试试呢
="大写："&IF(MOD(A2,10)=0,SUBSTITUTE(SUBSTITUTE(IF(ROUND(A2,2),TEXT(A2,";负")&TEXT(INT(ABS(A2)+0.5%),"[dbnum2]G/通用格式元;;")&TEXT(RIGHT(TEXT(A2,".00"),2),"[dbnum2]0角0分;;整"),),"零角",IF(A2^2<1,,"零")),"零分","整"),
SUBSTITUTE(SUBSTITUTE(IF(ROUND(A2,2),TEXT(A2,";负")&TEXT(INT(ABS(A2)+0.5%),"[dbnum2]G/通用格式元;;")&TEXT(RIGHT(TEXT(A2,".00"),2),"[dbnum2]0角0分;;"),),"零角",IF(A2^2<1,,"零")),"零分",""))追问

小数点后是零的时候显示整，不为零则不显示

追答

="大写："&IF(MOD(A2*100,100)=0,SUBSTITUTE(SUBSTITUTE(IF(ROUND(A2,2),TEXT(A2,";负")&TEXT(INT(ABS(A2)+0.5%),"[dbnum2]G/通用格式元;;")&TEXT(RIGHT(TEXT(A2,".00"),2),"[dbnum2]0角0分;;整"),),"零角",IF(A2^2<1,,"零")),"零分","整"),
SUBSTITUTE(SUBSTITUTE(IF(ROUND(A2,2),TEXT(A2,";负")&TEXT(INT(ABS(A2)+0.5%),"[dbnum2]G/通用格式元;;")&TEXT(RIGHT(TEXT(A2,".00"),2),"[dbnum2]0角0分;;"),),"零角",IF(A2^2<1,,"零")),"零分",""))

追问

谢谢您！

本回答被提问者采纳

Answer 3

B1=4151.52
=SUBSTITUTE(SUBSTITUTE(IF(-RMB(B1),IF(B1>0,,"负")&TEXT(INT(ABS(B1)+0.5%),"[dbnum2]G/通用格式元;;")&TEXT(RIGHT(RMB(B1,2),2),"[dbnum2]0角0分;;整")
结果是
肆仟壹佰伍拾壹元伍角贰分追问

显示该公式缺少左括号

追答

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