EXCEL金额大小写转换公式大全

EXCEL金额大小写转换公式大全
2007-12-15 16:08
先选中需要转换的单元格，在格式（或者右击设置单元格格式中）——单元格格式——数据——特殊——右边类型：中文小写、中文大写
=NUMBERSTRING($A$1,1)
1
=IF(A5=0,"",CONCATENA TE(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)),"[D BNum2]"))&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)),"[DBN um2]"))&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)),"[DBN um2]"))&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,"","整")),"零元零",""),"零元","")
=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(ROU ND(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(R OUND(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(ROU ND(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]")&"分")))
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]")&"分")
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(n * 100) / 100 + 0.001
End If
Select Case n
Case Is > 9999999999999.99
UpperNum = "数据不符"
MsgBox "金额不能大于9999999999999.99！", vbOKOnly, "出错提示"
Case Else
Const cNum = "零壹贰叁肆伍陆柒捌玖-万仟佰拾亿仟佰拾万仟佰拾元角分"
Const cCha = "零仟零佰零拾零零零零零亿零万零元亿万零角零分零整-零零零零零亿万元亿零整整"
UpperNum = ""
sNum = ""
s = Trim(Str(n))
For i = 1 To Len(s) - 1
If Mid(s, i, 1) <> "." Then sNum = sNum + Mid(s, i, 1)
Next i
For i = 1 To Len(sNum) '逐位转换
UpperNum = UpperNum + Mid(cNum, (Mid(sNum, i, 1)) + 1, 1) + Mid(cNum, 26 - Len(sNum) + i, 1)
Next i
For i = 0 To 11 '去掉多余的零
UpperNum = Replace(UpperNum, Mid(cCha, i * 2 + 1, 2), Mid(cCha, i + 26, 1))
Next i
End Select
If 正负判断= "负" Then UpperNum = "负" & UpperNum
End Function
12
Function JEZH(X As Range)
If X >= 1 Then
If Int(X) = X Or Round(X, 2) = Int(X) Then
JEZH = Application.WorksheetFunction.Text(Int(X), "[DBNUM2]") & "元"
ElseIf Int(X * 10) = X * 10 Or Int(X * 10) = Round(X, 2) * 10 Then
JEZH = Application.WorksheetFunction.Text(Int(X), "[DBNUM2]") & "元" & Application.WorksheetFunction.Text(Right(Round(X, 2), 1), "[DBNUM2]") & "角"
Else
JEZH = Application.WorksheetFunction.Text(Int(X), "[DBNUM2]") & "元" & Application.WorksheetFunction.Text(Left(Right(Round(X, 2), 2), 1), "[DBNUM2]") & "角" & Application.WorksheetFunction.Text(Right(Round(X, 2), 1), "[DBNUM2]") & "分"
End If
ElseIf X = 0 Then
JEZH = Application.WorksheetFunction.Text(Int(X), "[DBNUM2]") & "元"
ElseIf X < 1 And X > 0 Then
If Int(X * 10) = X * 10 Then
JEZH = Application.WorksheetFunction.Text(Right(X, 1), "[DBNUM2]") & "角"
Else
JEZH = Application.WorksheetFunction.Text(Left(Right(Round(X, 2), 2), 1), "[DBNUM2]") & "角" & Application.WorksheetFunction.Text(Right(Round(X, 2), 1),
"[DBNUM2]") & "分"
End If
Else
If Int(X) = X Or Round(Abs(X), 2) = Int(Abs(X)) Then
JEZH = "负" & Application.WorksheetFunction.Text(Int(Abs(X)), "[DBNUM2]") & "元"
ElseIf Int(X * 10) = X * 10 Then
JEZH = "负" & Application.WorksheetFunction.Text(Int(Abs(X)), "[DBNUM2]") & "元" & Application.WorksheetFunction.Text(Right(X, 1), "[DBNUM2]") & "角"
Else
JEZH = "负" & Application.WorksheetFunction.Text(Int(Abs(X)), "[DBNUM2]") & "元" & Application.WorksheetFunction.Text(Left(Right(Round(X, 2), 2), 1), "[DBNUM2]") & "角" & Application.WorksheetFunction.Text(Right(Round(X, 2), 1), "[DBNUM2]") & "分"
End If
End If
End Function。