excel 如何在A1输入小写金额后，在B1自动出现大写金额？

答案:3 悬赏:60 手机版

解决时间 2021-11-18 23:17

提问者网友：自食苦果
2021-11-18 03:54

最佳答案

五星知识达人网友：执傲
2021-11-18 05:16

方法一、在A1单元格输入数字，B1单元格输入“=SUBSTITUTE(SUBSTITUTE(IF(ISNUMBER(FIND(".",A1)),TEXT(LEFt(A1,FIND(".",A1)-1),"[dbnum2]")&"元"&TEXT(MId(A1,FIND(".",A1)+1,1),"[dbnum2]")&"角"&TEXT(MId(A1,FIND(".",A1)+2,1),"[dbnum2]")&"分",TEXT(A1,"[dbnum2]")&"元整"),"角分","角整"),"-","负")”，回车即可。

　　方法二、在A1单元格输入数字，B1单元格输入“=IF(A2<0,"负","")&TEXT(TRUNC(ABS(ROUND(A2,2))),"[DBNum2]")&"元"&IF(ISERR(FIND(".",ROUND(A2,2))),"",TEXT(RIGHt(TRUNC(ROUND(A2,2)*10)),"[DBNum2]"))&IF(ISERR(FIND(".0",TEXT(A2,"0.00"))),"角","")&IF(LEFt(RIGHt(ROUND(A2,2),3))=".",TEXT(RIGHt(ROUND(A2,2)),"[DBNum2]")&"分","整")”，回车

全部回答

1楼网友：孤老序
2021-11-18 05:58

选中整数所在的的单元格区域 CTRL+1 数字自定义类型内输入 [DBNum2]G/通用格式"整" 确定

2楼网友：舍身薄凉客
2021-11-18 05:34

=TEXT(A1,"[DBNum2]G/通用格式")

=IF(A1=INT(A1),TEXT(A1,"[DBNum2]G/通用格式")&"元整",TEXT(INT(A1),"[DBNum2]G/通用格式")&"元整"&TEXT(RIGHt(A1*10,1),"[DBNum2]G/通用格式")&"角"&TEXT(RIGHt(A1*100,1),"[DBNum2]G/通用格式")&"分")追问不行，没写“元整”字样追答下面的公式就完整了追问不行啊，“角”跟“分”相同了，一旦有小数点还会显示“元整x角x分”例如：
136.56

壹佰叁拾陆元整陆角陆分追答=IF(A1=INT(A1),TEXT(A1,"[DBNum2]G/通用格式")&"元整",TEXT(INT(A1),"[DBNum2]G/通用格式")&"元整"&TEXT(RIGHt(INT(A1*10),1),"[DBNum2]G/通用格式")&"角"&TEXT(RIGHt(INT(A1*100),1),"[DBNum2]G/通用格式")&"分")

我要举报

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