登录  
 加关注
查看详情
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

专属秘密基地

最初描绘的那个梦想,你现在还记得吗?

 
 
 

日志

 
 

用Excel建立一套小型人事数据管理系统  

2010-03-25 14:47:58|  分类: Excel |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

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

建立数据库框架具体的项目可根据您的实际情况来设定。>>设置自动填充提高效率做好这步,可以让Excel自动填充某些数据内容,减小工作量。>>实现自动提醒功能到期提醒是很常用的一个功能,这里我们通过劳动合同到期提醒来说明这项功能的实现方法。>>用记录单录入数据逐行键入人事信息,很快会让人产生疲劳感,录入错误信息。我们可以用Excel自带的“记录单”功能来解决这个问题。>>冻结窗口以便查询可以把标题行或列固定,这样在查询的时候可以一眼看到某个数据体现的是什么内容。>>自动筛选以统计数据Excel自动筛选功能可以帮助我们快速找到目标数据并作统计,如本单位的本科生有多少。>>用数据透视表快速汇总数据一般年底我们需要对人事情况进行大量的信息总结,比如人员的离职情况,入职情况,各类数据构成比例等。用数据透视表可以快速完成这一工作。>>
作者新创作了建立人事信息库的简明教程,点这里查看
正文:
首先我们打开一个新的Excel表建立一个人事信息库框架,信息项目的设置您可根据本单位实际情况而定。如图(一)所示:

用Excel建立一套小型人事数据管理系统 - Seven - 专属秘密基地

                                                                图(一)

 

接下来请您不要急着录入人员信息,我们要对一些信息项进行函数设置,以便系统可以自动生成相关信息,这会使我们的工作产生事半功倍的效果。
1、 性别、出生月日、年龄的自动填充功能设置
我们先对“性别”“出生年月”“年龄”进行函数设置。当我们输入某人身份证号码时,系统便会自动生成“性别”,“出生年月”及“年龄”,这样就减少了我们录入的工作量。请分别选择性别、出生月日、年龄信息项单元格输入下列公式:
(1)性别:=IF(MOD(IF(LEN(E3)=15,MID(E3,15,1),MID(E3,17,1)),2)=1,"男","女")
含义:“LEN(E3)=15”表示看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,"男","女"”表示所取字符除以2如果余数为1显示男,否则显示女。
(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,TODAY(),"Y")
含义:“DATEDIF(date1,date2,“Y”)”表示两个日期的差值;
“TODAY()”表示系统自带的日期即显示当日日期;
“DATEDIF(G3,TODAY(),"Y")”表示今天的日期与G3所表示的出生月日之间的年份差值,这样一个人的年龄就会容易的显示出来了。





2、 劳动合同期限的自动生成和提前30天定期提醒功能设置
劳动合同管理也是人事管理中不可缺少的一部分,特别是劳动合同到期续签问题更是不可忽视。如果由于我们的人为原因造成了劳动合同漏签、延期签订等问题可能会给公司以及员工个人带来一定的损失。从一个人入职签订试用期合同开始,再到签订正式劳动合同以及后来的续签劳动合同,一系列的日期如何能够让系统自动生成,并且形成系统提前30天自动提醒我们的功能呢?这需要对一些信息项进行函数设置。如图(二)所示:
用Excel建立一套小型人事数据管理系统 - Seven - 专属秘密基地

                                                                             图(二)


(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年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天提醒:=IF(DATEDIF(TODAY(),Q3,"d")=7,"试用期快结束了","")
这里用到了DATEDIF函数,表示两个日期差值,但是需要注意的是,我们要表示提前7天提醒,所以,将TODAY()函数写到试用期时间前面即TODAY(),Q3而不能表示成Q3,TODAY()。其中“d”表示两个日期天数差值。我们用IF()函数来表示显示要求,那么这个函数设置的含义为:如果差值为7则显示“试用期快结束了”否则不显示信息,在编辑函数时用“”表示不显示任何信息。
(5)提前30天提醒:=IF(DATEDIF(TODAY(),S4,"m")=1,"该签合同了","")
函数设置方法同上,其含义是两个日期相差1个月则显示“该签合同了”否则不显示任何信息。这里没有设置成相差30天提醒是因为考虑到设置成月更利于我们人事工作的操作。同样需要注意的是不要将显示“今天日期”函数与显示“合同到期日期”函数顺序颠倒。其中"m"表示月的含义。

3、 采用“记录单”录入信息
通过对一些人事信息项进行函数设置后,我们便可以开始录入信息了。逐行的键入人事信息,会让人很快产生疲劳感,甚至会出现串行或输错信息的工作失误。我们可以采用Excel自带的“记录单”功能来解决这个问题。请点击编辑栏中的“数据”―“记录单”如图(三)我们可以用“Tab键进行项目的换行录入如图(四)。”
用Excel建立一套小型人事数据管理系统 - Seven - 专属秘密基地
                                                                       图(三)

用Excel建立一套小型人事数据管理系统 - Seven - 专属秘密基地

                                                                     图四

 

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

用Excel建立一套小型人事数据管理系统 - Seven - 专属秘密基地

                                                                             图五

这样我们会非常容易查到某人相关信息,但是值得注意的是设置窗口冻结的规律:如果你想冻结第2行请将光标放到第3行单元格处,进行冻结窗口设置。如果你想冻结C列,请将光标放到D列单元格处,进行冻结窗口设置。如果你即想冻结第2行又想冻结C列,请将光标放到他们的交叉单元格D3上进行设置。如果想取消冻结功能可以点击“窗口”菜单选择取“消冻结窗口”功能。

5、“自动筛选”功能可以进行简单的数据统计
有时我们想非常快速的得到一些数据,比如本公司在职人员中,本科生学历的男生有多少人?这时我们可以采用自动筛选功能。请选择“人员类别”单元格,点击编辑菜单栏上的“数据”选择“筛选”,点击“自动筛选”。在每个信息项单元格右下角都会出现选择按钮。我们分别在人员类别处选择在职(图(六)),在学历处选择本科(图(七)),在性别处选择男(图(八)),最后用鼠标将显示的性别全部选上,这时注意图(九)中用红笔圈住的地方就是我们所需要的数据了。

用Excel建立一套小型人事数据管理系统 - Seven - 专属秘密基地

                                                                                                 图六
用Excel建立一套小型人事数据管理系统 - Seven - 专属秘密基地

                                                                    图七

用Excel建立一套小型人事数据管理系统 - Seven - 专属秘密基地

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

建立数据库框架具体的项目可根据您的实际情况来设定。>>设置自动填充提高效率做好这步,可以让Excel自动填充某些数据内容,减小工作量。>>实现自动提醒功能到期提醒是很常用的一个功能,这里我们通过劳动合同到期提醒来说明这项功能的实现方法。>>用记录单录入数据逐行键入人事信息,很快会让人产生疲劳感,录入错误信息。我们可以用Excel自带的“记录单”功能来解决这个问题。>>冻结窗口以便查询可以把标题行或列固定,这样在查询的时候可以一眼看到某个数据体现的是什么内容。>>自动筛选以统计数据Excel自动筛选功能可以帮助我们快速找到目标数据并作统计,如本单位的本科生有多少。>>用数据透视表快速汇总数据一般年底我们需要对人事情况进行大量的信息总结,比如人员的离职情况,入职情况,各类数据构成比例等。用数据透视表可以快速完成这一工作。>>
作者新创作了建立人事信息库的简明教程,点这里查看
正文:
首先我们打开一个新的Excel表建立一个人事信息库框架,信息项目的设置您可根据本单位实际情况而定。如图(一)所示:

用Excel建立一套小型人事数据管理系统 - Seven - 专属秘密基地

                                                                图(一)

 

接下来请您不要急着录入人员信息,我们要对一些信息项进行函数设置,以便系统可以自动生成相关信息,这会使我们的工作产生事半功倍的效果。
1、 性别、出生月日、年龄的自动填充功能设置
我们先对“性别”“出生年月”“年龄”进行函数设置。当我们输入某人身份证号码时,系统便会自动生成“性别”,“出生年月”及“年龄”,这样就减少了我们录入的工作量。请分别选择性别、出生月日、年龄信息项单元格输入下列公式:
(1)性别:=IF(MOD(IF(LEN(E3)=15,MID(E3,15,1),MID(E3,17,1)),2)=1,"男","女")
含义:“LEN(E3)=15”表示看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,"男","女"”表示所取字符除以2如果余数为1显示男,否则显示女。
(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,TODAY(),"Y")
含义:“DATEDIF(date1,date2,“Y”)”表示两个日期的差值;
“TODAY()”表示系统自带的日期即显示当日日期;
“DATEDIF(G3,TODAY(),"Y")”表示今天的日期与G3所表示的出生月日之间的年份差值,这样一个人的年龄就会容易的显示出来了。





2、 劳动合同期限的自动生成和提前30天定期提醒功能设置
劳动合同管理也是人事管理中不可缺少的一部分,特别是劳动合同到期续签问题更是不可忽视。如果由于我们的人为原因造成了劳动合同漏签、延期签订等问题可能会给公司以及员工个人带来一定的损失。从一个人入职签订试用期合同开始,再到签订正式劳动合同以及后来的续签劳动合同,一系列的日期如何能够让系统自动生成,并且形成系统提前30天自动提醒我们的功能呢?这需要对一些信息项进行函数设置。如图(二)所示:
用Excel建立一套小型人事数据管理系统 - Seven - 专属秘密基地

                                                                             图(二)


(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年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天提醒:=IF(DATEDIF(TODAY(),Q3,"d")=7,"试用期快结束了","")
这里用到了DATEDIF函数,表示两个日期差值,但是需要注意的是,我们要表示提前7天提醒,所以,将TODAY()函数写到试用期时间前面即TODAY(),Q3而不能表示成Q3,TODAY()。其中“d”表示两个日期天数差值。我们用IF()函数来表示显示要求,那么这个函数设置的含义为:如果差值为7则显示“试用期快结束了”否则不显示信息,在编辑函数时用“”表示不显示任何信息。
(5)提前30天提醒:=IF(DATEDIF(TODAY(),S4,"m")=1,"该签合同了","")
函数设置方法同上,其含义是两个日期相差1个月则显示“该签合同了”否则不显示任何信息。这里没有设置成相差30天提醒是因为考虑到设置成月更利于我们人事工作的操作。同样需要注意的是不要将显示“今天日期”函数与显示“合同到期日期”函数顺序颠倒。其中"m"表示月的含义。

3、 采用“记录单”录入信息
通过对一些人事信息项进行函数设置后,我们便可以开始录入信息了。逐行的键入人事信息,会让人很快产生疲劳感,甚至会出现串行或输错信息的工作失误。我们可以采用Excel自带的“记录单”功能来解决这个问题。请点击编辑栏中的“数据”―“记录单”如图(三)我们可以用“Tab键进行项目的换行录入如图(四)。”
用Excel建立一套小型人事数据管理系统 - Seven - 专属秘密基地
                                                                       图(三)

用Excel建立一套小型人事数据管理系统 - Seven - 专属秘密基地

                                                                     图四

 

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

用Excel建立一套小型人事数据管理系统 - Seven - 专属秘密基地

                                                                             图五

这样我们会非常容易查到某人相关信息,但是值得注意的是设置窗口冻结的规律:如果你想冻结第2行请将光标放到第3行单元格处,进行冻结窗口设置。如果你想冻结C列,请将光标放到D列单元格处,进行冻结窗口设置。如果你即想冻结第2行又想冻结C列,请将光标放到他们的交叉单元格D3上进行设置。如果想取消冻结功能可以点击“窗口”菜单选择取“消冻结窗口”功能。

5、“自动筛选”功能可以进行简单的数据统计
有时我们想非常快速的得到一些数据,比如本公司在职人员中,本科生学历的男生有多少人?这时我们可以采用自动筛选功能。请选择“人员类别”单元格,点击编辑菜单栏上的“数据”选择“筛选”,点击“自动筛选”。在每个信息项单元格右下角都会出现选择按钮。我们分别在人员类别处选择在职(图(六)),在学历处选择本科(图(七)),在性别处选择男(图(八)),最后用鼠标将显示的性别全部选上,这时注意图(九)中用红笔圈住的地方就是我们所需要的数据了。

用Excel建立一套小型人事数据管理系统 - Seven - 专属秘密基地

                                                                                                 图六
用Excel建立一套小型人事数据管理系统 - Seven - 专属秘密基地

                                                                    图七

用Excel建立一套小型人事数据管理系统 - Seven - 专属秘密基地

                                                                                 图八

用Excel建立一套小型人事数据管理系统 - Seven - 专属秘密基地

                                                                               图九

 

6、用“数据透视表”功能快速汇总各项数据
以上功能的设置多用于日常人事工作中,但到年底我们需要对这一年的人事情况进行大量的信息总结,比如人员的离职情况,入职情况,各类数据构成比例等。这项工作更是一项非常繁重的工作。我们可以利用Excel自带的“数据透视表”功能为我们排忧解难。
请点击编辑菜单栏中“数据”选项,选择“数据透视表和数据透视图”选项,如图(十)

用Excel建立一套小型人事数据管理系统 - Seven - 专属秘密基地
                                                                              图十

选择“数据透视表”接下来点击“下一步”即可,我们需要确定建立数据透视表的数据源区域,一般系统会自动将整个信息库区域设置成我们要选择的区域。在出现“数据透视表和数据透视图向导-3”第3步时我们选择“现有工作表”,将工作表区域选择在新的sheet中A3单元格” 如图(十一)。点击“完成”后,便会出现(图(十二))结果。这里解释一下我们为什么要选择A3单元格,这主要是因为显示区上面有两行用来放置页字段。用Excel建立一套小型人事数据管理系统 - Seven - 专属秘密基地

                                                                        图十一

用Excel建立一套小型人事数据管理系统 - Seven - 专属秘密基地

                                                                           图十二

例如我们现在想统计各部门2003年入职人员情况。我们便可以将相关项目用鼠标托至指定位置,如图(十二)红色箭头显示。通过对信息项目的拖拽,系统会自动出现相关信息的统计数据。如果我们还想出现统计数据的图示,这时我们可以点击数据透视表编辑菜单栏上的图例图标,如图(十二)蓝圈圈住的地方。这时会出现chart1,如图(十三)所示。如果我们想改变图例显示类型可以点击图表向导进行自由选择。
用Excel建立一套小型人事数据管理系统 - Seven - 专属秘密基地

                                                                        图十三

通过这个功能我们可以很容易的统计出各种人事资料,比如学历构成,性别比例等。同时您还可以根据自己的需要来设置布局。当出现“数据透视表和数据透视图向导”第3步时,我们可以点击“布局”按钮,接下来会出现“数据透视表和数据透视图向导-布局”在这里我们可以通过对话框中右侧的数据按钮添加或删除我们需要的数据项。如图(十四)

用Excel建立一套小型人事数据管理系统 - Seven - 专属秘密基地

                                                                                     图十四

以上讲述的是一些常用功能的设置,您还可以举一反三,加入一些好用的小功能。查阅天极办公软件栏目http://soft.yesky.com/office能获得丰富的参考信息。

  评论这张
 
阅读(338)| 评论(0)

历史上的今天

在LOFTER的更多文章

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2018