如果将EXCEL中的数字用函数转换成中文金额大写?
发布网友
发布时间：2022-04-23 03:09
共1个回答
热心网友
时间：2022-04-15 03:55
在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
