在excel中如何将数字自动转变成大写金额，如86.00元变成：捌拾陆元零角零分，而不是捌拾陆元整

答案:6 悬赏:40 手机版

解决时间 2021-02-14 02:35

提问者网友：树红树绿
2021-02-13 23:44

最佳答案

五星知识达人网友：雪起风沙痕
2021-02-14 00:51

假如86.00元在A1，B1=IF(A1=0,"",IF(ABS(A1)<0.995,"",TEXT(INT(ROUND(ABS(A1),2)),"[DBNum2]")&"元")&IF(RIGHt(TEXT(A1,".00"),2)*1=0,IF(ABS(A1)<0.005,"","整"),TEXT(IF(ABS(A1)<0.095,"",LEFt(RIGHt(TEXT(A1,".00"),2))),"[dbnum2]")&IF(LEFt(RIGHt(TEXT(A1,".00"),2))*1=0,"","角")&IF(RIGHt(TEXT(A1,".00"))*1=0,"整",TEXT(RIGHt(TEXT(A1,".00")),"[dbnum2]")&"分")))

全部回答

1楼网友：思契十里
2021-02-14 04:50

在格式选自定义 [DBNum2][$-804]G/通用格式"元""整"

2楼网友：躲不过心动
2021-02-14 03:39

A1单元格是金额,B1单元格输入公式 =if(A1="","",TEXT(A1*100,"[DBNum2]0元0角0分"))

3楼网友：杯酒困英雄
2021-02-14 03:15

=if(a1=0,"",if(abs(a1)<0.995,"",text(int(round(abs(a1),2)),"[dbnum2]")&"元")&if(right(text(a1,".00"),2)*1=0,if(abs(a1)<0.005,"","零角零分"),text(if(abs(a1)<0.095,"",left(right(text(a1,".00"),2))),"[dbnum2]")&if(left(right(text(a1,".00"),2))*1=0,"","角")&if(right(text(a1,".00"))*1=0,"零分",text(right(text(a1,".00")),"[dbnum2]")&"分")))&"整"

4楼网友：你可爱的野爹
2021-02-14 02:34

=IF(A1<0,"负"&IF(ISERROR(FIND(".",ABS(A1))),NUMBERSTRING(INT(ABS(A1)),2)&"元零角零分",IF(ISERROR(NUMBERSTRING(MId(ABS(A1),FIND(".",ABS(A1))+2,1),2)),NUMBERSTRING(INT(ABS(A1)),2)&"元"&NUMBERSTRING(MId(ABS(A1),FIND(".",ABS(A1))+1,1),2)&"角整",NUMBERSTRING(INT(ABS(A1)),2)&"元"&NUMBERSTRING(MId(ABS(A1),FIND(".",ABS(A1))+1,1),2)&"角"&NUMBERSTRING(MId(ABS(A1),FIND(".",ABS(A1))+2,1),2)&"分")),IF(ISERROR(FIND(".",A1)),NUMBERSTRING(INT(A1),2)&"元零角零分",IF(ISERROR(NUMBERSTRING(MId(A1,FIND(".",A1)+2,1),2)),NUMBERSTRING(INT(A1),2)&"元"&NUMBERSTRING(MId(A1,FIND(".",A1)+1,1),2)&"角整",NUMBERSTRING(INT(A1),2)&"元"&NUMBERSTRING(MId(A1,FIND(".",A1)+1,1),2)&"角"&NUMBERSTRING(MId(A1,FIND(".",A1)+2,1),2)&"分")))

5楼网友：第幾種人
2021-02-14 01:11

假设数据在A1,在B1输入： =if(a1="","",TEXT(INT(RMB(A1,2)),"[DBNUM2]G/通用格式元")&TEXT(RIGHt(RMB(A1,2),2),"[DBNUM2]0角0分"))

我要举报

如以上问答信息为低俗、色情、不良、暴力、侵权、涉及违法等信息，可以点下面链接进行举报！