Function Wgeshui(n As Range, x)
If x = 1 Then
Select Case n - 3500
Case Is > 80000
Wgeshui = (n - 3500) * 0.45 - 13505
Case Is > 55000
Wgeshui = (n - 3500) * 0.35 - 5505
Case Is > 35000
Wgeshui = (n - 3500) * 0.3 - 2755
Case Is > 9000
Wgeshui = (n - 3500) * 0.25 - 1005
Case Is > 4500
Wgeshui = (n - 3500) * 0.2 - 555
Case Is > 1500
Wgeshui = (n - 3500) * 0.1 - 105
Case Is > 0
Wgeshui = (n - 3500) * 0.03
End Select
ElseIf x = 2 Then
Select Case n
Case Is > (80000 * 0.35 - 5505)
Wgeshui = (n + 13505) / 0.45 + 3500
Case Is > (55000 * 0.3 - 2755)
Wgeshui = (n + 5505) / 0.35 + 3500
Case Is > (35000 * 0.25 - 1005)
Wgeshui = (n + 2755) / 0.3 + 3500
Case Is > (9000 * 0.2 - 555)
Wgeshui = (n + 1005) / 0.25 + 3500
Case Is > (4500 * 0.1 - 105)
Wgeshui = (n + 555) / 0.2 + 3500
Case Is > 1500 * 0.03
Wgeshui = (n + 105) / 0.1 + 3500
Case Is > 0
Wgeshui = n / 0.03 + 3500
End Select
End If
End Function
Function wgeshui(n, x)
Const a As Integer = 3500
If x = 1 Then
If (n - a) <= 1500 Then
wgeshui = (n - a) * 0.03
ElseIf (n - a) <= 4500 Then
wgeshui = (n - a) * 0.1 - 105
ElseIf (n - a) <= 9000 Then
wgeshui = (n - a) * 0.2 - 555
ElseIf (n - a) <= 35000 Then
wgeshui = (n - a) * 0.25 - 1005
ElseIf (n - a) <= 55000 Then
wgeshui = (n - a) * 0.3 - 2755
ElseIf (n - a) <= 80000 Then
wgeshui = (n - a) * 0.35 - 5505
ElseIf (n - a) > 80000 Then
wgeshui = (n - a) * 0.45 - 13505
End If
ElseIf x = 0 Then
If n <= 45 Then
wgeshui = n / 0.03 + a
ElseIf n <= 345 Then
wgeshui = (n + 105) / 0.1 + a
ElseIf n <= 1245 Then
wgeshui = (n + 555) / 0.2 + a
ElseIf n <= 7745 Then
wgeshui = (n + 1005) / 0.25 + a
ElseIf n <= 13745 Then
wgeshui = (n + 2755) / 0.3 + a
ElseIf n <= 22595 Then
wgeshui = (n + 5505) / 0.35 + a
ElseIf n > 22595 Then
wgeshui = (n + 13505) / 0.45 + a
End If
End If
End Function
回复一下，看看内容
本帖最后由 chrissha 于 2012-2-17 13:44 编辑
C10: chrissha
Function WgeShui(n, x)
SuoDe = n - 3500
If x = 1 Then
If SuoDe <= 0 Then
WgeShui = 0
ElseIf SuoDe <= 1500 Then
WgeShui = SuoDe * 3 / 100 - 0
ElseIf SuoDe > 1500 And SuoDe <= 4500 Then
WgeShui = SuoDe * 10 / 100 - 105
ElseIf SuoDe > 4500 And SuoDe <= 9000 Then
WgeShui = SuoDe * 20 / 100 - 555
ElseIf SuoDe > 9000 And SuoDe <= 35000 Then
WgeShui = SuoDe * 25 / 100 - 1005
ElseIf SuoDe > 35000 And SuoDe <= 55000 Then
WgeShui = SuoDe * 30 / 100 - 2755
ElseIf SuoDe > 55000 And SuoDe <= 80000 Then
WgeShui = SuoDe * 35 / 100 - 5505
ElseIf SuoDe > 80000 Then
WgeShui = SuoDe * 45 / 100 - 13505
End If
ElseIf x = 2 Then
If n <= 0 Then
WgeShui = "工资不超过3500"
ElseIf n <= 45 Then
WgeShui = (n + 105) / 0.1 + 3500
ElseIf n <= 345 Then
WgeShui = (n + 555) / 0.2 + 3500
ElseIf n <= 1245 Then
WgeShui = (n + 1005) / 0.25 + 3500
ElseIf n <= 7745 Then
WgeShui = (n + 2755) / 0.3 + 3500
ElseIf n <= 13745 Then
WgeShui = (n + 5505) / 0.35 + 3500
ElseIf n <= 22495 Or n > 22495 Then
WgeShui = (n + 13505) / 0.45 + 3500
End If
End If
End Function
:dizzy::dizzy::dizzy:晕死了，折腾一天，先请老师指点，纯个人折腾代码：
(我个人的困惑再于，变量设置后，不知道应该谁用哪个变量……）
Function Wgeshui(n, x) '这里x即是开关正算个税，倒推工资代码
Dim i As Integer
Dim AR As Integer'AR代表速算扣额
i = n - 3500
If x = 1 Then
If n > 0 And n <= 3500 Then
Wgeshui = 0
ElseIf i <= 1500 Then
Wgeshui = i * 0.03 - 0
ElseIf i <= 4500 Then
Wgeshui = i * 0.1 - 105
ElseIf i <= 9000 Then
Wgeshui = i * 0.2 - 555
ElseIf i <= 35000 Then
Wgeshui = i * 0.25 - 1005
ElseIf i <= 55000 Then
Wgeshui = i * 0.3 - 2755
ElseIf i <= 80000 Then
Wgeshui = i * 0.35 - 5505
ElseIf i > 80000 Then
Wgeshui = i * 0.45 - 13505
End If
ElseIf x = 2 Then '工资税额=(500+555)/0.2+3500=8775
If AR > 0 And AR < 105 Then
Wgeshui = AR / 0.03 + 3500
ElseIf AR >= 105 Then
Wgeshui = (AR + 105) / 0.1 + 3500
ElseIf AR >= 555 Then
Wgeshui = (AR + 555) / 0.2 + 3500
ElseIf AR >= 1005 Then
Wgeshui = (AR + 1005) / 0.25 + 3500
ElseIf AR >= 2755 Then
Wgeshui = (AR + 2755) / 0.3 + 3500
ElseIf AR >= 5505 Then
Wgeshui = (AR + 5505) / 0.35 + 3500
ElseIf AR >= 13505 Then
Wgeshui = (AR + 13505) / 0.45 + 3500
End If
End If
End Function
下面代码是通过看群里聊天信息获得的灵感，敬请老师批改：（真希望是对的）
Function Wgeshui(n, x) '这里x即是开关正算个税，倒推工资代码
Dim i As Currency
i = n - 3500
If x = 1 Then
If n > 0 And n <= 3500 Then
Wgeshui = 0
ElseIf i <= 1500 Then
Wgeshui = i * 0.03 - 0
ElseIf i <= 4500 Then
Wgeshui = i * 0.1 - 105
ElseIf i <= 9000 Then
Wgeshui = i * 0.2 - 555
ElseIf i <= 35000 Then
Wgeshui = i * 0.25 - 1005
ElseIf i <= 55000 Then
Wgeshui = i * 0.3 - 2755
ElseIf i <= 80000 Then
Wgeshui = i * 0.35 - 5505
ElseIf i > 80000 Then
Wgeshui = i * 0.45 - 13505
End If
ElseIf x = 2 Then '工资税额=(500+555)/0.2+3500=8775
If n <= 1500 Then
Wgeshui = (1500 * 0.03) / 0.03 + 3500
ElseIf n <= 4500 Then
Wgeshui = 4500 + 3500 '((4500*0.1-105)+105)/0.1+3500 绕半天这步可以直接用4500+3500吧
ElseIf n <= 9000 Then
Wgeshui = 9000 + 3500
ElseIf n <= 35000 Then
Wgeshui = 35000 + 3500
ElseIf n <= 55000 Then
Wgeshui = 55000 + 3500
ElseIf n <= 80000 Then
Wgeshui = 80000 + 3500
ElseIf n > 80000 Then
Wgeshui = 10000 + 3500
End If
End If
End Function
:'$商量商量给个辛苦分吧……
交下练习！
H15:hactnet
这里的A1我用的是变量n代替哦！希望不要错！
Function wgeshui(n, x)
Dim sdk, sl, kcs, zgz
If x = 1 Then
sdk = n - 3500
If sdk <= 0 Then
sl = 0
kcs = 0
ElseIf sdk <= 1500 Then
sl = 0.03
kcs = 0
ElseIf sdk <= 4500 Then
sl = 0.1
kcs = 105
ElseIf sdk <= 9000 Then
sl = 0.2
kcs = 555
ElseIf sdk <= 35000 Then
sl = 0.25
kcs = 1005
ElseIf sdk <= 55000 Then
sl = 0.3
kcs = 2755
ElseIf sdk <= 80000 Then
sl = 0.35
kcs = 5505
ElseIf sdk > 80000 Then
sl = 0.45
kcs = 13505
End If
wgeshui = (n - 3500) * sl - kcs
ElseIf x = 2 Then
If n > 22495 Then '工资=(个税额+扣除数)/税率+3500
zgz = (n + 13505) / 0.45 + 3500
ElseIf n > 13745 Then
zgz = (n + 5505) / 0.35 + 3500
ElseIf n > 7745 Then
zgz = (n + 2755) / 0.3 + 3500
ElseIf n > 1245 Then
zgz = (n + 1005) / 0.25 + 3500
ElseIf n > 345 Then
zgz = (n + 555) / 0.2 + 3500
ElseIf n > 45 Then
zgz = (n + 105) / 0.1 + 3500
ElseIf n > 0 Then
zgz = (n + 0) / 0.03 + 3500
ElseIf n <= 0 Then
zgz = (n + 0) + 3500
End If
wgeshui = zgz
End If
End Function
Function Wgeshui(n, x) '这里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 < 0
Wgeshui = 0
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
本帖最后由 从从容容 于 2012-2-17 20:34 编辑
学习兰校长的VBA80集，尝试用数组来完成，有不妥之处，望老师多多指教。不胜感激！！！！
Public Function Wgeshui(n, x)
Dim I As Long
Dim s As Long
Dim ARR1, ARR2, ARR3
ARR1 = Array(1500, 4500, 9000, 35000, 55000, 80000)
ARR2 = Array(0.03, 0.1, 0.2, 0.25, 0.3, 0.35)
ARR3 = Array(0, 105, 555, 1005, 2755, 5505)
If x = 1 Then
If n < 3500 Then
MsgBox "你的工资达不到起征点"
Exit Function
End If
For I = 0 To UBound(ARR1)
If (n - 3500) <= ARR1(I) Then
s = (n - 3500) * ARR2(I) - ARR3(I)
Exit For
ElseIf (n - 3500) > 80000 Then
s = (n - 3500) * 0.45 - 13505
End If
Next I
ElseIf x = 2 Then
If n <= 0 Then
MsgBox "你的工资等于或小于起征点"
Exit Function
End If
For I = 0 To UBound(ARR1)
If n < ARR1(I) * ARR2(I) - ARR3(I) Then
s = (n + ARR3(I)) / ARR2(I) + 3500
Exit For
ElseIf n > 80000 * 0.45 - 13505 Then
s = (n + 13505) / 0.45 + 3500
End If
Next I
End If
Wgeshui = s
End Function
本帖最后由 ykfexcel 于 2012-2-18 10:31 编辑
Function Wgeshui(n, 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 = (n - 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 = 0 Then
Wgeshui = "工资小于等于3500"
ElseIf 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
Function wgeshui(n As Range, x)
If x = 1 Then
If n - 3500 <= 0 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 - 45) / 0.1 + 1500 + 3500
ElseIf n <= 1245 Then
wgeshui = (n - 345) / 0.2 + 4500 + 3500
ElseIf n <= 7745 Then
wgeshui = (n - 1245) / 0.25 + 9000 + 3500
ElseIf n <= 13745 Then
wgeshui = (n - 7745) / 0.3 + 35000 + 3500
ElseIf n <= 22495 Then
wgeshui = (n - 13745) / 0.35 + 55000 + 3500
ElseIf n > 22495 Then
wgeshui = (n - 22495) / 0.45 + 80000 + 3500
End If
End If
End Function