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

EXCEL金额大小写转换公式大全
2009年05月22日 星期五 23:37

先选中需要转换的单元格，在格式（或者右击设置单元格格式中）——单元格格式——数据——特殊——右边类型：中文小写、中文大写 =NUMBERSTRING($A$1,1) 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(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]")&"分") 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(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