Excel中将小写金额转换为大写金额的5种方法

创作时间:

作者:

@小白创作中心

Excel中将小写金额转换为大写金额的5种方法

引用

来源

https://www.huoban.com/news/post/154690.html

在Excel中将小写金额转换为大写金额是一个常见的需求，但Excel本身并没有直接提供这样的功能。本文将介绍5种简单而高效的方法，帮助你轻松解决这个问题。无论你是Excel初学者还是专家，都能找到适合自己的解决方案。

方法一：使用VBA宏函数

如果你熟悉VBA（Visual Basic for Applications），那么可以使用VBA宏函数来实现金额转换。以下是一个示例的VBA宏函数代码：

Function ConvertToChinese(ByVal MyNumber) 
    Dim StartValue
    Dim DecimalPlace, Count
    ReDim Place(9) As String
    Place(2) = " 仟 "
    Place(3) = " 佰 "
    Place(4) = " 拾 "
    Place(5) = " 亿 "
    Place(6) = " 仟 "
    Place(7) = " 佰 "
    Place(8) = " 拾 "
    DecimalPlace = " 角 分 "
    ReDim Temp(6) As String
    MyNumber = Trim(CStr(MyNumber))
    Count = 1
    If MyNumber("") Then ReDim Preserve Amount(Count - 1) As String
    StartValue = Int(MyNumber)
    MyNumber = CStr(Int(Abs(MyNumber) * 100 + 0.5))
    DecimalPlace = Mid(DecimalPlace & " 角 分 ", 12 - Len(MyNumber), 2)
    Temp(2) = GetTens(Left(MyNumber, 1))
    Temp(3) = GetDigit(Mid(MyNumber, 2))
    Temp(4) = GetTens(Mid(MyNumber, 3))
    Temp(5) = GetDigit(Mid(MyNumber, 4))
    Temp(6) = GetTens(Mid(MyNumber, 5))
    ConvertToChinese = StartValue & "元" & Temp(2) & Temp(3) _
    & Temp(4) & Temp(5) & Temp(6) & DecimalPlace
End Function

Function GetTens(TensText)
    Dim Result As String
    Result = ""           ' Null out the temporary function value.
    If Val(Left(TensText, 1)) = 1 Then   ' If value between 10-19...
        Select Case Val(TensText)
            Case 10: Result = " 十 "
            Case 11: Result = " 十一 "
            Case 12: Result = " 十二 "
            Case 13: Result = " 十三 "
            Case 14: Result = " 十四 "
            Case 15: Result = " 十五 "
            Case 16: Result = " 十六 "
            Case 17: Result = " 十七 "
            Case 18: Result = " 十八 "
            Case 19: Result = " 十九 "
            Case Else
        End Select
    Else                                 ' If value between 20-99...
        Select Case Val(Left(TensText, 1))
            Case 2: Result = " 二十 "
            Case 3: Result = " 三十 "
            Case 4: Result = " 四十 "
            Case 5: Result = " 五十 "
            Case 6: Result = " 六十 "
            Case 7: Result = " 七十 "
            Case 8: Result = " 八十 "
            Case 9: Result = " 九十 "
            Case Else
        End Select
        Result = Result & GetDigit _
            (Right(TensText, 1))   ' Retrieve ones
    End If
    GetTens = Result
End Function

Function GetDigit(Digit)
    Select Case Val(Digit)
        Case 1: GetDigit = " 一 "
        Case 2: GetDigit = " 二 "
        Case 3: GetDigit = " 三 "
        Case 4: GetDigit = " 四 "
        Case 5: GetDigit = " 五 "
        Case 6: GetDigit = " 六 "
        Case 7: GetDigit = " 七 "
        Case 8: GetDigit = " 八 "
        Case 9: GetDigit = " 九 "
        Case Else: GetDigit = ""
    End Select
End Function

方法二：使用牛顿迭代法

牛顿迭代法是一种数值计算方法，在Excel中可以用来进行复杂的数学计算。使用牛顿迭代法来转换小写金额为大写金额的方法如下：

在B1单元格输入小写金额
在C1单元格输入“=牛顿迭代(10^12,B1)”
在D1单元格输入“=C1*10^-(LEN(INT(B1))-2)”
在E1单元格输入“=TEXT(D1,"0 仟佰拾亿仟佰拾") & "元整"”

方法三：使用Excel内置函数

Excel内置函数可以帮助我们完成各种计算和转换的操作。以下是使用内置函数将小写金额转换为大写金额的方法：

在B1单元格输入小写金额
在C1单元格输入“=TEXT(B1,"0 仟佰拾亿仟佰拾") & "元整"”

方法四：使用自定义Excel函数

自定义Excel函数可以根据我们的需求，编写出符合特定功能的函数。以下是使用自定义函数将小写金额转换为大写金额的方法：

按下Alt+F11打开VBA编辑器
在模块中输入以下代码：

Function ConvertToChinese(ByVal MyNumber As Double) As String
    Dim MyArray(9) As String
    Dim NEW_ARRAY As String
    Dim DecimalPlace As String
    Dim Count As Integer
    Dim Temp As String
    Dim DecimalSeparator As String
    Dim Unit As String
    Dim i As Integer
    Dim India As Integer

    DecimalSeparator = “.”
    ReDim DecimalPart(9) As String
    DecimalPlace = “点”

    If MyNumber = 0 Then
        ConvertToChinese = “零圆”
        Exit Function
    End If

    If MyNumber < 0 Then
        NEW_ARRAY = “负”
    Else
        NEW_ARRAY = “”
    End If

    Temp = CStr(Abs(MyNumber))

    If InStr(Temp, "E") <> 0 Then
        Temp = Format(MyNumber, "0.00")
        Temp = Replace(Temp, ".", "")
        Temp = Replace(Temp, "E", "=
^")
    Else
        If InStr(Temp, "E") <> 0 Then
            Temp = Replace(Temp, "E", "=
^")
        End If
    End If

    Count = Len(Temp) - 2
    ReDim MyArray(Count)

    If Count = 0 Then
        ConvertToChinese = “请输入大于等于0的金额”
        Exit Function
    End If

    For i = 1 To Count
        MyArray(i) = GetChinese(Val(Mid(Temp, i + 2, 1)))
    Next

    Unit = "圆"
    ConvertToChinese = NEW_ARRAY

    For i = 1 To Count
        Select Case i
            Case 1, 4, 7
                India = 0
                Select Case i
                    Case 1
                        Unit = "圆"
                    Case 4
                        Unit = "万"
                    Case 7
                        Unit = "亿"
                End Select
            Case Else
                If i > 1 And i < 4 Then
                    Unit = "仟"
                End If
                If i > 4 And i < 7 Then
                    Unit = "佰"
                End If
                If i > 7 Then
                    Unit = "拾"
                End If
        End Select

        If Mid(Temp, i + 2, 1) = "0" Then
            ConvertToChinese = ConvertToChinese & "零"
        Else
            ConvertToChinese = ConvertToChinese & MyArray(i) & Unit
        End If
    Next

    ConvertToChinese = ConvertToChinese & "整"
End Function

Function GetChinese(Number As Integer) As String
    Select Case Number
        Case 0
            GetChinese = "零"
        Case 1
            GetChinese = "壹"
        Case 2
            GetChinese = "贰"
        Case 3
            GetChinese = "叁"
        Case 4
            GetChinese = "肆"
        Case 5
            GetChinese = "伍"
        Case 6
            GetChinese = "陆"
        Case 7
            GetChinese = "柒"
        Case 8
            GetChinese = "捌"
        Case 9
            GetChinese = "玖"
        Case Else
            GetChinese = "出错"
    End Select
End Function