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

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

2、如图（一）所示：图一打开一个新的Excel表建立一个人事信息库框架接下来请您不要急着录入人员信息，我们要对一些信息项进行函数设置，以便系统可以 自动生成相关信息，这会使我们的工作产生事半功倍的效果。1 、 性别、出生月日、年龄的自动填充功能设置我们先对“性别”“出生年月”“年龄”进行函数设置。 当我们输入某人身份证号码时，系统便会自动生成“性别”，“出生年月”及“年龄”，这样就减少了我们录入的工作量。请分别选择性别、出生月日、年龄信息项单元格输入下列公式:(1) 性别 : = IF(MOD(IF(LEN(E3)=15,MID(E3,15,1),MID(E3,17,1),2)=1,"

3、男 "," 女 ")含义：“ 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,

4、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) 年龄 : =DATED

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

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

7、月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(DATE

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

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

10、请点击编辑栏中的“数据”一“记录单”如图(三) 我们可以用“Tab键进行项目的换行录入如图 (四)。图三记录单a nct L1,3(舅%am1 hJ4K76"11 ：,.6?，氏* F C<g . octIQ F QW11 *,1 厂。1JjJoli14 g is . m?:运, DM1T Q|f13 ' Cfil«*H.门Jli&£F4WnHkt *f力一aJi更UM9itsUtL frfeS*H =tl.M正 虫Rtt*第丘制Ad"zn»-ii-fqiuai. ”Emw-il情MiEWHK-Vli-f叁 aiUElT

11、- 3IC -AJKIAM睥脚jprrLT7*.*FW-Tip .KU那 号* | *F |ffllMI *拒；”12«0E-1：L 2TO-2-S*WT3)/T-EMM-M 力叫”1图四Tab键进行项目的换行录入4、用“窗口冻结”功能可以进行简单的数据查询当我们录入完数据后， 我们希望能够非常方便的查询信息，但是由于信息库所涉及的项目很多，我们常常会遇到这样的情况：看到左边的信息又看不到右边的信息，或者看到下面的信息又不知道此信息所对应的信息项。这时我们可以采用“窗口冻结”功能。例如我现在想保留各信息项，同时保留每个人的编号、姓名、部门，让其他信息可以根据需要进行查找， 这时我们可

12、以点击 D3单元格，然后点击编辑菜单栏的“窗口”，选择“冻结窗口”，就可 以出现如图（五）情况：图五查询信息这样我们会非常容易查到某人相关信息，但是值得注意的是设置窗口冻结的规律：如果你想冻结第2行请将光标放到第 3行单元格处，进行冻结窗口设置。 如果你想冻结 C列，请 将光标放到D列单元格处，进行冻结窗口设置。 如果你即想冻结第 2行又想冻结C列，请将 光标放到他们的交叉单元格D3上进行设置。如果想取消冻结功能可以点击“窗口”菜单选择取“消冻结窗口”功能。5、“自动筛选”功能可以进行简单的数据统计有时我们想非常快速的得到一些数据，比如本公司在职人员中， 本科生学历的男生有多少人？这时我们可以

13、采用自动筛选功能。请选择“人员类别”单元格，点击编辑菜单栏上的“数据”选择“筛选”，点击“自动筛选”。在每个信息项单元格右下角都会出现选择按钮。我们分别在人员类别处选择在职（图（六），在学历处选择本科（图（七），在性别处选择男（图（八），最后用鼠标将显示的性别全部选上，这时注意图（九）中用红笔圈住的地方就是我们所需要的数据了。图六分别在人员类别处选择在职图七在学历处选择本科图八在性别处选择男图九 红笔圈住的地方就是我们所需要的数据6、用“数据透视表”功能快速汇总各项数据口51| “1， «®i K(D H< k|i 工 Jj WS Ui in *ii ：i3 印二 #

14、 *中，七 £ 少 口&-5-* W： MJg W*n 一1 . UY * 如ffninirgw f *1，j' ''i'f 后"ini' &n, +t*n 鼾函中职A，7之堪中一丁 一 - TW 毛：M3- a-Jd和CBTfW*MTT- ll 看上JTCUI I - i JHi J-3-JllMt |iai-M r »>hm|iw-1-1or± w± 也】一：171不:nWw-I.以上功能的设置多用于日常人事工作中，但到年底我们需要对这一年的人事情况进行大量的信息总结，比如人员的

15、离职情况，入职情况，各类数据构成比例等。这项工作更是一项非常繁重的工作。我们可以利用Excel自带的“数据透视表”功能为我们排忧解难。请点击编辑菜单栏中“数据”选项，选择“数据透视表和数据透视图”选项， 如图（十）三-回产口百瓦国力、* T4清丁 I算。） 户 L e I a i £ 一匕E£ H .庆 利EAWBHMHUEMI 名财懂-jUnt!h12'Xd潍笃,后富日13工”XffWjg产配,皿11界&1；五 jqanHE.iaMM-ih *2!-中倡得丘 Z5fOM-5-G MMMhMRL& 1-TTN宿，|别:?卞劝 ±l*b Q-

16、tX .：i . -.1._uu_-r-a自 F用 I4*| .1! ij m*A5Vantitav£pg-IipMW I UXHUilBIEj 叁;HB1力iMQ为 5 jli*.L/EL加语-*$/：w>g|iRiN3ag I：4Fl173lU'-4-E:"维m:网"I*出 于士 MTM-rEtVUiJ I AMT 国照|士工*;抬*必,图十 选择“数据透视表和数据透视图”选项选择“数据透视表”接下来点击“下一步”即可，我们需要确定建立数据透视表的数据源区域，一般系统会自动将整个信息库区域设置成我们要选择的区域。在出现“数据透视表和数据透视图向导

17、-3”第3步时我们选择“现有工作表”，将工作表区域选择在新的 sheet中A3单元格”如图（十一）。点击“完成”后，便会出现（图（十二）结果。这里解释一下我 们为什么要选择A3单元格，这主要是因为显示区上面有两行用来放置页字段。图H一将工作表区域选择在新的 sheet中A3单元格a> mm习荤砂 电法|卢L4所在El 字啊梗 口事业 习孑所 u学悔TILI比牛斗翼将时同 一 hE1 J' IX-人学都尊养 K理KI要 ?;钳昌利炉图十二蓝圈圈住的地方例如我们现在想统计各部门2003年入职人员情况。我们便可以将相关项目用鼠标托至指定位置，如图（十二）红色箭头显示。通过对信息项目的拖拽， 系统会自动出现相关信息的 统计数据。如果我们还想出现统计数据的图示，这时我们可以点击数据透视表编辑菜单栏上的图例图标，如图（十二）蓝圈圈住的地方。这