如题所述
第1个回答 推荐于2016-09-04
在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本回答被网友采纳
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本回答被网友采纳