EXCEL小写转大写公式
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
=IF(ABS(K20)=0,"",IF(K20<0,"负",)&IF(INT(ABS(K20)),TEXT(INT(ABS(K20)),"[dbnum2]")&"元",)&IF(INT(ABS(K20)*10)-INT(ABS(K20))*10,TEXT(INT(ABS(K20)*10)-INT(ABS(K20))*10,"[dbnum2]")&"角",IF(INT(ABS(K20))=ABS(K20),,IF(ABS(K20)<0.1,,"零")))&IF(ROUND(ABS(K20)*100-INT(ABS(K20)*10)*10,),TEXT(ROUND(ABS(K20)*100-INT(ABS(K20)*10)*10,),"[dbnum2]")&"分","整"))
=SUBSTITUTE(SUBSTITUTE(IF(K20<0,"负","")&TEXT(TRUNC(ABS(ROUND(K20,2))),"[DBNum2]")&"元"&IF(ISERR(FIND(".",ROUND(K20,2))),"",TEXT(RIGHT(TRUNC(ROUND(K20,2)*10)),"[DBNum2]"))&IF(ISERR(FIND(".0",TEXT(K20,"0.00"))),"角","")&IF(LEFT(RIGHT(ROUND(K20,2),3))=".",TEXT(RIGHT(ROUND(K20,2)),"[DBNum2]")&"分",IF(ROUND(K20,2)=0,"","整")),"零元零",""),"零元","")
公式二：
=CONCATENATE(IF(K20<0,"负",""),TEXT(IF(TRUNC(K20)=0,"",TRUNC(ABS(K20))),"[DBNum2]"),IF(INT(TRUNC(K20))=0,"","元"),TEXT(IF(OR(ABS(K20)<0.1,TRUNC(K20)=K20),"",RIGHT(TRUNC(K20*10),1)),"[DBNum2]"),IF(RIGHT(TRUNC(K20*10),1)="0","","角"),TEXT(IF(RIGHT(TRUNC(K20*100),1)="0","",RIGHT(TRUNC(K20*100),1)),"[DBNum2]"),IF(RIGHT(TRUNC(K20*100),1)="0","","分"))
公式三：
=IF(ROUND(K20,2)=0,"",IF(ROUND(ABS(K20),2)>=1,TEXT(INT(ROUND(ABS(K20),2)),"[DBNum2]")&"元","")&IF(RIGHT(TEXT(K20,".00"),2)*1=0,"整",IF(RIGHT(TEXT(K20,".00"),4)*1>=1,IF(RIGHT(TEXT(K20,".00"),2)*1>9,"","零"),IF(ROUND(ABS(K20),2)>=1,"零",""))&IF(RIGHT(TEXT(K20,".00"),2)*1>9,TEXT(LEFT(RIGHT(TEXT(K20,".00"),2)),"[DBNum2]")&"角","")&IF(RIGHT(TEXT(K20,".00"))*1>0,TEXT(RIGHT(TEXT(K20,".00")),"[DBNum2]")&"分","整")))
公式四：
=IF(K20=0,"",IF(ABS(K20)<0.995,"",TEXT(INT(ROUND(ABS(K20),2)),"[DBNum2]")&"元")&IF(RIGHT(TEXT(K20,".00"),2)*1=0,IF(ABS(K20)<0.005,"","整"),TEXT(IF(ABS(K20)<0.095,"",LEFT(RIGHT(TEXT(K20,".00"),2))),"[dbnum2]")&IF(LEFT(RIGHT(TEXT(K20,".00"),2))*1=0,"","角")&IF(RIGHT(TEXT(K20,".00"))*1=0,"整",TEXT(RIGHT(TEXT(K20,".00")),"[dbnum2]")&"分")))
公式五：
=IF(K20=0,"",IF(ABS(K20)<1,"",TEXT(TRUNC(ABS(K20)),"[DBNum2]")&"元")&IF(RIGHT(TRUNC(K20*100),2)*1=0,IF(ABS(K20)<0.01,"","整"),IF(ABS(K20)<0.1,"",TEXT(RIGHT(TRUNC(K20*10)),"[dbnum2]"))&IF(RIGHT(TRUNC(K20*10))*1=0,"","角")&IF(RIGHT(TRUNC(K20*100))*1=0,"整",TEXT(RIGHT(TRUNC(K20*100)),"[dbnum2]")&"分")))
公式六：
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(IF(K20<0,"负",)&TEXT(INT(ABS(K20)),"[dbnum2];; ")&TEXT(MOD(ABS(K20)*100,100),"[>9][dbnum2]圆0角0分;[=0]圆整;[dbnum2]圆零0分"),"零分","整")," 圆零",)," 圆",)