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

EXCEL金额大小写转换公式大全2007-12-15 16:08先选中需要转换的单元格，在格式（或者右击设置单元格格式中）单元格格式数据特殊右边类型：中文小写、中文大写=NUMBERSTRING($A$1,1)1=IF(A5=0,CONCATENATE(IF(INT(A5)=0,TEXT(INT(A5),DBNum2G/通用格式元),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),DBNum2G/通用格式角),IF(INT(MID(RIGHT(FIXED(A5,2,1),1),1,1)=0,整,TEXT(INT(MID(RIGHT(FIXED(A5,2,1),1),1,1),DBNum2G/通用格式分)2=IF(A51,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(A50,负,)&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(A50,負,)&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(A50,无效数值,IF(A50.005,零,IF(A50.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)&分)10Function BAITURMBDX(ByVal n) As String n as CurrencyConst cNum As String = 零壹贰叁肆伍陆柒捌玖-万仟佰拾亿仟佰拾万仟佰拾元角分Const cCha As String = 零仟零佰零拾零零零零零亿零万零元亿万零角零分零整-零零零零零亿万元亿零整整Dim sNum As StringDim i As LongIf (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 = (负) + BAITURMBDXElse BAITURMBDX = IIf(n = 0, 零元, 溢出)End IfEnd Function11Function UpperNum(n) n as single 数字大写函数If n 4 Then n = (Int(n * 100) + 1) / 100 + 0.001Else n = Int(n * 100) / 100 + 0.001End 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 = 负 & UpperNumEnd Function12Function 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 IfElseIf X = 0 Then JEZH = Application.WorksheetFunction.Text(Int(X), DBNUM2) & 元 ElseIf 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 IfElse 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(