毕业设计的实施步骤:EXCEL的金额大小写转换方法存在问题

EXCEL的金额大小写转换方法存在问题

按现在财务制度书写要求，我试过的金额大小写转换方法都不合格，比如按现在的财务制度要求，金额203405.01正确的大写应该是“贰拾万零叁仟肆佰零伍元零壹分”，注意这里的蓝颜色的零，是必须有的。而我所试过的方法只能转换为“贰拾万叁仟肆佰零伍元零壹分”，零没有了，是一种错误的大写。有高手请重新给出一个正确的转换方法吧，谢谢了。

你这是哪门子的规定？我看到的制度规定却是：

阿拉伯金额数字万位或元位是“0”，或者数字中间连续有几个“0”，万位、元位也是“0”，但仟位、角位不是“0”时，中文大写金额中可以只写一个零字，也可以不写“零”字，如￥1，680.32，应写成人民币壹仟陆佰捌拾元零三角贰分，或者写成人民币壹仟陆佰捌拾元三角贰分；又如￥107，000.53，应写成人民币壹拾万柒仟元零伍角叁分，或者写成人民币壹拾万柒仟元伍角叁分。

这就是说，这种情况下可以有零，也可以没零。不过，要加上零也并非难事，不过公式效率要打些折扣了。

四舍五入公式
=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要写零的情况，如果要加上，四舍五入公式就要变为：

=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自动转换数字大小写具体的操作步骤如下： 

1. 启动excel。按“alt+f11”快捷键打开“visual basic编辑器”。

2. 在“visual basic编辑器”中，单击“插入”菜单栏中的“模块”命令，插入一个模块。双击左侧“工程”窗口中的“模块1”选项，在窗口右边展开“模块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

输 入完成后，关闭“visual basic编辑器”返回工作表。现在，我们可测试一下这个函数。首先，在a1单元格中输入一个小写数字的金额，例如123456.78，然后，在a2单元 格中输入公式：=dx(a1)，确认后即可将a1所表示的小写数字金额转换为大写的数字金额，并在a2单元格中显示出来。怎么样，方便吧？