工资管理系统设计(有图)
当员工较多或变动频繁时,工资管理是一件麻烦的事。本文介绍了一种在 Excel上开发的工资管理系统,可以很方便地进行工资的管理。
一.工资管理系统的设计步骤
“工资管理系统”的设计步骤如下:
(1)以Excel一张工作表的一行反映一名员工的工资数据
选定Excel的一张工作表,命名为“工资单”,在该表上设计反映员工工资的栏目。如图1。
图1 工资管理系统界面
(2)用公式建立工资数据之间的关联关系
图1中,“个人所得税”、“实领金额”、“应发工资总额”和“实发工资总额”栏数据,均通过设置公式自动生成。公式设置如图2。
图2 工资管理系统公式设置
在“个人所得税”栏公式中的IF函数使用了嵌套的方法,分别依级确定纳税额。最高级数上限设定为月收入20000元。如果有必要,可增加嵌套层数,提高最高级数上限设定。
“应发工资总额”和“实发工资总额”栏公式,系统假定汇总至10000行。
“应扣款项”栏仅设计了“个人所得税”、“房租”、“水电费”,以及一个待定项目,可按实际需要增加栏目,并相应修改有关公式。
(3)设计一个宏,自动填充公式。
“个人所得税”和“实领金额”栏公式设计了一行,其它行公式通过设计的“计算工资”宏自动填充。宏代码为
Sub 计算工资()
x = 5
Do While Not (IsEmpty(Sheets("工资单").Cells(x, 1).Value))
x = x + 1
Loop
'确定记录的末行数x-1.
Range("D5:H5").Select
'选定区域D5:H5.
Selection.AutoFill Destination:=Range(Cells(5, 4), Cells(x - 1, 8)), Type:=xlFillDefault
'向下自动填充公式.
End Sub
为方便执行宏,“计算工资”宏赋于“工资单”上的按钮“计算工资”,参见前面图1。
二.关于“计算工资”宏的说明
1.在系统设计中,也可以不使用宏,用Excel的自动填充功能事先设定足够的“个人所得税”和“实领金额”栏公式,例如,10000行。但这样做,可能不需要这么多行公式,浪费资源,甚至影响运行速度。最好是有多少职工,就形成多少行公式,通过前面的“计算工资”宏就可以达到这一点。
2.“计算工资”宏中的自动填充公式代码
Selection.AutoFill Destination:=Range(Cells(5, 4), Cells(x - 1, 8)), Type:=xlFillDefault
是通过“录制”的方式改写而来,方法如下:
(1)选菜单栏[工具/宏/录制新宏]命令,显现“停止录制”浮标。
(2)选定区域D5:H5。
(3)光标指定区域D5:H5右下角,显现小“+”字。
(4)按下鼠标左键向下拉动填充公式,假定至第8行。
(5)点击“停止录制”浮标。
完成以上各步后,在新录制的宏中即可见第(2)至第(4)步自动填充公式操作所转换成的代码
Range("D5:H5").Select
Selection.AutoFill Destination:=Range("D5:H8"), Type:=xlFillDefault
将以上代码中的固定区域D5:H8改为变动区域Cells(5, 4), Cells(x - 1, 8)。这样,就得到了“计算工资”宏的关键代码
Selection.AutoFill Destination:=Range(Cells(5, 4), Cells(x - 1, 8)), Type:=xlFillDefault
值得特别一提的是,通过录制的方式获得关键代码,这一技术可以将困难的编程变得如此轻松,这是VBA的一大特点。
三.应用举例
假定某单位员工记录如图3。
图3 某单位工资记录
击“计算工资”按钮,即自动计算“个人所得税”、“实领金额”等数据,如图4。
图4 某单位工资清单
四.工资调整
在工资管理中,普遍的工资调整一般需要特别处理。下面的例子可以自动完成这一工作。
设某学校的职工人事数据存在Excel工作表中,如图5所示。
图5 调整前的工作表
现在按职称提升每位职工的工资,各种职称的工作增长情况如下:教授150,副教授130,讲师100,助教80,高级工程师150,工程师140,助工90。
如果用人工的方式为每位职工增加工资,当单位人数较多时,不但麻烦,而且容易出错。
现在,在该工作薄的VBA模块中建立一个如下的函数:
Function addsalary( 职称 )
Select Case 职称
Case "教授", "高级工程师"
addsalary = 150
Case "副教授"
addsalary = 130
Case "讲师"
addsalary = 100
Case "助教"
addsalary = 80
Case "工程师"
addsalary = 140
Case "助工"
addsalary = 90
End Select
End Function
在VBA中,变量可以用汉字定义,所以把“职称”作为一个参数,然后用Select Case语句判定这个参数是哪种职称,并根据它的值决定增加的工资多少。比如
Case "副教授"
addsalary = 130
其意义为:如果“职称”参数的值为“副教授”,就增加工资130元。因为教授和高级工程师增加的工资是相同的,所以把他们写在一条语句中:Case "教授", "高级工程师" addsalary = 150。
编写好上面的函数之后,在工资表的E4单元格中输入公式“=addsalary(c4)”,然后把该公式向下填充复制到最后一位职工所在的数据行,Excel就会利用自定义函数addsalary计算出每位职工应增加的工资。E列数据计算出来之后,F列的数据就轻松可得了。图6显示出了利用自定义函数求解的结果。
图6 用自定义函数计算增加工资
在自定义函数addsalary中使用了VBA的Select Case 语句。
Select Case语句是VBA的一种多分支情况语句,它的语法结构如下:
Select Case 测试表达式
Case 表达式1
语句块1
[ Case 表达式2
语句块2]
……
[Case Else
语句n ]
End Select
Select Case语句的功能是从多个Case语句中选择一个符合条件的Case语句执行。也可以用条件语句
If…Then
…
Else
…
End If
达到同样的效果,但要麻烦得多。