再議“Excel環境下金額數字大寫轉換的方法探討”

[摘 要] 利用公式法對Excel環境下金額數字進行大寫轉換是非常實用的方法，通過對公式代碼進行優化，不僅增加了公式的功能，還可以簡化錄入、增強公式的可理解性、方便公式再加工，通過對公式法進行原理解析，可以方便初學者使用和理解公式，也有利于對公式的改造和應用領域的拓展。

[關鍵詞] Excel;大寫轉換;公式;代碼優化;原理解析

doi : 10 . 3969 / j . issn . 1673 - 0194 . 2010 . 18 . 003

[中圖分類號]F232;TP317.3 [文獻標識碼]A [文章編號]1673 - 0194(2010)18- 0007 - 02

一、引 言

拜讀了《中國管理信息化》2009年第6期王光偉的文章《Excel環境下金額數字大寫轉換的方法探討》(以下簡稱王文)后收獲很大，該文中列舉的金額數字大寫轉換的幾種方法在財會工作中具有很強的實用性和較高的實用價值。在實際工作中廣大財會人員并非都具備豐富的計算機編程知識，利用VBA編寫金額大寫轉換程序或自定義函數有一定難度，且不同財會人員對金額大寫轉換有不同需求，為了適應不同需求而修改程序或自定義函數的過程比較煩瑣，為此，本文只探討利用公式法對金額數字實施大寫轉換。

二、公式法代碼優化

王文中公式法的公式為=IF(TYPE(FIND(\".\"，TEXT(E2，\"[dbnum2]\")，1))=16，TEXT(E2，\"[dbnum2]\")\"元整\"，LEFT(TEXT(E2，\"[dbnum2]\")，FIND(\".\"，TEXT(E2，\"[dbnum2]\")，1)-1)\"元\"IF(LEN(MID(TEXT(E2，\"[dbnum2]\")，FIND(\".\"，TEXT(E2，\"[dbnum2]\")，1)+1，2))=1，MID(TEXT(E2，\"[dbnum2]\")，FIND(\".\"，TEXT(E2，\"[dbnum2]\")，1)+1，1)\"角整\"，IF(MID(TEXT(E2，\"[dbnum2]\")，FIND(\".\"，TEXT(E2，\"[dbnum2]\")，1)+1，1)=\"零\"，\"零\"MID(TEXT(E2，\"[dbnum2]\")，FIND(\".\"，TEXT(E2，\"[dbnum2]\")，1)+2，1)\"分\"，(MID(TEXT(E2，\"[dbnum2]\")，FIND(\".\"，TEXT(E2，\"[dbnum2]\")，1)+1，1)\"角\"(MID(TEXT(E2，\"[dbnum2]\")，FIND(\".\"，TEXT(E2，\"[dbnum2]\")，1)+2，1)\"分\")))))(以下簡稱公式1)。公式1雖然邏輯關系簡單，但代碼較長，嵌套較多，結構復雜，且作者未對該公式的原理進行詳細解釋，因而Excel函數知識掌握有欠缺的人員在使用該公式時，錄入、理解和再加工都有一定難度。經測試，該公式在如下方面存在改善空間:

1.純小數經轉換后多了“零元”，例如0.45轉換后顯示“零元肆角伍分”。

2.負數加了“-”號，例如-0.45轉換后顯示“-零元肆角伍分”。

3.小數點后多于兩位的小數無法四舍五入，而是直接截取，例如0.455轉換后顯示“零元肆角伍分”。

4.不能區別空白單元格與值為0的單元格，例如將空單元與0均轉換為“零元整”。

5.在轉換的大寫金額前未加貨幣名稱。

6.公式代碼長度較長(為544個字符)，理解困難。

在王文啟發的基礎上，根據以上問題，可將公式簡化為=SUBSTITUTE(SUBSTITUTE(IF(EXACT(E2，\"\")，，\"人民幣\"IF(-RMB(E2，2)，TEXT(E2，\";負\")TEXT(INT(ABS(E2)+0.5%)，\"[dbnum2]G/通用格式元;;\")TEXT(RIGHT(RMB(E2，2)，2)，\"[dbnum2]0角0分;;整\")，\"零元整\"))，\"零角\"，IF(E2^2<1，，\"零\"))，\"零分\"，\"整\")(以下簡稱公式2)。公式2提供了如下功能:

1.在轉換的大寫金額前加貨幣名稱，例如0.45轉換后顯示“人民幣肆角伍分”。

2.在轉換的負數前加漢字“負”，例如-0.45轉換后顯示“人民幣負肆角伍分”。

3.能區別空白單元格與值為0的單元格，例如將空單元轉換后保留空單元，0轉換為“人民幣零元整”。

4.對小數點后多于兩位的小數進行四舍五入，保留兩位小數，例如0.455轉換后顯示“人民幣肆角陸分”。

5.既能轉換數值型數字，也能轉換文本型數字。

6.代碼更精練(為200個字符)，錄入、理解和再加工更方便。

代碼優化前后對比見圖1。

三、公式法原理解析

公式1原理解析見圖2。由圖2可見，公式1的特點是邏輯關系簡單，結構清晰，但其代碼實現方法復雜，轉換過程中代碼重復較多。

公式2原理解析及函數說明見圖3。公式2的思路是:第一步，利用函數EXACT()判斷待轉換單元格是否為空，當單元格為空時結束轉換，否則進入第二步。第二步，利用RMB()函數對待轉換數據進行四舍五入保留兩位小數，當四舍五入保留兩位小數后的值為0時，返回字符串“零元整”，否則進入第三步。第三步，利用TEXT()函數判斷四舍五入保留兩位小數后的值是否為負，當是負數時，返回字符串“負”，否則返回空值(這里TEXT(A1，“;負”)不僅能判斷是否為負，還具備轉換字符型數據的功能，如果用IF(A7>0，“負”)判斷是否為負，則不具備此功能)。第四步，取四舍五入保留兩位小數后的整數部分，整數部分為0則返回空值，否則利用TEXT()函數轉換為“X元”形式。第五步，取四舍五入保留兩位小數后的小數部分，小數部分為0則返回字符串“整”，否則利用TEXT()函數轉換為“X角X分”形式。第六步，利用將第三至第五步的返回值連接為“X元X角X分形式”或“負X元X角X分”形式。第七步，利用在第二步及第六步結果前加貨幣名稱“人民幣”。第八步，當待轉換數據為純小數時，利用函數SUBSTITUTE()將第七步結果中含有“零角”的內容替換為空值，否則替換為“零”。第九步，利用函數SUBSTITUTE()將第八步結果中含有“零分”的內容替換為“整”，最終完成整體轉換。

由圖3可見，公式2的特點是邏輯關系雖比公式1復雜，結構層次較多，但具備功能較多且代碼實現過程簡單，公式條理清晰，各功能相對獨立，易于對公式具備的功能進行取舍、增刪，以適應不同需求。例如，當不需要在轉換數據前加貨幣名稱時，只需刪除公式中的代碼“\"人民幣\"”即可;當不涉及空單元格時，只需刪除公式中函數IF((EXACT(E2，\"\")，，)即可。

四、結束語

公式法代碼的優化是在王文的啟發下，利用Excel函數組合構建而成，經優化后得到的公式也許并非最優結果，旨在提供一種利用Excel解決問題的思路。以優化后的公式為主進行的原理解析，目的是使不熟悉Excel的人員能在理解公式的基礎上應用公式法，同時也為相關人員對公式法進行加工、改造及拓展該公式的應用領域提供了參考。

主要參考文獻

[1]王光偉.Excel環境下金額數字大寫轉換的方法探討[J].中國管理信息化，2009(6).

[2]田輝.利用Excel函數實現金額數字大寫的標準轉換[J].辦公自動化，2009(12).