wenchduan
发表于 2012-2-20 08:11
本帖最后由 wenchduan 于 2012-2-21 11:25 编辑
Function Wgeshui(n, x)
Dim yl
If x = 1 Then
yl = n - 3500
Select Case yl
Case Is <= 0
Wgeshui = 0
Case Is < 1500
Wgeshui = yl * 3 / 100
Case Is < 4500
Wgeshui = yl * 10 / 100 - 105
Case Is < 9000
weshui = yl * 20 / 100 - 555
Case Is < 35000
Wgeshui = yl * 25 / 100 - 1005
Case Is < 55000
Wgeshui = yl * 30 / 100 - 2755
Case Is < 80000
Wgeshui = yl * 35 / 100 - 5505
Case Is >= 80000
Wgeshui = yl * 45 / 100 - 13505
End Select
ElseIf x = 2 Then
Select Case n
Case Is >= 22495
Wgeshui = (n + 13505) * 100 / 45 + 3500
Case Is < 13745
Wgeshui = (n + 5505) * 100 / 35 + 3500
Case Is < 7845
Wgeshui = (n + 2755) * 100 / 30 + 3500
Case Is < 1245
weshui = (n + 1005) * 100 / 25 + 3500
Case Is > 450
Wgeshui = (n + 555) * 100 / 20 + 3500
Case Is < 345
Wgeshui = (n + 105) * 100 / 10 + 3500
Case Is <= 45
Wgeshui = n * 100 / 3 + 3500
Case Is < 0
Wgeshui = "工资低于3500"
End Select
End If
End Function
亦铭
发表于 2012-2-20 13:34
Function WGeShui(N, X)
If X = 1 Then
Select Case N - 3500
Case Is <= 0
WGeShui = 0
Case Is <= 1500
WGeShui = (N - 3500) * 0.03
Case Is <= 4500
WGeShui = (N - 3500) * 0.1 - 105
Case Is <= 9000
WGeShui = (N - 3500) * 0.2 - 555
Case Is <= 35000
WGeShui = (N - 3500) * 0.25 - 1005
Case Is <= 55000
WGeShui = (N - 3500) * 0.3 - 2755
Case Is <= 80000
WGeShui = (N - 3500) * 0.35 - 5505
Case Else
WGeShui = (N - 3500) * 0.45 - 13505
End Select
ElseIf X = 2 Then
Select Case N
Case Is <= 45
WGeShui = N / 0.03 + 3500
Case Is <= 345
WGeShui = (N + 105) / 0.1 + 3500
Case Is <= 1245
WGeShui = (N + 555) / 0.2 + 3500
Case Is <= 7745
WGeShui = (N + 1005) / 0.25 + 3500
Case Is <= 13745
WGeShui = (N + 2755) / 0.3 + 3500
Case Is <= 22495
WGeShui = (N + 5505) / 0.35 + 3500
Case Else
WGeShui = (N + 13505) / 0.45 + 3500
End Select
End If
End Function
qushui
发表于 2012-2-20 13:39
Function geshui(a!, x As Byte)
Dim b!, w
If x = 1 Then
b = a - 3500
Select Case b
Case Is <= 0
w = 0
Case Is <= 1500
w = b * 0.03
Case Is <= 4500
w = b * 0.1 - 105
Case Is <= 9000
w = b * 0.2 - 555
Case Is <= 35000
w = b * 0.25 - 1005
Case Is <= 55000
w = b * 0.3 - 2755
Case Is <= 80000
w = b * 0.35 - 5505
Case Else
w = b * 0.45 - 13505
End Select
ElseIf x = 2 Then
Select Case a
Case 0
w = "<3500"
Case Is <= 15 * 3
w = a / 0.03 + 3500
Case Is <= 45 * 10 - 105
w = (a + 105) / 0.1 + 3500
Case Is <= 90 * 20 - 555
w = (a + 555) / 0.2 + 3500
Case Is <= 350 * 25 - 1005
w = (a + 1005) / 0.25 + 3500
Case Is <= 550 * 30 - 2755
w = (a + 2755) / 0.3 + 3500
Case Is <= 800 * 35 - 5505
w = (a + 5505) / 0.35 + 3500
Case Else
w = (a + 13505) / 0.45 + 3500
End Select
End If
If w <> "<3500" Then
geshui = Format(w, "###.00")
Else
geshui = w
End If
End Function
wuhairong2011
发表于 2012-2-20 14:51
Function WGeShui(rg1 As Range, x)
If x = 1 Then
z = rg1 - 3500
Select Case z
Case Is < 0
WGeShui = 0
Case Is <= 1500
WGeShui = (rg1 - 3500) * 0.03 - 0
Case Is <= 4500
WGeShui = (rg1 - 3500) * 0.1 - 105
Case Is <= 9000
WGeShui = (rg1 - 3500) * 0.2 - 555
Case Is <= 35000
WGeShui = (rg1 - 3500) * 0.25 - 1005
Case Is <= 55000
WGeShui = (rg1 - 3500) * 0.3 - 2755
Case Is <= 80000
WGeShui = (rg1 - 3500) * 0.35 - 5505
Case Is > 80000
WGeShui = (rg1 - 3500) * 0.45 - 13505
End Select
Else
Select Case rg1
Case 0
WGeShui = "不足3500元"
Case Is > 22495
WGeShui = (rg1 + 13505) / 0.45 + 3500
Case 13745 To 22495
WGeShui = (rg1 + 5505) / 0.35 + 3500
Case 7745 To 13745
WGeShui = (rg1 + 2755) / 0.3 + 3500
Case 1245 To 7745
WGeShui = (rg1 + 1005) / 0.25 + 3500
Case 345 To 1245
WGeShui = (rg1 + 555) / 0.2 + 3500
Case 45 To 345
WGeShui = (rg1 + 105) / 0.1 + 3500
Case 0 To 45
WGeShui = rg1 / 0.03 + 3500
End Select
End If
End Function
yl_li
发表于 2012-2-20 15:23
反算按练习提示有点问题,先交语句
Function Wgeshui(rg As Range, x As Byte)
If x = 1 Then
Select Case rg.Value - 3500
Case Is < 1500
Wgeshui = (rg.Value - 3500) * 0.03
Case Is <= 4500
Wgeshui = (rg.Value - 3500) * 0.1 - 105
Case Is <= 9000
Wgeshui = (rg.Value - 3500) * 0.2 - 555
Case Is <= 35000
Wgeshui = (rg.Value - 3500) * 0.25 - 1005
Case Is <= 55000
Wgeshui = (rg.Value - 3500) * 0.3 - 2755
Case Is <= 80000
Wgeshui = (rg.Value - 3500) * 0.35 - 5505
Case Else
Wgeshui = (rg.Value - 3500) * 0.45 - 13505
End Select
ElseIf x = 2 Then
Select Case rg.Value
Case Is = 0
Wgeshui = 3500
Case Is <= 105
Wgeshui = (rg.Value + 105) / 0.1 + 3500
Case Is <= 555
Wgeshui = (rg.Value + 555) / 0.2 + 3500
Case Is <= 1005
Wgeshui = (rg.Value + 1005) / 0.25 + 3500
Case Is <= 2755
Wgeshui = (rg.Value + 2755) / 0.3 + 3500
Case Is <= 5505
Wgeshui = (rg.Value + 5505) / 0.35 + 3500
Case Else
Wgeshui = (rg.Value + 13505) / 0.45 + 3500
End Select
End If
End Function
linmm
发表于 2012-2-20 20:47
Function Wgeshui(n As Range, x) '这里x即是开关
If x = 1 Then
If n <= 3500 Then
Wgeshui = 0
ElseIf n <= 5000 Then
Wgeshui = (n - 3500) * 0.03
ElseIf n <= 8000 Then
Wgeshui = (n - 3500) * 0.1 - 105
ElseIf n <= 12500 Then
Wgeshui = (n - 3500) * 0.2 - 555
ElseIf n <= 38500 Then
Wgeshui = (gz - 3500) * 0.25 - 1005
ElseIf n <= 58500 Then
Wgeshui = (n - 3500) * 0.3 - 2755
ElseIf n <= 83500 Then
Wgeshui = (n - 3500) * 0.35 - 5505
ElseIf n > 83500 Then
Wgeshui = (n - 3500) * 0.45 - 13505
End If
ElseIf x = 2 Then
If n <= 1500 * 0.03 Then
Wgeshui = n / 0.03 + 3500
ElseIf n <= 4500 * 0.1 Then
Wgeshui = (n + 105) / 0.1 + 3500
ElseIf n <= 9000 * 0.2 Then
Wgeshui = (n + 555) / 0.2 + 3500
ElseIf n <= 35000 * 0.25 Then
Wgeshui = (n + 1005) / 0.25 + 3500
ElseIf n <= 55000 * 0.3 Then
Wgeshui = (n + 2755) / 0.3 + 3500
ElseIf n <= 80000 * 0.35 Then
Wgeshui = (n + 5505) / 0.35 + 3500
ElseIf n > 80000 * 0.45 Then
Wgeshui = (n + 13505) / 0.45 + 3500
End If
End If
End Function
decheng
发表于 2012-2-21 00:10
老师您好 不好意思我现在重新跟帖了 因为之前没有考虑好工资在3500以下的情况
您看一下这个代码是否正确
Function Wgeshui(n, x) '这里x即是开关
If x = 1 Then
If n - 3500 <= 1500 Then
Wgeshui = 0
ElseIf n - 3500 <= 1500 Then
Wgeshui = (n - 3500) * 0.03 - 0
ElseIf n - 3500 <= 4500 Then
Wgeshui = (n - 3500) * 0.1 - 105
ElseIf n - 3500 <= 9000 Then
Wgeshui = (n - 3500) * 0.2 - 555
ElseIf n - 3500 <= 35000 Then
Wgeshui = (n - 3500) * 0.25 - 1005
ElseIf n - 3500 <= 55000 Then
Wgeshui = (n - 3500) * 0.3 - 2755
ElseIf n - 3500 <= 80000 Then
Wgeshui = (n - 3500) * 0.35 - 5505
ElseIf n - 3500 > 80000 Then
Wgeshui = (n - 3500) * 0.45 - 13505
End If
ElseIf x = 2 Then
If n <= 45 Then
Wgeshui = (n) / 0.03 + 3500
ElseIf n <= 345 Then
Wgeshui = (n + 105) / 0.1 + 3500
ElseIf n <= 1245 Then
Wgeshui = (n + 555) / 0.2 + 3500
ElseIf n <= 7745 Then
Wgeshui = (n + 1005) / 0.25 + 3500
ElseIf n <= 13745 Then
Wgeshui = (n + 2755) / 0.3 + 3500
ElseIf n <= 22495 Then
Wgeshui = (n + 5505) / 0.35 + 3500
ElseIf n > 22495 Then
Wgeshui = (n + 13505) / 0.45 + 3500
End If
End If
End Function
yijundanny
发表于 2012-2-21 10:12
**** Hidden Message *****
vbamaster
发表于 2012-2-21 18:29
Function WGeShui(gz As Range, x As Integer)'注gz为引用单元格中的工资数额
Dim lShuoDeE As Long
lShuoDeE = gz - 3500
If x = 1 Then
If lShuoDeE <= 0 Then
WGeShui = 0
ElseIf lShuoDeE <= 1500 Then
WGeShui = lShuoDeE * 0.03
ElseIf lShuoDeE > 1500 And lShuoDeE <= 4500 Then
WGeShui = lShuoDeE * 0.1 - 105
ElseIf lShuoDeE > 4500 And lShuoDeE <= 9000 Then
WGeShui = lShuoDeE * 0.2 - 555
ElseIf lShuoDeE > 9000 And lShuoDeE <= 35000 Then
WGeShui = lShuoDeE * 0.25 - 1005
ElseIf lShuoDeE > 35000 And lShuoDeE <= 55000 Then
WGeShui = lShuoDeE * 0.3 - 2755
ElseIf lShuoDeE > 55000 And lShuoDeE <= 80000 Then
WGeShui = lShuoDeE * 0.35 - 5505
ElseIf lShuoDeE > 80000 Then
WGeShui = lShuoDeE * 0.45 - 13505
End If
Else
If gz = 0 Then
WGeShui = "不超过3500元"
ElseIf gz > 0 And gz <= 105 Then
If (105 - gz) < (105 / 2) Then
WGeShui = (gz + 105) / 0.1 + 3500
Else
WGeShui = (gz + 0) / 0.03 + 3500
End If
ElseIf gz > 105 And gz <= 555 Then
If (555 - gz) < (555 / 2) Then
WGeShui = (gz + 555) / 0.2 + 3500
Else
WGeShui = (gz + 105) / 0.1 + 3500
End If
ElseIf gz > 555 And gz <= 1005 Then
If (1005 - gz) < (1005 / 2) Then
WGeShui = (gz + 1005) / 0.25 + 3500
Else
WGeShui = (gz + 555) / 0.2 + 3500
End If
ElseIf gz > 1005 And gz <= 2755 Then
If (2755 - gz) < (2755 / 2) Then
WGeShui = (gz + 2755) / 0.3 + 3500
Else
WGeShui = (gz + 1005) / 0.25 + 3500
End If
ElseIf gz > 2755 And gz <= 5505 Then
If (5505 - gz) < (5505 / 2) Then
WGeShui = (gz + 555) / 0.35 + 3500
Else
WGeShui = (gz + 2755) / 0.3 + 3500
End If
ElseIf gz > 5505 Then
WGeShui = (gz + 13505) / 0.45 + 3500
End If
End If
End Function
这回经测试一切正确,兰版辛苦了。
D05:vbamaster
ykymj
发表于 2012-2-21 19:04
本帖最后由 ykymj 于 2012-2-21 19:07 编辑
ykymj 发表于 2012-2-19 13:26 http://www.excelpx.com/static/image/common/back.gif
Function wgeshui(n As Range, x As Range)
If x = 1 Then
If n - 3500 < 0 Then
校长,我再次放到模块里,wgeshui()函数可以出结果的。怪了