设计标准化规范化基础表格案例分享

设计标准化规范化基础表格案例分享2014-09-1020:39:07

阅读（29199）

评论（222）

收藏（827）分享到：\o"分享到新浪微博"\o"分享到腾讯微博"\o"分享到QQ空间"

在9月4日的文章中，我谈到了设计科学规范基础表格的重要性和基本原则。下面我们以人力资源管理中一个简单的员工信息表为例，来说明标准化表格的设计方法和技巧。这个表格的基本要求如下：

·员工工号从“G0001”开始编号，中间不能断号，新入职员工依次分配连续的工号

·员工姓名中不允许输入空格

·所属部门必须快速准确输入企业存在的部门，要名称统一

·学历必须快速规范输入

·婚姻状况要快速规范输入

·身份证号码必须是18位的文本，不允许重复

·出生日期、年龄、性别从身份证号码中自动提取

·入职时间必须是合法的日期

·本公司工龄自动计算得出

·为便于分析流动性，工作表要有离职时间和离职原因两列数据，离职原因是固定的几种类型

·新员工输入后，该员工的工号、生日、年龄、工龄等等计算公式自动往下复制

·表格自动美化

1、表格结构设计

根据人力资源中对员工信息管理和分析的需要，创建一个工作表，命名为“员工信息”，数据列标题如下图。

2、工号的自动连续填充

在单元格A2输入公式“="G"&TEXT(ROW(A1),"0000")”，就得到能够往下连续填充工号的计算公式。这里，ROW函数是获取指定单元格的行号（ROW(A1)的结果就是1，ROW(A2)的结果就是2，以此类推），TEXT函数是把一个数字按照指定的格式转换为文本。那么这个公式的结果就是G0001。如果把该公式复制到单元格A3，该公式就变成了“="G"&TEXT(ROW(A2),"0000")”，结果就是G0002。

3、规范姓名的输入，不允许在姓名文字中输入空格

选中单元格B2，设置有效性，其有效性的自定义公式为“=SUBSTITUTE(B2,"

","")=B2”，如下图。这里，先使用SUBSTITUTE函数把输入的姓名中的所有空格替换掉，然后再跟输入的姓名进行比较，如果两者相等，表明输入的姓名中没有空格，否则就是有空格，就不允许输入到单元格。

4、规范快速输入部门名称

公司部门是确定的，在一定时期是不会变化的，因此可以使用数据有效性来快速规范输入部门名称。假如企业的部门有：总经办、财务部、人力资源部、贸易部、后勤部、技术部、生产部、销售部、信息部、质检部、市场部，那么选择单元格C2，设置数据有效性，即选择“序列”，来源为“总经办,财务部,人力资源部,贸易部,后勤部,技术部,生产部,销售部,信息部,质检部,市场部”，如下图。这样，就为单元格设置了一个下拉列表，从下拉列表里快速选择输入某个部门名称。

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单元格，设置日期数据的有效性。

15、规范输入离职原因

离职原因用来分析员工的流动性和离职状态，因此必须规范离职原因的表述文字。假如企业对离职原因的描述是下述的文字：

合同到期但个人不愿续签

合同到期但公司不愿续签

因个人原因辞职

因公司原因辞职

违反公司规定辞退

生产任务变化辞退

考核不合要求辞退

退休

死亡

其他

那么就可以使用有效性来快速准确输入这些描述文字。

16、创建表格，自动复制有效性和公式，并自动美化表格

前面我们做的数据有效性、设置公式等，都是仅仅设置了第2行的单元格，并没有选择整列来做，因为这样的话会使文件变得很大，复制很多公式也是很不方便。Excel提供了表格功能，不仅可以自动复制有效性和公式，还可以自动美化表格。

先在第1行输入第一个人的信息，然后单击数据区域的任意单元格，在单击“插入”选项卡里的“表”命令（Excel2007）或“表格”命令（Excel2010），如图2-20所示，即可把普通的数据区域变成了一个表格，然后再在“设计”选项卡中的“表格样式”选择一个自己喜欢的样式，就可以把表格自动美化。

这样，我们就得到了一个标准化规范化的员工信息管理表格，在此表格基础上，就可以建立各种自动化分析模版了，比如员工属性分析模版，员工流动性分析模版，等等。

下图是员工信息基础表的样子：

下面的三个表分别是基于此基本信息表格数据制作的员工属性分析报告、员工流动性分析报告、以及离职分析报告。这4张工作表，就构成了员工信息统计分析模版。【作者简介】

韩小良，国内著名资深实战型Excel培训讲师和应用解决方案专家。Excel应用方面的著作第一人，出版了近40部关于Excel企业管理应用方面的实用书籍。从事Excel研究及应用多年，对Excel及VBA在企业管理中的应用有着较深的研究和独特的认识，对Excel及VBA在企业管理中的高级应用培训有着丰富的实战经验，已经为数千家大中型企业进行了Excel培训和应用方案解决咨询服务，在业界享有很高的评价，深得客户认可。主要著作：《构建高效数据分析模版——Excel函数和动态图表高级应用》，《Excel高效财务管理和分析模版应用大全》，《Excel会计与财务高效应用从入门到精通》等等。【专注领域】

Excel高效办公技能提升系列

Excel高效数据分析与信息挖掘系列

Excel高效财务管理应用系列

Excel高效人力资源管理系列

Excel企业运营数据分析系列

Excel金融建模与财务建模系列

Excel宏和VBA自动化数据分析系列9月10日案例的几个分析报告赏析2014-09-1222:26:44

阅读（8536）

评论（93）

收藏（306）分享到：\o"分享到新浪微博"\o"分享到腾讯微博"HYPERLINK"/send/?webid=qzone&uid=1541047&url=http%3A%2F%2F%2Frz%2F13274274.html&title=%E3%80%909%E6%9C%8810%E6%97%A5%E6%A1%88%E4%BE%8B%E7%9A%84%E5%87%A0%E4%B8%AA%E5%88%86%E6%9E%90%E6%8A%A5%E5%91%8A%E8%B5%8F%E6%9E%90%E3%80%91-+%E4%B8%89%E8%8C%85%E6%80%BB%E7%BB%93&summary=%E4%B8%89%E8%8C%85%E6%80%BB%E7%BB%93%EF%BC%9A%E5%9C%A89%E6%9C%8810%E6%97%A5%EF%BC%8C%E6%88%91%E7%BB%99%E5%A4%A7%E5%AE%B6%E4%BB%8B%E7%BB%8D%E4%BA%86%E5%A6%82%E4%BD%95%E8%AE%BE%E8%AE%A1%E4%B8%80%E4%B8%AA%E7%A7%91%E5%AD%A6%E8%A7%84%E8%8C%83%E7%9A%84%E5%9F%BA%E7%A1%80%E8%A1%A8%E6%A0%BC%EF%BC%8C%E7%84%B6%E5%90%8E%E5%B0%B1%E5%8F%AF%E4%BB%A5%E6%A0%B9%E6%8D%AE%E8%BF%99%E4%B8%AA%E