第六讲练习题：正反计算个税和工资（奖励积分3分） - 第4页 - Excel VBA实战营2期 - Excel精英培训网

bikong01 发表于 2012-2-17 22:33

提示3：好像不正确
工资8775那个税是=(8775-3500)*0.2-555=500 依据提示3 工资税额=(500+555)/0.2+3500=8775 没问题
如果工资9500 个税=(9500-3500)*0.2-555=645 依据提示3 税率表中找到比税额大又最近的速算扣除数，那么这一层次就是所求税率和速算扣除数。那就是 25% 1005这一栏
工资=(645+1005)/0.25+3500=10100和开始的9500不一致

开心妙妙 发表于 2012-2-18 09:04

Function Wgeshui(n As Double, x As Integer)
If x = 1 Then
   Select Case n
   Case Is <= 3500
         Wgeshui = 0
   Case Is <= 5000
         Wgeshui = (n - 3500) * 0.03
   Case Is <= 8000
         Wgeshui = (n - 3500) * 0.1 - 105
   Case Is <= 12500
         Wgeshui = (n - 3500) * 0.2 - 555
   Case Is <= 38500
         Wgeshui = (n - 3500) * 0.25 - 1005
   Case Is <= 58500
         Wgeshui = (n - 3500) * 0.3 - 2755
   Case Is <= 83500
         Wgeshui = (n - 3500) * 0.35 - 5505
   Case Is > 83500
         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 Is > 22495
         Wgeshui = (n + 13505) / 0.45 + 3500
   End Select
End If
End Function

tcn541 发表于 2012-2-18 11:07

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 = (n13505) / 0.45 + 3500
End If
End If
End Function

370311805 发表于 2012-2-18 17:00

老师，提示3可能有误，不能按这个方法倒算。
举个例子，当工资为9055时，查表可知税率为20%，速算扣除数为555，所交个税应为556。
若按老师给的提示，在税率表中找到比税额大又最近的速算扣除数，那么这一层次就是所求税率和速算扣除数，那么速算扣除数为1005了，税率也变成了25%了，那么最终计算出的工资9744，与实际不符。
所以倒推应该计算临界值，以下是我的解答：
---------------------------------------------------------------------------------------------------------------------------------------------
Function Wgeshui(n, switch)
Dim x As Single
Dim y As Single
Dim z As Single
z = n - 3500
If switch = 1 Then
   If z <= 0 Then
         x = 0
         y = 0
   ElseIf z <= 1500 Then
         x = 0.03
         y = 0
   ElseIf z <= 4500 Then
         x = 0.1
         y = 105
   ElseIf z <= 9000 Then
         x = 0.2
         y = 555
   ElseIf z <= 35000 Then
         x = 0.25
         y = 1005
   ElseIf z <= 55000 Then
         x = 0.3
         y = 2755
   ElseIf z <= 80000 Then
         x = 0.35
         y = 5505
   ElseIf z > 80000 Then
         x = 0.45
         y = 13505
   End If
   Wgeshui = z * x - y
ElseIf switch = 2 Then
   If n <= 45 Then
         x = 0.03
         y = 0
   ElseIf n <= 345 Then
         x = 0.1
         y = 105
   ElseIf z <= 1245 Then
         x = 0.2
         y = 555
   ElseIf z <= 7745 Then
         x = 0.25
         y = 1005
   ElseIf z <= 13745 Then
         x = 0.3
         y = 2755
   ElseIf z <= 22495 Then
         x = 0.35
         y = 5505
   ElseIf z > 22495 Then
         x = 0.45
         y = 13505
   End If
   Wgeshui = (n + y) / x + 3500
End If
End Function

decheng 发表于 2012-2-19 12:21

老师您好您看一下我的这个答案是否正确
Function Wgeshui(n, x) '这里x即是开关
               If x = 1 Then
                     If 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

ykymj 发表于 2012-2-19 13:26

本帖最后由 ykymj 于 2012-2-19 15:28 编辑

Function wgeshui(n As Range, x As Range)
If x = 1 Then
If n - 3500 < 0 Then
wgeshui = 0
ElseIf n - 3500 <= 1500 Then
wgeshui = (n - 3500) * 0.03
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
Else: wgeshui = (n - 3500) * 0.45 - 13505
End If

ElseIf x = 2 Then
If n > 22495 Then
wgeshui = (n + 13505) / 0.45 + 3500
ElseIf n > 13745 Then
wgeshui = (n + 5505) / 0.35 + 3500
ElseIf n > 7745 Then
wgeshui = (n + 2755) / 0.3 + 3500
ElseIf n > 1245 Then
wgeshui = (n + 1005) / 0.25 + 3500
ElseIf n > 345 Then
wgeshui = (n + 555) / 0.2 + 3500
ElseIf n > 45 Then
wgeshui = (n + 105) / 0.1 + 3500
ElseIf n > 0 Then
wgeshui = n / 0.03 + 3500
Else: wgeshui = "<=3500"
End If
Else: MsgBox "B列输入有误,请重新输入。提示:A列若为工资,请在B列输入1计算个税,否则B列输入2计算工资"
End If

End Function

禾火亦 发表于 2012-2-19 15:14

C07 禾火亦

禾火亦 发表于 2012-2-19 15:17

C07 禾火亦Function Wgeshui(n, x)
Dim k As Long
k = n - 3500
If x = 1 Then
   If k <= 0 Then
         Wgeshui = 0
   ElseIf k <= 1500 Then
         Wgeshui = k * 0.03
   ElseIf k <= 4500 Then
         Wgeshui = k * 0.1 - 105
   ElseIf k <= 9000 Then
         Wgeshui = k * 0.2 - 555
   ElseIf k <= 35000 Then
         Wgeshui = k * 0.25 - 1005
   ElseIf k <= 55000 Then
         Wgeshui = k * 0.3 - 2755
   ElseIf k <= 80000 Then
         Wgeshui = k * 0.35 - 5505
   ElseIf k > 80000 Then
         Wgeshui = k * 0.45 - 13505
   End If
End If
If 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 < 22945 Then
         Wgeshui = (n + 5505) / 0.35 + 3500
   ElseIf n > 22945 Then
         Wgeshui = (n + 13505) / 0.45 + 3500
   End If
End If
End Function

qzc804030 发表于 2012-2-19 23:26

反算有误差,不知道我做的对不对
E组长:qzc804030Function GeShui(gz)
If gz <= 3500 Then
GeShui = 0
ElseIf gz <= 5000 Then
GeShui = (gz - 3500) * 0.03
ElseIf gz <= 8000 Then
GeShui = (gz - 3500) * 0.1 - 105
ElseIf gz <= 12500 Then
GeShui = (gz - 3500) * 0.2 - 555
   ElseIf gz <= 38500 Then
   GeShui = (gz - 3500) * 0.25 - 1005
      ElseIf gz <= 58500 Then
      GeShui = (gz - 3500) * 0.3 - 2755
   ElseIf gz <= 83500 Then
GeShui = (gz - 3500) * 0.35 - 5505
ElseIf gz > 83500 Then
GeShui = (gz - 3500) * 0.45 - 13505
End If
End Function
Function Wgeshui(n, x)
If x = 1 Then
If n <= 5000 And n > 3500 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 And n <= 45 Then
      Wgeshui = (n + 0) / 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 <= 24495 Then
Wgeshui = (n + 5505) / 0.35 + 3500
ElseIf n > 24495 Then
Wgeshui = (n + 13505) / 0.45 + 3500
End If
End If
End Function

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

页: 1 2 3 [4] 5 6 7

Excel精英培训网's Archiver