表格与人力资源管理
Excel综合应用之标准化表格设计
设计标准化规范化的Excel基础表格，是高效数据分析的第一步，因为数据分析的源头就是基础表格数据。
设计基础表格的总体原则是：
结构的科学性
数据的易读性
汇总的便宜性
分析的灵活性
外观的美观性
结构的科学性，就是要按照工作的性质，管理的内容，数据的种类，分别设计基础管理表格，分别保存不同数据。基础表格要越简单越好，那些把所有数据都装在一个工作表中的做法是绝对不可取的。比如，要做入库出库管理，你会如何设计这样的基础表格呢？要用几个表格来反映入库出库数据？每个表格要怎么保存数据？
数据的易读性包含两个方面：利用函数读数（取数）方便，叫函数读数；眼睛查看数据容易，叫人工读数。一个杂而乱的表格，是很难实现这两种高效读数的。数据易读性差的主要原因有：表格结构设计不合理；数据保存不合理；残缺不全的表格数据结构。
汇总的便宜性是指不论多大的数量，汇总要简单方便容易。你可以问自己：我设计的工作表内据汇总方便吗？大量表格数据之间的汇总方便吗？如果不方便，或者做起来非常吃力，Excel很好用的工具也用不上，那唯一的原因就是基础表格设计有问题，不要发牢骚说Excel 太难了，总是学不会，尤其是函数太难掌握了！
分析的灵活性，是指不论做何种分析，要讲究数据分析的灵活多变。因为我们对数据进行分析的目的，是要针对企业的数据进行深度挖掘，从不同方面找问题、找原因、找对策，这就要求基础数据必须能够精准反映企业的管理流程，制作的分析报告也必须具有灵活性，能够在几分钟内通过转换分析角度而得到另外一份分析报告。
外观的美观性。不论是基础表还是报告，都尽量要求把表格进行美化。基础表的美化以容易管理数据标准，而报告的美化以分析结果清楚为标准。特别强调的是，不论是基础表还是报告，很多人喜欢把数据区域加上边框，并保持工作表默认的网格线。其实，我们可以取消网格线，而把数据区域设置为非常简练的线条表格，并把单元格字体、颜色、边框等进行合理的设置。
下面我们以人力资源管理中一个简单的员工信息表为例，来说明标准化表格的设计方法和技巧。这个表格的基本要求如下：
·员工工号从“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行输入第一个人的信息，然后单击数据区域的任意单元格，在单击“插入”选项卡里的“表”命令（Excel 2007）或“表格”命令（Excel 2010），如图2-20所示，即可把普通的数据区域变成了一个表格，然后再在“设计”选项卡中的“表格样式”选择一个自己喜欢的样式，就可以把表格自动美化。
这样，我们就得到了一个标准化规范化的员工信息管理表格，在此表格基础上，就可以建立各种自动化分析模版了，比如员工属性分析模版，员工流动性分析模版，等等。
下图是员工信息基础表的样子：
下面的三个表分别是基于此基本信息表格数据制作的员工属性
分析报告、员工流动性分析报告、以及离职分析报告。这4张工作表，就构成了员工信息统计分析模版。
Excel综合应用之打卡考勤数据汇总（一）
马上又要月底了，又要面临着把从刷卡机里导出的12月的考勤数据进行统计汇总。我们总不能还是花几天时间来手工处理这些考勤数据吧？前天晚上给HR们上课，正好介绍到考勤数据统计分析的一个例子，现在拿出来跟大家分享。
实际数据如下图所示，从打卡机导出的数据是一个流水，要求：
1、找出每个人最早打卡时间和最晚打卡时间；
2、判断每个人每天的迟到、早退情况、加班情况。
说明：出勤时间标准是8:00-17:30，18点以后为加班时间。
这个表格数据的特点是：每个人的刷卡数据有好几个，分成了几行保存，而且刷卡日期和时间是保存在一个单元格，因此我们首先要
把刷卡日期和时间分开，这个工作可使用“分列”工具栏完成，处理结果如下：
剩下的问题，就是要解决本文前面提出的问题了。这些问题的解决，有很多方法，其中一个最容易掌握、也最简单的方法，是利用简单的几个函数IF、COUNTIFS、INDEX函数进行处理。下面进行具体说明。
在右侧插入一个辅助列，标题为“第几次刷卡”，在单元格F2输入公式=COUNTIFS($B$2:B2,B2,$D$2:D2,D2)，往下复制，就可以统计E列的某个刷卡时间是某人某天的第几次刷卡了。
再插入两个辅助列，标题分别为“最早刷卡”和“最晚刷卡”。
在单元格G2输入公式=IF(F2=1,E2,"") ，往下复制，即可得到某个人在某天的最早刷卡时间。
在单元格H2输入公
式=IF(G2<>"",INDEX(E2:E875,COUNTIFS(B:B,B2,D:D,D2)),"")，往下复制，即可得到某个人在某天的最晚刷卡时间。
这三个公式的思路、逻辑和原理，这里就不再细说了。
这样，就得到了如下的处理结果。
将F列至H列的公式，采用选择性粘贴的方法转换为数值，然后筛选出所有空值单元格，予以删除，并删除E列和F列，就得到下面的每个人的刷卡考勤数据，以此数据来做考勤计算：
在此表格的右侧进行迟到、早退、加班的计算，数字1表示迟到或早退，具体的时间表示加班时间，如下表：
其中，各单元格的公式分别如下：
单元格G2：=IF(E2>8/24,1,"")
单元格H2：=IF(F2<17.5/24,1,"")
单元格I2：=IF(F2>18/24,F2-18/24,"")
的迟到、早退、加班汇总数据，如下：
大家看到了吧，考勤数据的处理并不复杂，也不难，只要掌握了Excel的几个实用技能（分列工具、常用函数、透视表），就可以在
几分钟把看起来繁琐无比的考勤数据进行高效快速处理和统计汇总。
Excel综合应用之打卡考勤数据汇总（二）
在12月20日的总结中，给大家介绍了每个人考勤数据分行保存的例子。这几天很多卡卡们问我，我公司的是在一个单元格内保存的日期和打卡时间，这样的表格该如何处理呢？
请看下图，就是一个典型的例子：
这个表格的难点，是每个人的刷卡日期和时间保存在一行的一个单元格里，但是存在某人早上刷卡（也许刷了好几次）而下班没刷卡的，或者上班没刷卡但下班有刷卡（也许刷了好几次）的。
这个问题的解决，第一步就是使用分列工具把日期和时间分开，如下结果：
有了这个分列后的日期和时间，就可以进一步处理了。公司的出勤是早晚8:30-17:30，这样就可以通过辅助列来解决早签到时间和晚签退时间的提取问题，如下表：
在单元格K2输入公式：
=IF(AND(MIN(F2:J2)<14/24,MAX(F2:J2)<>""),MIN(F2:J2),"")，计算早签到时间
在单元格L2输入公式：
=IF(AND(MAX(F2:J2)>14/24,MIN(F2:J2)<>0),MAX(F2:J2),"")，计算晚签退时间
这样，我们就可以判断某个人是否迟到和早退了，如下图：
迟到有两种情况：上班没有打卡；打卡时间迟于8:30，因此单元格M2 公式为：=IF(OR(K2="",K2>8.5/24),1,"")
早退有两种情况：下班没有打卡；打卡时间早于17:30，因此单元格N2 公式为：=IF(OR(L2="",L2<17.5/24),1,"")
最后，利用透视表进行汇总，就可以计算出每个人的迟到次数和早退次数。假如公司规定迟到一次扣50元，早退一次扣80元，那么汇总结果如下所示：
考勤数据的处理并不复杂，也不难，只要掌握了Excel的几个实用技能（分列工具、常用函数、透视表），就可以在几分钟把看起来繁琐无比的考勤数据进行高效快速处理和统计汇总。有关Excel数据处理和分析的问题，欢迎大家加入千人Excel交流学习群（群
号228678049），随时跟大家交流实际工作中的问题，分享最新案例模版，了解最新学习动态，学习最新Excel技能。
运用Excel函数、数据透视表和图表灵活分析薪酬数据
1、月薪酬数据的多维度分析
对于这个问题，很多人会觉得是多余的，觉得没什么分析的必要。但是，假如领导给你要这个月，每个部门的社保合计是多少？加班费是