用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位。 1 “IF(MOD(IF(LEN(E3)=15,MID(E3,15,1),MID(E3,17,1)),2)=1,"男","女"”表示所取字符除以2如果余数为1显示男，否则显示女。 (2)出生年月： =IF(LEN(E3)=15,DATE(MID(E3,7,2),MID(K2,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,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) 2 含义：“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键进行项目的换行录入如图(四)。” 3 图三 图四 4、 用“窗口冻结”功能可以进行简单的数据查询 当我们录入完数据后，我们希望能够非常方便的查询信息，但是由于信息库所涉及的 项目很多，我们常常会遇到这样的情况:看到左边的信息又看不到右边的信息，或者看到下 面的信息又不知道此信息所对应的信息项。这时我们可以采用“窗口冻结”功能。例如我现在想保留各信息项，同时保留每个人的编号、姓名、部门，让其他信息可以根据需要进行 查找，这时我们可以点击D3单元格，然后点击编辑菜单栏的“窗口”，选择“冻结窗口”，就可以出现如图(五)情况: 4 图五 这样我们会非常容易查到某人相关信息，但是值得注意的是设置窗口冻结的规律:如果你想冻结第2行请将光标放到第3行单元格处，进行冻结窗口设置。如果你想冻结C列，请将光标放到D列单元格处，进行冻结窗口设置。如果你即想冻结第2行又想冻结C列，请将光标放到他们的交叉单元格D3上进行设置。如果想取消冻结功能可以点击“窗口”菜单选择取“消冻结窗口”功能。 5、“自动筛选”功能可以进行简单的数据统计 有时我们想非常快速的得到一些数据，比如本公司在职人员中，本科生学历的男生有 多少人?这时我们可以采用自动筛选功能。请选择“人员类别”单元格，点击编辑菜单栏上的 “数据”选择“筛选”，点击“自动筛选”。在每个信息项单元格右下角都会出现选择按钮。我 们分别在人员类别处选择在职(图(六))，在学历处选择本科(图(七))，在性别处选择男(图(八))，最后用鼠标将显示的性别全部选上，这时注意图(九)中用红笔圈住的地方就是我们 所需要的数据了。 5 图七 图八 6 图九 6、用“数据透视表”功能快速汇总各项数据 以上功能的设置多用于日常人事工作中，但到年底我们需要对这一年的人事情况进行 大量的信息，比如人员的离职情况，入职情况，各类数据构成比例等。这项工作更是 一项非常繁重的工作。我们可以利用Excel自带的“数据透视表”功能为我们排忧解难。 请点击编辑菜单栏中“数据”选项，选择“数据透视表和数据透视图”选项，如图(十) 图十 7 选择“数据透视表”接下来点击“下一步”即可，我们需要确定建立数据透视表的数据源 区域，一般系统会自动将整个信息库区域设置成我们要选择的区域。在出现“数据透视表和数据透视图向导-3”第3步时我们选择“现有工作表”，将工作表区域选择在新的sheet中A3单元格” 如图(十一)。点击“完成”后，便会出现(图(十二))结果。这里解释一下我们为什么要选择A3单元格，这主要是因为显示区上面有两行用来放置页字段。 图十一 图十二 8 例如我们现在想统计各部门2003年入职人员情况。我们便可以将相关项目用鼠标托 至指定位置，如图(十二)红色箭头显示。通过对信息项目的拖拽，系统会自动出现相关信 息的统计数据。如果我们还想出现统计数据的图示，这时我们可以点击数据透视表编辑菜 单栏上的图例图标，如图(十二)蓝圈圈住的地方。这时会出现chart1，如图(十三)所示。如果我们想改变图例显示类型可以点击图表向导进行自由选择。 图十三 通过这个功能我们可以很容易的统计出各种人事，比如学历构成，性别比例等。 同时您还可以根据自己的需要来设置布局。当出现“数据透视表和数据透视图向导”第3步时，我们可以点击“布局”按钮，接下来会出现“数据透视表和数据透视图向导-布局”在这里我们可以通过对话框中右侧的数据按钮添加或删除我们需要的数据项。如图(十四) 9 图十四 7、按性别统计职工数 (1)函数分解 COUNTIF函数计算区域中满足给定条件的单元格的个数。 语法：COUNTIF(range,criteria) Range为需要计算其中满足条件的单元格数目的单元格区域;Criteria为确定哪些单元格将被计算在内的条件，其形式可以为数字、表达式或文本。 (2)实例分析 假设上面使用的人事管理工作表中有599条记录，统计职工中男性和女性人数的方法 是:选中单元格D601(或其他用不上的空白单元格)，统计男性职工人数可以在其中输入公 式“="男"&COUNTIF(D2:D600,"男")&"人"”;接着选中单元格D602，在其中输入公式“="女"&COUNTIF(D2:D227,"女")&"人"”。回车后即可得到“男399人”、“女200人”。 上式中D2:D600是对“性别”列数据区域的引用，实际使用时必须根据数据个数进行修 改。“男”或“女”则是条件判断语句，用来判断区域中符合条件的数据然后进行统计。“&”则是字符连接符，可以在统计结果的前后加上“男”、“人”字样，使其更具有可读性。 在人事管理工作中，统计分布在各个年龄段中的职工人数也是一项经常性工作。假设 前例《Excel函数应用实例：按性别统计职工数》介绍的工作表的E2:E600单元格存放职工的工龄，我们要以5年为一段分别统计年龄小于20岁、20至25岁之间，一直到55至60岁之间的年龄段人数，可以采用下面的操作方法。 8、职工年龄统计 10 (1)函数分解 FREQUENCY函数以一列垂直数组返回某个区域中数据的频率分布。 语法:FREQUENCY(data_array,bins_array) Data_array为一数组或对一组数值的引用，用来计算频率。如果data_array中不包含任何数值，函数FREQUENCY返回零数组；Bins_array为间隔的数组或对间隔的引用， 该间隔用于对data_array中的数值进行分组。如果bins_array中不包含任何数值，函数FREQUENCY返回data_array中元素的个数。 (2)实例分析 首先在工作表中找到空白的I列(或其他列)，自I2单元格开始依次输入20、25、30、35、40...60，分别表示统计年龄小于20、20至25之间、25至30之间等的人数。然后在该列旁边选中相同个数的单元格，例如J2:J10准备存放各年龄段的统计结果。然后在编辑 栏输入公式“=FREQUENCY(YEAR(TODAY())-YEAR(E2:E600),I2:I10)”，按下Ctrl+Shift+Enter组合键即可在选中单元格中看到计算结果。其中位于J2单元格中的结果表示年龄小于20岁的职工人数，J3单元格中的数值表示年龄在20至25之间的职工人数等。 Excel提供的“记录单”功能可以查询记录，如果要查询人事管理工作表中的某条记录， 然后把它打印出来，可以采用下面介绍的方法。 (1) INDEX函数返回数据清单或数组中的元素值，此元素由行序号和列序号的索引值给 定。 INDEX函数有两种语法形式:数组和引用。数组形式通常返回数值或数值数组，引用 形式通常返回引用。当函数INDEX的第一个参数为数组常数时，使用数组形式。 语法1(数组形式):INDEX(array,row_num,column_num) Array为单元格区域或数组常量。如果数组只包含一行或一列，则相对应的参数 row_num或column_num为可选。如果数组有多行和多列，但只使用row_num或column_num，函数INDEX返回数组中的整行或整列，且返回值也为数组；Row_num为数组中某行的行序号，函数从该行返回数值。如果省略row_num，则必须有column_num；Column_num为数组中某列的列序号，函数从该列返回数值。如果省略column_num，则必须有row_num。 语法2(引用形式):INDEX(reference,row_num,column_num,area_num) Reference表示对一个或多个单元格区域的引用。如果为引用输入一个不连续的区域， 必须用括号括起来。如果引用中的每个区域只包含一行或一列，则相应的参数row_num 11 或column_num分别为可选项；Row_num引用中某行的行序号，函数从该行返回一个引 用；Column_num引用中某列的列序号，函数从该列返回一个引用；Area_num选择引用中的一个区域，并返回该区域中row_num和column_num的交叉区域。选中或输入的第 一个区域序号为1，第二个为2，以此类推。如果省略area_num，函数INDEX使用区域1。 MATCH函数返回在指定方式下与指定数值匹配的数组中元素的相应位置。 语法:MATCH(lookup_value,lookup_array,match_type) Lookup_value为需要在数据表中查找的数值；Lookup_value为需要在Look_array中查找的数值；Match_type为数字-1、0或1。 (2) 如果上面的人事管理工作表放在Sheet1中，为了防止因查询操作而破坏它(必要时可以添加只读保护)，我们可以打开另外一个空白工作表Sheet2，把上一个数据清单中的列 标记复制到第一行。假如你要以“身份证号码”作为查询关键字，就要在C2单元格中输入公式“=INDEX(Sheet1!C2:C600,MATCH( SC S5,Sheet1! SC S2: SC S600,0),1)”。其中的参数“ SC S5”引用公式所在工作表中的C5单元格(也可以选用其他单元格)，执行查询时要在其中输入查询关键字，也就是待查询记录中的身份证号码。参数“Sheet1!C2:C600”设定INDEX函数的查询范围，引用的是数据清单C列的所有单元格。MATCH函数中的参数“0”指定它查找“Sheet1! SC S2: SC S600”区域中等于 SC S5的第一个值，并且引用的区域 “Sheet1! SC S2: SC S600,0”可以按任意顺序排列。 上面的公式执行数据查询操作时，首先由MATCH函数在“Sheet1! SC S2: SC S600”区域搜索，找到“ SC S5”单元格中的数据在引用区域中的位置(自上而下第几个单元格)，从而得知待查询数据在引用区域中的第几行。 接下来INDEX函数根据MATCH函数给出的行号，返回“Sheet1!C2:C600”区域中对应行数单元格中的数据。假设其中待查询的“身份证号码”是“3234567896”，它位于“Sheet1! SC S2: SC S600”区域的第三行，MATCH函数就会返回“3”。接着INDEX函数返回“Sheet1!C2:C600”区域中行数是“3”的数据，也就是“3234567896”。 然后，我们将光标放到C2单元格的填充柄上，当十字光标出现以后向右拖动，从而 把C2中的公式复制到D2、E2等单元格(然后再向左拖动，以便把公式复制到B2、A2单元格)，这样就可以获得与该身份证号对应的性别、籍贯等数据。 注意:公式复制到D2、E2等单元格以后，INDEX函数引用的区域就会发生变化，由 C2:C600变成D2:D600、E2:E600等等。但是MATCH函数返回的(相对)行号仍然由查询关键字给出，此后INDEX函数就会根据MATCH函数返回的行号从引用区域中找到数据。 12 在Sheet2工作表中进行查询时只要在查询输入单元格中输入关键字，回车后即可在 工作表的C2单元格内看到查询出来的身份证号码。如果输入的身份证号码关键字不存在 或输入错误，则单元格内会显示“#N/A”字样。 13