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

用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所表示出生月日之间年份差值，这么一个人年纪就会轻易显示出来了。
注：性别、出生月日、年纪自动填充后，通常还要复制选择性粘贴，选“值”，因为Excel单元格中公告轻易犯错，还是真实数值比较安全。
2、 劳动协议期限自动生成和提前30天定时提醒功效设置
　　劳动协议管理也是人事管理中不可缺乏一部分，尤其是劳动协议到期续签问题更是不可忽略。假如因为我们人为原因造成了劳动协议漏签、延期签署等问题可能会给企业和职员个人带来一定损失。从一个人入职签署试用期协议开始，再到签署正式劳动协议和以后续签劳动协议，一系列日期怎样能够让系统自动生成，而且形成系统提前30天自动提醒我们功效呢？这需要对部分信息项进行函数设置。图(二)所表示:

图二 点击可看清楚大图
　　(1)试用期到期时间: =DATE(YEARP3),MONTH(P3)+3,DAY(P3)-1)
　　含义：“DATE(YEAR(),MONTH(),DAY())”显示指定日期;
　　在这里我们假设试用期为3个月，我们需要在Q3单元格中输入上述公式，其中MONTH(P3)+3表示在此人入职时间月基础上增加三个月。而DAY(P3)-1是依据劳动协议签署为整年正月而设置。比如11月6日到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键进行项目标换行录入图(四)。”

图三 点击可看清楚大图
图四 点击可看清楚大图
　　4、 用“窗口冻结”功效能够进行简单数据查询
　　当我们录入完数据后，我们期望能够很方便查询信息，不过因为信息库所包含项目很多，我们常常会碰到这么情况:看到左边信息又看不到右边信息，或看到下面信息又不知道此信息所对应信息项。这时我们能够采取“窗口冻结”功效。比如我现在想保留各信息项，同时保留每个人编号、姓名、部门，让其它信息能够依据需要进行查找，这时我们能够点击D3单元格，然后点击编辑菜单栏“窗口”，选择“冻结窗口”，就能够出现图(五)情况:
图五 点击可看清楚大图
　　这么我们会很轻易查到某人相关信息，不过值得注意是设置窗口冻结规律:假如你想冻结第2行请将光标放到第3行单元格处，进行冻结窗口设置。假如你想冻结C列，请将光标放到D列单元格处，进行冻结窗口设置。假如你即想冻结第2行又想冻结C列，请将光标放到她们交叉单元格D3上进行设置。假如想取消冻结功效能够点击“窗口”菜单选择取“消冻结窗口”功效。
5、“自动筛选”功效能够进行简单数据统计
　　有时我们想很快速得到部分数据，比如本企业在职人员中，本科生学历男生有多少人?这时我们能够采取自动筛选功效。请选择“人员类别”单元格，点击编辑菜单栏上“数据”选择“筛选”，点击“自动筛选”。在每个信息项单元格右下角全部会出现选择按钮。我们分别在人员类别处选择在职(图(六))，在学历处选择本科(图(七))，在性别处选择男(图(八))，最终用鼠标将显示性别全部选上，这时注意图(九)中用红笔圈住地方就是我们所需要数据了。
图六 点击可看清楚大图
图七 点击可看清楚大图
图八 点击可看清楚大图
图九 点击可看清楚大图
6、用“数据透视表”功效快速汇总各项数据
　　以上功效设置多用于日常人事工作中，但到年底我们需要对这十二个月人事情况进行大量信息总结，比如人员离职情况，入职情况，各类数据组成百分比等。这项工作更是一项很繁重工作。我们能够利用Excel自带“数据透视表”功效为我们排忧解难。
　　请点击编辑菜单栏中“数据”选项，选择“数据透视表和数据透视图”选项，图(十)
图十 点击可看清楚大图
　　选择“数据透视表”接下来点击“下一步”即可，我们需要确定建立数据透视表数据源区域，通常系统会自动将整个信息库区域设置成我们要选择区域。在出现“数据透视表和数据透视图向导-3”第3步时我们选择“现有工作表”，将工作表区域选择在新sheet中A3单元格” 图(十一)。点击“完成”后，便会出现(图(十二))结果。这里解释一下我们为何要选择A3单元格，这关键是因为显示区上面有两行用来放置页字段。
图十一 点击可看清楚大图
图十二 点击可看清楚大图
　　比如我们现在想统计各部门入职人员情况。我们便能够将相关项目用鼠标托至指定位置，图(十二)红色箭头显示。经过对信息项目标拖拽，系统会自动出现相关信息统计数据。假如我们还想出现统计数据图示，这时我们能够点击数据透视表编辑菜单栏上图例图标，图(十二)蓝圈圈住地方。这时会出现chart1，图(十三)所表示。假如我们想改变图例显示类型能够点击图表向导进行自由选择。
图十三 点击可看清楚大图
　　经过这个功效我们能够很轻易统计出多种人事资料，比如学历组成，性别百分比等。同时您还能够依据自己需要来设置布局。当出现“数据透视表和数据透视图向导”第3步时，我们能够点击“布局”按钮，接下来会出现“数据透视表和数据透视图向导-布局”在这里我们能够经过对话框中右侧数据按钮添加或删除我们需要数据项。图(十四)
图十四 点击可看清楚大图
　　以上讲述是部分常见功效设置，您还能够举一反三，加入部分好用小功效。查阅天极办公软件栏目 能取得丰富参考信息。