作业要求:根据奖金规则,计算每位员工的奖励比例及销售奖金
奖金规则:
第一类方法:Excel公式法
方法1:=HLOOKUP(C3,'练习6-奖金规则'!$C$3:$F$4,2,TRUE)
方法2:=IF(C3>=20000,8%,IF(C3>=10000,6%,IF(C3>=5000,4%,2%)))
方法3:=INDEX('练习6-奖金规则'!$C$3:$F$4,2,MATCH('练习6-index+match'!C3,'练习6-奖金规则'!$C$3:$F$3,1))
方法4:奖金规则转置后,=VLOOKUP(C3,'练习6-奖金规则'!$C$6:$D$9,2,TRUE)
第二类方法:Excel VBA
Sub bonus()
Dim ratio As Single
For i = 3 To 22 Step 1
Select Case Cells(i, "C")
Case 0 To 5000
ratio = 0.02
Case 5001 To 10000
ratio = 0.04
Case 10001 To 20000
ratio = 0.06
Case Else
ratio = 0.08
End Select
Cells(i, "D") = ratio
Next i
End Sub