统计VBA学习小组正式组第六课（第七讲）的积分帖之作业上交贴（第8周） - 第6页 - Excel VBA实战营2期 - Excel精英培训网

w2001pf 发表于 2012-2-17 09:29

Sub 单元格填充()
With Sheets("sheet3")
.Range("A1") = 100
.Range("A3") = 900
.Range("A5") = 100
.Range("A8") = 4500
End With
End Sub

Function GeShui(gz) '注gz为引用单元格中的工资数额
Select Case gz
Case Is <= 3500
GeShui = 0
Case Is <= 5000
GeShui = (gz - 3500) * 0.03
Case Is <= 8000
GeShui = (gz - 3500) * 0.1 - 105
Case Is <= 12500
GeShui = (gz - 3500) * 0.2 - 555
Case Is <= 38500
GeShui = (gz - 3500) * 0.25 - 1005
Case Is <= 58500
GeShui = (gz - 3500) * 0.3 - 2755
Case Is <= 83500
GeShui = (gz - 3500) * 0.35 - 5505
Case Is > 83500
GeShui = (gz - 3500) * 0.45 - 13505
Case Else
End Select
End Function

sunjing-zxl 发表于 2012-2-17 09:53

E学委:sunjing-zxlSub 单元格填充()
With Sheets("sheets")
   .Range("A1") = 100
   .Range("A3") = 900
   .Range("A5") = 100
   .Range("A8") = 4500
End With
End Sub
Function GeShui(gz As Double)
If gz - 3500 <= 0 Then
   GeShui = 0
ElseIf gz - 3500 <= 1500 Then
   GeShui = (gz - 3500) * 3 / 100
ElseIf gz - 3500 <= 4500 Then
   GeShui = (gz - 3500) * 10 / 100 - 105
ElseIf gz - 3500 <= 9000 Then
   GeShui = (gz - 3500) * 20 / 100 - 555
ElseIf gz - 3500 <= 35000 Then
   GeShui = (gz - 3500) * 25 / 100 - 1005
ElseIf gz - 3500 <= 55000 Then
   GeShui = (gz - 3500) * 30 / 100 - 2755
ElseIf gz - 3500 <= 80000 Then
   GeShui = (gz - 3500) * 35 / 100 - 5505
Else
   GeShui = (gz - 3500) * 45 / 100 - 13505
End If
End Function

awxgaige 发表于 2012-2-17 10:26

改成whit语句后为
Sub 单元格填充()
With Sheets("sheet3")
      Range("A1") = 100
      Range("A3") = 900
      Range("A5") = 100
      Range("A8") = 4500
End With
End Sub
我只会做第一题.
第二题的if语句我会做出来,但定义函数我就没办法了

chrissha 发表于 2012-2-17 10:52

第一题：把下面的代码用With语句来表示。
Sub 单元格填充()
Sheets("sheet3").Range("A1") = 100
Sheets("sheet3").Range("A3") = 900
Sheets("sheet3").Range("A5") = 100
Sheets("sheet3").Range("A8") = 4500
End Sub
答案：
Sub 单元格填充()
With Sheets("SHEET3")
.Range("A1") = 100
.Range("A3") = 900
.Range("A5") = 100
.Range("A8") = 4500
End With
End Sub

第二题：根据下面的条件，编写计算个人所得税的自定义函数GeShui
个人所得税的计算公式=(工资-3500)*税率-速算扣除数

全月应纳税所得额税率速算扣除数（元）
全月应纳税额不超过1500元3%0
全月应纳税额超过1500元至4500元10%105
全月应纳税额超过4500元至9000元20%555
全月应纳税额超过9000元至35000元25%1005
全月应纳税额超过35000元至55000元30%2755
全月应纳税额超过55000元至80000元35%5505
全月应纳税额超过80000元45%13505

例如：单元格A2中工资为6000。如果人工计算应该为：全月应纳税所得额=6000-3500=2500
从上表中2500对应的税率是10%，速算扣除数为105，所以工资6000的个税应该为：=（6000-3500）*10%-105
要求用自定函数完成以上功能
Function GeShui(gz) ‘注gz为引用单元格中的工资数额
你的答案
End Function

答案：
Function GeShui(gz)
SuoDe = gz - 3500
If SuoDe <= 0 Then
GeShui = 0
ElseIf SuoDe <= 1500 Then
GeShui = SuoDe * 3 / 100 - 0
ElseIf SuoDe > 1500 And SuoDe <= 4500 Then
GeShui = SuoDe * 10 / 100 - 105
ElseIf SuoDe > 4500 And SuoDe <= 9000 Then
GeShui = SuoDe * 20 / 100 - 555
ElseIf SuoDe > 9000 And SuoDe <= 35000 Then
GeShui = SuoDe * 25 / 100 - 1005
ElseIf SuoDe > 35000 And SuoDe <= 55000 Then
GeShui = SuoDe * 30 / 100 - 2755
ElseIf SuoDe > 55000 And SuoDe <= 80000 Then
GeShui = SuoDe * 35 / 100 - 5505
ElseIf SuoDe > 80000 Then
GeShui = SuoDe * 45 / 100 - 13505
End If
End Function

开心妙妙 发表于 2012-2-17 10:56

'第一题:把下面的代码用With语句来表示?
Sub 单元格填充作业()
With Sheets("SHEET3")
   .Range("A1") = 100
   .Range("A3") = 900
   .Range("A5") = 100
   .Range("A8") = 4500
End With
End Sub
'第二题:编写计算个人所得税的自定义函数GeShui
Function GeShui(gz As Range)
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

君子豹变 发表于 2012-2-17 12:42

第一题答案：
Sub 单元格填充()
With Sheets("sheet3")
   .Range("a1") = 100
   .Range("a3") = 900
   .Range("a5") = 100
   .Range("a8") = 4500
End With
End Sub

第二题答案：
Function geshui(gz)
Dim yns, sl, kcs
yns = gz - 3500
Select Case yns
   Case Is < 0
   MsgBox ("工资太低，不需要交税！")
   Exit Function
   Case Is < 1500
   sl = 0.03
   kcs = 0
   Case Is <= 4500
   sl = 0.1
   kcs = 105
   Case Is <= 9000
   sl = 0.2
   kcs = 555
   Case Is <= 35000
   sl = 0.25
   kcs = 1005
   Case Is <= 55000
   sl = 0.3
   kcs = 2755
   Case Is <= 80000
   sl = 0.35
   kcs = 5505
   Case Is > 80000
   sl = 0.45
   kcs = 13505
   Case Else
End Select
geshui = yns * sl - kcs
End Function

dsjohn 发表于 2012-2-17 13:20

dsjohn 发表于 2012-2-16 10:52 static/image/common/back.gif
Sub 单元格填充()
With Sheets("sheet3")
.Range("A1") = 100

第二题有错，现修改
Function GeShui(gz As Range)
If (gz - 3500) <= 0 Then
            GeShui = 0
ElseIf (gz - 3500) <=1500 Then
            GeShui = (gz - 3500)*（3/100)
ElseIf (gz - 3500) <= 4500 Then
            GeShui = (gz - 3500) * (10 / 100) - 105
   ElseIf (gz - 3500) <= 9000 Then
         GeShui = (gz - 3500) * (20 / 100) - 555
   ElseIf (gz - 3500) <= 35000 Then
         GeShui = (gz - 3500) * (25 / 100) - 1005
   ElseIf (gz - 3500) <= 55000 Then
         GeShui = (gz - 3500) * (30 / 100) - 2755
   ElseIf (gz - 3500) <= 80000 Then
         GeShui = (gz - 3500) * (35 / 100) - 5505
   ElseIf (gz - 3500) > 80000 Then
         GeShui = (gz - 3500) * (45 / 100) - 13505
   End If
End Function

hactnet 发表于 2012-2-17 13:44

交下作业了！
H:15 hactnet
Sub 单元格填充()
With Sheets("sheet3")
   .Range("A1") = 100
   .Range("A3") = 900
   .Range("A5") = 100
   .Range("A8") = 4500
End With
End Sub

Function GeShui(gz)
Dim sdk, sl, kcs
sdk = gz - 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
GeShui = (gz - 3500) * sl - kcs
End Function

jxncfxsf 发表于 2012-2-17 13:55

第1题
Sub 单元格填充()
With Sheets("Sheet3")
.Range("A1") = 100
.Range("A3") = 900
.Range("A5") = 100
.Range("A8") = 4500
End With
End Sub
第2题
Function gehui(gz As Range)
Select Case gz
Case Is <= 3500
gehiu = 0
Case Is <= 1500 + 3500
gehui = (gz - 3500) * 0.03
Case Is <= 4500 + 3500
gehui = (gz - 3500) * 0.1 - 105
Case Is <= 9000 + 3500
gehui = (gz - 3500) * 0.2 - 555
Case Is <= 35000 + 3500
gehui = (gz - 3500) * 0.25 - 1005
Case Is <= 55000 + 3500
gehui = (gz - 3500) * 0.3 - 2755
Case Is <= 80000 + 3500
gehui = (gz - 3500) * 0.35 - 5505
Case Is > 80000 + 3500
gehui = (gz - 3500) * 0.45 - 13505

End Select
End Function

亦铭发表于 2012-2-17 14:40

1、Sub 单元格填充()
With Sheets("sheet3")
.Range("A1") = 100
.Range("A3") = 900
.Range("A5") = 100
.Range("A8") = 4500
End With
2、Function GeShui(gz) '注gz为引用单元格中的工资数额
Select Case gz - 3500
Case Is <= 1500
   GeShui = (gz - 3500) * 0.03
Case Is <= 4500
   GeShui = (gz - 3500) * 0.1 - 105
Case Is <= 9000
   GeShui = (gz - 3500) * 0.2 - 555
Case Is <= 35000
   GeShui = (gz - 3500) * 0.25 - 1005
Case Is <= 55000
   GeShui = (gz - 3500) * 0.3 - 2755
Case Is <= 80000
   GeShui = (gz - 3500) * 0.35 - 5505
Case Else
   GeShui = (gz - 3500) * 0.45 - 13505
End Select
End Function

页: 1 2 3 4 5 [6] 7 8 9 10 11 12

Excel精英培训网's Archiver