EXCEL的金額大小寫轉(zhuǎn)換方法存在問題
按現(xiàn)在財務(wù)制度書寫要求，我試過的金額大小寫轉(zhuǎn)換方法都不合格，比如按現(xiàn)在的財務(wù)制度要求，金額203405.01正確的大寫應(yīng)該是“貳拾萬零叁仟肆佰零伍元零壹分”，注意這里的藍(lán)顏色的零，是必須有的。而我所試過的方法只能轉(zhuǎn)換為“貳拾萬叁仟肆佰零伍元零壹分”，零沒有了，是一種錯誤的大寫。有高手請重新給出一個正確的轉(zhuǎn)換方法吧，謝謝了。
你這是哪門子的規(guī)定？我看到的制度規(guī)定卻是：
阿拉伯金額數(shù)字萬位或元位是“0”，或者數(shù)字中間連續(xù)有幾個“0”，萬位、元位也是“0”，但仟位、角位不是“0”時，中文大寫金額中可以只寫一個零字，也可以不寫“零”字，如￥1，680.32，應(yīng)寫成人民幣壹仟陸佰捌拾元零三角貳分，或者寫成人民幣壹仟陸佰捌拾元三角貳分；又如￥107，000.53，應(yīng)寫成人民幣壹拾萬柒仟元零伍角叁分，或者寫成人民幣壹拾萬柒仟元伍角叁分。
這就是說，這種情況下可以有零，也可以沒零。不過，要加上零也并非難事，不過公式效率要打些折扣了。
四舍五入公式
=IF(A1=0,"",IF(ABS(A1)<0.995,"",IF(AND(RIGHT(INT(ROUND(ABS(A1),2)/10000))="0",RIGHT(INT(ROUND(ABS(A1),2)/1000))<>"0"),SUBSTITUTE(TEXT(INT(ROUND(ABS(A1),2)),"[DBNum2]"),"萬","萬零"),TEXT(INT(ROUND(ABS(A1),2)),"[DBNum2]"))&"元")&IF(RIGHT(TEXT(A1,".00"),2)*1=0,IF(ABS(A1)<0.005,"","整"),TEXT(IF(ABS(A1)<0.095,"",LEFT(RIGHT(TEXT(A1,".00"),2))),"[dbnum2]")&IF(LEFT(RIGHT(TEXT(A1,".00"),2))*1=0,"","角")&IF(RIGHT(TEXT(A1,".00"))*1=0,"整",TEXT(RIGHT(TEXT(A1,".00")),"[dbnum2]")&"分")))
截尾公式
=IF(A1=0,"",IF(ABS(A1)<1,"",IF(AND(RIGHT(TRUNC(ABS(A1)/10000))="0",RIGHT(TRUNC(ABS(A1)/1000))<>"0"),SUBSTITUTE(TEXT(TRUNC(ABS(A1)),"[DBNum2]"),"萬","萬零"),TEXT(TRUNC(ABS(A1)),"[DBNum2]"))&"元")&IF(RIGHT(TRUNC(A1*100),2)*1=0,IF(ABS(A1)<0.01,"","整"),IF(ABS(A1)<0.1,"",TEXT(RIGHT(TRUNC(A1*10)),"[dbnum2]"))&IF(RIGHT(TRUNC(A1*10))*1=0,"","角")&IF(RIGHT(TRUNC(A1*100))*1=0,"整",TEXT(RIGHT(TRUNC(A1*100)),"[dbnum2]")&"分")))
公式出處：http://fanju.blog.hexun.com/1549898_d.html
效率是指在表格公式比較多的情況下，去處速度比較慢。
上述公式還未包括元位為0要寫零的情況，如果要加上，四舍五入公式就要變?yōu)椋?span lang="EN-US">
=IF(A1=0,"",IF(ABS(A1)<0.995,"",IF(AND(RIGHT(INT(ROUND(ABS(A1),2)/10000))="0",RIGHT(INT(ROUND(ABS(A1),2)/1000))<>"0"),SUBSTITUTE(TEXT(INT(ROUND(ABS(A1),2)),"[DBNum2]"),"萬","萬零"),TEXT(INT(ROUND(ABS(A1),2)),"[DBNum2]"))&"元")&IF(AND(RIGHT(INT(A1),1)="0",LEFT(RIGHT(TEXT(A1,".00"),2),1)<>"0"),"零","")&IF(RIGHT(TEXT(A1,".00"),2)*1=0,IF(ABS(A1)<0.005,"","整"),TEXT(IF(ABS(A1)<0.095,"",LEFT(RIGHT(TEXT(A1,".00"),2))),"[dbnum2]")&IF(LEFT(RIGHT(TEXT(A1,".00"),2))*1=0,"","角")&IF(RIGHT(TEXT(A1,".00"))*1=0,"整",TEXT(RIGHT(TEXT(A1,".00")),"[dbnum2]")&"分")))
Excel自動轉(zhuǎn)換數(shù)字大小寫具體的操作步驟如下：
1. 啟動excel。按“alt+f
2. 在“visual basic編輯器”中，單擊“插入”菜單欄中的“模塊”命令，插入一個模塊。雙擊左側(cè)“工程”窗口中的“模塊
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編輯器”返回工作表。現(xiàn)在，我們可測試一下這個函數(shù)。首先，在a1單元格中輸入一個小寫數(shù)字的金額，例如123456.78，然后，在a2單元 格中輸入公式：=dx(a1)，確認(rèn)后即可將a1所表示的小寫數(shù)字金額轉(zhuǎn)換為大寫的數(shù)字金額，并在a2單元格中顯示出來。怎么樣，方便吧？
