- =IF(ABS(K18)=0,"",IF(K18<0,"负",)&IF(INT(ABS(K18))=ABS(K18),TEXT(INT(ABS(K18)),"[dbnum2]")&"元整",TEXT(INT(ABS(K18)),"[dbnum2]")&"元")&IF(INT(ABS(K18)*10)-INT(ABS(K18))*10,TEXT(INT(ABS(K18)*10)-INT(ABS(K18))*10,"[dbnum2]")&"角",IF(INT(ABS(K18))=ABS(K18),,IF(ABS(K18)<0.1,,"零")))&IF(ROUND(ABS(K18)*100-INT(ABS(K18)*10)*10,),TEXT(ROUND(ABS(K18)*100-INT(ABS(K18)*10)*10,),"[dbnum2]")&"分",""))
复制代码- =CONCATENATE(IF(K18<0,"负",""),TEXT(IF(TRUNC(K18)=0,"",TRUNC(ABS(K18))),"[DBNum2]"),IF(INT(TRUNC(K18))=0,"","元"),TEXT(IF(OR(ABS(K18)<0.1,TRUNC(K18)=K18),"",RIGHT(TRUNC(K18*10),1)),"[DBNum2]"),IF(RIGHT(TRUNC(K18*10),1)="0","","角"),TEXT(IF(RIGHT(TRUNC(K18*100),1)="0","",RIGHT(TRUNC(K18*100),1)),"[DBNum2]"),IF(RIGHT(TRUNC(K18*100),1)="0","","分"))
复制代码- =IF(ROUND(K18,2)=0,"",IF(ROUND(ABS(K18),2)>=1,TEXT(INT(ROUND(ABS(K18),2)),"[DBNum2]")&"元","")&IF(RIGHT(TEXT(K18,".00"),2)*1=0,"整",IF(RIGHT(TEXT(K18,".00"),4)*1>=1,IF(RIGHT(TEXT(K18,".00"),2)*1>9,"","零"),IF(ROUND(ABS(K18),2)>=1,"零",""))&IF(RIGHT(TEXT(K18,".00"),2)*1>9,TEXT(LEFT(RIGHT(TEXT(K18,".00"),2)),"[DBNum2]")&"角","")&IF(RIGHT(TEXT(K18,".00"))*1>0,TEXT(RIGHT(TEXT(K18,".00")),"[DBNum2]")&"分","整")))
复制代码- =SUBSTITUTE(SUBSTITUTE(IF(K18<0,"负","")&TEXT(TRUNC(ABS(ROUND(K18,2))),"[DBNum2]")&"元"&IF(ISERR(FIND(".",ROUND(K18,2))),"",TEXT(RIGHT(TRUNC(ROUND(K18,2)*10)),"[DBNum2]"))&IF(ISERR(FIND(".0",TEXT(K18,"0.00"))),"角","")&IF(LEFT(RIGHT(ROUND(K18,2),3))=".",TEXT(RIGHT(ROUND(K18,2)),"[DBNum2]")&"分",IF(ROUND(K18,2)=0,"","整")),"零元零",""),"零元","")
复制代码
|