5、快速输入学历名称
员工的学历也是固定的几种。假若是以下几个:博士、硕士、本科、大专、中专、高中,那么也可以使用数据有效性来快速规范输入学历名称。选择单元格D2,设置数据有效性,即选择“序列”,来源为“博士,硕士,本科,大专,中专,高中”。
6、快速输入婚姻状况
单元格E2输入员工的婚姻状况。婚姻状况也就两种数据:已婚和未婚,因此也可以使用数据有效性来控制输入,也就是在数据有效性对话框的“来源”中输入“已婚,未婚”。
7、输入不重复的18位身份证号码
每个员工的身份证号码是不重复的,并且必须是18位,因此单元格F2输入身份证号码时也要使用数据有效性来控制。首先将F列的单元格格式设置成文本,然后选择单元格F2,其有效性的自定义公式
=AND(LEN(F2)=18,COUNTIF($F$2:F2,F2)=1)
这里,使用LEN函数判断输入的身份证号码是不是18位,即LEN(F2)=18;使用COUNTIF统计在前面已经输入的身份证号码中,即将输入的身份证号码是不是还没有输过,即COUNTIF($F$2:F2,F2)=1;然后用AND函数把这两个条件组合起来。如果两个条件都成立,表明输入的身份证号码有效。
8、自动输入性别
员工性别从身份证号码中自动提取,不需要人工输入。选择单元格G2,输入公式
=IF(ISEVEN(MID(F2,17,1)),"女","男")
这里,先用MID函数提取身份证号码的第17位数字,再用ISEVEN函数判断是否为偶数,如果是偶数,该员工性别就是女,否则就是男,判断处理则使用了IF函数。
9、自动输入出生日期
员工的出生日期也是从身份证号码中自动提取,不需要人工输入。选择单元格H2,输入公式
=1*TEXT(MID(F2,7,8),"0000-00-00")
这里,先用MID函数提取身份证号码的中间8位生日数字,再用TEXT函数把这8位数字按照日期的格式转换成文本型日期格式,最后把TEXT函数的结果乘以数字1,将文本型日期转换为真正的日期。
10、自动计算年龄
有了出生日期,我们就可以使用DATEDIF函数自动计算年龄。选择单元格I2,输入下面的公式,就自动得到员工的实际年龄:
=DATEDIF(H2,TODAY(),"Y")
11、规范输入入职时间
入职时间是一个非常重要的数据,因为要根据这列日期计算工龄,分析流动性。由于这列日期要手工输入,就必须规范输入的入职时间数据合法有效,也就是要输入正确格式的日期。选择J2单元格,设置数据有
12、自动计算本公司工龄
有了入职时间,我们就可以使用DATEDIF函数自动计算本公司工龄。选择单元格K2,输入下面的公式,就自动得到员工的本公司工龄:=DATEDIF(J2,TODAY(),"Y")
13、保证员工基本信息的完整性
由于B列至K列是员工的最基本信息,是不能缺少这些数据的,因此需要保证每个员工基本信息完整不缺。选择B2单元格,把数据有效性的条件修改为
=AND(SUBSTITUTE(B2," ","")=B2,COUNTA($B1:$K1)=10)
也就是增加了一个条件COUNTA($B1:$K1)=10,它用来判断上一行的B列至K列的数据是否都完整了(共有10列数据)
14、规范输入离职时间
离职时间是一个非常重要的数据,因为要根据这列日期来分析离职。由于这列日期要手工输入,就必须规范输入的离职时间数据,也就是要输入正确格式的日期。选择L2单元格,设置日期数据的有效性。
16、创建表格,自动复制有效性和公式,并自动美化表格
前面我们做的数据有效性、设置公式等,都是仅仅设置了第2行的单元格,并没有选择整列来做,因为这样的话会使文件变得很大,复制很多公式也是很不方便。Excel提供了表格功能,不仅可以自动复制有效性和公式,还可以自动美化表格。
先在第1行输入第一个人的信息,然后单击数据区域的任意单元格,在单击“插入”选项卡里的“表”命令(Excel 2007)或“表格”命令(Excel 2010),如图2-20所示,即可把普通的数据区域变成了一个表格,然后再在“设计”选项卡中的“表格样式”选择一个自己喜欢的样式,就可以把表格自动美化。
这样,我们就得到了一个标准化规范化的员工信息管理表格,在此表格基础上,就可以建立各种自动化分析模版了,比如员工属性分析模版,员工流动性分析模版,等等。