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

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

zjyxp 发表于 2012-2-17 15:58

Sub 作业1()
With Sheets("sheet3")
                  .Range("A1") = 100
                  .Range("A3") = 900
                  .Range("A5") = 100
                  .Range("A8") = 4500
            End With
End Sub作业2：Function GeShui(gz As Range)
Select Case gz
Case Is <= 3500
GeShui = 0
Case Is <= 4500
GeShui = (gz - 3500) * 0.03
Case Is <= 8000
GeShui = (gz - 3500) * 0.1 - 105
Case Is <= 13500
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

ykfexcel 发表于 2012-2-17 16:21

第一题：把下面的代码用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

happym8888 发表于 2012-2-17 18:57

Sub 单元格填充()
With Sheets("Sheet3")
.Range("A1") = 100
.Range("A3") = 900
.Range("A5") = 100
.Range("A8") = 4500
End With
End Sub
Function WGeShui(gz, x)
If x = 1 Then
SuodeE = gz - 3500
Select Case SuodeE
Case Is < 0
WGeShui = 0
Case Is < 1500
WGeShui = SuodeE * 0.03
Case 1500 To 4500
WGeShui = SuodeE * 0.1 - 105
Case 4500 To 9000
WGeShui = SuodeE * 0.2 - 555
Case 9000 To 35000
WGeShui = SuodeE * 0.25 - 1005
Case 35000 To 55000
WGeShui = SuodeE * 0.3 - 2755
Case 55000 To 80000
WGeShui = SuodeE * 0.35 - 5505
Case Else
WGeShui = SuodeE * 0.45 - 13505
End Select
Else
Select Case gz
Case Is > 22495
WGeShui = (gz + 13505) / 0.45 + 3500
Case 13745 To 22495
WGeShui = (gz + 5505) / 0.35 + 3500
Case 7745 To 13745
WGeShui = (gz + 2755) / 0.3 + 3500
Case 1245 To 7745
WGeShui = (gz + 1005) / 0.25 + 3500
Case 345 To 1245
WGeShui = (gz + 555) / 0.2 + 3500
Case 45 To 345
WGeShui = (gz + 105) / 0.1 + 3500
Case 0 To 45
WGeShui = gz / 0.03 + 3500
Case Else
WGeShui = "你的工资不到3500元。"
End Select
End If
End Function

tcn541 发表于 2012-2-17 21:30

1.Sub 单元格填充()
With Sheets("sheet3")
.Range("A1") = 100
.Range("A3") = 900
.Range("A5") = 100
.Range("A8") = 4500
End with
End Sub
2.Function GeShui(gz As Range)
If gz < 3500 Then
GeShui = 0
ElseIf gz - 3500 <= 1500 Then
GeShui = (gz - 3500) * 0.03
ElseIf gz - 3500 <= 4500 Then
GeShui = (gz - 3500) * 0.1 - 105
ElseIf gz - 3500 <= 9000 Then
GeShui = (gz - 3500) * 0.2 - 555
ElseIf gz - 3500 <= 35000 Then
GeShui = (gz - 3500) * 0.25 - 1005
ElseIf gz - 3500 <= 55000 Then
GeShui = (gz - 3500) * 0.3 - 2755
ElseIf gz - 3500 <= 80000 Then
GeShui = (gz - 3500) * 0.35 - 5505
ElseIf gz - 3500 > 80000 Then
GeShui = (gz - 3500) * 0.45 - 13505
End If
End Function

gaoshuichang1 发表于 2012-2-17 22:35

第一题：
Sub 单元格填充()
With Sheets("sheet3")
.Range("A1") = 100
.Range("A3") = 900
.Range("A5") = 100
.Range("A8") = 4500
End With
End Sub
第二题：
Function Geshui1(rg As Range)
If rg - 3500 <= 0 Then
   Geshui1 = 0
ElseIf rg - 3500 <= 1500 Then
   Geshui1 = Round((rg - 3500) * 0.03, 2)
ElseIf rg - 3500 <= 4500 Then
   Geshui1 = Round((rg - 3500) * 0.1 - 105, 2)
ElseIf rg - 3500 <= 9000 Then
   Geshui1 = Round((rg - 3500) * 0.2 - 555, 2)
ElseIf rg - 3500 <= 35000 Then
   Geshui1 = Round((rg - 3500) * 0.25 - 1005, 2)
ElseIf rg - 3500 <= 55000 Then
   Geshui1 = Round((rg - 3500) * 0.3 - 2755, 2)
ElseIf rg - 3500 <= 80000 Then
   Geshui1 = Round((rg - 3500) * 0.35 - 5505, 2)
ElseIf rg - 3500 > 80000 Then
   Geshui1 = Round((rg - 3500) * 0.45 - 13505, 2)
End If
End Function
********************************************************************
Function Geshui(rg As Range)
Select Case rg - 3500
   Case Is <= 0
         Geshui = 0
   Case Is <= 1500
         Geshui = Round((rg - 3500) * 0.03, 2)
   Case Is <= 4500
         Geshui = Round((rg - 3500) * 0.1 - 105, 2)
   Case Is <= 9000
         Geshui = Round((rg - 3500) * 0.2 - 555, 2)
   Case Is <= 35000
         Geshui = Round((rg - 3500) * 0.25 - 1005, 2)
   Case Is <= 55000
         Geshui = Round((rg - 3500) * 0.3 - 2755, 2)
   Case Is <= 80000
         Geshui = Round((rg - 3500) * 0.35 - 5505, 2)
   Case Is > 80000
         Geshui = Round((rg - 3500) * 0.45 - 13505, 2)
End Select
End Function

1982zyh 发表于 2012-2-18 12:28

第一题：

With Sheets("sheet3")
   .Range("A1") = 100
   .Range("A3") = 900
   .Range("A5") = 100
   .Range("A8") = 4500
End With

第二题：

Dim sss As Double

Dim SRate As Double

Dim SShui As Double

sss = gz - 3500

Select Case sss

Case Is >= 80000

SRate = 0.45

SShui = 13505

Case Is >= 55000

SRate = 0.35

SShui = 5505

Case Is >= 35000

SRate = 0.3

SShui = 2755

Case Is >= 9000

SRate = 0.25

SShui = 1005

Case Is >= 4500

SRate = 0.2

SShui = 555

Case Is >= 1500

SRate = 0.1

SShui = 105

Case Else

SRate = 0.03

SShui = 0

End Select

GeShui = (gz - 3500) * SRate - SShui

jinglingshu 发表于 2012-2-18 13:48

作业第一题答案：
Sub 单元格填充()
With Sheets("sheet3")
      .Range("A1") = 100
      .Range("A3") = 900
      .Range("A5") = 100
      .Range("A8") = 4500
End With
End Sub

第二题答案：
Function GeShui(gz As Range)
If gz < 3500 Then
GeShui = 0
ElseIf gz - 3500 <= 1500 Then
GeShui = (gz - 3500) * 0.03
ElseIf gz - 3500 <= 4500 Then
GeShui = (gz - 3500) * 0.1 - 105
ElseIf gz - 3500 <= 9000 Then
GeShui = (gz - 3500) * 0.2 - 555
ElseIf gz - 3500 <= 35000 Then
GeShui = (gz - 3500) * 0.25 - 1005
ElseIf gz - 3500 <= 55000 Then
GeShui = (gz - 3500) * 0.3 - 2755
ElseIf gz - 3500 <= 80000 Then
GeShui = (gz - 3500) * 0.35 - 5505
ElseIf gz - 3500 > 80000 Then
GeShui = (gz - 3500) * 0.45 - 13505
End If
End Function

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

Excel精英培训网's Archiver