excel实用技巧建立人力资源系统 2.docx

excel实用技巧建立人力资源系统 2.docx

《excel实用技巧建立人力资源系统 2.docx》由会员分享，可在线阅读，更多相关《excel实用技巧建立人力资源系统 2.docx（11页珍藏版）》请在冰豆网上搜索。

excel实用技巧建立人力资源系统2

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

    前言：

　　在现实生活中，许多小型企业并没有给人事部门配备特地的人事管理软件。

事实上，一套特地的人事管理软件费用昂贵，而且不必须适合企业的实际需求。

　　自己动手，用Excel来建立适于本企业详细需求的人事管理系统是一个不错的选择。

Excel的功能强大，而操作又很便利，每月精确无误的统计企业员工增减变更状况，年底分析大量的人事数据等等困难的工作都能通过Excel轻松完成。

此时此刻我们给读者挚友们供应这样一个实例——通过Excel来建立一套小型人事数据管理系统。

   正文：

　　首先我们翻开一个新的Excel表建立一个人事信息库框架，信息工程的设置您可依据本单位实际状况而定。

如图

（一）所示:

图一翻开一个新的Excel表建立一个人事信息库框架

　　接下来请您不要急着录入人员信息，我们要对一些信息项进展函数设置，以便系统可以自动生成相关信息，这会使我们的工作产生事半功倍的效果。

　　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天自动提示我们的功能呢？

这须要对一些信息项进展函数设置。

如图

（二）所示:

图二信息项进展函数设置

（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）+3,MONTH（P3）,DAY（P3）-1）

　　我们同样采纳上述函数的设置方法。

这里我们假设劳动合同期限为3年，那么我们须要设置成YEAR（P3）+3，另外这个数值仍旧以入职日期为计算依据，所以天数上还要设置成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键进展工程的换行录入如图（四）。

”

图三记录单

图四Tab键进展工程的换行录入

　4、用“窗口冻结”功能可以进展简洁的数据查询

　　当我们录入完数据后，我们盼望能够特别便利的查询信息，但是由于信息库所涉及的工程许多，我们时时会遇到这样的状况:

看到左边的信息又看不到右边的信息，或者看到下面的信息又不知道此信息所对应的信息项。

这时我们可以采纳“窗口冻结”功能。

例如我此时此刻想保存各信息项，同时保存每个人的编号、姓名、部门，让其他信息可以依据须要进展查找，这时我们可以点击D3单元格，然后点击编辑菜单栏的“窗口”，选择“冻结窗口”，就可以出现如图（五）状况:

图五查询信息

　　这样我们会特别简洁查到某人相关信息，但是值得留意的是设置窗口冻结的规律:

假如你想冻结第2行请将光标放到第3行单元格处，进展冻结窗口设置。

假如你想冻结C列，请将光标放到D列单元格处，进展冻结窗口设置。

假如你即想冻结第2行又想冻结C列，请将光标放到他们的穿插单元格D3上进展设置。

假如想取消冻结功能可以点击“窗口”菜单项选择择取“消冻结窗口”功能。

5、“自动筛选”功能可以进展简洁的数据统计

　　有时我们想特别快速的得到一些数据，比方本公司在职人员中，本科生学历的男生有多少人?

这时我们可以采纳自动筛选功能。

请选择“人员类别”单元格，点击编辑菜单栏上的“数据”选择“筛选”，点击“自动筛选”。

在每个信息项单元格右下角都会出现选择按钮。

我们分别在人员类别处选择在职（图（六）），在学历处选择本科（图（七）），在性别处选择男（图（八）），最终用鼠标将显示的性别全部选上，这时留意图（九）中用红笔圈住的地方就是我们所须要的数据了。

图六分别在人员类别处选择在职

图七在学历处选择本科

图八在性别处选择男

图九红笔圈住的地方就是我们所须要的数据

6、用“数据透视表”功能快速汇总各项数据

　　以上功能的设置多用于日常人事工作中，但到年底我们须要对这一年的人事状况进展大量的信息总结，比方人员的离职状况，入职状况，各类数据构成比例等。

这项工作更是一项特别繁重的工作。

我们可以利用Excel自带的“数据透视表”功能为我们排忧解难。

　　请点击编辑菜单栏中“数据”选项，选择“数据透视表和数据透视图”选项，如图（十）

图十选择“数据透视表和数据透视图”选项

　　选择“数据透视表”接下来点击“下一步”即可，我们须要确定建立数据透视表的数据源区域，一般系统会自动将整个信息库区域设置成我们要选择的区域。

在出现“数据透视表和数据透视图向导-3”第3步时我们选择“现有工作表”，将工作表区域选择在新的sheet中A3单元格”如图（十一）。

点击“完成”后，便会出现（图（十二））结果。

这里说明一下我们为什么要选择A3单元格，这主要是因为显示区上面有两行用来放置页字段。

图十一将工作表区域选择在新的sheet中A3单元格

图十二蓝圈圈住的地方

　　例如我们此时此刻想统计各部门2003年入职人员状况。

我们便可以将相关工程用鼠标托至指定位置，如图（十二）红色箭头显示。

通过对信息工程的拖拽，系统会自动出现相关信息的统计数据。

假如我们还想出现统计数据的图示，这时我们可以点击数据透视表编辑菜单栏上的图例图标，如图（十二）蓝圈圈住的地方。

这时会出现chart1，如图（十三）所示。

假如我们想变更图例显示类型可以点击图表向导进展自由选择。

图十三完成

　　通过这个功能我们可以很简洁的统计出各种人事资料，比方学历构成，性别比例等。

同时您还可以依据自己的须要来设置布局。

当出现“数据透视表和数据透视图向导”第3步时，我们可以点击“布局”按钮，接下来会出现“数据透视表和数据透视图向导-布局”在这里我们可以通过对话框中右侧的数据按钮添加或删除我们须要的数据项。

如图（十四）

图十四统计人事资料

　　以上讲解并描述的是一些常用功能的设置，您还可以举一反三，参加一些好用的小功能。