下载掌阅APP，畅读海量书库
立即打开
在员工关系管理实操中，数据的规范化管理对人力资源管理中的事务性工作有着极大的影响，规范、整齐、统一的数据有利于大家对数据进行各维度的计算与分析，从而提高解决实际问题的效率。
Excel中的分列功能可以实现文本字符串的拆分，同时还可以实现数值型数字与文本型数字之间的转换以及不规范日期的整理。该选项位于【数据】选项卡中。
如图3-1所示，是一份员工入职信息表（局部）。其中，“入职时间”列中某些单元格的数据不能参与计算（如图3-1中用红色虚线框圈住的部分），因为它们的日期格式不规范。那么，我们怎样才能快速地修正该表中的不规范日期呢？
图3-1
具体操作步骤如下。
Step-01 选中F列，之后选择【数据】选项卡，单击【分列】按钮，如图3-2所示。
图3-2
Step-02 打开【文本分列向导】对话框，第1步与第2步均选择单击【下一步】按钮，如图3-3所示。在第3步时，选择【日期】选项，最后单击【完成】按钮，如图3-4所示，结果如图3-5所示。
图3-3
图3-4
图3-5
拓展
在Excel中，日期格式是系统默认能识别的格式，规范的日期格式一般有yyyy-mm-dd、yyyyy/mm/dd及yyyy年m月d日等。在上述案例中，图3-1中用虚线框住的日期格式不能被Excel识别。如果要让Excel能够识别yyyy.mm.dd格式，则需要将计算机系统的日期显示格式更改为yyyy.mm.dd格式。
需要注意的是，如果从其他文件格式如TXT格式的文件导入数据，则系统会自动打开【文本分列向导】对话框。此时一定要将诸如身份证号等列的格式设置为文本类型；否则，导入的身份证号将会变成数字，无法使用。
定位功能是Excel中最常用的一个功能，主要用于快速定位某种特定数据类型或者单元格形态。该功能可通过【开始】选项卡的【查找和选择】组开启。除此之外，还可以使用<F5>键或者<Ctrl+G>组合键打开【定位】对话框。
如图3-6所示，表中的B列只填写了第一次出现的各部门名称。如何将表中B列的数据向下填充完整呢？
图3-6
具体操作步骤如下。
Step01 选中数据区域B2:B14，按<F5>键，打开【定位】对话框。单击【定位条件】按钮，打开【定位条件】对话框，选择【空值】选项，最后单击【确定】按钮，如图3-7所示。
图3-7
Step-02 直接在公式编辑栏中输入公式：=B2（见图3-8），按<Ctrl+Enter>组合键完成批量填充。如有必要，可将公式转换成数值。
图3-8
结果如图3-9所示。
图3-9
拓展
<Ctrl+Enter>组合键具有批量填充的功能，可以用于多个不连续的单元格区域的公式与常量的批量填充。
重复值会影响到计算结果的准确性。所以，在进行数据运算前应该先删除这些重复值。在Excel中，删除重复值的功能可通过【数据】选项卡的【删除重复值】按钮开启。
如图3-10所示，将表中重复的记录删除，保留唯一的记录。
图3-10
具体操作步骤如下。
选中数据区域中的任意一个单元格，之后选择【数据】选项卡，单击【删除重复值】按钮，在打开的【删除重复值】对话框中单击【确定】按钮，如图3-11所示。
图3-11
完成以上操作后，界面中会弹出提示窗口，提示所发现的重复值的数量与保留的唯一值的数量，如图3-12所示。
图3-12
拓展
删除重复值是Excel中一个比较强大的功能，有不少使用技巧。大家需要注意以下两点：
● 如果先选中数据区域中的某一列，之后执行【删除重复值】操作，则会弹出提示，如图3-13所示。
图3-13
如果选择【扩展选定区域】选项，则会将选定区域扩展为整个数据区域，检查每一行是否重复，若重复则删除整行；如果选择【以当前选定区域排序】选项，则会只删除当前选定列的重复值，此时一定要谨慎，明确要删除的是重复的整行数据，还是仅删除当前选定列的单元格中的值。
● 在选择整个区域后，可以按各个字段的值的重复情况有针对性地删除每列的重复数据，保留唯一值，如图3-14所示。
图3-14
若只选择“员工编号”列，则会将“员工编号”中具有重复值的行全部删除，而不是只删除“员工编号”列的重复值。当然，也可以同时选中多列。Excel会判断选中的多列是否有重复值，如果有重复值，则删除具有重复值的行。
数据是否规范决定着数据整理与分析效率的高低，同时影响着数据统计与核算的准确性。作为人力资源管理中最基础也是最主要的一个数据载体，员工花名册对数据的规范化程度要求非常高。本节主要介绍如何限定员工花名册中各个字段的数据输入规则。
数据验证：可通过【数据】选项卡调用【数据验证】功能。在Excel 2010中，该功能被称为“数据有效性”。
设置员工花名册中的“区域”为一级下拉菜单，下拉可选的内容为北京、广州、深圳、上海、重庆、天津。将身份证号的格式设置为文本并限制其输入长度为18位。之后，设置“入职日期”列的单元格提示，提示内容为“日期格式为YYYY-MM-DD”。
具体操作步骤如下。
Step-01 选中数据区域B2:B9，之后选择【数据】选项卡，单击【数据验证】按钮，打开【数据验证】对话框。切换到【设置】选项卡，在【允许】下拉列表框中选择【序列】选项，在【来源】编辑框中输入内容“北京，广州，深圳，上海，重庆，天津”，最后单击【确定】按钮，如图3-15所示。
图3-15
结果如图3-16所示。
图3-16
Step-02 选中数据区域E2:E9，重复Step-01中的步骤，在【允许】下拉列表框中选择【文本长度】选项，在【数据】下拉列表框中选择【等于】选项，在【长度】编辑框中输入“18”，最后单击【确定】按钮，如图3-17所示。
图3-17
如果用户所输入的身份证号的位数不等于18位，则会报错，如图3-18所示。
图3-18
Step-03 选中数据区域F2:F9，重复Step-01的步骤，打开【数据验证】对话框，切换到【输入信息】选项卡，勾选【选定单元格时显示输入信息】复选框，在【标题】文本框中输入“注意”，在【输入信息】文本框中输入“格式为YYYY-MM-DD”，最后单击【确定】按钮，如图3-19所示。
图3-19
当用鼠标选择单元格时，出现的提示信息如图3-20所示。
图3-20
拓展
除了采用上面案例中的方法定义限制条件外，还能使用公式来自定义限制条件。
数据验证还可以设置其他条件，比如限制录入重复的身份证号、员工编号需要以特别字符开头、指定年龄的范围、指定入职日期的范围等。
在3.1.4节中讲解了使用数据验证功能制作一级下拉菜单的方法，那么，二级联动下拉菜单又该如何设置呢？比如，实现如下功能：在“大区”列中选择了“华南”，在“分公司”列的下拉菜单中显示“华南”所对应的分公司的名称。本节将讲述这一功能的实现过程。
如图3-21所示，在“员工花名册”工作表的D列下拉菜单中选择不同的大区名称，在E列的下拉菜单中会出现当前所选大区所对应的分公司名称。
图3-21
具体操作步骤如下。
Step-01 准备制作二级联动下拉菜单的对照表，如图3-22所示。
图3-22
Step-02 切换到“下拉菜单对照表”工作表，选择【公式】选项卡，单击【定义名称】按钮，打开【新建名称】对话框。在【名称】文本框中输入“大区”，在【引用位置】编辑框中输入以下公式，最后单击【确定】按钮，如图3-23所示：
=OFFSET($A$1,,,,COUNTA($1:$1))
图3-23
公式解释：
COUNTA（$1:$1）会计算对照表中第一行不为空的单元格的个数。
OFFSET函数将“下拉菜单对照表”中A1单元格向右偏移的宽度设置为COUNTA（$1:$1）个单元格，该函数省略了第2～4个参数。这构成了一个区域，即第一行中连续的数据区域，如图3-22中的A1:G1数据区域。如果有新的数据添加进来，就会自动扩展至新添加的单元格位置。
Step-03 切换到“员工花名册”工作表，选择数据区域D2:D9，之后选择【数据】选项卡，单击【数据验证】按钮，打开【数据验证】对话框。在【允许】下拉列表框中选择【序列】选项，在【来源】编辑框中输入“=大区”，最后单击【确定】按钮，如图3-24所示。
图3-24
Step-04 选择E2单元格，参照前面的步骤，打开【新建名称】对话框。在【名称】文本框中输入“分公司”，在【引用位置】编辑框中输入以下公式，最后单击【确定】按钮，如图3-25所示：
=OFFSET(下拉菜单对照表!$A$2,MATCH(员工花名册!$D2,下拉菜单对照表!$1:$1,0)-1,COUNTA(OFFSET(下拉菜单对照表!$A$2,,MATCH(员工花名册!$D2,下拉菜单对照表!$1:$1,0)-1,65536)))
图3-25
公式解释：
整个公式的意思如下：以“下拉菜单对照表”里的$A$2单元格为基点，向下偏移0行，向右偏移“员工花名册”工作表中D列当前所选择的值在“下拉菜单对照表”中第一行中所处的位置减去1，向下偏移的高度为“员工花名册”中D列当前所选择的值在“下拉菜单对照表”中所对应的列不为空的单元格的个数。
MATCH(员工花名册!$D2，下拉菜单对照表!$1:$1,0)-1部分将返回D2单元格中的值在“下拉菜单对照表”中的第一行的位置，也就是处于第一行的第几列。返回的值是一个数字（即作为外层的OFFSET函数的向右偏移的参数）。
OFFSET(下拉菜单对照表!$A$2，MATCH(员工花名册!$D2，下拉菜单对照表!$1:$1,0)-1，65536)部分表示以“下拉菜单对照表”中的$A$2单元格为起点，以当前选择的值（即D2）所在的列的第一行到第65536行，作为向下偏移的高度。这里的65536也可以是其他的一个较大的数，只要大于实际的最大行数即可。
COUNTA则是统计OFFSET(下拉菜单对照表!$A$2，MATCH(员工花名册!$D2，下拉菜单对照表!$1:$1,0)-1，65536)部分的区域内不为空的单元格个数，为外层的OFFSET函数提供偏移的高度（即多少行不为空的数量）。
Step-05 选择数据区域E2:E9，之后选择【数据】选项卡，单击【数据验证】按钮，打开【数据验证】对话框。在【允许】下拉列表框中选择【序列】选项，取消【忽略空值】复选框的勾选状态，在【来源】编辑框中输入“=分公司”，单击【确定】按钮，如图3-26所示。
图3-26
设置完成以后，在D列下拉菜单中选择“大区”后，在E列下拉菜单中再次选择时，仅会出现当前所选“大区”对应的分公司。如果“下拉菜单对照表”里的数据源发生变化时，则“员工花名册”中的下拉选项也会自动更新。
在工作中，一些机构常常会下发一些带公式的数据模板，让相关部门与下属公司的人员进行填写，但是这些公式经常会被大家无意中删除或者修改。那么，如何才能保证这些公式不被删除或者修改呢？
如图3-27所示，下面是一份劳动合同的月报表，表中的“下次签订日期”与“到期提醒”是由公式计算得到的。将图3-27中的公式区域进行密码保护，不允许删除或修改该公式。
图3-27
具体操作步骤如下。
Step-01 按<Ctrl+1>组合键，打开【设置单元格格式】对话框，切换到【保护】选项卡，取消【锁定】复选框的勾选状态，最后单击【确定】按钮，如图3-28所示。
图3-28
Step-02 选择数据区域K4:L8，按<Ctrl+G>组合键，打开【定位】对话框，单击【定位条件】按钮，打开【定位条件】对话框，选择【公式】选项，最后单击【确定】按钮，如图3-29所示。
图3-29
Step-03 再次按<Ctrl+1>组合键，打开【设置单元格格式】对话框，切换到【保护】选项卡，勾选【锁定】复选框，最后单击【确定】按钮，如图3-30所示。
图3-30
Step-04 选择【审阅】选项卡，单击【保护工作表】按钮，打开【保护工作表】对话框。在【取消工作表保护时使用的密码】文本框中输入密码，然后在【允许此工作表的所有用户进行】下拉列表框中选择相应的功能，之后单击【确定】按钮，打开【确认密码】对话框。在【重新输入密码】文本框中再次输入相同的密码，最后单击【确定】按钮，如图3-31所示。
图3-31
完成上述设置以后，双击带有公式的单元格时，则弹出提示，如图3-32所示。
图3-32
在数据的收集与整理过程中，我们经常会在工作表中录入一些数据。当然，最常见的方法是直接在工作表中录入这些数据。这里也可以使用Excel提供的“记录单”功能实现数据的录入。本节主要讲解如何使用“记录单”功能快速地录入数据。
Excel的功能区中默认不显示记录单的功能。我们可选择表中的任意一个单元格，之后依次按下<Alt>键、<D>键和<O>键，打开“记录单”。
如图3-33所示，使用“记录单”功能将数据录入到表中。
图3-33
具体操作步骤如下。
单击数据区域中的任意一个单元格，之后依次按<Alt>键、<D>键和<O>键，打开记录单对话框，这时显示的是当前数据表中的内容。单击【新建】按钮，之后打开一个新的对话框（见图3-34，该对话框的名称与当前操作的工作表名称一致），在此输入信息记录后单击【新建】按钮，Excel会自动将信息保存至工作表中数据区域的最后一行。
图3-34
拓展
在使用“记录单”功能时需要注意以下几点：
● 在调出记录单对话框时选择的单元格最好是有数据的单元格，不然可能会导致在所打开的对话框中取不到标题字段。
● 带有公式的字段无须输入，将相关字段中的数据录入完成后，记录单中会自动计算结果，并将它们添加到新记录中。
● “条件”功能可用来设置搜索时的条件。在设置相关的条件后，当用户单击【上一条】按钮或【下一条】按钮的时候，就会显示出符合条件的记录。
● “记录单”功能不支持具有合并单元格的标题行，同时多重表头工作表调出的记录单无法显示字段标题。