excel电子表格大小写金额转换[整理版]

excel电子格大小写金额转换[整理版] Excel电子表格大小写金额转换(实用) 2011-05-30 23:03:47| 分类: 电脑知识 | 标签: |字号大中小 订阅 方法一: 在单元格A2中输入小写数字123.12 B2处输入以下公式 =SUBSTITUTE(SUBSTITUTE(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(IS ERR(FIND(".0",TEXT(A2,"0.00"))),"角 ","")&IF(LEFT(RIGHT(ROUND(A2,2),3))=".",TEXT(RIGHT(ROUND(A2,2)),"[DBNum2]")&"分 ",IF(ROUND(A2,2)=0,"","整")),"零元零",""),"零元","") 方法二: =IF(A2="","",IF(A2<0,"负",)&IF(INT(A2),TEXT(INT(ABS(A2)),"[dbnum2]")&"元",)&IF(INT(ABS(A2)*10)-INT(ABS(A2))*10,TEXT(INT(ABS(A2)*10)-INT(ABS(A2))*10,"[dbnum2]")&"角 ",IF(INT(ABS(A2))=ABS(C3),,IF(ABS(A2)<0.1,,"零 ")))&IF(ROUND(ABS(A2)*100-INT(ABS(A2)*10)*10,),TEXT(ROUND(ABS(A2)*100-INT(ABS(A2)*10)*10 ,),"[dbnum2]")&"分","整")) 方法三: =IF((INT(A2*10)-INT(A2)*10)=0,TEXT(INT(A2),"[DBNum2]G/通用格式")&"元 "&IF((INT(A2*100)-INT((A2)*10)*10)=0,"整","零 "&TEXT(INT(A2*100)-INT(A2*10)*10,"[DBNum2]G/通用格式")&"分 "),TEXT(INT(A2),"[DBNum2]G/通用格式")&"元 "&IF((INT(A2*100)-INT((A2)*10)*10)=0,TEXT((INT(A2*10)-INT(A2)*10),"[DBNum2]G/通用格 式")&"角整",TEXT((INT(A2*10)-INT(A2)*10),"[DBNum2]G/通用格式")&"角"&TEXT(INT(A2*100)-INT(A2*10)*10,"[DBNum2]G/通用格式")&"分")) 方法四: =IF(A2-INT(A2)=0,TEXT(INT(A2),"[DBNum2]G/通用格式")&"圆整",TEXT(INT(A2),"[DBNum2]G/通用格式")&"圆"&TEXT(INT((A2-INT(A2))*10),"[DBNum2]G/通用格式")&"角"&TEXT(INT((A2*10-INT(A2*10))*10),"[DBNum2]G/通用格式")&"分") 方法五: =IF((A2-INT(A2))=0,TEXT(A2,"[DBNUM2]")&"元整 ",IF(INT(A2*10)-A2*10=0,TEXT(INT(A2),"[DBNUM2]")&"元"&TEXT((INT(A2*10)-INT(A2)*10),"[DBNUM2]")&"角整",TEXT(INT(A2),"[DBNUM2]")&"元"&IF(INT(A2*10)-INT(A2)*10=0,"零",TEXT(INT(A2*10)-INT(A2)*10,"[DBNUM2]")&"角 ")&TEXT(RIGHT(A2,1),"[DBNUM2]")&"分"))