人民币大小写转换excel公式

《人民币大小写转换excel公式》由会员上传分享，免费在线阅读，更多相关内容在工程资料-天天文库。

1、人民币大小写转换Excel公式以下假设你在A1单元格中输入金额数字，在其它单元格输入以下公式就可以了公式1=SUBSTITUTE(SUBSTITUTE(IF(A1<0,"负","")&TEXT(TRUNC(ABS(ROUND(A1,2))),"[DBNum2]")&"元"&IF(ISERR(FIND(".",ROUND(A1,2))),"",TEXT(RIGHT(TRUNC(ROUND(A1,2)*10)),"[DBNum2]"))&IF(ISERR(FIND(".0",TEXT(A1,"0.00"))),"角","")&IF(LEFT(RIGHT(ROUND(A1,2),3))=".",

2、TEXT(RIGHT(ROUND(A1,2)),"[DBNum2]")&"分",IF(ROUND(A1,2)=0,"","整")),"零元零",""),"零元","")公式2=IF((A1-INT(A1))=0,TEXT(A1,"[DBNUM2]")&"元整",IF(INT(A1*10)-A1*10=0,TEXT(INT(A1),"[DBNUM2]")&"元"&TEXT((INT(A1*10)-INT(A1)*10),"[DBNUM2]")&"角整",TEXT(INT(A1),"[DBNUM2]")&"元"&IF(INT(A1*10)-INT(A1)*10=0,"零",TEXT(INT(A

3、1*10)-INT(A1)*10,"[DBNUM2]")&"角")&TEXT(RIGHT(A1,1),"[DBNUM2]")&"分"))公式3=IF(ABS(A1)<0.005,"",IF(A1<0,"负",)&IF(INT(ABS(A1)),TEXT(INT(ABS(A1)),"[dbnum2]")&"元",)&IF(INT(ABS(A1)*10)-INT(ABS(A1))*10,TEXT(INT(ABS(A1)*10)-INT(ABS(A1))*10,"[dbnum2]")&"角",IF(INT(ABS(A1))=ABS(A1),,IF(ABS(A1)<0.1,,"零")))&IF(R

4、OUND(ABS(A1)*100-INT(ABS(A1)*10)*10,),TEXT(ROUND(ABS(A1)*100-INT(ABS(A1)*10)*10,),"[dbnum2]")&"分","整"))以上公式包含在附件的excel表格中。下面有更多网友提供的excel公式(未验证)1=IF(A1=0,"",CONCATENATE(IF(INT(A1)=0,"",TEXT(INT(A1),"[DBNum2]G/通用格式元")),IF(INT(MID(RIGHT(FIXED(A1,2,1),2),1,1))=0,IF(INT(MID(RIGHT(FIXED(A1,2,1),1),1,1)

5、)=0,"",IF(INT(A1)=0,"","零")),TEXT(INT(MID(RIGHT(FIXED(A1,2,1),2),1,1)),"[DBNum2]G/通用格式角")),IF(INT(MID(RIGHT(FIXED(A1,2,1),1),1,1))=0,"整",TEXT(INT(MID(RIGHT(FIXED(A1,2,1),1),1,1)),"[DBNum2]G/通用格式分"))))2=IF(A1<0,"負","")&IF(ABS(A1)>1,TEXT(TRUNC(ABS(ROUND(A1,2))),"[DBNum2]")&"元","")&IF(ISERR(FIND(".",

6、ROUND(A1,2))),"",TEXT(RIGHT(TRUNC(ROUND(A1,2)*10)),"[DBNum2]"))&IF(ISERR(FIND(".0",TEXT(A1,"0.00"))),"角","")&IF(LEFT(RIGHT(ROUND(A1,2),3))=".",TEXT(RIGHT(ROUND(A1,2)),"[DBNum2]")&"分","整")3beconsistentwithinthesamedisk.Alternateunifiedcorerequirementsplacedontheterminalstripterminals,onlineidentity

7、andensurethecoppercoreisnotexposed.6.4.6enclosurewithinthesametothecablecoreprovidesbindingintoacircle,harnesstiespacingisgenerally100mm;branchofficesshallbebindingonbothends,eachcore=IF(A1<0,"负","")&IF(TRUNC(ROU