全部整合EXCEL人事工资管理组织系统.doc

《全部整合EXCEL人事工资管理组织系统.doc》由会员分享，可在线阅读，更多相关《全部整合EXCEL人事工资管理组织系统.doc（39页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

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

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

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

4、:iv).Select 选中第R列至IV列 Selection.EntireColumn.Hidden = TrueEnd Sub代码说明：以上代码是对选中的单元格进行隐藏，使系统封面更加美观。上述代码输入完成后退出并保存，然后在【工具】 【宏】菜单中运行“隐藏封面单元格”宏，即可实现单元格的隐藏。（4）执行【工具】【选项】命令，打开【选项】对话框，单击【视图】选项卡，在“窗口选项中”中保留“水平滚动条”和“垂直滚动条”两个复选框外，取消其他的复选框（如图2）。（5）执行【工具】【保护】【保护工作表】，打开【保护工作表】对话框，在“允许此工作表的所有用户进行”列表中取消选中的复选框，并输入密码

5、（如图3）。输入密码 图2 图33、“欢迎界面”工作表的设计步骤与“封面”工作表的步骤基本相同，只不过界面中多了一个单位、制作者、操作提示和【人事管理系统】菜单（如图4）。按此菜单进入系统 图4要隐藏“欢迎界面”工作表中的单元格，可在工作簿的VBE窗口中插入一个模块2，并输入如下代码如下：Private Sub 隐藏欢迎界面单元格()Sheets(欢迎界面).ActivateRows(36:65536).Select 选中36行至65536行 Selection.EntireRow.Hidden = True 隐藏选中的单元格Columns(r:iv).Select 选中第R列至IV列 Sel

6、ection.EntireColumn.Hidden = TrueEnd Sub代码说明：以上代码是对选中的单元格进行隐藏，使欢界面更加美观。上述代码输入完成后退出并保存，然后在【工具】 【宏】菜单中运行“隐藏欢迎界面单元格”宏，即可实现“欢迎界面”工作表中单元格的隐藏。至此人事工资管理系统工作簿建立完成，系统框架也基本形成。2007年3月15日通讯地址：浙江省诸暨市滨江中路19号会计核算中心 许国 办公室电话 05757375026 邮政编码311800 Email：xuguo网址：最新EXCEL人事工资管理系统设计系统登录窗口（一）诸暨市教育局核算中心 许 国诸暨市教育局 郑文建任何一个应

7、用系统，都应有一个系统登录窗口，以保护应用系统的安全。当打开应用系统时，系统将弹出登录窗口，只有输入了正确的用户名和密码后，才能进入系统。二、 用户名和密码的保存用户名和密码既可以直接编写在登录程序中，也可以单独设计一个用来存放用户名和密码的工作表，后者具有较大的灵活性，可以随时增加用户名和密码，也可以随时更改用户名和密码。本人事工资管理系统的登录用户名和密码的设置采用第二种方式，但用户名和密码都为预设，不能更改。在“用户密码”工作表中的A列存放用户名（如图5），B列存放与A列用户名对应的登录密码，该工作表被保护起来，并被隐藏（用VBA代码隐藏），以防修改和泄露密码。 图5三、 用户登录窗口1

8、、 用户登录窗口结构用户登录窗口的结构如图6所示。由1个用户窗体和2个标签、2个文本框及2个命令按钮组成，用户窗体和各个控件的功能及属性设置如下。 图6（1） 用户窗体：名称重命名为“用户登录”，Caption属性设置为“用户登录”。（2） 标签Label1和Label2：对两个文本框的功能进行说明，它们的Caption属性分别设置为“用户名”和“密码”。（3） 文本框Textbox1：输入用户名（4） 文本框Textbox2：输入密码，其PassworkChar属性设置为“*”。（5） 命令按钮CommandButton1：输入正确的用户名和密码后，单击此按钮进入系统，其Caption属性设

9、置为“进入系统”，Default属性设置为True.（6） 命令按钮CommandButton2：退出系统，其Caption属性设置为“退出系统”。2、 登录窗口代码设计（1）在VBE窗口的窗体设计中，双击“进入系统”按钮为其设置Click事件，程序代码如下：Private Sub CommandButton1_Click()如果出现错误，就转向错误处理程序errorhandleOn Error GoTo errorhandleDim ws As WorksheetSet ws = Worksheets(用户密码) 赋值If TextBox1.Text = Then如果用户名为空，就将焦点移到

10、用户名文本框，并退出程序TextBox1.SetFocusExit Sub: End If如果用户名和密码都正确，就卸载用户登录窗口，创建自定义菜单，退出程序If ws.Range(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 ThenMs

11、gBox 密码错误!, vbCritical, 警告TextBox2 = TextBox2.SetFocus: Exit SubElseIf ws.Range(a2).Text TextBox1.Text _And ws.Range(b2).Text = TextBox2.Text ThenMsgBox 用户名错误!, vbCritical, 警告TextBox2 = TextBox2.SetFocus: Exit Sub: End IfMsgBox 用户名和密码不存在!, vbCritical, 警告TextBox1 = : TextBox2 = TextBox1.SetFocus: Exi

12、t Suberrorhandle: 进入错误处理程序ThisWorkbook.Close savechanges:=False 关闭工作簿但不保存End Sub代码说明：以上代码是对系统登录窗口中输入的用户名和密码与“用户密码”工作表中的用户名和密码比对，如果不正确，则弹出有关的警告信息框，如果正确，就关闭登录窗口，然后创建自定义菜单，进入欢迎界面工作簿。（2）在VBE窗口的窗体设计中，双击“退出系统”按钮为其设置Click事件，程序代码如下：Private Sub CommandButton2_Click()ThisWorkbook.Close savechanges:=False 关闭工作

13、簿但不保存End Sub代码说明：以上代码是在按“退出系统”按钮时关闭工作簿但不保存。3、 设计工作簿对象的Open事件程序为了能够在打开“人事工资管理系统”工作簿时启动登录系统窗口，对工作簿对象设置Open事件程序，在VBE窗口中双击“Thisworkbook”（如图7），选择右边窗口中的Workbook和Open事件，并输入如下代码：此处选择Workbook和Open事件 图7Private Sub Workbook_Open()Sheets(封面).ActivateCall 启动系统End Sub代码说明：以上代码使得在打开“人事工资管理系统”时，系统就调用“启动系统”模块，出现登录窗口

14、。2007年3月15日通讯地址：浙江省诸暨市滨江中路19号会计核算中心 许国 办公室电话 05757375026 邮政编码311800 Email：xuguo网址：最新EXCEL人事工资管理系统设计系统登录窗口（二）诸暨市教育局核算中心 许 国诸暨市教育局 郑文建在打开“人事工资管理系统”工作簿时，首先激活工作簿对象的Open事件程序，随之会出现一个“封面工作表”和一个“登录窗口”界面，此界面需要调用下面的系统启动程序。4、系统启动程序在工作簿的VBE窗口中插入一个模块3，输入如下代码：Public Sub 启动系统()Worksheets(封面).ActivateRange(a1).Acti

15、vateActiveWindow.DisplayWorkbookTabs = False 不显示工作表标签If Worksheets(用户密码).Visible = True Then如果用户密码表不隐藏，就隐藏它Worksheets(用户密码).Visible = xlSheetVeryHiddenEnd If保护封面工作表ActiveSheet.Protect DrawingObjects:=True, contents:=True, Scenarios:=TrueActiveSheet.EnableSelection = xlNoSelectionuserform1.Show 启动用户登

16、录窗口End Sub代码说明：以上代码首先激活“封面”工作表，然后隐藏工作表标签，判断“用户密码”工作表是否隐藏，如果不隐藏，就隐藏它，并对“封面”工作表进行保护，启动用户登录窗口。5、人事管理菜单程序在登录窗口中输入正确的用户名和密码后，就可进入人事管理系统的欢迎界面，如图8。人事管理系统菜单 图8图中的“人事管理菜单”需要调用“自定义菜单”程序，此程序可在工作簿的VBE窗口中插入一个模块4生成，其完整代码如下：Dim mymenuobject As CommandBarPopupDim mymenuitem As ObjectDim mysubmenuitem As ObjectPubli

17、c Sub 自定义菜单()On Error Resume Next 如果出现错误，就忽略它，继续运行程序删除已经存在的自定义菜单Application.CommandBars(1).Controls(【人事管理系统】).Delete创建自定义菜单Set mymenuobject = Application.CommandBars(1). _Controls.Add(Type:=msoControlPopup, before:=11, temporary:=True)With mymenuobject .Caption = 【人事管理系统】 .Width = 70: .BeginGroup =

18、True: End With 为自定义菜单添加“事业在职”主菜单并添加有关的子菜单 Call 主菜单(事业在职) Call 子菜单(在职名册表, 在职名册表) Call 子菜单(在职审批表, 在职审批表) Call 子菜单(在职报批表, 在职报批表) Call 主菜单(事业离退休) Call 子菜单(离退休名册表, 离退休名册表) Call 子菜单(离退休审批表, 离退休审批表) Call 子菜单(离退休报批表, 离退休报批表)End Sub子程序“主菜单”Public Sub 主菜单(mycapTion As String)Set mymenuitem = mymenuobject.Cont

19、rols.Add(Type:=msoControlPopup)With mymenuitem .Caption = mycapTion .BeginGroup = True: End WithEnd Sub子程序“子菜单”Public Sub 子菜单(mycapTion As String, myonaction As String)Set mysubmenuitem = mymenuitem.Controls.Add(Type:=msoControlButton)With mysubmenuitem .Caption = mycapTion .OnAction = myonaction .B

20、eginGroup = True: End WithEnd Sub注：为节省篇幅,在一行中使用了冒号隔开多条指令（下同）。代码说明：以上代码是在进入系统后首先删除已经存在的自定义菜单，然后再创建一个自定义菜单，并调用子程序的“主菜单”和“子菜单”程序。6、激活人事管理菜单为了能使上面创建的“人事管理系统”菜单能够使用，必须使用程序激活，可在工作簿的VBE窗口中插入一个模块5完成，其完整代码如下：Private Sub 在职名册表()Sheets(事业).ActivateEnd SubPrivate Sub 在职审批表()Sheets(事业审批表).ActivateEnd SubPrivate

21、Sub 在职报批表()Sheets(事业(报批表).ActivateEnd SubPrivate Sub 离退休名册表()Sheets(离退休).ActivateEnd SubPrivate Sub 离退休审批表()Sheets(离退休审批表).ActivateEnd SubPrivate Sub 离退休报批表()Sheets(离退休(报批表).ActivateEnd Sub代码说明：以上代码是在分别单击“人事管理系统”菜单中的“在职名册表”等子菜单时，激活自定义菜单，然后相应激活各自的工作表，激活后即可对工作表进行操作。至此，系统登录窗口的设计工作完成，为用户提供了一个比较友好的人机交互界面

22、。2007年3月15日通讯地址：浙江省诸暨市滨江中路19号会计核算中心 许国 办公室电话 05757375026 邮政编码311800 Email：xuguo网址：最新EXCEL人事工资管理系统设计在职人员工改名册表（一）诸暨市教育局核算中心 许 国诸暨市教育局 郑文建进入系统后，选择【人事管理系统】 【事业在职】【在职名册表】菜单，即可打开“诸暨市事业单位工作人员收入分配制度改革名册表”（以下简称在职名册表），如图9。 图9四、在职名册表1、按工改的要求设计好此表格的相应栏目，然后输入单位工作人员的相关信息，其中A列至S列的信息由手工输入，W列的信息如果与K列的信息一致，则可由计算自动给出，

23、如果不一致，则需手工输入，这里我们暂以手工输入为例说明，其他列的信息由计算机自动给出。2、表中的岗位名称，职务名称要统一标准。（1）岗位名称分为管理、专技、工人、见习。（2）职务名称要按岗位分为四大类，其中管理岗位的职务名称分为处级正职、处级副职、科级正职、科级副职、科员、办事员；专技岗位的职务名称分为正高、副高、中级、助理级、员级；工人岗位的职务名称分为高级级技师、技师、高级工、中级工、初级工、普工。见习岗位（即新分配人员）的职务名称为见习。（3）岗位为见习的人员，由于在城区和非城区工作其工资标准有差别，必须在备注栏列注明是“城区”还是“非城区”。3、需要手工输入的信息录入完成后，我们就需要

24、对计算机自动给出的列进行处理。（1）T列（工改前工资中的津贴30%）、U列（工改前工资中的10%浮动工资）和AB列（工改后工资中的10%浮动工资）工资标准的处理。在工作簿的VBE窗口中插入一个模块6，并输入代码如下：Private Sub 津贴30及浮动10()Row = 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 &

25、 i) = 专技 ThenRange(T & i) = WorksheetFunction.Round(Range(s & i) * 1.1 * 3 / 7, 0)Range(u & i) = WorksheetFunction.Round(Range(s & i) * 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) = W

26、orksheetFunction.Round(Range(s & i) * 3 / 7, 0)Range(u & i) = 15Range(AB & i) = 15如果J列I行单元格为见习,并且AF列I行单元格为非城区,那么T列的公式如下,U列和AB列的值为0ElseIf 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行单

27、元格为城区,那么T列,U列,AA列和AB列的值为0ElseIf Range(j & i) = 见习 And Range(af & i) = 城区 ThenRange(T & i) = 0Range(u & i) = 0Range(AB & i) = 0Range(aa & i) = 0End If: Next iApplication.ScreenUpdating = True 打开屏幕更新End Sub代码说明：以上代码是在先判断J列I行单元格以及AF列I行单元格的内容，然后根据此内容对T列、U列和AB列进行处理，自动获得相应公式或数值。上述代码输入完成后退出并保存，然后在【工具】 【宏】菜

28、单中运行“津贴30及浮动10”宏，即可得到T列、U列和AB列的值。（2）Y列单元格的数据处理。Y列为工改后的岗位工资标准，岗位工资的标准是根据现任职称或职务而定的，如中级职称为680元， 要使此列的标准自动给出，需建立一个自定义函数，具体可在工作簿的VBE窗口中插入一个模块7完成，其完整代码如下：Function gz(a As String) As Singlegz = 0如果职务为处级正职，那么岗位工资为1045If a = 处级正职 Then gz = 1045 ElseIf a = 处级副职 Then gz = 850 ElseIf a = 科级正职 Then gz = 720 Els

29、eIf a = 科级副职 Then gz = 640 ElseIf a = 科员 Then gz = 590 ElseIf a = 办事员 Then gz = 550 ElseIf a = 正高 Then gz = 1420 ElseIf a = 副高 Then gz = 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 = 高级工 T

30、hen gz = 615 ElseIf a = 中级工 Then gz = 575 ElseIf a = 初级工 Then gz = 545 ElseIf a = 普工 Then gz = 540Else: End IfEnd Function代码说明：以上代码是一个自定义函数，函数名gz，a为变量，代表职称或职务，如职务为处级正职，则岗位工资为1045元。上述代码输入完成后退出并保存，在Y8单元格中输入“=gz(w8)”，可得到Y8单元格的值，向下拖动此公式可得到其他单元格的岗位工资标准。2007年3月15日此文在软件报2007年第14期开始连载通讯地址：浙江省诸暨市滨江中路19号会计核算中

31、心 许国 办公室电话 05757375026 邮政编码311800 Email：xuguo网址：最新EXCEL人事工资管理系统设计在职人员工改名册表（二）诸暨市教育局核算中心 许 国诸暨市教育局 郑文建（3）X列单元格的数据处理。X列为工改后岗位工资的岗位级别，它与岗位工资的标准或职称或职务相对应，如中级职称为十级，岗位工资680元的也为十级，要使此列的标准自动给出，需建立一个自定义函数，具体可在工作簿的VBE窗口中插入一个模块8完成，其完整代码如下：Function dj(a As String) As Stringdj = If a = 处级正职 Then dj = 五级 ElseIf a

32、 = 处级副职 Then dj = 六级 ElseIf a = 科级正职 Then dj = 七级 ElseIf a = 科级副职 Then dj = 八级 ElseIf a = 科员 Then dj = 九级 ElseIf a = 办事员 Then dj = 十级 ElseIf a = 正高 Then dj = 四级 ElseIf a = 副高 Then dj = 七级 ElseIf a = 中级 Then dj = 十级 ElseIf a = 助理级 Then dj = 十二级 ElseIf a = 员级 Then dj = 十三级 Else: End IfEnd Function代码说明

33、：以上代码是一个自定义函数，函数名dj，a为变量，代表职称或职务，如职务为中级，则岗位级别为十级。上述代码输入完成后退出并保存，在X8单元格中输入“=dj (w8)”，可得到X8单元格的值，向下拖动此公式可得到其他单元格的岗位级别。（4）AA列单元格的数据处理。AA列为工改后的薪级工资标准，薪级工资标准是根据套改“三要素”（职务或职称、任职年限、套改年限）进行套改得到的，如中级职称是2001年7月取得（至2006年6月，其任职年限为6年），套改年限18年，则对照套改表可得到其薪级工资标准为365元。要使此列的标准自动给出，需建立几个自定义函数，具体可在工作簿的VBE窗口中插入几个模块完成，由于

34、此自定义函数较长，需分成两个模块，分别为模块9和10。岗位为管理和专技的薪级工资的自定义函数部分代码如下：Function xj(a As String, b, c) As Singlexj = 0If a = 处级正职 And b = 3 ThenIf c = 15 And 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 = 52

35、7 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：End If：End Function代码说明：以上代码是一个自定义函数，函数名xj，a、b和c都为变量，分别为套改三要素中的职务、任职年限和套改年限，如职务为处级，任职年限为3年，套改年限为18年的人员，其薪级工资为527元。上述代码输入完成后退出并保存，在AA8单元格中输入“=xj (w8,n8,i8)”，即可在AA8单元格中

36、得到此人的薪级工资，向下拖动此公式可得到其他人员的薪级工资。岗位为工人的薪级工资的自定义函数部分代码如下：Function grxj(a As String, b, c) As Singlegrxj = 0 If a = 技师 And b = 2 ThenIf c = 21 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

37、(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 & 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(x & i) = f：Range(z & i) = gNext i：Application.ScreenUpdating = TrueEnd Sub代码说明：以上代码是先对AF列单元格内容进行判断，如果有“低职”两字的，就删除“低职”两字，然后分别用现任职务和低一职务进行套改，如果用现任职务套改