excel表格金额大小写转换

excel表格金额大小写转换 篇一:Excel表中小写金额直接转换成大写金额的 Excel表中小写金额直接转换成大写金额的公式: 一、 =IF(OR(A1<0,A1=),,IF(VALUE(LEFT(A1,1))=0,,NUMBERSTRING(INT(A1),2)& 元)&IF(ISERROR(FIND(.,A1)),整 零,IF(VALUE(MID(A1,FIND(.,A1)+1,1))=0, ,IF(ISERROR(FIND(.,A1)),,NUMBERSTRING(MID(A1,FIND(.,A1)+1,1),2)& 角)))&IF(ISERROR(FIND(.,A1)),,IF(LEN(ROUND(A1,2))=(FIND(.,A1)+2),NUMBERSTRING(MID(ROUND(A1,2),FIND(.,A1)+2,1),2)& 分,)))&IF(ISERROR(FIND(.,A1)),,) 二、 假定你要在B1输入阿拉佰数字,C1转换成中文大写金额 (含元角分),请在C1单元格输入如下公式: 1 =SUBSTITUTE(SUBSTITUTE(IF(-RMB(B1),IF(B10,,)&TEXT(INT(ABS(B1)+0.5%),[dbnum2]G/通用格负式 元;;)&TEXT(RIGHT(RMB(B1,2),2),[dbnum2]0角0 分;;整),),零角,IF(B1 <1,,零)),零分,整) 三、 用公式就可以，我不懂做会计的如何用大写表示如128.40 这样的角 不为0但分为0的值，是“壹佰贰拾捌元肆角整”还是“壹佰 贰拾捌元肆角”, 如果是“壹佰贰拾捌元肆角”，请用这个公式: =TEXT(INT(B2),[DBNUM2])&元 &IF(INT(B2)=B2, 整,(IF(INT(B2*10)-INT(B2)*100,TEXT(INT(B2*10)-INT(B2)*10,[DBNUM2])&角, 零)&IF(B2*100-INT(B2*10)*100,TEXT(B2*100-INT(B2*10)*10,[dbnum2])&分,))) 如果是“壹佰贰拾捌元肆角整”，请用这个公式: =TEXT(INT(B2),[DBNUM2])&元 &(IF(INT(B2*10)-INT(B2)*100,TEXT(INT(B2*10)-INT(B2)*10,[DBNUM2])&角,IF(B2*100-INT(B2*10)*100,零,))&IF(B2*100-INT(B2*10)*100,TEXT(B2*100-INT(B2*10)*10,[dbnum2])&分,整)) 2 说明: 1、将以上公式中的B2换成你要换算的值或对应的单元 格。 2、公式中我只考虑了两位小数，后面如果还有小数，将 被忽略。 excel2007 =IF(C10<0,金额为负无效,IF(OR(C10=0,C10=),零元 整,IF(C10<1,,TEXT(INT(C10),[dbnum2]G/通用格 式)&元))) & IF(INT(C10*10)-INT(C10)*10=0,IF(INT(C10)*(INT(C10*1 00)-INT(C10*10)*10)=0,, ),TEXT(INT(C10*10)-INT(C10)*10,[dbnum2])& )&IF((INT(C10*100)-INT(C10*10)*10)=0, &qu(转载于:www.XltkWJ.Com 小 龙文档 网:excel表格 金额大小写转 换)ot;,TEXT((INT(C10*100)-INT(C10*10)*10),[dbnum2])& amp;分) 零角整 篇二:Excel电子表格大小写金额转换 Excel电子表格大小写金额转换(实用) 2011-05-30 23:03:47| 分类: 电脑知识 | 标签: |字号 大中小 订阅 方法一: 3 在单元格A2中输入小写数字123.12 B2处输入以下公式 =SUBSTITUTE(SUBSTITUTE(IF(A2<0, 负,)&TEXT(TRUNC(ABS(ROUND(A2,2))),[DBNum2])&元 &IF(ISERR(FIND(.,ROUND(A2,2))),,TEXT(RIGHT(TRUNC(ROUND(A2,2)*10)),[DBNum2]))&IF(IS ERR(FIND(.0,TEXT(A2,0.00))),角 ,)&IF(LEFT(RIGHT(ROUND(A2,2),3))=.,TEXT(RIGHT(ROUND(A2,2)),[DBNum2])&分 ,IF(ROUND(A2,2)=0,,整)),零元零,),零元,) 方法二: =IF(A2=,,IF(A2<0, 负,)&IF(INT(A2),TEXT(INT(ABS(A2)),[dbnum2])&元 ,)&IF(INT(ABS(A2)*10)-INT(ABS(A2))*10,TEXT(INT(ABS(A2)*10)-INT(ABS(A2))*10,[dbnum2])&角 ,IF(INT(ABS(A2))=ABS(C3),,IF(ABS(A2)<0.1,,零 )))&IF(ROUND(ABS(A2)*100-INT(ABS(A2)*10)*10,),TEXT(ROUND(ABS(A2)*100-INT(ABS(A2)*10)*10 ,),[dbnum2])&分,整)) 方法三: 4 =IF((INT(A2*10)-INT(A2)*10)=0,TEXT(INT(A2),[DBNum2]G/通用格式)&元 &IF((INT(A2*100)-INT((A2)*10)*10)=0,整,零 &TEXT(INT(A2*100)-INT(A2*10)*10,[DBNum2]G/通用格式)&分 ),TEXT(INT(A2),[DBNum2]G/通用格式)&元 &IF((INT(A2*100)-INT((A2)*10)*10)=0,TEXT((INT(A2*10)-INT(A2)*10),[DBNum2]G/通用格 式)&角 整,TEXT((INT(A2*10)-INT(A2)*10),[DBNum2]G/通用格 式)&角 &TEXT(INT(A2*100)-INT(A2*10)*10,[DBNum2]G/通用格式)&分)) 方法四: =IF(A2-INT(A2)=0,TEXT(INT(A2),[DBNum2]G/通用格 式)&圆整 ,TEXT(INT(A2),[DBNum2]G/通用格式)&圆 &TEXT(INT((A2-INT(A2))*10),[DBNum2]G/通 用格式)&角 &TEXT(INT((A2*10-INT(A2*10))*10),[DBNum2]G/通 5 用格式)&分) 方法五: =IF((A2-INT(A2))=0,TEXT(A2,[DBNUM2])&元整 ,IF(INT(A2*10)-A2*10=0,TEXT(INT(A2),[DBNUM2])&a mp;元 &TEXT((INT(A2*10)-INT(A2)*10),[DBNUM2])& 角整,TEXT(INT(A2),[DBNUM2])&元 &IF(INT(A2*10)-INT(A2)*10=0,零,TEXT(INT(A2*10)-INT(A2)*10,[DBNUM2])&角 )&TEXT(RIGHT(A2,1),[DBNUM2])&分)) 篇三:EXCEL金额大小写转换公式大全 EXCEL金额大小写转换公式大全 2007-12-15 16:08 先选中需要转换的单元格，在格式(或者右击设置单元格 格式中)——单元格格式——数据——特殊——右边类型:中文小写、中文大写 =NUMBERSTRING($A$1,1) 1 =IF(A5=0,,CONCATENATE(IF(INT(A5)=0,,TEXT(INT(A5) ,[DBNum2]G/通用格式 6 元)),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(ISER 7 R(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,,整)),零元零,),零元,) =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) 8 *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(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, 9 [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])& 10 角)&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 11 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 12 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 13 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]) & 元 14 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 15 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 16