[原创]金额大写（公式处理，简单、明了）

wvgang · 发表于 2005-3-12 17:48

试一试下面的公式=IF(OR(ISNUMBER(E15)=FALSE,ROUND(E15,2)=0),"",IF(E15<0,"负","")&IF(INT(ROUND(ABS(E15),2))=0,"",TEXT(INT(ROUND(ABS(E15),2)), "[DBNum2]")&"元")&IF(INT(ROUND(ABS(E15),2))=ROUND(ABS(E15),2),"整",IF(INT(ROUND(ABS(E15),2)*10)=ROUND(ABS(E15)*10,1),TEXT(RIGHT(INT(ROUND(ABS(E15),2)*10),1),"[DBNum2]")&"角整",TEXT(RIGHT(INT(ROUND(ABS(E15),2)*10),1),"[DBNum2]")&IF(RIGHT(INT(ROUND(ABS(E15),2)*10),1)="0","","角")&TEXT(RIGHT(ROUND(ABS(E15),2),1),"[DBNum2]")&"分")))

[此贴子已经被作者于2008-3-15 20:02:10编辑过]

glassant · 发表于 2005-3-12 20:06

行的，谢谢分享啊

tof · 发表于 2005-3-12 21:54

金额大写论坛里已经有太多的讨论了。
不过我认为楼主如果能够把你的思路能用文字解释一遍，相信能得到更多会员的支持！！！
新手还是会被你的公式吓坏的！！！

[此贴子已经被作者于2005-3-12 21:55:40编辑过]

hwangaries · 发表于 2005-3-13 09:16

公式还有待更进[em04]

wjd74 · 发表于 2005-3-13 15:20

Function DONG1(Range1 As Range)
Dim Z As Byte
Dim Y As String
Dim X As Byte
Dim W As String
Dim D1 As String
Dim iNo As String
Dim iUnit As String
DONG1 = ""
iNo = "分角元拾佰仟零壹贰叁肆伍陆柒捌玖"
Y = Right(CStr(Round(Range1 * 100, 0)), 15)
X = Len(Right(CStr(Round(Range1 * 100, 0)), 15))
For Z = 1 To Len(Y)
W = Mid$(Y, Z, 1)
DONG1 = DONG1 & IIf(D1 & W = "00", "", Mid(iNo, Val(W) + 7, 1))
If (X = 7 Or X = 11) And W = "0" Then DONG1 = Left$(DONG1, Len(DONG1) - 1)
iUnit = ""
Select Case X
      Case 7, 11
         iUnit = IIf(X = 7, IIf(Right$(DONG1, 1) = "亿", "", "万"), "亿")
      Case 4 To 6, 8 To 10, 12 To 14
         iUnit = IIf(W = "0", "", Mid(iNo, X - Switch(X > 11, 11 - 3, X > 7, 7 - 3, X > 3, 3 - 3), 1))
      Case 1 To 3
         iUnit = Mid(iNo, X, 1)
End Select
DONG1 = DONG1 & IIf(X = 7 Or X = 11, iUnit, IIf(W = "0", "", iUnit))
D1 = W
X = X - 1
Next
W = Right$(DONG1, 1)
If W = "零" Then DONG1 = Left$(DONG1, Len(DONG1) - 1)
W = Right$(DONG1, 1)
If Right$(Y, 2) = "00" Then DONG1 = DONG1 & IIf(W = "元", "整", "元整")
If Right$(DONG1, 1) = "角" Then DONG1 = DONG1 & "零分"
End Function

leon_zhang · 发表于 2005-3-20 18:32

=TEXT(INT(B12),"[DBNum2]G/通用格式")&"元"&IF((INT(B12*10)-INT(B12)*10)=0,IF((INT(B12*100)-INT(B12)*100)>0,"零",""),TEXT(INT(B12*10)-INT(B12)*10,"[DBNum2]G/通用格式")&"角")&IF((INT(B12*100)-INT(B12*10)*10)=0,"整",TEXT(INT(B12*100)-INT(B12*10)*10,"[DBNum2]G/通用格式")&"分")

wvgang · 发表于 2005-3-21 10:18

6楼的请注意哈，你的公式有问题哟，你试一试1.999的结果哈

p37575 · 发表于 2005-3-25 10:13

本人在华军软件园看到这样的例子，但对[DBNum2]G/通用格式")的具体功能不是很了解，请高手赐教！

wintercom · 发表于 2005-3-25 15:38

这是我自己做的,其实先将那个填阿拉伯数字的单元格设置一下有效性,设与最多两位小数,然后再用公式=IF(A10=0,"",(TEXT(ROUNDDOWN(A10,0),"[DBNum2]")&"元"&IF(ROUNDDOWN(A10,0)=A10,"整",IF(ROUNDDOWN(A10*10,0)=A10*10,TEXT(RIGHT(A10*10,1),"[DBNum2]")&"角整",IF(ROUNDDOWN(A10*10,0)=ROUNDDOWN(A10,0)*10,"零"&TEXT(RIGHT(A10*100,1),"[DBNum2]")&"分",TEXT(RIGHT(ROUNDDOWN(A10*10,0),1),"[DBNum2]")&"角"&TEXT(RIGHT(A10*100,1),"[DBNum2]")&"分")))))

[此贴子已经被作者于2005-3-25 15:39:36编辑过]

liyh67 · 发表于 2005-3-25 22:40

在有效性中能设置最多两位小数吗？会不会又是版本不同的问题，我用的是Excel2000

		自动登录	找回密码
密码			免费注册

[原创] [原创]金额大写（公式处理，简单、明了）