Question

在excel中怎么把数字的金额转换成中文大写的？例如：¥123.23，转换成壹佰贰拾叁元贰角叁分

Answer 1

用VBA自定义一个大写金额转换的函数.在公式中输入此函数名就可以了. 具体方法:按 Alt+F11 .在VBA编辑器菜单中点 插入→模块.将下面的函数复制到插入的模块当中. ＇=================================================================== Function CChinese(StrEng As String) As String ＇将阿拉伯数字转成中文字的程式例如:1560890 转成 "壹佰伍拾陆万零捌佰玖拾". ＇程式限制为不可输入超过16个数字 If Not IsNumeric(StrEng) Or StrEng Like "＊.＊" Or StrEng Like "＊-＊" Then If Trim(StrEng) ＜＞ "" Then MsgBox "无效的数字" CChinese = "": Exit Function End If Dim intLen As Integer. intCounter As Integer Dim strCh As String. strTempCh As String Dim strSeqCh1 As String. strSeqCh2 As String Dim strEng2Ch As String strEng2Ch = "零壹贰叁肆伍陆柒捌玖" strSeqCh1 = " 拾佰仟 拾佰仟 拾佰仟 拾佰仟" strSeqCh2 = " 万亿兆" StrEng = CStr(CDec(StrEng)) intLen = Len(StrEng) For intCounter = 1 To intLen strTempCh = Mid(strEng2Ch. Val(Mid(StrEng. intCounter. 1)) + 1. 1) If strTempCh = "零" And intLen ＜＞ 1 Then If Mid(StrEng. intCounter + 1. 1) = "0" Or (intLen - intCounter + 1) Mod 4 = 1 Then strTempCh = "" End If Else strTempCh = strTempCh ＆ Trim(Mid(strSeqCh1. intLen - intCounter + 1. 1)) End If If (intLen - intCounter + 1) Mod 4 = 1 Then strTempCh = strTempCh ＆ Mid(strSeqCh2. (intLen - intCounter + 1) 4 + 1. 1) If intCounter ＞ 3 Then If Mid(StrEng. intCounter - 3. 4) = "0000" Then strTempCh = Left(strTempCh. Len(strTempCh) - 1) End If End If strCh = strCh ＆ Trim(strTempCh) Next CChinese = strCh End Function ＇=================================================================== ＇=================================================================== Function daxie(money As String) As String ＇实现货币金额中文大写转换的程序 ＇程式限制为不可输入超过16个数字 Dim x As String. y As String Const zimu = ".sbqwsbqysbqwsbq" ＇定义位置代码 Const letter = "0123456789sbqwy.zjf" ＇定义汉字缩写 Const upcase = "零壹贰叁肆伍陆柒捌玖拾佰仟万亿圆整角分" ＇定义大写汉字 If CDbl(money) ＞= 1E+16 Then daxie = "＃VALUE!": Exit Function ＇只能转换一亿亿元以下数目的货币! x = Format(money. "0.00") ＇格式化货币 y = "" For i = 1 To Len(x) - 3 y = y ＆ Mid(x. i. 1) ＆ Mid(zimu. Len(x) - 2 - i. 1) Next If Right(x. 3) = ".00" Then y = y ＆ "z" ＇＊＊＊元整 Else y = y ＆ Left(Right(x. 2). 1) ＆ "j" ＆ Right(x. 1) ＆ "f" ＇＊元＊角＊分 End If y = Replace(y. "0q". "0") ＇避免零千(如:40200肆万零千零贰佰) y = Replace(y. "0b". "0") ＇避免零百(如:41000肆万壹千零佰) y = Replace(y. "0s". "0") ＇避免零十(如:204贰佰零拾零肆) y = Replace(y. "0j". "0") ＇避免零角 y = Replace(y. "0f". "") ＇避免零分 Do While y ＜＞ Replace(y. "00". "0") y = Replace(y. "00". "0") ＇避免双零(如:1004壹仟零零肆) Loop y = Replace(y. "0y". "y") ＇避免零亿(如:210亿 贰佰壹十零亿) y = Replace(y. "0w". "w") ＇避免零万(如:210万 贰佰壹十零万) y = IIf(x ＜ 0.1. Right(y. Len(y) - 3). y) ＇避免零几分(如:0.01零壹分,0.04零肆分) y = IIf(Len(x) = 5 And Left(y. 1) = "1". Right(y. Len(y) - 1). y) ＇避免壹十(如:14壹拾肆,10壹拾) y = IIf(Len(x) = 4. Replace(y. "0.". ""). Replace(y. "0.". ".")) ＇避免零元(如:20.00贰拾零圆,0.12零圆壹角贰分) For i = 1 To 19 y = Replace(y. Mid(letter. i. 1). Mid(upcase. i. 1)) ＇大写汉字 Next daxie = y End Function ＇=================================================================== 回到Excel工作表中.若要转化A1单元格的数字.则可在其他任意单元格中输入公式 =CChinese(A1).要转换成大写金额.则可在其他任意单元格中输入公式 =daxie(A1).

Answer 2

=IF(ISERROR(FIND(".",V24)),NUMBERSTRING(INT(V24),2)&"元整",IF(ISERROR(NUMBERSTRING(MID(V24,FIND(".",V24)+2,1),2)),NUMBERSTRING(INT(V24),2)&"元"&NUMBERSTRING(MID(V24,FIND(".",V24)+1,1),2)&"角整",NUMBERSTRING(INT(V24),2)&"元"&NUMBERSTRING(MID(V24,FIND(".",V24)+1,1),2)&"角"&NUMBERSTRING(MID(V24,FIND(".",V24)+2,1),2)&"分"))

注意：V24是小写金额所在的单元格哦，把其更改成你的小写金额所在单元格就行了，注意要认真，不要漏改

Answer 3

假设小写数字先放在A1单元格，则在B1中输入
=NUMBERSTRING(G15,2)&"圆"&IF(ISERROR(FIND(".",G15)),"",IF(VALUE(MID(G15,FIND(".",G15)+1,1))=0,"零",NUMBERSTRING(MID(G15,FIND(".",G15)+1,1),2)&"角")&NUMBERSTRING(RIGHT(G15,1),2)&"分")
就是你要的结果了

Answer 4

假如123.23在a1，在b1放大写，则b1=IF(OR(A1<=0,COUNTBLANK(A1)),"",TEXT(INT(A1),"[dbnum2]")&IF(INT(A1)=A1,"元整","元")&IF(INT(A1)=A1,"",TEXT(--RIGHT(INT(A1*10)),IF(--RIGHT(INT(A1*100))>0,"[=0]零;[dbnum2]#角","[dbnum2]#角;;;"))&TEXT(--RIGHT(A1*100),"[dbnum2]#分;;;")))