如果将EXCEL中的数字用函数转换成中文金额大写?

 我来答

1个回答

#热议# 不吃早饭真的会得胆结石吗？

王拔沃尔云
2019-06-10 · TA获得超过3912个赞

知道大有可为答主

回答量：3154

采纳率：34%

帮助的人：177万

我也去答题访问个人页

关注

展开全部

在EXCEL创建如下vba函数，然后可以把JEZH当内部函数使用。
Function
JEZH(X
As
Range)
If
X
>=
1
Then
If
Int(X)
=
X
Or
Round(X,
2)
=
Int(X)
Then
JEZH
=
Application.WorksheetFunction.Text(Int(X),
"[DBNUM2]")
&
"元整"
ElseIf
Int(X
*
10)
=
X
*
10
Or
Int(X
*
10)
=
Round(X,
2)
*
10
Then
JEZH
=
Application.WorksheetFunction.Text(Int(X),
"[DBNUM2]")
&
"元"
&
Application.WorksheetFunction.Text(Right(Round(X,
2),
1),
"[DBNUM2]")
&
"角"
ElseIf
Right(Int(X
*
10),
1)
<>
0
Then
JEZH
=
Application.WorksheetFunction.Text(Int(X),
"[DBNUM2]")
&
"元"
&
Application.WorksheetFunction.Text(Left(Right(Round(X,
2),
2),
1),
"[DBNUM2]")
&
"角"
&
Application.WorksheetFunction.Text(Right(Round(X,
2),
1),
"[DBNUM2]")
&
"分"
Else
JEZH
=
Application.WorksheetFunction.Text(Int(X),
"[DBNUM2]")
&
"元"
&
Application.WorksheetFunction.Text(Left(Right(Round(X,
2),
2),
1),
"[DBNUM2]")
&
Application.WorksheetFunction.Text(Right(Round(X,
2),
1),
"[DBNUM2]")
&
"分"
End
If
ElseIf
X
=
0
Then
JEZH
=
Application.WorksheetFunction.Text(Int(X),
"[DBNUM2]")
&
"元"
ElseIf
X
<
1
And
X
>
0
Then
If
Int(X
*
10)
=
X
*
10
Then
JEZH
=
Application.WorksheetFunction.Text(Right(X,
1),
"[DBNUM2]")
&
"角整"
ElseIf
Right(Int(X
*
10),
1)
<>
0
Then
JEZH
=
Application.WorksheetFunction.Text(Left(Right(Round(X,
2),
2),
1),
"[DBNUM2]")
&
"角"
&
Application.WorksheetFunction.Text(Right(Round(X,
2),
1),
"[DBNUM2]")
&
"分"
Else
JEZH
=
Application.WorksheetFunction.Text(Right(Round(X,
2),
1),
"[DBNUM2]")
&
"分"
End
If
ElseIf
X
<=
-1
Then
If
Int(X)
=
X
Or
Round(Abs(X),
2)
=
Int(Abs(X))
Then
JEZH
=
"负"
&
Application.WorksheetFunction.Text(Int(Abs(X)),
"[DBNUM2]")
&
"元整"
ElseIf
Int(X
*
10)
=
X
*
10
Then
JEZH
=
"负"
&
Application.WorksheetFunction.Text(Int(Abs(X)),
"[DBNUM2]")
&
"元"
&
Application.WorksheetFunction.Text(Right(X,
1),
"[DBNUM2]")
&
"角"
ElseIf
Right(Int(X
*
10),
1)
<>
1
Then
JEZH
=
"负"
&
Application.WorksheetFunction.Text(Int(Abs(X)),
"[DBNUM2]")
&
"元"
&
Application.WorksheetFunction.Text(Left(Right(Round(X,
2),
2),
1),
"[DBNUM2]")
&
"角"
&
Application.WorksheetFunction.Text(Right(Round(X,
2),
1),
"[DBNUM2]")
&
"分"
Else
JEZH
=
"负"
&
Application.WorksheetFunction.Text(Int(Abs(X)),
"[DBNUM2]")
&
"元"
&
Application.WorksheetFunction.Text(Left(Right(Round(X,
2),
2),
1),
"[DBNUM2]")
&
Application.WorksheetFunction.Text(Right(Round(X,
2),
1),
"[DBNUM2]")
&
"分"
End
If
ElseIf
0
>
X
>
-1
Then
ElseIf
Int(X
*
10)
=
X
*
10
Then
JEZH
=
"负"
&
Application.WorksheetFunction.Text(Right(X,
1),
"[DBNUM2]")
&
"角整"
ElseIf
Right(Int(X
*
10),
1)
<>
1
Then
JEZH
=
"负"
&
Application.WorksheetFunction.Text(Left(Right(Round(X,
2),
2),
1),
"[DBNUM2]")
&
"角"
&
Application.WorksheetFunction.Text(Right(Round(X,
2),
1),
"[DBNUM2]")
&
"分"
Else
JEZH
=
"负"
&
Application.WorksheetFunction.Text(Right(Round(X,
2),
1),
"[DBNUM2]")
&
"分"
End
If
End
Function