第六讲练习题：正反计算个税和工资（奖励积分3分）

兰色幻想 发表于 2012-2-16 10:07

本帖最后由兰色幻想于 2012-2-22 09:08 编辑

   本课的作业第二题是编写可以计算个税的自定义函数，那么本课练习题就在这个题目基础上增加一些难度，要求编写一个即可根据工资计算个税，又可以根据个税倒推出工资的自定义函数. WgeShui
   提示1：
         为了达到可以正反计算的目的，可以设置两个参数，第一个参数引用工资或个税，第二个参数设置一个开关，用户可以通过它来决定正算或倒算。即
         Function Wgeshui（n,x）'这里x即是开关
               IF x=1 then
                  由工资算个税的代码
               elseIF x=2 then
                  由个税倒推工资的代码
            End IF
         End function
   =WgeShui（a1,1) 可以根据A1的金额计算个税
   =Wgeshui(a1,2) 可以根据A1的金额倒推出工资总额
提示2
      IF判断可以设置多层。如：
               IF 条件 then
                     IF 条件 then如果外层判断成立，再次进行里层判断
                        ...
                     End IF ‘里层也需要有End IF和IF对应
               elseif 条件 then
                     ....
               End if
   提示3：倒算的方法
            倒推时关键是税率和速算扣除数怎么确定，我们可以用税额和速算扣除数进行对比，在税率表中找到比税额大又最近的速算扣除数，那么这一层次就是所求税率和速算扣除数。
            如500，我们在税率表中找到555这一档。555就是速算扣除数，20%就是税率，然后计算出工资总额即可
            工资税额=(500+555)/0.2+3500=8775

         答案直接跟贴上传，只有做对的才奖励，所以大家上交前一定要测试正确可用了，再提交答案。

jinglingshu 发表于 2012-2-16 10:33

谢谢校长提示，还要好好学习学习

zjcat35 发表于 2012-2-16 10:45

E02:zjcat35Function Wgeshui(n, x)
Dim k As Long
If x = 1 Then
   k = n - 3500
Select Case k
Case Is < 0
   Wgeshui = 0
Case Is < 1500
   Wgeshui = k * 0.03 - 0
Case Is < 4500
   Wgeshui = k * 0.1 - 105
Case Is < 9000
   Wgeshui = k * 0.2 - 555
Case Is < 35000
   Wgeshui = k * 0.25 - 1005
Case Is < 55000
   Wgeshui = k * 0.3 - 2755
Case Is < 80000
   Wgeshui = k * 0.35 - 5505
Case Is >= 80000
Wgeshui = k * 0.45 - 13505
End Select
ElseIf x = 2 Then
   Select Case n
   Case Is > 22495
      Wgeshui = (n + 13505) / 0.45 + 3500
   Case Is > 13745
      Wgeshui = (n + 5505) / 0.35 + 3500
   Case Is > 7745
      Wgeshui = (n + 2755) / 0.3 + 3500
   Case Is > 1245
      Wgeshui = (n + 1005) / 0.25 + 3500
   Case Is > 345
      Wgeshui = (n + 555) / 0.2 + 3500
   Case Is > 45
      Wgeshui = (n + 105) / 0.1 + 3500
   Case Is > 0
      Wgeshui = n / 0.03 + 3500
   End Select
End If
End Function

hrpotter 发表于 2012-2-16 11:08

本帖最后由 hrpotter 于 2012-2-16 11:09 编辑

C12:hrpotter Function Wgeshui(n, x) '这里x即是开关
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 <= 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
   Else
         Wgeshui = (n + 13505) / 0.45 + 3500
   End If
End If
End Function

yangrenchao 发表于 2012-2-16 11:47

vba入门21组呱呱101826590

本帖最后由 yangrenchao 于 2012-2-20 18:19 编辑

Function geshui(gg As Range) '用select case编写
Select Case gg.Value - 3500
Case Is > 80000
geshui = (gg.Value - 3500) * 0.45 - 13505
Case Is >= 55000
geshui = (gg.Value - 3500) * 0.35 - 5505
Case Is >= 35000
geshui = (gg.Value - 3500) * 0.3 - 2705
Case Is >= 9000
geshui = (gg.Value - 3500) * 0.25 - 1005
Case Is >= 4500
geshui = (gg.Value - 3500) * 0.2 - 555
Case Is >= 1500
geshui = (gg.Value - 3500) * 0.1 - 105
Case Is < 1500
geshui = 0
End Select
End Function

windimi007 发表于 2012-2-16 12:20

D组学委:windimi007Function Wgeshui(n#, Optional x As Byte = 1)
Dim ynssd&
If x = 1 Then
   ynssd = n - 3500
   Select Case ynssd
   Case Is < -3500
         Wgeshui = "工资输入有误！"
   Case Is <= 0
         Wgeshui = 0
   Case Is <= 1500
         Wgeshui = ynssd * 0.03
   Case Is <= 4500
         Wgeshui = ynssd * 0.1 - 105
   Case Is <= 9000
         Wgeshui = ynssd * 0.2 - 555
   Case Is <= 35000
         Wgeshui = ynssd * 0.25 - 1005
   Case Is <= 55000
         Wgeshui = ynssd * 0.3 - 2755
   Case Is <= 80000
         Wgeshui = ynssd * 0.35 - 5505
   Case Else
         Wgeshui = ynssd * 0.45 - 13505
   End Select
ElseIf x = 2 Then
   Select Case n
   Case Is < 0
         Wgeshui = "税额不可能小于0！"
   Case 0
         Wgeshui = "工资小于3500RMB！"
   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-16 12:41

首先根据作业中的数据反推出各个税率的最大值

然后再写代码

Function 正反算(金额, 算法)
Dim SY As Double, SS As Integer
If 算法 = "正算" Then
Select Case 金额 - 3500
Case Is <= 0
   SY = 1
Case Is <= 1500
   SY = 0.03
   SS = 0
Case Is <= 4500
   SY = 0.1
   SS = 105
Case Is <= 9000
   SY = 0.2
   SS = 555
Case Is <= 35000
   SY = 0.25
   SS = 1005
Case Is <= 55000
   SY = 0.3
   SS = 2755
Case Is <= 8000
   SY = 0.35
   SS = 5505
Case Else
   SY = 0.4
   SS = 13505
End Select
正反算 = (金额 - 3500) * SY - SS
If SY = 1 Then 正反算 = 0
ElseIf 算法 = "反算" Then
Select Case 金额
   Case 0 To 45
   SY = 0.03
   SS = 0
   Case Is < 346
   SY = 0.1
   SS = 105
   Case Is < 1246
   SY = 0.2
   SS = 155
   Case Is < 7746
   SY = 0.25
   SS = 1005
   Case Is < 13746
   SY = 0.3
   SS = 2755
   Case Is < 18946
   SY = 0.35
   SS = 5505
   Case Is > 18945
   SY = 0.45
   SS = 13505
End Select
正反算 = (金额 + SS) / SY + 3500
Else
正反算 = "参数二只能是“正算”和“反算”,注意添加上英文引号"
End If
End Function

dsjohn 发表于 2012-2-16 13:06

本帖最后由 dsjohn 于 2012-2-17 13:25 编辑

Function WGeShui(n As Range, x)
Dim Rg As Long
Rg = (n - 3500)
If x = 1 Then
   If Rg <= 0 Then
         WGeShui = 0
   ElseIf Rg <= 1500 Then
                  WGeShui = Rg * (3/ 100)
   ElseIf Rg <= 4500 Then
         WGeShui = Rg * (10 / 100) - 105
   ElseIf Rg <= 9000 Then
         WGeShui = Rg * (20 / 100) - 555
   ElseIf Rg <= 35000 Then
         WGeShui = Rg * (25 / 100) - 1005
   ElseIf Rg <= 55000 Then
         WGeShui = Rg * (30 / 100) - 2755
   ElseIf Rg <= 80000 Then
         WGeShui = Rg * (35 / 100) - 5505
   ElseIf Rg > 80000 Then
         WGeShui = Rg * (45 / 100) - 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 <= 21270 Then
         WGeShui = (n + 5505) / 0.35 + 3500
   ElseIf n > 21270 Then
         WGeShui = (n + 13505) / 0.45 + 3500
   End If
End If
End Function

SZHPBS 发表于 2012-2-16 13:20

szhpbs 上交练习，谢谢老师批改

sliang28 发表于 2012-2-16 13:31

本帖最后由 sliang28 于 2012-2-16 21:58 编辑

C组 sliang28 提交答案

Function WGeShui(n As Double, x As Integer) As Variant
Dim NaShui As Double
Dim ns(10) As Double
Dim gz(10) As Double
If x = 1 Then
NaShui = n - 3500
ns(1) = 0
ns(2) = NaShui * 0.03 - 0
ns(3) = NaShui * 0.1 - 105
ns(4) = NaShui * 0.2 - 555
ns(5) = NaShui * 0.25 - 1005
ns(6) = NaShui * 0.3 - 2755
ns(7) = NaShui * 0.35 - 5505
ns(8) = NaShui * 0.45 - 13505
WGeShui = WorksheetFunction.Max(ns(1), ns(2), ns(3), ns(4), ns(5), ns(6), ns(7), ns(8))
ElseIf x = 2 Then
gz(1) = n / 0.03 + 3500
gz(2) = (n + 105) / 0.1 + 3500
gz(3) = (n + 555) / 0.2 + 3500
gz(4) = (n + 1005) / 0.25 + 3500
gz(5) = (n + 2755) / 0.3 + 3500
gz(6) = (n + 5505) / 0.35 + 3500
gz(7) = (n + 13505) / 0.45 + 3500
If n = 0 Then
WGeShui = "工资都不够纳税，丢人"
Else
WGeShui = WorksheetFunction.Min(gz(1), gz(2), gz(3), gz(4), gz(5), gz(6), gz(7))
End If
End If

页: [1] 2 3 4 5 6 7

Excel精英培训网's Archiver

第六讲练习题：正反计算个税和工资（奖励积分3分）

vba入门21组 呱呱101826590

vba入门21组呱呱101826590