开心六月综合激情婷婷|欧美精品成人动漫二区|国产中文字幕综合色|亚洲人在线成视频

EXCEL的金額大小寫轉(zhuǎn)換方法存在問題

按現(xiàn)在財(cái)務(wù)制度書寫要求，我試過的金額大小寫轉(zhuǎn)換方法都不合格，比如按現(xiàn)在的財(cái)務(wù)制度要求，金額203405.01正確的大寫應(yīng)該是“貳拾萬零叁仟肆佰零伍元零壹分”，注意這里的藍(lán)顏色的零，是必須有的。而我所試過的方法只能轉(zhuǎn)換為“貳拾萬叁仟肆佰零伍元零壹分”，零沒有了，是一種錯(cuò)誤的大寫。有高手請(qǐng)重新給出一個(gè)正確的轉(zhuǎn)換方法吧，謝謝了。

你這是哪門子的規(guī)定？我看到的制度規(guī)定卻是：

阿拉伯金額數(shù)字萬位或元位是“0”，或者數(shù)字中間連續(xù)有幾個(gè)“0”，萬位、元位也是“0”，但仟位、角位不是“0”時(shí)，中文大寫金額中可以只寫一個(gè)零字，也可以不寫“零”字，如￥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自動(dòng)轉(zhuǎn)換數(shù)字大小寫具體的操作步驟如下： 

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編輯器”返回工作表。現(xiàn)在，我們可測(cè)試一下這個(gè)函數(shù)。首先，在a1單元格中輸入一個(gè)小寫數(shù)字的金額，例如123456.78，然后，在a2單元 格中輸入公式：=dx(a1)，確認(rèn)后即可將a1所表示的小寫數(shù)字金額轉(zhuǎn)換為大寫的數(shù)字金額，并在a2單元格中顯示出來。怎么樣，方便吧？