在EXCEL中利用自訂函數將數值金額含有角分者轉換成中文金額

在EXCEL中要轉換中文金額(不含角分者)，只要變更儲存格格式，再以自訂方式加上”元整”即可，但是這只能針對整數金額，對於要轉換至角分位數，則無法用這個方法完成，在網路上有不少人分享解決的方法，都是用現有內定函數交錯重組而成，以下是他人分享的內容。

例一：

=IF((INT(A5*10)-INT(A5)*10)=0,TEXT(INT(A5),"[DBNum2]G/通用格式")&"元"&IF((INT(A5*100)-INT((A5)*10)*10)=0,"整","零"&TEXT(INT(A5*100)-INT(A5*10)*10,"[DBNum2]G/通用格式")&"分"),TEXT(INT(A5),"[DBNum2]G/通用格式")&"元"&IF((INT(A5*100)-INT((A5)*10)*10)=0,TEXT((INT(A5*10)-INT(A5)*10),"[DBNum2]G/通用格式")&"角整",TEXT((INT(A5*10)-INT(A5)*10),"[DBNum2]G/通用格式")&"角"&TEXT(INT(A5*100)-INT(A5*10)*10,"[DBNum2]G/通用格式")&"分")) & "整"

例二：

=SUBSTITUTE(SUBSTITUTE(IF(A1<0," span="">负","")&TEXT(TRUNC(ABS(ROUND(A1,2))),"[DBNum2]")&"元"&IF(ISERR(FIND(".",ROUND(A1,2))),"",TEXT(RIGHT(TRUNC(ROUND(A1,2)*10)),"[DBNum2]"))&IF(ISERR(FIND(".0",TEXT(A1,"0.00"))),"角","")&IF(LEFT(RIGHT(ROUND(A1,2),3))=".",TEXT(RIGHT(ROUND(A1,2)),"[DBNum2]")&"分",IF(ROUND(A1,2)=0,"","整")),"零元零",""),"零元","")

例三：

IF(TRUNC(A1*10)-TRUNC(A1*10,-1)=0,IF(TRUNC(A1*100)-TRUNC(A1*100,-1)<>0,"零",""),TEXT(TRUNC(A1*10)-TRUNC(A1*10,-1),"[DBNum2]G/通用格式")&"角")&IF(TRUNC(A1*100)-TRUNC(A1*100,-1)=0,"",TEXT(TRUNC(A1*100)-TRUNC(A1*100,-1),"[DBNum2]G/通用格式")&"分")&IF(TRUNC(A1*100)-TRUNC(A1*100,-1)=0，"整"，"")

例四：

="人民币"&IF(ROUND(G10,2)<0," span="">负","")&IF(ROUND(ABS(K9),2)=0,"",IF(ROUND(K9,2)<0," span="">负","")&IF(ROUND(ABS(K9),2)<1,"",text(int(round(abs(k9),2)),"[dbnum2]")&" span="">元")&IF(LEFT(RIGHT(TEXT(K9,"0.00"),2),1)="0",IF(RIGHT(TEXT(K9,"0.00"),1)="0","整",IF(ROUND(ABS(K9),2)<1,""," span="">零")),TEXT(LEFT(RIGHT(TEXT(K9,"0.00"),2),1),"[dbnum2]")&"角")&IF(RIGHT(TEXT(K9,"0.00"),1)="0","",TEXT(RIGHT(TEXT(K9,"0.00"),1),"[dbnum2]")&"分"))

 上述四例，是不是讓人看得眼花潦亂，想要引用時著實不便，深恐哪個地方不小心差池了，就很難捉出錯誤所在。基於這個理由，本人私下費了一些時間，用VBA寫了一個能將含有角分的數值金額轉成中文金額的函數，只要將這函數複製貼放到模組中，即可在工作表中輕鬆引用。有寫VB的人，亦可直接加入自己的程式模組中。

用法步驟：

1. 開啟excel
2. 編輯新檔或打開要引用這個函數的excel檔案
3. 在【開發人員】/點【visual basic】
4. 選功能列【插入】/【模組】
5. 複製以下TCMny函數完整內容
6. 貼至模組編輯區塊，點儲存
7. 到excel工作表編輯區，就可以引用這個函數了

下圖即是D2儲存格用函數引用A2資料的情形及顯示：=tcmny(A2)

'===============================================================================

'Function TCMny(Mny As String) As String

'=========將金額數值轉成中文金額至兆位==============

'

'==============================================================================

'目前只轉換至兆位數億元止

Function TCMny(Mny As String) As String

      Dim ReturnC As String

      Dim Cunit(), CunitP(), Cnumb(), MnyA() As String

      Dim i, numberLen, oneN, nN, sN As Integer

      Dim NP As Boolean

      ReturnC = ""

      'Cunit = Array("元", "拾", "佰", "仟", "萬", "拾", "佰", "仟", "億", "拾", "佰", "仟", "兆")

      CunitP = Array("分", "角", ".", "元", "拾", "佰", ",", "仟", "萬", "拾", ",", "佰", "仟", "億", ",", "拾", "佰", "仟", ",", "兆", "拾", "佰", ",", "仟")

      Cnumb = Array("零", "壹", "貳", "參", "肆", "伍", "陸", "柒", "捌", "玖")

      '金額不格式化小數第二位零時會被省略,致使無法取得一致的格式

      '所以用format產一個與CunitP相同對應的格式

      Mny = Format(Mny, "#,##0.00")

      'ReDim MnyA(Len(Mny) - 1)

      'For i = 0 To UBound(MnyA)

      '   MnyA(i) = Mid(Mny, Len(Mny) - i, 1)

      'Next

            'MsgBox "len=" & Mny

         For i = 0 To Len(Mny) - 1

            'MsgBox "len=" & Len(Mny)

            '不理 0 . ,

            If Mid(Mny, i + 1, 1) = "." Or Mid(Mny, i + 1, 1) = "," Then

               sN = i   '欺騙用

            Else

                If Mid(Mny, i + 1, 1) <> "0" Then

                    ReturnC = ReturnC + Cnumb(Val(Mid(Mny, i + 1, 1)))

                    'MsgBox Val(Mid(Mny, i + 1, 1)) & "  " & i

                    ReturnC = ReturnC + CunitP(Len(Mny) - i - 1)

                Else

                    If Len(Mny) - i = 20 Then

                        ReturnC = ReturnC + "兆"

                    End If

                    If Len(Mny) - i = 14 Then

                        ReturnC = ReturnC + "億"

                    End If

                    If Len(Mny) - i = 9 Then

                        ReturnC = ReturnC + "萬"

                    End If

                    If Len(Mny) - i = 4 Then

                        ReturnC = ReturnC + "元"

                    End If

                 End If

            End If

         Next i

          ReturnC = ReturnC + "整"

      '//ShowMessage(ReturnC);

      'return ReturnC;

      TCMny = ReturnC

End Function