宜信财富杭州环境:关于EXCEL中转换中文大写货币格式-1
来源:百度文库 编辑:中财网 时间:2024/04/28 04:20:48
关于EXCEL中转换中文大写货币格式
(2007-05-14 21:45:32)转载 分类: 转载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
EXCEL中转换大写的问题
关于 MS Excel 中文大写数字
如何在PPT中转换繁体体中文和简体中文
如何改变2003EXCEL表格单元格特殊格式中文大写数字按元角分显示或保持小数点后两位(即不显示两位后数字)?
1至10的中文大写
怎么把图形文件转化成EXCEL文件?怎么把阿拉伯数字转化为中文大写?
excel有没有公式可以把小写阿拉伯数字转换成中文大写数字
Excel中如何将小数转换成中文大写整数。请各位高手指教,谢谢!
关于excel格式的请教
38的中文大写!!!!!!!!!!
怎样在excel里将数字转换成中文货币形式?
怎样在excel里将数字转换成中文货币形式?
EXCEL,首字母大写怎么关?
1~10的中文大写是怎么写?
1-10的中文大写,有什么来历吗?
关于货币
关于货币
关于货币
怎么在不同的视频格式中转换,比如说把其他的视频格式转换成为DVD或VCD格式?
怎样在excel中一个框中输入阿拉伯数字,在另一个框中显示中文大写数字包括角分
用Excel电子表格如何显示中文大写金额呢?(当然,除了一个字一个字敲进去)
在Excel中转换行和列
在Excel同一单元格中,如何能将所输入的数字直接变换成大写金额格式?
“9”的中文大写是什么?