用Excel建立人事管理系统.doc

1、用 Excel 建立一套小型人事数据管理系统前言：在现实生活中，很多小型企业并没有给人事部门配备专门的人事管理软件。实际上，一套专门的人事管理软件费用昂贵，而且不一定适合企业的实际需求。自己动手，用 Excel 来建立适于本企业具体需求的人事管理系统是一个不错的选择。Excel 的功能强大，而操作又很方便，每月准确无误的统计企业员工增减变化情况，年底分析大量的人事数据等等复杂的工作都能通过 Excel 轻松完成。现在我们给读者朋友们提供这样一个实例通过 Excel 来建立一套小型人事数据管理系统。正文：首先我们打开一个新的 Excel 表建立一个人事信息库框架，信息项目的设置您可根据本单位实际

2、情况而定。如图(一) 所示:图一 点击可看清晰大图接下来请您不要急着录入人员信息，我们要对一些信息项进行函数设置，以便系统可以自动生成相关信息，这会使我们的工作产生事半功倍的效果。1、 性别、出生月日、年龄的自动填充功能设置我们先对“性别”“出生年月”“年龄”进行函数设置。当我们输入某人身份证号码时，系统便会自动生成“性别”，“出生年月”及“年龄”，这样就减少了我们录入的工作量。请分别选择性别、出生月日、年龄信息项单元格输入下列公式:(1)性别: =IF(MOD(IF(LEN(E3)=15,MID(E3,15,1),MID(E3,17,1),2)=1,“男“,“女“)含义：“LEN(E3)=1

3、5”表示看 E3 中是否有 15 个字符;“MID(E3,15,1)”表示在 E3 中从第 15 位开始提取 1 位字符;“MOD(MID() ，2)=1 ”表示提取的字符除以 2 余数为 1;“IF(LEN(E3)=15,MID(E3,15,1),MID(E3,17,1)”表示看 E3 中是否够 15 个字符，如果够就从第 15 个字符开始取 1 个字符，如果不够 15 个字符就从第 17 个字符开始取 1 个字符。我们的身份证号码一般是 15位或 18 位。“IF(MOD(IF(LEN(E3)=15,MID(E3,15,1),MID(E3,17,1),2)=1,“男“,“女“”表示所取字符

4、除以 2 如果余数为 1 显示男，否则显示女。(2)出生年月: = IF(LEN(E3)=15,DATE(MID(E3,7,2),MID(E3,9,2),MID(E3,11,2),DATE(MID(E3,7,4),MID(E3,11,2),MID(E3,13,2)含义：DATE(YEAR,MONTH,DAY);“MID(E3,7,4)”表示在 E3 中从第 7 个字符开始连续取 4 个字符表示年，用类似的表示方法一个人的出生年月日便可以通过函数设置表示出来，如果为了看起来方便，我们可以将单元格格式设置成年、月、日的日期格式，这样显示的结果会非常容易理解。(3)年龄: =DATEDIF(G3,T

5、ODAY(),“Y“)含义：“DATEDIF(date1,date2, “Y”)”表示两个日期的差值;“TODAY() ”表示系统自带的日期即显示当日日期 ;“DATEDIF(G3,TODAY(),“Y“)”表示今天的日期与 G3 所表示的出生月日之间的年份差值，这样一个人的年龄就会容易的显示出来了。2、 劳动合同期限的自动生成和提前 30 天定期提醒功能设置劳动合同管理也是人事管理中不可缺少的一部分，特别是劳动合同到期续签问题更是不可忽视。如果由于我们的人为原因造成了劳动合同漏签、延期签订等问题可能会给公司以及员工个人带来一定的损失。从一个人入职签订试用期合同开始，再到签订正式劳动合同以及后

6、来的续签劳动合同，一系列的日期如何能够让系统自动生成，并且形成系统提前 30 天自动提醒我们的功能呢？这需要对一些信息项进行函数设置。如图(二) 所示:图二 点击可看清晰大图(1)试用期到期时间: =DATE(YEAR(P3),MONTH(P3)+3,DAY(P3)-1)含义：“DATE(YEAR(),MONTH(),DAY()”显示指定日期;在这里我们假设试用期为 3 个月，我们需要在 Q3 单元格中输入上述公式，其中 MONTH(P3)+3 表示在此人入职时间月的基础上增加三个月。而 DAY(P3)-1 是根据劳动合同签订为整年正月而设置的。比如 2005 年 11 月 6 日到 2006

7、 年 11 月 5 日为一个劳动合同签订期。(2)劳动合同到期时间: =DATE(YEAR(P3)+1,MONTH(P3),DAY(P3)-1)我们同样采用上述函数的设置方法。这里我们假设劳动合同期限为 1 年，则我们需要设置成 YEAR(P3)+1，另外这个数值依然以入职日期为计算根据，所以天数上还要设置成 DAY(P3)-1 的格式。(3)续签合同到期时间: =DATE(YEAR(S3)+1,MONTH(S3),DAY(S3)这里需要注意的是续签合同计算是以前份合同签订到期日期为根据的，所以只在前一份合同到期时间的基础上增加 1 年即可，无需天数上减 1。(4)试用期提前 7 天提醒: =

8、IF(DATEDIF(TODAY(),Q3,“d“)=7,“试用期快结束了“,“)这里用到了 DATEDIF 函数，表示两个日期差值，但是需要注意的是，我们要表示提前 7 天提醒，所以，将 TODAY()函数写到试用期时间前面即 TODAY(),Q3 而不能表示成 Q3，TODAY()。其中“d”表示两个日期天数差值。我们用 IF()函数来表示显示要求，那么这个函数设置的含义为:如果差值为 7 则显示“试用期快结束了”否则不显示信息，在编辑函数时用“”表示不显示任何信息。(5)提前 30 天提醒: =IF(DATEDIF(TODAY(),S4,“m“)=1,“该签合同了 “,“)函数设置方法同

9、上，其含义是两个日期相差 1 个月则显示“该签合同了”否则不显示任何信息。这里没有设置成相差 30 天提醒是因为考虑到设置成月更利于我们人事工作的操作。同样需要注意的是不要将显示“今天日期”函数与显示“合同到期日期”函数顺序颠倒。其中“m“表示月的含义。3、 采用“记录单”录入信息通过对一些人事信息项进行函数设置后，我们便可以开始录入信息了。逐行的键入人事信息，会让人很快产生疲劳感，甚至会出现串行或输错信息的工作失误。我们可以采用 Excel 自带的“记录单”功能来解决这个问题。请点击编辑栏中的“数据”“记录单”如图(三)我们可以用“Tab 键进行项目的换行录入如图(四) 。”图三 点击可看清

10、晰大图图四 点击可看清晰大图4、 用“窗口冻结”功能可以进行简单的数据查询当我们录入完数据后，我们希望能够非常方便的查询信息，但是由于信息库所涉及的项目很多，我们常常会遇到这样的情况:看到左边的信息又看不到右边的信息，或者看到下面的信息又不知道此信息所对应的信息项。这时我们可以采用“窗口冻结”功能。例如我现在想保留各信息项，同时保留每个人的编号、姓名、部门，让其他信息可以根据需要进行查找，这时我们可以点击 D3 单元格，然后点击编辑菜单栏的“窗口”，选择“冻结窗口”，就可以出现如图(五) 情况:图五 点击可看清晰大图这样我们会非常容易查到某人相关信息，但是值得注意的是设置窗口冻结的规律:如果你

11、想冻结第 2 行请将光标放到第 3 行单元格处，进行冻结窗口设置。如果你想冻结 C 列，请将光标放到 D 列单元格处，进行冻结窗口设置。如果你即想冻结第 2 行又想冻结 C 列，请将光标放到他们的交叉单元格 D3 上进行设置。如果想取消冻结功能可以点击“窗口”菜单选择取“消冻结窗口”功能。5、“自动筛选”功能可以进行简单的数据统计有时我们想非常快速的得到一些数据，比如本公司在职人员中，本科生学历的男生有多少人?这时我们可以采用自动筛选功能。请选择“人员类别”单元格，点击编辑菜单栏上的“数据”选择“筛选”，点击“自动筛选”。在每个信息项单元格右下角都会出现选择按钮。我们分别在人员类别处选择在职(

12、图( 六) ，在学历处选择本科(图(七) ，在性别处选择男(图(八)，最后用鼠标将显示的性别全部选上，这时注意图( 九)中用红笔圈住的地方就是我们所需要的数据了。图六 点击可看清晰大图图七 点击可看清晰大图图八 点击可看清晰大图图九 点击可看清晰大图6、用“数据透视表”功能快速汇总各项数据以上功能的设置多用于日常人事工作中，但到年底我们需要对这一年的人事情况进行大量的信息总结，比如人员的离职情况，入职情况，各类数据构成比例等。这项工作更是一项非常繁重的工作。我们可以利用 Excel 自带的“数据透视表”功能为我们排忧解难。请点击编辑菜单栏中“数据”选项，选择“数据透视表和数据透视图”选项，如图

13、(十)图十 点击可看清晰大图选择“数据透视表”接下来点击“下一步”即可，我们需要确定建立数据透视表的数据源区域，一般系统会自动将整个信息库区域设置成我们要选择的区域。在出现“数据透视表和数据透视图向导-3”第 3 步时我们选择“现有工作表”，将工作表区域选择在新的 sheet 中 A3 单元格” 如图(十一) 。点击“完成”后，便会出现(图( 十二) 结果。这里解释一下我们为什么要选择 A3 单元格，这主要是因为显示区上面有两行用来放置页字段。图十一 点击可看清晰大图图十二 点击可看清晰大图例如我们现在想统计各部门 2003 年入职人员情况。我们便可以将相关项目用鼠标托至指定位置，如图(十二)红色箭头显示。通过对信息项目的拖拽，系统会自动出现相关信息的统计数据。如果我们还想出现统计数据的图示，这时我们可以点击数据透视表编辑菜单栏上的图例图标，如图(十二) 蓝圈圈住的地方。这时会出现 chart1，如图(十三)所示。如果我们想改变图例显示类型可以点击图表向导进行自由选择。