Question

在EXCEL中如何将小写金额用函数转换成大写金额，谢谢

Answer 1

1、=SUBSTITUTE(SUBSTITUTE(IF(A11<0,"负","")&TEXT(TRUNC(ABS(ROUND(A11,2))),"[DBNum2]")&"元"&IF(ISERR(FIND(".",ROUND(A11,2))),"",TEXT(RIGHT(TRUNC(ROUND(A11,2)*10)),"[DBNum2]"))&IF(ISERR(FIND(".0",TEXT(A11,"0.00"))),"角","")&IF(LEFT(RIGHT(ROUND(A11,2),3))=".",TEXT(RIGHT(ROUND(A11,2)),"[DBNum2]")&"分",IF(ROUND(A11,2)=0,"","整")),"零元零",""),"零元","零")
2、=IF(ROUND(A12,2)<0,"无效数值",IF(ROUND(A12,2)=0,"零",IF(ROUND(A12,2)<1,"",TEXT(INT(ROUND(A12,2)),"[dbnum2]")&"元")&IF(INT(ROUND(A12,2)*10)-INT(ROUND(A12,2))*10=0,IF(INT(ROUND(A12,2))*(INT(ROUND(A12,2)*100)-INT(ROUND(A12,2)*10)*10)=0,"","零"),TEXT(INT(ROUND(A12,2)*10)-INT(ROUND(A12,2))*10,"[dbnum2]")&"角")&IF((INT(ROUND(A12,2)*100)-INT(ROUND(A12,2)*10)*10)=0,"整",TEXT((INT(ROUND(A12,2)*100)-INT(ROUND(A12,2)*10)*10),"[dbnum2]")&"分")))
3、=IF(A13=0,"零",IF(AND(A13<1,A13>0.09,A13*10-INT(A13*10)=0),NUMBERSTRING((INT(A13*10)/10-INT(A13))*10,2)&"角整",IF(AND(A13<1,A13>0.09,A13*10-INT(A13*10)<>0),NUMBERSTRING((INT(A13*10)/10-INT(A13))*10,2)&"角"&NUMBERSTRING((INT(A13*100)/100-INT(A13*10)/10)*100,2)&"分",IF(A13<=0.09,NUMBERSTRING((INT(A13*100)/100-INT(A13*10)/10)*100,2)&"分",IF(A13-INT(A13)=0,NUMBERSTRING(A13,2)&"元整",IF(AND(A13-INT(A13*10)/10=0,A13>1),NUMBERSTRING(INT(A13),2)&"元"&NUMBERSTRING((INT(A13*10)/10-INT(A13))*10,2)&"角整",)))))&IF(A13<0,"无效数值",IF(AND(A13-INT(A13*10)/10>0,INT(A13*10)-INT(A13)*10>0,A13>1),NUMBERSTRING(INT(A13),2)&"元"&NUMBERSTRING((INT(A13*10)/10-INT(A13))*10,2)&"角"&NUMBERSTRING((INT(A13*100)/100-INT(A13*10)/10)*100,2)&"分",IF(AND(A13-INT(A13*10)/10>0,INT(A13*10)-INT(A13)*10=0,A13>1),NUMBERSTRING(INT(A13),2)&"元"&"零"&NUMBERSTRING((INT(A13*100)/100-INT(A13*10)/10)*100,2)&"分",""))))

Answer 2

=SUBSTITUTE(SUBSTITUTE(IF(A1>-0.5%,,"负")&TEXT(INT(FIXED(ABS(A1))),"[dbnum2]G/通用格式元;;")&TEXT(RIGHT(FIXED(A1),2),"[dbnum2]0角0分;;"&IF(ABS(A1)>1%,"整",)),"零角",IF(ABS(A1)<1,,"零")),"零分","整")

Answer 3

=IF(B1<0,"金额为负无效",(IF(B1=0,"(人民币)零元整",IF(B1<0.1,"(人民币)零元零角"&NUMBERSTRING(INT(B1*100),2)&"分",IF(B1<1,"（人民币）零元"&NUMBERSTRING(INT(B1*10),2)&"角"&NUMBERSTRING((INT(B1*100)-INT(B1*10)*10),2)&"分","（人民币）"&NUMBERSTRING(INT(B1),2))&"元"&NUMBERSTRING(INT((B1-INT(B1))*10),2)&"角"&NUMBERSTRING((INT((B1*10-INT(B1*10))*10)),2)&"分"))))

Answer 4

=IF(OR(A1="",A1=0),"",TEXT(INT(A1),"[dbnum2];;")&IF(INT(A1)=0,"",IF(INT(A1)=A1,"元整","元"))&IF(A1*100=--RIGHT(A1),"",IF(INT(A1)<>A1,TEXT(--RIGHT(INT(A1*10)),"[=0]零;[>0][dbnum2]#角"),""))&TEXT(--RIGHT(INT(A1*100)),"[dbnum2]#分;;"))

Answer 5

EXCEL是需要函数的，最新的WPS表格中，设置单元格格式，选择特殊格式中的对应格式就可以了