excel自动生成金额大写

excel自动生成金额大写

1
=IF(A5=0,"",CONCATENATE(IF(INT(A5)=0,"",TEXT(INT(A5),"[DBNum2]G/通用格式元")),IF(INT(MID(RIGHT(FIXED(A5,2,1),2),1,1))=0,IF(INT(MID(RIGHT(FIXED(A5,2,1),1),1,1))=0,"",IF(INT(A5)=0,"","零")),TEXT(INT(MID(RIGHT(FIXED(A5,2,1),2),1,1)),"[DBNum2]G/通用格式角")),IF(INT(MID(RIGHT(FIXED(A5,2,1),1),1,1))=0,"整",TEXT(INT(MID(RIGHT(FIXED(A5,2,1),1),1,1)),"[DBNum2]G/通用格式分"))))

2
=IF(A5<0,"負","")&IF(ABS(A5)>1,TEXT(TRUNC(ABS(ROUND(A5,2))),"[DBNum2]")&"元","")&IF(ISERR(FIND(".",ROUND(A5,2))),"",TEXT(RIGHT(TRUNC(ROUND(A5,2)*10)),"[DBNum2]"))&IF(ISERR(FIND(".0",TEXT(A5,"0.00"))),"角","")&IF(LEFT(RIGHT(ROUND(A5,2),3))=".",TEXT(RIGHT(ROUND(A5,2)),"[DBNum2]")&"分","整")

3
=IF(A5<0,"负","")&IF(TRUNC(ROUND(A5,2))=0,"",TEXT(TRUNC(ABS(ROUND(A5,2))),"[DBNum2]")&"元")&IF(ISERR(FIND(".",ROUND(A5,2))),"",TEXT(RIGHT(TRUNC(ROUND(A5,2)*10)),"[DBNum2]"))&IF(ISERR(FIND(".0",TEXT(A5,"0.00"))),"角","")&IF(LEFT(RIGHT(ROUND(A5,2),3))=".",TEXT(RIGHT(ROUND(A5,2)),"[DBNum2]")&"分","整")


4
=SUBSTITUTE(SUBSTITUTE(IF(A5<0,"負","")&TEXT(TRUNC(ABS(ROUND(A5,2))),"[DBNum2]")&"元"&IF(ISERR(FIND(".",ROUND(A5,2))),"",TEXT(RIGHT(TRUNC(ROUND(A5,2)*10)),"[DBNum2]"))&IF(ISERR(FIND(".0",TEXT(A5,"0.00"))),"角","")&IF(LEFT(RIGHT(ROUND(A5,2),3))=".",TEXT(RIGHT(ROUND(A5,2)),"[DBNum2]")&"分",IF(ROUND(A5,2)=0,"","整")),"零元零",""),"零元","")

5
=IF(ROUND(A5,2)<0,"无效数值",IF(ROUND(A5,2)=0,"零",IF(ROUND(A5,2)<1,"",TEXT(INT(ROUND(A5,2)),"[dbnum2]")&"元")&IF(INT(ROUND(A5,2)*10)-INT(ROUND(A5,2))*10=0,IF(INT(ROUND(A5,2))*(INT(ROUND(A5,2)*100)-INT(ROUND(A5,2)*10)*10)=0,"","零"),TEXT(INT(ROUND(A5,2)*10)-INT(ROUND(A5,2))*10,"[dbnum2]")&"角")&IF((INT(ROUND(A5,2)*100)-INT(ROUND(A5,2)*10)*10)=0,"整",TEXT((INT(ROUND(A5,2)*100)-INT(ROUND(A5,2)*10)*10),"[dbnum2]")&"分")))


6
=IF(ISNUMBER(A5),IF(A5<0,"无效数值",IF(A5<0.005,"零",IF(A5<0.995,"",TEXT(INT(A5+0.005),"[dbnum2]")&"元")&IF(LEFT(RIGHT(FIXED(A5,2),2),1)="0",IF(RIGHT(FIXED(A5,2),1)="0","",IF(A5>0.995,"零","")),TEXT(LEFT(RIGHT(FIXED(A5,2),2),1),"[dbnum2]")&"角")&IF(RIGHT(FIXED(A5,2),1)="0","整",TEXT(RIGHT(FIXED(A5,2),1),"[dbnum2]")&"分"))),"非数值!")

7
=IF(ISNUMBER(A5),IF(ROUND(A5,2)<0,"无效数值",IF(ROUND(A5,2)=0,"零",IF(ROUND(A5,2)<1,"",TEXT(INT(ROUND(A5,2)),"[dbnum2]")&"元")&IF(INT(ROUND(A5,2)*10)-INT(ROUND(A5,2))*10=0,IF((INT(ROUND(A5,2)*100)-INT(ROUND(A5,2)*10)*10)=0,"","零"),TEXT(INT(ROUND(A5,2)*10)-INT(ROUND(A5,2))*10,"[dbnum2]")&"角")&IF((INT(ROUND(A5,2)*100)-INT(ROUND(A5,2)*10)*10)=0,"整",TEXT((INT(ROUND(A5,2)*100)-INT(ROUND(A5,2)*10)*10),"[dbnum2]")&"分"))),"非数值！！！")

8
=IF(ROUND(A5,2)<0,"无效数值",IF(ROUND(A5,2)=0,"零",IF(ROUND(A5,2)<1,"",TEXT(INT(ROUND(A5,2)),"[dbnum2]")&"元")&IF(INT(ROUND(A5,2)*10)-INT(ROUND(A5,2))*10=0,IF(INT(ROUND(A5,2))*(INT(ROUND(A5,2)*100)-INT(ROUND(A5,2)*10)*10)=0,"","零"),TEXT(INT(ROUND(A5,2)*10)-
INT(ROUND(A5,2))*10,"[dbnum2]")&"角")&IF((INT(ROUND(A5,2)*100)-INT(ROUND(A5,2)*10)*10)=0,"整",TEXT((INT(ROUN

excel自动生成金额大写

D(A5,2)*100)-INT(ROUND(A5,2)*10)*10),"[dbnum2]")&"分")))


9
=TEXT(INT(A5),"[dbnum2]")&"元"&IF(INT(A5*10)-INT(A5)*10=0,"",TEXT(INT(A5*10)-INT(A5)*10,"[dbnum2]")&"角")&IF(INT(A5*100)-INT(A5*10)*10=0,"整",TEXT(INT(A5*100)-INT(A5*10)*10,"[dbnum2]")&"分")

10

Function BAITURMBDX(ByVal n) As String 'n as Currency
Const cNum As String = "零壹贰叁肆伍陆柒捌玖-万仟佰拾亿仟佰拾万仟佰拾元角分"
Const cCha As String = "零仟零佰零拾零零零零零亿零万零元亿万零角零分零整-零零零零零亿万元亿零整整"
Dim sNum As String
Dim i As Long

If (n <> 0) And (Abs(n) < 10000000000000#) Then
sNum = Trim(Str(Int(Abs(n) * 100)))
For i = 1 To Len(sNum) '逐位转换
BAITURMBDX = BAITURMBDX + Mid(cNum, (Mid(sNum, i, 1)) + 1, 1) + Mid(cNum, 26 - Len(sNum) + i, 1)
Next
For i = 0 To 11 '去掉多余的零
BAITURMBDX = Replace(BAITURMBDX, Mid(cCha, i * 2 + 1, 2), Mid(cCha, i + 26, 1))
Next
If n < 0 Then BAITURMBDX = "(负)" + BAITURMBDX
Else
BAITURMBDX = IIf(n = 0, "零元", "溢出")
End If
End Function

11

Function UpperNum(n) 'n as single '数字大写函数
If n < 0 Then
正负判断 = "负"
n = -n
End If
n = n + 0.0001
If Int(n * 1000) - Int(n * 100) * 10 > 4 Then
n = (Int(n * 100) + 1) / 100 + 0.001
Else
n = Int( …… 此处隐藏：3865字，全部文档内容请下载后查看。喜欢就下载吧 ……