user 发表于 2011-2-22 08:40:22

如何把Excel中的数字自动转换成大写?

excel 数字小写转大写的函数公式
大写：设置单元格格式》数字》特殊》中文大写数字OK
1
=IF(A5=0,"",CONCATENATE(IF(INT(A5)=0,"",TEXT(INT(A5),"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)),"G/通用格式角")),IF(INT(MID(RIGHT(FIXED(A5,2,1),1),1,1))=0,"整",TEXT(INT(MID(RIGHT(FIXED(A5,2,1),1),1,1)),"G/通用格式分"))))

2
=IF(A5<0,"負","")&IF(ABS(A5)>1,TEXT(TRUNC(ABS(ROUND(A5,2))),"")&"元","")&IF(ISERR(FIND(".",ROUND(A5,2))),"",TEXT(RIGHT(TRUNC(ROUND(A5,2)*10)),""))&IF(ISERR(FIND(".0",TEXT(A5,"0.00"))),"角","")&IF(LEFT(RIGHT(ROUND(A5,2),3))=".",TEXT(RIGHT(ROUND(A5,2)),"")&"分","整")

3
=IF(A5<0,"负","")&IF(TRUNC(ROUND(A5,2))=0,"",TEXT(TRUNC(ABS(ROUND(A5,2))),"")&"元")&IF(ISERR(FIND(".",ROUND(A5,2))),"",TEXT(RIGHT(TRUNC(ROUND(A5,2)*10)),""))&IF(ISERR(FIND(".0",TEXT(A5,"0.00"))),"角","")&IF(LEFT(RIGHT(ROUND(A5,2),3))=".",TEXT(RIGHT(ROUND(A5,2)),"")&"分","整")


4
=SUBSTITUTE(SUBSTITUTE(IF(A5<0,"負","")&TEXT(TRUNC(ABS(ROUND(A5,2))),"")&"元"&IF(ISERR(FIND(".",ROUND(A5,2))),"",TEXT(RIGHT(TRUNC(ROUND(A5,2)*10)),""))&IF(ISERR(FIND(".0",TEXT(A5,"0.00"))),"角","")&IF(LEFT(RIGHT(ROUND(A5,2),3))=".",TEXT(RIGHT(ROUND(A5,2)),"")&"分",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)),"")&"元")&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,"")&"角")&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),"")&"分")))


6
=IF(ISNUMBER(A5),IF(A5<0,"无效数值",IF(A5<0.005,"零",IF(A5<0.995,"",TEXT(INT(A5+0.005),"")&"元")&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),"")&"角")&IF(RIGHT(FIXED(A5,2),1)="0","整",TEXT(RIGHT(FIXED(A5,2),1),"")&"分"))),"非数值!")

7
=IF(ISNUMBER(A5),IF(ROUND(A5,2)<0,"无效数值",IF(ROUND(A5,2)=0,"零",IF(ROUND(A5,2)<1,"",TEXT(INT(ROUND(A5,2)),"")&"元")&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,"")&"角")&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),"")&"分"))),"非数值！！！")

8
=IF(ROUND(A5,2)<0,"无效数值",IF(ROUND(A5,2)=0,"零",IF(ROUND(A5,2)<1,"",TEXT(INT(ROUND(A5,2)),"")&"元")&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,"")&"角")&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),"")&"分")))


9
=TEXT(INT(A5),"")&"元"&IF(INT(A5*10)-INT(A5)*10=0,"",TEXT(INT(A5*10)-INT(A5)*10,"")&"角")&IF(INT(A5*100)-INT(A5*10)*10=0,"整",TEXT(INT(A5*100)-INT(A5*10)*10,"")&"分")

user 发表于 2011-2-22 08:40:42

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

user 发表于 2011-2-22 08:41:15

11

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), "") & "元"

    ElseIf Int(X * 10) = X * 10 Or Int(X * 10) = Round(X, 2) * 10 Then
       JEZH = Application.WorksheetFunction.Text(Int(X), "") & "元" & Application.WorksheetFunction.Text(Right(Round(X, 2), 1), "") & "角"
    Else
       JEZH = Application.WorksheetFunction.Text(Int(X), "") & "元" & Application.WorksheetFunction.Text(Left(Right(Round(X, 2), 2), 1), "") & "角" & Application.WorksheetFunction.Text(Right(Round(X, 2), 1), "") & "分"
    End If
ElseIf X = 0 Then
    JEZH = Application.WorksheetFunction.Text(Int(X), "") & "元"
   
ElseIf X < 1 And X > 0 Then

      If Int(X * 10) = X * 10 Then
       JEZH = Application.WorksheetFunction.Text(Right(X, 1), "") & "角"
      Else
       JEZH = Application.WorksheetFunction.Text(Left(Right(Round(X, 2), 2), 1), "") & "角" & Application.WorksheetFunction.Text(Right(Round(X, 2), 1), "") & "分"
      End If
Else
    If Int(X) = X Or Round(Abs(X), 2) = Int(Abs(X)) Then
       JEZH = "负" & Application.WorksheetFunction.Text(Int(Abs(X)), "") & "元"

    ElseIf Int(X * 10) = X * 10 Then
       JEZH = "负" & Application.WorksheetFunction.Text(Int(Abs(X)), "") & "元" & Application.WorksheetFunction.Text(Right(X, 1), "") & "角"
    Else
       JEZH = "负" & Application.WorksheetFunction.Text(Int(Abs(X)), "") & "元" & Application.WorksheetFunction.Text(Left(Right(Round(X, 2), 2), 1), "") & "角" & Application.WorksheetFunction.Text(Right(Round(X, 2), 1), "") & "分"
    End If
End If
End Function

user 发表于 2011-2-22 08:41:34

12
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

九等莲花 发表于 2011-5-17 10:51:53

不错的。谢谢喽

弱智老师 发表于 2011-5-17 12:04:44

对于搞财务的很有用的，可以减少很多错误，记信不好更适合的。
页: [1]
查看完整版本: 如何把Excel中的数字自动转换成大写?