在Excel中，金額大小寫自動(dòng)保持一致

Excel自動(dòng)轉(zhuǎn)換數(shù)字大小寫具體的操作步驟如下： （對(duì)于做預(yù)算報(bào)價(jià)很有用處哦）

1. 啟動(dòng)excel。按“alt+f11”快捷鍵打開“visual basic編輯器”。

2. 在“visual basic編輯器”中，單擊“插入”菜單欄中的“模塊”命令，插入一個(gè)模塊。雙擊左側(cè)“工程”窗口中的“模塊1”選項(xiàng)，在窗口右邊展開“模塊1（代碼）”編輯窗口，然后輸入如下代碼:

function dx(q)

dim cur as long,yuan as long

dim jiao as integer,fen as integer

dim cnyuan as string, cnjiao as string, cnfen as string

if q = "" then

dx = 0

exit function

end if

cur = round(q * 100)

yuan = int(cur / 100)

jiao = int(cur / 10) - yuan * 10

fen = cur - yuan * 100 - jiao * 10

cnyuan = application.worksheetfunction.text(yuan, "[dbnum2]")

cnjiao = application.worksheetfunction.text(jiao, "[dbnum2]")

cnfen = application.worksheetfunction.text(fen, "[dbnum2]")

dx = cnyuan & "元" & "整"

d1 = cnyuan & "元"

if fen <> 0 and jiao <> 0 then

dx = d1 & cnjiao & "角" & cnfen & "分"

if yuan = 0 then

dx = cnjiao & "角" & cnfen & "分"

end if

end if

if fen = 0 and jiao <> 0 then

dx = d1 & cnjiao & "角" & "整"

if yuan = 0 then

dx = cnjiao & "角" & "整"

end if

end if

if fen <> 0 and jiao = 0 then

dx = d1 & cnjiao & cnfen & "分"

if yuan = 0 then

dx = cnfen & "分"

end if

end if

end function

輸 入完成后，關(guān)閉“visual basic編輯器”返回工作表?，F(xiàn)在，我們可測(cè)試一下這個(gè)函數(shù)。首先，在a1單元格中輸入一個(gè)小寫數(shù)字的金額，例如123456.78，然后，在a2單元 格中輸入公式：=dx(a1)，確認(rèn)后即可將a1所表示的小寫數(shù)字金額轉(zhuǎn)換為大寫的數(shù)字金額，并在a2單元格中顯示出來(lái)。怎么樣，方便吧？