关于EXCEL中转换中文大写货币格式

关于EXCEL中转换中文大写货币格式

(2007-05-14 21:45:32)

转载

分类： 转载

 

一、通过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]")&"分","整")

3、其他函数

=IF(ISNUMBER(A1),IF(TRUNC(A1)=0,IF(AND(MID(TEXT(TRUNC(A1,2),"0.00"),LEN(TEXT (TRUNC(A1,2),"0.00"))-1,1)="0",RIGHT(TEXT(TRUNC(A1,2),"0.00"),1)="0"),"零元",""),TEXT(TRUNC(A1),"[DBNUM2]")&"元")&(IF(MID(TEXT(TRUNC(A1,2), "0.00"),LEN(TEXT(TRUNC(A1,2),"0.00"))-1,1)="0",IF(OR(TRUNC(A1)=0,RIGHT(TEXT (TRUNC(A1,2),"0.00"),1)="0"),"","零"),TEXT(MID(A1,LEN(TEXT(TRUNC(A1,2), "0.00"))-1,1),"[DBNUM2]")&"角"))&(IF(RIGHT(TEXT(TRUNC(A1,2), "0.00"),1)="0","",TEXT(RIGHT(TEXT(TRUNC(A1,2),"0.00"),1),"[DBNUM2]")& "分")),IF(A1="","","不是有效金额,请重新输入!"))


二、通过自定义函数转化

通过自定义函数，以后在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