关于MS Excel 中文大写数字
发布网友
发布时间：2022-05-05 17:19
共2个回答
热心网友
时间：2022-06-27 20:15
打开Excel，按Alt+F11（或者选择菜单 工具→宏→Visual Basic 编辑器），进入后新建模块（插入→模块），键入以下语句：
Function NCN(account) 'Nummary Capital Number
ybb = Round(account * 100)
y = Int(ybb / 100)
j = Int(ybb / 10) - y * 10
f = ybb - y * 100 - j * 10
zy = Application.WorksheetFunction.Text(y, "[dbnum2]")
zj = Application.WorksheetFunction.Text(j, "[dbnum2]")
zf = Application.WorksheetFunction.Text(f, "[dbnum2]")
NCN = zy & "圆"
If j = 0 And f = 0 Then
NCN = NCN & "整"
End If
If f <> 0 And j <> 0 Then
NCN = NCN & zj & "角" & zf & "分"
If y = 0 Then
NCN = zj & "角" & zf & "分"
End If
End If
If f = 0 And j <> 0 Then
NCN = NCN & zj & "角"
If y = 0 Then
NCN = zj & "角"
End If
End If
If f <> 0 And j = 0 Then
NCN = NCN & zj & zf & "分"
If y = 0 Then
NCN = zf & "分"
End If
End If
If account = "" Then
NCN = ""
End If
End Function
这样就可以了，关闭VB编辑器，进入Excel界面会发现在插入函数时多了一类“用户定义”，里面有个函数“NCN”，就是你要的函数。起名为NCN，意思是“Nummary Capital Number”货币大写数字。如果不喜欢，可以在角本里改，用理想的函数名替换"NCN"即可。
试试看吧！在A1里键入“1025.3”，在B1里写“=NCN(A1)”，效果不错吧？
关闭之前可以把你自己写的模块导出到一个.bas文件，以后想用的时候导入即可。因为是自己编写的，所以没有数字签名，打开带有此函数的文件时会提示有风险。降低安全级别即可，打开之后再改回来。
就说这么多了，如果还有什么不懂的再问我吧
热心网友
时间：2022-06-27 20:16
关键词： EXCEL 数字金额 转换 中文大写金额
如何在EXCEL中实现数字金额转换成中文大写金额？
行者transer
工作中经常会遇到这样的转换，特别是做财务工作的朋友，如果手工录入，很费时间又常常担心出错。
笔者参考了一些文章，根据自己的经验，总结了下面两种很实用的方法：
一、通过EXCEL原有函数和自定义格式转化
1、这个看起来比较烦琐，不过终究达到了效果。
=TEXT(TRUNC(ROUND(待转换数字或单元格,2),0),"[DBNUM2]G/通用格式"&"元")&(IF((ROUND(待转换数字或单元格,2)-TRUNC(ROUND(待转换数字或单元格,2),0))<0.01,"",IF(MIDB(ROUND(待转换数字或单元格,2),IF((ROUND(待转换数字或单元格,2)-TRUNC(ROUND(待转换数字或单元格,2),1))<0.01,(LEN(ROUND(待转换数字或单元格,2))),(LEN(ROUND(待转换数字或单元格,2))-1)),1)="0","零",TEXT(MIDB(ROUND(待转换数字或单元格,2),IF((ROUND(待转换数字或单元格,2)-TRUNC(ROUND(待转换数字或单元格,2),1))<0.01,(LEN(ROUND(待转换数字或单元格,2))),(LEN(ROUND(待转换数字或单元格,2))-1)),1),"[DBNUM2]G/通用格式")&"角")))&(IF((ROUND(待转换数字或单元格,2)-TRUNC(ROUND(待转换数字或单元格,2),1))<0.01,"整",TEXT(RIGHT(ROUND(待转换数字或单元格,2),1),"[DBNUM2]G/通用格式")&"分"))
2、这个是最简洁的函数实现方式
=IF(待转换数字或单元格<0,"负","")&IF(TRUNC(ROUND(待转换数字或单元格,2))=0,"",TEXT(TRUNC(ABS(ROUND(待转换数字或单元格,2))),"[DBNum2]")&"元")&IF(ISERR(FIND(".",ROUND(待转换数字或单元格,2))),"",TEXT(RIGHT(TRUNC(ROUND(待转换数字或单元格,2)*10)),"[DBNum2]"))&IF(ISERR(FIND(".0",TEXT(待转换数字或单元格,"0.00"))),"角","")&IF(LEFT(RIGHT(ROUND(待转换数字或单元格,2),3))=".",TEXT(RIGHT(ROUND(待转换数字或单元格,2)),"[DBNum2]")&"分","整")
二、通过自定义函数转化
通过自定义函数，以后在EXCEL中就可以直接调用函数MoneyTrans()来实现了。
如果熟悉VBA或者熟悉自定义函数的朋友可以尝试一下，关于自定义函数可参考Excel自定义函数实例剖析(转).
以下是数字金额转换成中文大写金额自定义函数,可以在VBA模块中添加就可以了。
Public Function MoneyTrans(Money As Currency) As String
On Error GoTo Doerr
Dim CN(9) As String
Dim CU(15) As String
Dim Temp As String, strNum As String
Dim CM As String
Dim tFirst As String, tEnd As String
Dim i As Long, j As Long, k As Long
CN(0) = "零"
CN(1) = "壹"
CN(2) = "贰"
CN(3) = "叁"
CN(4) = "肆"
CN(5) = "伍"
CN(6) = "陆"
CN(7) = "柒"
CN(8) = "捌"
CN(9) = "玖"
' CU(0) = "分"
' CU(1) = "角"
CU(0) = "元"
CU(1) = "拾"
CU(2) = "佰"
CU(3) = "仟"
CU(4) = "万"
CU(5) = "拾"
CU(6) = "佰"
CU(7) = "仟"
CU(8) = "亿"
CU(9) = "拾"
CU(10) = "佰"
CU(11) = "仟"
If Money = 0 Then
CM = "零元整"
GoTo Complete
End If
strNum = Trim(Str(FormatCurrency(Money, 2, vbTrue, vbFalse, vbFalse)))
If Left(strNum, 1) = "-" Then
tFirst = "负"
strNum = Right(strNum, Len(strNum) - 1)
Else
tFirst = ""
End If
i = InStrRev(strNum, ".")
If i <> 0 Then
Temp = Right(strNum, i)
If Len(strNum) - i = 1 Then Temp = Temp + "0"
CM = CN(CInt(Left(Right(Temp, 2), 1))) + "角" + CN(CInt(Right(Temp, 1))) + "分"
tEnd = ""
strNum = Left(strNum, i - 1)
Else
tEnd = "整"
End If
i = 0
For j = Len(strNum) To 1 Step -1
k = CInt(Right(Left(strNum, j), 1))
If k = 0 Then
If i <> 0 And i <> 4 And i <> 8 Then
CM = CN(k) + CM
Else
CM = CN(k) + CU(i) + CM
End If
Else
CM = CN(k) + CU(i) + CM
End If
' CM = CN(k) + CU(i) + CM
i = i + 1
Next j
CM = tFirst + CM + tEnd
CM = Replace(CM, "零零", "零")
CM = Replace(CM, "零零", "零")
CM = Replace(CM, "亿零万零元", "亿元")
CM = Replace(CM, "亿零万", "亿零")
CM = Replace(CM, "万零元", "万元")
CM = Replace(CM, "零亿", "亿")
CM = Replace(CM, "零万", "万")
CM = Replace(CM, "零元", "元")
CM = Replace(CM, "零零", "零")
CM = Replace(CM, "零零", "零") '重复替换一次
Complete:
Gerr = 0 '操作成功,无错误发生
MoneyTrans = CM
Exit Function
Doerr:
Gerr = -1 '未知错误
Errexit:
MoneyTrans = ""
End Function
参考资料：http://transer.bokee.com/4494828.html
