最新EXCEL人事工资管理系统.doc

《最新EXCEL人事工资管理系统.doc》由会员分享，可在线阅读，更多相关《最新EXCEL人事工资管理系统.doc（31页珍藏版）》请在三一文库上搜索。

1、word最新EXCEL人事工资管理系统建立人事工资管理系统工作簿某某市教育局核算中心 许 国某某市教育局 X文建按照某某省人事厅的统一部署，我市于2007年2月初进展了机关事业单位工资制度改革，这次工资制度改革是新中国成立以来第四次大的改革，与以往历次工资制度改革相比，也是情况最复杂、难度最大的一次，具体工改时要根据套改“三要素职务或职称、任职年限、套改年限进展套改。这对于具体负责工资调整的办事人员来说，增加了许多工作，就一所学校来说，工资调整时都要手工填写好几百X内容差不多的表格，工作量很大，本人经过摸索，发现用EXCEL的VBA功能可以轻松地处理调资工作，只要根底工作一次性做好后，调资就变

2、得非常方便，下面简要地介绍一下具体的开发过程。一、 建立人事工资管理系统工作簿1、在计算机的磁盘上建立一个名称为“2006人事工资管理系统的EXCEL工作簿文件，并在此工作簿中建立“封面、“欢迎界面、“事业名册表、“ 事业审批表、“事业报批表、“离退休名册表、“离退休审批表、“离退休报批表等工作表，以与存放用户名和密码的工作表“用户密码等组成，这些工作表事先由手工插入并重命名。当系统运行后，一些工作表将被自动隐藏如“用户密码工作表。2、“封面工作表是进入工资管理系统的首页，当打开工作簿和关闭工作簿时系统都将激活“封面工作表，“封面工作表的界面如图1所示，其设计步骤如下：图11在当前的工作表中插

3、入艺术字“欢迎使用XG人事工资管理系统V1.0分两行输入“欢迎使用和“XG人事工资管理系统V1.0 。2选取整X工作表，将单元格颜色填充为“蓝-灰色。3将第36行至65536行、第R列至IV列全部隐藏。由于用手工隐藏单元格比拟麻烦，可在工作簿中的VBE窗口中插入一个模块1，并输入如下代码：Private Sub 隐藏封面单元格()Sheets(封面).ActivateRows(36:65536).Select 选中36行至65536行 Selection.EntireRow.Hidden = True 隐藏选中的单元格Columns(r:iv).Select 选中第R列至IV列 Selecti

4、on.EntireColumn.Hidden = TrueEnd Sub代码说明：以上代码是对选中的单元格进展隐藏，使系统封面更加美观。上述代码输入完成后退出并保存，然后在【工具】 【宏】菜单中运行“隐藏封面单元格宏，即可实现单元格的隐藏。4执行【工具】【选项】命令，打开【选项】对话框，单击【视图】选项卡，在“窗口选项中中保存“水平滚动条和“垂直滚动条两个复选框外，取消其他的复选框如图2。5执行【工具】【保护】【保护工作表】，打开【保护工作表】对话框，在“允许此工作表的所有用户进展列表中取消选中的复选框，并输入密码如图3。输入密码图2图33、“欢迎界面工作表的设计步骤与“封面工作表的步骤根本一

5、样，只不过界面中多了一个单位、制作者、操作提示和【人事管理系统】菜单如图4。按此菜单进入系统图4要隐藏“欢迎界面工作表中的单元格，可在工作簿的VBE窗口中插入一个模块2，并输入如下代码如下：Private Sub 隐藏欢迎界面单元格()Sheets(欢迎界面).ActivateRows(36:65536).Select 选中36行至65536行 Selection.EntireRow.Hidden = True 隐藏选中的单元格Columns(r:iv).Select 选中第R列至IV列 Selection.EntireColumn.Hidden = TrueEnd Sub代码说明：以上代码是

6、对选中的单元格进展隐藏，使欢界面更加美观。上述代码输入完成后退出并保存，然后在【工具】 【宏】菜单中运行“隐藏欢迎界面单元格宏，即可实现“欢迎界面工作表中单元格的隐藏。至此人事工资管理系统工作簿建立完成，系统框架也根本形成。2007年3月15日通讯地址：某某省某某市滨江中路19号会计核算中心 许国 办公室 05757375026 邮政编码311800 Email：xuguozjjy. .zjhszx.最新EXCEL人事工资管理系统设计系统登录窗口一某某市教育局核算中心 许 国某某市教育局 X文建任何一个应用系统，都应有一个系统登录窗口，以保护应用系统的安全。当打开应用系统时，系统将弹出登录窗口

7、只有输入了正确的用户名和密码后，才能进入系统。二、 用户名和密码的保存用户名和密码既可以直接编写在登录程序中，也可以单独设计一个用来存放用户名和密码的工作表，后者具有较大的灵活性，可以随时增加用户名和密码，也可以随时更改用户名和密码。本人事工资管理系统的登录用户名和密码的设置采用第二种方式，但用户名和密码都为预设，不能更改。在“用户密码工作表中的A列存放用户名如图5，B列存放与A列用户名对应的登录密码，该工作表被保护起来，并被隐藏用VBA代码隐藏，以防修改和泄露密码。图5三、 用户登录窗口1、 用户登录窗口结构用户登录窗口的结构如图6所示。由1个用户窗体和2个标签、2个文本框与2个命令按钮组

8、成，用户窗体和各个控件的功能与属性设置如下。图6（1） 用户窗体：名称重命名为“用户登录，Caption属性设置为“用户登录。（2） 标签Label1和Label2：对两个文本框的功能进展说明，它们的Caption属性分别设置为“用户名和“密码。（3） 文本框Textbox1：输入用户名（4） 文本框Textbox2：输入密码，其PassworkChar属性设置为“*。（5） 命令按钮mandButton1：输入正确的用户名和密码后，单击此按钮进入系统，其Caption属性设置为“进入系统，Default属性设置为True.（6） 命令按钮mandButton2：退出系统，其Caption属性

9、设置为“退出系统。2、 登录窗口代码设计1在VBE窗口的窗体设计中，双击“进入系统按钮为其设置Click事件，程序代码如下：Private Sub mandButton1_Click()如果出现错误，就转向错误处理程序errorhandleOn Error GoTo errorhandleDim ws As WorksheetSet ws = Worksheets(用户密码) 赋值If TextBox1.Text = Then如果用户名为空，就将焦点移到用户名文本框，并退出程序Exit Sub: End If如果用户名和密码都正确，就卸载用户登录窗口，创建自定义菜单，退出程序If ws.Ran

10、ge(a2).Text = TextBox1.Text _And ws.Range(b2).Text = TextBox2.Text ThenUnload userform1Call 自定义菜单: Sheets(欢迎界面).ActivateExit SubElseIf ws.Range(a2).Text = TextBox1.Text _And ws.Range(b2).Text TextBox2.Text ThenMsgBox 密码错误!, vbCritical, 警告TextBox2 = TextBox2.SetFocus: Exit SubElseIf ws.Range(a2).Text

11、 TextBox1.Text _And ws.Range(b2).Text = TextBox2.Text ThenMsgBox 用户名错误!, vbCritical, 警告TextBox2 = TextBox2.SetFocus: Exit Sub: End IfMsgBox 用户名和密码不存在!, vbCritical, 警告TextBox1 = : TextBox2 = TextBox1.SetFocus: Exit Suberrorhandle: 进入错误处理程序ThisWorkbook.Close savechanges:=False 关闭工作簿但不保存End Sub代码说明：以上代

12、码是对系统登录窗口中输入的用户名和密码与“用户密码工作表中的用户名和密码比对，如果不正确，如此弹出有关的警告信息框，如果正确，就关闭登录窗口，然后创建自定义菜单，进入欢迎界面工作簿。2在VBE窗口的窗体设计中，双击“退出系统按钮为其设置Click事件，程序代码如下：Private Sub mandButton2_Click()ThisWorkbook.Close savechanges:=False 关闭工作簿但不保存End Sub代码说明：以上代码是在按“退出系统按钮时关闭工作簿但不保存。3、 设计工作簿对象的Open事件程序为了能够在打开“人事工资管理系统工作簿时启动登录系统窗口，对工作簿

13、对象设置Open事件程序，在VBE窗口中双击“Thisworkbook如图7，选择右边窗口中的Workbook和Open事件，并输入如下代码：此处选择Workbook和Open事件图7Private Sub Workbook_Open()Sheets(封面).ActivateCall 启动系统End Sub代码说明：以上代码使得在打开“人事工资管理系统时，系统就调用“启动系统模块，出现登录窗口。2007年3月15日通讯地址：某某省某某市滨江中路19号会计核算中心 许国 办公室 05757375026 邮政编码311800 Email：xuguozjjy. .zjhszx.标准文档最新EXCEL

14、人事工资管理系统设计系统登录窗口二某某市教育局核算中心 许 国某某市教育局 X文建在打开“人事工资管理系统工作簿时，首先激活工作簿对象的Open事件程序，随之会出现一个“封面工作表和一个“登录窗口界面，此界面需要调用下面的系统启动程序。4、系统启动程序在工作簿的VBE窗口中插入一个模块3，输入如下代码：Public Sub 启动系统()Worksheets(封面).ActivateRange(a1).ActivateActiveWindow.DisplayWorkbookTabs = False 不显示工作表标签If Worksheets(用户密码).Visible = True Then如果

15、用户密码表不隐藏，就隐藏它Worksheets(用户密码).Visible = xlSheetVeryHiddenEnd If保护封面工作表ActiveSheet.Protect DrawingObjects:=True, contents:=True, Scenarios:=TrueActiveSheet.EnableSelection = xlNoSelectionuserform1.Show 启动用户登录窗口End Sub代码说明：以上代码首先激活“封面工作表，然后隐藏工作表标签，判断“用户密码工作表是否隐藏，如果不隐藏，就隐藏它，并对“封面工作表进展保护，启动用户登录窗口。5、人事管理

16、菜单程序在登录窗口中输入正确的用户名和密码后，就可进入人事管理系统的欢迎界面，如图8。人事管理系统菜单图8图中的“人事管理菜单需要调用“自定义菜单程序，此程序可在工作簿的VBE窗口中插入一个模块4生成，其完整代码如下：Dim mymenuobject As mandBarPopupDim mymenuitem As ObjectDim mysubmenuitem As ObjectPublic Sub 自定义菜单()On Error Resume Next 如果出现错误，就忽略它，继续运行程序删除已经存在的自定义菜单Application.mandBars(1).Controls(【人事管理系

17、统】).Delete创建自定义菜单Set mymenuobject = Application.mandBars(1). _Controls.Add(Type:=msoControlPopup, before:=11, temporary:=True)With mymenuobject .Caption = 【人事管理系统】 .Width = 70: .BeginGroup = True: End With 为自定义菜单添加“事业在职主菜单并添加有关的子菜单 Call 主菜单(事业在职) Call 子菜单(在职名册表, 在职名册表) Call 子菜单(在职审批表, 在职审批表) Call 子菜

18、单(在职报批表, 在职报批表) Call 主菜单(事业离退休) Call 子菜单(离退休名册表, 离退休名册表) Call 子菜单(离退休审批表, 离退休审批表) Call 子菜单(离退休报批表, 离退休报批表)End Sub子程序“主菜单Public Sub 主菜单(mycapTion As String)Set mymenuitem = mymenuobject.Controls.Add(Type:=msoControlPopup)With mymenuitem .Caption = mycapTion .BeginGroup = True: End WithEnd Sub子程序“子菜单P

19、ublic Sub 子菜单(mycapTion As String, myonaction As String)Set mysubmenuitem = mymenuitem.Controls.Add(Type:=msoControlButton)With mysubmenuitem .Caption = mycapTion .OnAction = myonaction .BeginGroup = True: End WithEnd Sub注：为节省篇幅,在一行中使用了冒号隔开多条指令下同。代码说明：以上代码是在进入系统后首先删除已经存在的自定义菜单，然后再创建一个自定义菜单，并调用子程序的“主

20、菜单和“子菜单程序。6、激活人事管理菜单为了能使上面创建的“人事管理系统菜单能够使用，必须使用程序激活，可在工作簿的VBE窗口中插入一个模块5完成，其完整代码如下：Private Sub 在职名册表()Sheets(事业).ActivateEnd SubPrivate Sub 在职审批表()Sheets(事业审批表).ActivateEnd SubPrivate Sub 在职报批表()Sheets(事业(报批表).ActivateEnd SubPrivate Sub 离退休名册表()Sheets(离退休).ActivateEnd SubPrivate Sub 离退休审批表()Sheets(离退

21、休审批表).ActivateEnd SubPrivate Sub 离退休报批表()Sheets(离退休(报批表).ActivateEnd Sub代码说明：以上代码是在分别单击“人事管理系统菜单中的“在职名册表等子菜单时，激活自定义菜单，然后相应激活各自的工作表，激活后即可对工作表进展操作。至此，系统登录窗口的设计工作完成，为用户提供了一个比拟友好的人机交互界面。2007年3月15日通讯地址：某某省某某市滨江中路19号会计核算中心 许国 办公室 05757375026 邮政编码311800 Email：xuguozjjy. .zjhszx.最新EXCEL人事工资管理系统设计在职人员工改名册表一某

22、某市教育局核算中心 许 国某某市教育局 X文建进入系统后，选择【人事管理系统】 【事业在职】【在职名册表】菜单，即可打开“某某市事业单位工作人员收入分配制度改革名册表以下简称在职名册表，如图9。图9四、在职名册表1、按工改的要求设计好此表格的相应栏目，然后输入单位工作人员的相关信息，其中A列至S列的信息由手工输入，W列的信息如果与K列的信息一致，如此可由计算自动给出，如果不一致，如此需手工输入，这里我们暂以手工输入为例说明，其他列的信息由计算机自动给出。2、表中的岗位名称，职务名称要统一标准。1岗位名称分为管理、专技、工人、见习。2职务名称要按岗位分为四大类，其中管理岗位的职务名称分为处级正职

23、处级副职、科级正职、科级副职、科员、办事员；专技岗位的职务名称分为正高、副高、中级、助理级、员级；工人岗位的职务名称分为高级级技师、技师、高级工、中级工、初级工、普工。见习岗位即新分配人员的职务名称为见习。3岗位为见习的人员，由于在城区和非城区工作其工资标准有差异，必须在备注栏列注明是“城区还是“非城区。3、需要手工输入的信息录入完成后，我们就需要对计算机自动给出的列进展处理。1T列工改前工资中的津贴30%、U列工改前工资中的10%浮动工资和AB列工改后工资中的10%浮动工资工资标准的处理。在工作簿的VBE窗口中插入一个模块6，并输入代码如下：Private Sub 津贴30与浮动10()R

24、ow = Worksheets(事业).a65536.End(xlUp).Row 取得行数Application.ScreenUpdating = False 关闭屏幕更新For i = 8 To Row如果J列I行单元格为管理或者专技,那么T列,U列,AB列的公式如下If Range(j & i) = 管理 Or Range(j & i) = 专技 ThenRange(T & i) = WorksheetFunction.Round(Range(s & i) * 1.1 * 3 / 7, 0)Range(u & i) = WorksheetFunction.Round(Range(s & i

25、) * 0.1, 0)Range(AB & i) = WorksheetFunction.Round(Range(Y & i) + Range(aa & i) * 0.1, 0)如果J列I行单元格为工人,那么T列的公式如下,U列和AB列的值为15ElseIf Range(j & i) = 工人 ThenRange(T & i) = WorksheetFunction.Round(Range(s & i) * 3 / 7, 0)Range(u & i) = 15Range(AB & i) = 15如果J列I行单元格为见习,并且AF列I行单元格为非城区,那么T列的公式如下,U列和AB列的值为0El

26、seIf Range(j & i) = 见习 And Range(af & i) = 非城区 ThenRange(T & i) = WorksheetFunction.Round(Range(s & i) * 3 / 7, 0)Range(u & i) = 0Range(AB & i) = 0如果J列I行单元格为见习,并且AF列I行单元格为城区,那么T列,U列,AA列和AB列的值为0ElseIf Range(j & i) = 见习 And Range(af & i) = 城区 ThenRange(T & i) = 0Range(u & i) = 0Range(AB & i) = 0Range(

27、aa & i) = 0End If: Next iApplication.ScreenUpdating = True 打开屏幕更新End Sub代码说明：以上代码是在先判断J列I行单元格以与AF列I行单元格的内容，然后根据此内容对T列、U列和AB列进展处理，自动获得相应公式或数值。上述代码输入完成后退出并保存，然后在【工具】 【宏】菜单中运行“津贴30与浮动10宏，即可得到T列、U列和AB列的值。2Y列单元格的数据处理。Y列为工改后的岗位工资标准，岗位工资的标准是根据现任职称或职务而定的，如中级职称为680元， 要使此列的标准自动给出，需建立一个自定义函数，具体可在工作簿的VBE窗口中插入一个

28、模块7完成，其完整代码如下：Function gz(a As String) As Singlegz = 0如果职务为处级正职，那么岗位工资为1045If a = 处级正职 Then gz = 1045 ElseIf a = 处级副职 Then gz = 850 ElseIf a = 科级正职 Then gz = 720 ElseIf a = 科级副职 Then gz = 640 ElseIf a = 科员 Then gz = 590 ElseIf a = 办事员 Then gz = 550 ElseIf a = 正高 Then gz = 1420 ElseIf a = 副高 Then gz

29、 930 ElseIf a = 中级 Then gz = 680 ElseIf a = 助理级 Then gz = 590 ElseIf a = 员级 Then gz = 550 ElseIf a = 高级技师 Then gz = 830 ElseIf a = 技师 Then gz = 690 ElseIf a = 高级工 Then gz = 615 ElseIf a = 中级工 Then gz = 575 ElseIf a = 初级工 Then gz = 545 ElseIf a = 普工 Then gz = 540Else: End IfEnd Function代码说明：以上代码是一个自

30、定义函数，函数名gz，a为变量，代表职称或职务，如职务为处级正职，如此岗位工资为1045元。上述代码输入完成后退出并保存，在Y8单元格中输入“=gz(w8)，可得到Y8单元格的值，向下拖动此公式可得到其他单元格的岗位工资标准。2007年3月15日此文在软件报2007年第14期开始连载通讯地址：某某省某某市滨江中路19号会计核算中心 许国 办公室 05757375026 邮政编码311800 Email：xuguozjjy. .zjhszx.最新EXCEL人事工资管理系统设计在职人员工改名册表二某某市教育局核算中心 许 国某某市教育局 X文建3X列单元格的数据处理。X列为工改后岗位工资的岗位级别

31、它与岗位工资的标准或职称或职务相对应，如中级职称为十级，岗位工资680元的也为十级，要使此列的标准自动给出，需建立一个自定义函数，具体可在工作簿的VBE窗口中插入一个模块8完成，其完整代码如下：Function dj(a As String) As Stringdj = If a = 处级正职 Then dj = 五级 ElseIf a = 处级副职 Then dj = 六级 ElseIf a = 科级正职 Then dj = 七级 ElseIf a = 科级副职 Then dj = 八级 ElseIf a = 科员 Then dj = 九级 ElseIf a = 办事员 Then dj =

32、 十级 ElseIf a = 正高 Then dj = 四级 ElseIf a = 副高 Then dj = 七级 ElseIf a = 中级 Then dj = 十级 ElseIf a = 助理级 Then dj = 十二级 ElseIf a = 员级 Then dj = 十三级 Else: End IfEnd Function代码说明：以上代码是一个自定义函数，函数名dj，a为变量，代表职称或职务，如职务为中级，如此岗位级别为十级。上述代码输入完成后退出并保存，在X8单元格中输入“=dj (w8)，可得到X8单元格的值，向下拖动此公式可得到其他单元格的岗位级别。4AA列单元格的数据处理。A

33、A列为工改后的薪级工资标准，薪级工资标准是根据套改“三要素职务或职称、任职年限、套改年限进展套改得到的，如中级职称是2001年7月取得至2006年6月，其任职年限为6年，套改年限18年，如此对照套改表可得到其薪级工资标准为365元。要使此列的标准自动给出，需建立几个自定义函数，具体可在工作簿的VBE窗口中插入几个模块完成，由于此自定义函数较长，需分成两个模块，分别为模块9和10。岗位为管理和专技的薪级工资的自定义函数局部代码如下：Function xj(a As String, b, c) As Singlexj = 0If a = 处级正职 And b = 3 ThenIf c = 15 A

34、nd c = 17 And c = 20 And c = 22 And c = 25 And c = 27 And c = 30 And c = 32 And c = 35 And c = 37 And c = 40 And c = 42 And c = 4 And b = 15 And c = 16 Then xj = 527 ElseIf a = 处级副职 And b = 3 ThenIf c = 12 And c = 13 Then xj = 365 ElseIf a = 正高 And b = 2 ThenIf c = 17 And c = 18 Then xj = 583Else：En

35、d If：End Function代码说明：以上代码是一个自定义函数，函数名xj，a、b和c都为变量，分别为套改三要素中的职务、任职年限和套改年限，如职务为处级，任职年限为3年，套改年限为18年的人员，其薪级工资为527元。上述代码输入完成后退出并保存，在AA8单元格中输入“=xj (w8,n8,i8)，即可在AA8单元格中得到此人的薪级工资，向下拖动此公式可得到其他人员的薪级工资。岗位为工人的薪级工资的自定义函数局部代码如下：Function grxj(a As String, b, c) As Singlegrxj = 0 If a = 技师 And b = 2 ThenIf c = 21

36、 And c = 23 And c = 26 And c = 28 And c = 31 And c = 33 And c = 36 And c = 38 And c = 41 Then grxj = 675 ElseIf a = 高级工 And b = 3 ThenIf c = 16 And c = b ThenRange(aa & i) = a：Range(af & i).Interior.ColorIndex = 2ElseIf Range(j & i) = 管理 Or Range(j & i) = 专技 And a =d ThenRange(aa & i) = c：Range(af &

37、 i).Interior.ColorIndex = 2 ClearContentsElseIf Range(j & i) = 工人 And c d ThenRange(aa & i) = d：Range(af & i).Interior.ColorIndex = 34Range(af & i) = 低职 & Chr(10) & Range(af & i)End If：Next i：For i = 8 To Row 取得X、Y、Z列的值e = gz(Range(w & i)：f = dj(Range(w & i)g = js(Range(aa & i)：Range(y & i) = eRange

38、x & i) = f：Range(z & i) = gNext i：Application.ScreenUpdating = TrueEnd Sub代码说明：以上代码是先对AF列单元格内容进展判断，如果有“低职两字的，就删除“低职两字，然后分别用现任职务和低一职务进展套改，如果用现任职务套改的薪级工资大于低一职务的，就按现职套改，反之，如此按低一职务套改，并在如下单元格中添加“在职两字，最后用循环语句取得X、Y、Z列的值，免去手工输入在单元格中输入公式。上述代码输入完成后退出并保存，然后在【工具】 【宏】菜单中运行“生成新工资标准宏，即可实现单元格数据的自动输出。2007年3月15日通讯地址：某某省某某市滨江中路19号会计核算中心