统计VBA学习小组正式组第六课（第七讲）的积分帖之作业上交贴（第8周）

冠军欧洲2010 发表于 2012-2-16 08:39

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

说明:
统计帖每个学员只能跟帖回复一次，也就是在原来回复楼层的基础上点编缉，不要一个链接一层楼，否则不计算积分。
各小组学员上交作业时，一定要点击“我要参加”，并注明自己的新组编号和论坛ID，如果点击过“我要参加”但没有跟帖提交作业的，扣该学员5积分；如果跟帖提交了作业，但没有点“我要参加”的，不给予评分。

请各学员看清上面的说明，免得被扣了分分！
本帖为仅楼主可见帖，直接回复即可！

第六课（第七讲）作业链接：
http://www.excelpx.com/thread-222866-1-1.html

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

C12:hrpotter

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) '注gz为引用单元格中的工资数额
Dim s As Long
s = gz - 3500
Select Case s
Case Is < 0
   GeShui = 0
Case Is < 1500
   GeShui = s * 0.03
Case Is < 4500
   GeShui = s * 0.1 - 105
Case Is < 9000
   GeShui = s * 0.2 - 555
Case Is < 35000
   GeShui = s * 0.25 - 1005
Case Is < 55000
   GeShui = s * 0.3 - 2755
Case Is < 80000
   GeShui = s * 0.35 - 5505
Case Else
   GeShui = s * 0.45 - 13505
End Select
End Function

TOMJESSON 发表于 2012-2-16 08:43

第一题答案
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 - 3500
   Case Is <= 0
         GeShui = 0
   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 Is > 80000
         GeShui = (gz - 3500) * 0.45 - 13505
End Select
End Function

zjcat35 发表于 2012-2-16 09:08

E02:zjcat35Sub 单元格填充()
With Sheets("sheet3")
.Range("A1") = 100
.Range("A3") = 900
.Range("A5") = 100
.Range("A8") = 4500
End With
End SubFunction GeShui(gz)
Dim k As Integer
k = gz - 3500
Select Case k
Case Is < 0
   GeShui = 0
Case Is < 1500
   GeShui = k * 0.03 - 0
Case Is < 4500
   GeShui = k * 0.1 - 105
Case Is < 9000
   GeShui = k * 0.2 - 555
Case Is < 35000
   GeShui = k * 0.25 - 1005
Case Is < 55000
   GeShui = k * 0.3 - 2755
Case Is < 80000
   GeShui = k * 0.35 - 5505
Case Is >= 80000
GeShui = k * 0.45 - 13505
End Select
End Function

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

本帖最后由 windimi007 于 2012-2-16 12:21 编辑

D组学委:windimi007
第一题： Sub 单元格填充()
With Sheets("sheet3")
   .Range("A1") = 100
   .Range("A3") = 900
   .Range("A5") = 100
   .Range("A8") = 4500
End With
End SubFunction GeShui(gz As Range)
Dim ynssd&
ynssd = gz - 3500
Select Case ynssd
Case Is < -3500
   GeShui = "工资输入有误！"
Case Is <= 0
   GeShui = 0
Case Is <= 1500
   GeShui = ynssd * 0.03
Case Is <= 4500
   GeShui = ynssd * 0.1 - 105
Case Is <= 9000
   GeShui = ynssd * 0.2 - 555
Case Is <= 35000
   GeShui = ynssd * 0.25 - 1005
Case Is <= 55000
   GeShui = ynssd * 0.3 - 2755
Case Is <= 80000
   GeShui = ynssd * 0.35 - 5505
Case Else
   GeShui = ynssd * 0.45 - 13505
End Select
End Function

yijundanny 发表于 2012-2-16 09:40

**** Hidden Message *****

bynbyn 发表于 2012-2-16 10:04

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

冠军欧洲2010 发表于 2012-2-16 10:12

我也上交作业吧。
PS：不是抄别个同学的，是俺自己做的呦。
:lol:lol:lol:lol:lol
第一题：
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 < 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
Else: GeShui = (gz - 3500) * 0.45 - 13505
End If
End Function

wangyi860214 发表于 2012-2-16 10:14

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)
If gz <= 1500 Then
Range("b1") = gz * 0.03
ElseIf gz <= 4500 Then
Range("b1") = (gz - 3500) * 0.1 - 105
ElseIf gz <= 9000 Then
Range("b1") = (gz - 3500) * 0.2 - 555
ElseIf gz <= 35000 Then
Range("b1") = gz * 0.25 - 1005
ElseIf gz <= 55000 Then
Range("b1") = (gz - 3500) * 0.3 - 2755
ElseIf gz <= 80000 Then
Range("b1") = (gz - 3500) * 0.35 - 5505
ElseIf gz > 80000 Then
Range("b1") = (gz - 3500) * 0.45 - 13505
End If
End Function

一缕忧兰 发表于 2012-2-16 10:34

A07:一缕忧兰
第一题
'使用With语句
Sub 单元格填充()
With Sheet3
   .Range("a1") = 100
   .Range("a3") = 900
   .Range("a5") = 100
   .Range("a8") = 4500
End With
End Sub

第二题
Function GeShui(gz As Long) '注gz为引用单元格中的工资数额
Dim y As Long
y = gz - 3500 'y是应税额
If y <= 0 Then
GeShui = 0
ElseIf y > 0 And y <= 1500 Then
GeShui = y * 0.03
ElseIf y > 1500 And y <= 4500 Then
GeShui = y * 0.1 - 105
ElseIf y > 4500 And y <= 9000 Then
GeShui = y * 0.2 - 555
ElseIf y > 9000 And y <= 35000 Then
GeShui = y * 0.25 - 1005
ElseIf y > 35000 And y <= 55000 Then
GeShui = y * 0.3 - 2755
ElseIf y > 55000 And y <= 80000 Then
GeShui = y * 0.35 - 5505
ElseIf y > 80000 Then
GeShui = y * 0.45 - 13505
End If
End Function

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

Excel精英培训网's Archiver

统计VBA学习小组正式组第六课（第七讲）的积分帖之作业上交贴（第8周）