《2022年EXCEL金额大小写转换公式大全.docx》由会员分享，可在线阅读，更多相关《2022年EXCEL金额大小写转换公式大全.docx（10页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、名师归纳总结 精品学习资料 - - - - - - - - - - - - - - -名师整理 优秀资源EXCEL 金额大小写转换公式大全2007-12-15 16:08 先选中需要转换的单元格,在格式 （或者右击设置单元格格式中）单元格格式数据特别右边类型：中文小写、中文大写=NUMBERSTRING$A$1,1 1 =IFA5=0,CONCATENATEIFINTA5=0,TEXTINTA5,DBNum2G/通 用 格 式 元,IFINTMIDRIGHTFIXEDA5,2,1,2,1,1=0,IFINTMIDRIGHTFIXEDA5,2,1,1,1,1=0,IFINTA5=0,零 ,TEX
2、TINTMIDRIGHTFIXEDA5,2,1,2,1,1,DBNum2G/整通用格式角,IFINTMIDRIGHTFIXEDA5,2,1,1,1,1=0,TEXTINTMIDRIGHTFIXEDA5,2,1,1,1,1,DBNum2G/ 2 通用格式分 =IFA51,TEXTTRUNCABSROUNDA5,2,DBNum2&元,&IFISERRFIND.,ROUNDA5,2,TEXTRIGHTTRUNCROUNDA5,2*10,DBNum2&IFISERRFIND.0,TEXTA5,0.00, 角,&IFLEFTRIGHTROUNDA5,2,3=.,TEXTRIGHTROUNDA5,2,DB
3、Num2&分, 整 3 =IFA50, 负,&IFTRUNCROUNDA5,2=0,TEXTTRUNCABSROUNDA5,2,DBNum2&元&IFISERRFIND.,ROUNDA5,2,TEXTRIGHTTRUNCROUNDA5,2*10,DBNum2&IFISERRFIND.0,TEXTA5,0.00, 角,&IFLEFTRIGHTROUNDA5,2,3=.,TEXTRIGHTROUNDA5,2,DBNum2&分, 整 4 =SUBSTITUTESUBSTITUTEIFA50, 負,&TEXTTRUNCABSROUNDA5,2,DBNum2& 元&IFISERRFIND.,ROUNDA
4、5,2,TEXTRIGHTTRUNCROUNDA5,2*10,DBNum2&IFISERRFIND.0,TEXTA5,0.00, 角,&IFLEFTRIGHTROUNDA5,2,3=.,TEXTRIGHTROUNDA5,2,DBNum2&分,IFROUNDA5,2=0,整, 零元零 , 零元 , 细心整理归纳 精选学习资料 - - - - - - - - - - - - - - - 第 1 页,共 5 页 - - - - - - - - - 名师归纳总结 精品学习资料 - - - - - - - - - - - - - - -名师整理 优秀资源5 =IFROUNDA5,20,无效数值,IFRO
5、UNDA5,2=0,零,IFROUNDA5,21,TEXTINTROUNDA5,2,dbnum2&元&IFINTROUNDA5,2*10-INTROUNDA5,2*10=0,IFINTROUNDA5,2*INTROUNDA5,2*100-INTROUNDA5,2*10*10=0,分 零,TEXTINTROUNDA5,2*10-INTROUNDA5,2*10,dbnum2&角&IFINTROUNDA5,2*100-INTROUNDA5,2*10*10=0,整,TEXTINTROUNDA5,2*100-INTROUNDA5,2*10*10,dbnum2&6 =IFISNUMBERA5,IFA50,
6、无效数值,IFA50.005,零,IFA50.995, 零 ,TEXTLEFTRIGHTFIXEDA5,2,2,1,dbnum2& 角&IFRIGHTFIXEDA5,2,1=0, 整,TEXTRIGHTFIXEDA5,2,1,dbnum2& 分,非数值 . 7 =IFISNUMBERA5,IFROUNDA5,20,无效数值,IFROUNDA5,2=0,零,IFROUNDA5,21,TEXTINTROUNDA5,2,dbnum2&元&IFINTROUNDA5,2*10-INTROUNDA5,2*10=0,IFINTROUNDA5,2*100-INTROUNDA5,2*10*10=0, 零,TEX
7、TINTROUNDA5,2*10-INTROUNDA5,2*10,dbnum2& 角&IFINTROUNDA5,2*100-INTROUNDA5,2*10*10=0, 整,TEXTINTROUNDA5,2*100-INTROUNDA5,2*10*10,dbnum2& 分 , 非 数值！ 8 =IFROUNDA5,20,无效数值,IFROUNDA5,2=0,零,IFROUNDA5,21,TEXTINTROUNDA5,2,dbnum2&元&IFINTROUNDA5,2*10-INTROUNDA5,2*10=0,IFINTROUNDA5,2*INTROUNDA5,2*100-INTROUNDA5,2
8、*10*10=0,分 零,TEXTINTROUNDA5,2*10-INTROUNDA5,2*10,dbnum2&角&IFINTROUNDA5,2*100-INTROUNDA5,2*10*10=0,整,TEXTINTROUNDA5,2*100-INTROUNDA5,2*10*10,dbnum2&9 =TEXTINTA5,dbnum2&元 第 2 页,共 5 页 &IFINTA5*10-INTA5*10=0,TEXTINTA5*10-INTA5*10,dbnum2&角&IFINTA5*100-INTA5*10*10=0,整,TEXTINTA5*100-INTA5*10*10,dbnum2&分 细心
9、整理归纳 精选学习资料 - - - - - - - - - - - - - - - - - - - - - - - - 名师归纳总结 精品学习资料 - - - - - - - - - - - - - - -名师整理 优秀资源10 Function BAITURMBDXByVal n As String n as Currency Const cNum As String = 零壹贰叁肆伍陆柒捌玖-万仟佰拾亿仟佰拾万仟佰拾元角分 Const cCha As String = 零仟零佰零拾零零零零零亿零万零元亿万零角零分零整-零零零零零 亿万元亿零整整 Dim sNum As String Dim
10、 i As Long If n 0 And Absn 10000000000000# Then sNum = TrimStrIntAbsn * 100 For i = 1 To LensNum 逐位转换 BAITURMBDX = BAITURMBDX + MidcNum, MidsNum, i, 1 + 1, 1 + MidcNum, 26 - LensNum + i, 1 Next For i = 0 To 11 去掉余外的零 BAITURMBDX = ReplaceBAITURMBDX, MidcCha, i * 2 + 1, 2, MidcCha, i + 26, 1 Next If n
11、 0 Then BAITURMBDX = 负 + BAITURMBDX Else BAITURMBDX = IIfn = 0, 零元 , 溢出 End If End Function 11 Function UpperNumn n as single 数字大写函数 If n 4 Then n = Intn * 100 + 1 / 100 + 0.001 Else n = Intn * 100 / 100 + 0.001 End If Select Case n Case Is 9999999999999.99 UpperNum = 数据不符 MsgBox 金额不能大于 Case Else 99
12、99999999999.99！, vbOKOnly, 出错提示 细心整理归纳 精选学习资料 Const cNum = 零壹贰叁肆伍陆柒捌玖-万仟佰拾亿仟佰拾万仟佰拾元角分 第 3 页,共 5 页 - - - - - - - - - - - - - - - - - - - - - - - - 名师归纳总结 精品学习资料 - - - - - - - - - - - - - - -名师整理 优秀资源Const cCha = 零仟零佰零拾零零零零零亿零万零元亿万零角零分零整-零零零零 零亿万元亿零整整 UpperNum = sNum = s = TrimStrn For i = 1 To Lens -
13、 1 If Mids, i, 1 . Then sNum = sNum + Mids, i, 1 Next i For i = 1 To LensNum 逐位转换 UpperNum = UpperNum + MidcNum, MidsNum, i, 1 + 1, 1 + MidcNum, 26 - LensNum + i, 1 Next i For i = 0 To 11 去掉余外的零 UpperNum = ReplaceUpperNum, MidcCha, i * 2 + 1, 2, MidcCha, i + 26, 1 Next i End Select If 正负判定 = 负 Then
14、UpperNum = 负 & UpperNum End Function 12 Function JEZHX As Range If X = 1 Then If IntX = X Or RoundX, 2 = IntX Then JEZH = Application.WorksheetFunction.TextIntX, DBNUM2 & 元 ElseIf IntX * 10 = X * 10 Or IntX * 10 = RoundX, 2 * 10 Then JEZH = Application.WorksheetFunction.TextIntX, DBNUM2 & 元 & Applic
15、ation.WorksheetFunction.TextRightRoundX, 2, 1, DBNUM2 & 角 Else JEZH = Application.WorksheetFunction.TextIntX, DBNUM2 & 元 & Application.WorksheetFunction.TextLeftRightRoundX, 2, 2, 1, DBNUM2 & 角 & Application.WorksheetFunction.TextRightRoundX, 2, 1, DBNUM2 & 分 End If ElseIf X = 0 Then JEZH = Applicat
16、ion.WorksheetFunction.TextIntX, DBNUM2 & 元 ElseIf X 0 Then If IntX * 10 = X * 10 Then JEZH = Application.WorksheetFunction.TextRightX, 1, DBNUM2 & 角 Else JEZH = Application.WorksheetFunction.TextLeftRightRoundX, 2, 2, 1, 第 4 页,共 5 页 DBNUM2 & 角 & Application.WorksheetFunction.TextRightRoundX, 2, 1, 细
17、心整理归纳 精选学习资料 - - - - - - - - - - - - - - - - - - - - - - - - 名师归纳总结 精品学习资料 - - - - - - - - - - - - - - -DBNUM2 & 分 名师整理优秀资源End If Else If IntX = X Or RoundAbsX, 2 = IntAbsX Then JEZH = 负 & Application.WorksheetFunction.TextIntAbsX, DBNUM2 & 元 ElseIf IntX * 10 = X * 10 Then JEZH = 负 & Application.Wor
18、ksheetFunction.TextIntAbsX, DBNUM2 & 元 & Application.WorksheetFunction.TextRightX, 1, DBNUM2 & 角 Else JEZH = 负 & Application.WorksheetFunction.TextIntAbsX, DBNUM2 & 元 & Application.WorksheetFunction.TextLeftRightRoundX, 2, 2, 1, DBNUM2 & 角 & Application.WorksheetFunction.TextRightRoundX, 2, 1, DBNUM2 & 分 End If End If End Function 细心整理归纳 精选学习资料 - - - - - - - - - - - - - - - 第 5 页,共 5 页 - - - - - - - - -