表格与人力资源管理.doc

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 2023）或“表格”命令（Excel 2023），如图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交流学习群（群号 ），随时跟大家交流实际工作中的问题，分享最新案例模版，了解最新学习动态，学习最新Excel技能。

运用Excel函数、数据透视表和图表灵活分析薪酬数据
1、月薪酬数据的多维度分析
    对于这个问题，很多人会觉得是多余的，觉得没什么分析的必要。但是，假如领导给你要这个月，每个部门的社保合计是多少？加班费是多少？哪些部门的加班费 出现了异常？每个部门的人数是多少？最低工资如何、最高工资如何？人均工资如何？等等，你会如何在5分钟内作出相应的报告出来？
    这样的问题， 假如使用数据透视表来解决，将是非常方便和迅速的。下面举例说明。
    下图是某个月薪酬数据，现在规定制作各种维度的分析报表。 

    一方面以原始数据制作一个基本的透视表，然后进行基本布局，就得到每个部门的社保汇总表，如下

      将数据透视表进行重新布局，就得到如下的考勤汇总表格：

    重新布局透视表，并相应发合计进行不同的汇总计算，就得到如下的报表，然后绘制薪浮图，可以更加清楚的揭示每个部门的工资情况：

         运用透视表组合功能，还可以对工资区间进行分析，如下：

2、全年12个月工资汇总分析
    对于这个问题，最简朴的方法是先把12个月工资数据复制粘贴到一个工作表，然后再进行透视分析。此外，尚有一个更加科学和灵活的方法，就是使用现有连 接+SQL语句的方法制作动态汇总分析报表，但是这种方法对每个工资表的规定是极其严格的，并且要编写相应的SQL语句，此处就不再介绍了。

3、月度薪酬滚动分析模版
    前面的分析方法，都是对已有数据的静态分析。假如要对每月、每个部门、每个岗位、每个人、每个工资项目进行跟踪分析，并对预算和实际实现情况进行差异分 析，及时发现异动数据，又该如何做呢？此时，需要使用函数建立滚动跟踪分析模版了。由于这种模版的制作需要了解掌握很多函数，此处不再介绍了，感爱好的同 学可以加QQ群，跟同行和朋友进行交流，QQ交流学习群号：。
下面是一个薪酬滚动分析模版的示意图，供大家参考借鉴。

EXCEL在宽带薪酬调整中的应用
本篇总结不讲薪酬体系设计环节，仅讲解在各个环节中EXCEL运算数据的用法，各项数据都是我临时编的，仅供学习参考。本例中讲解是公司通过岗位价值评估后，将公司岗位职级划分为10级。
一、薪酬归级
以职级10级举例，各岗位名称和工资列举出来后，“现工资中点值”一列中，用函数公式计算，输入公式“=QUARTILE(C2:C11,2)”，C2:C11代表岗位1到岗位10的现工资水平。同样操作，将各个职级的中点值计算出来。

二、计算回归中点值
各个职级的中点值计算出来以后，用散点图得到曲线方程式，如下图，图表中有曲线方程式。

根据曲线方程式，我们可以计算回归中点值，在D1、E1、F1三个单元格分别输入1303.3、2.71828、0.2151，在C2单元格输入公式“=ROUND($D$1*$E$1^(A2*$F$1),0)”，下拉，得出回归中点值，如下图。

三、计算新中位值
假定各职级级差已拟定好，等级中位值按职级6作为基准，各职级新中点值计算公式如下图。

四、带宽设计
假定各职级分10个档级，带宽度已设定好，最小档级工资、最大档级工资、带宽计算方法为：分别在G1、H1、I1单元格输入公式“=ROUND(E2*2/(2+F2),)”、“=ROUND(G2*(1+F2),0)”、“ =H2-G2”，下拉，效果如下图。

五、薪酬标准表拟定
假定各档级间保持匀速递增，薪酬标准表如下图，D列与B列数据相同，在E2单元格中输入公式“=D2*($C2/$B2)^(1/(10-1))”，右拉下拉。

在上图中，还保存着小数，我们可以对它进行修正，采用四舍五入到十位的方法，在B14单元格输入公式“=ROUND(D2,-1)”，右拉下拉，最终结果如下图。

六、带薪酬线的宽带薪酬图

在本文演示中，由于各个环节几乎都四舍五入到个位，数额偏差也许会增大，在实际中，可以到最后的时候再四舍五入到十位，偏差会小一些。

Excel综合应用之人力资源优化配置
案例：
A公司拟定2023年的目的是主推五种产品，其中产品1、产品2、产品3三种产品全年销售额不能低于总销售额的15%、30%、20%，产品5全年销售额不能高于总销售额的10%，继续保持推出科技含量高的新产品引领市场。生产单位产品各工段需求人数、每个工段现有人数、最大用工量及每个产品的利润如下表，公司生产员工总共300人，假如你是人力资源经理，在保持公司利润最大化的情况下如何实现人力资源的优化配置？
    产品工段
产品1
产品2
产品3
产品4
产品5
现有人数
最大用工量
工段1
26
24
23
18
16
53
65
工段2
20
15
10
30
6
60
60
工段3
35
31
20
22
23
65
80
工段4
30
27
28
9
25
63
70
工段5
19
26
35
18
10
59
100
利润(万元)
3.3
1.9
2.85
2.3
1.7
-
-

案例解析：
如何对人力资源进行优化配置，是很多HR从业者需要面临的问题，案例中是要运用一定手段在保持利润最大化的情况下进行人力资源优化配置，EXCEL的线性规划求解正好可以解决这个问题。

一、设定决策变量
通过案例我们发现五种产品的单位产量是变量，设产品1、产品2、产品3、产品4、产品5的单位产量分别为X1、 X2 、X3 、X4、 X5。
二、设定目的函数
案例中是要在利润最大化的情况下进行人力资源优化配置，所以目的函数为利润最大化。设总利润为Z，则目的函数为：
MAX Z=3.3 X1+1.9 X2+2.85X3+2.3X4+1.7X5
三、设定约束条件函数
约束条件是为指实现系统目的的限制因素。在本案例中，每个工序都有最大人数的限制，并且产品1、产品2、产品3和产品5有销售比例的限制。
根据工段1最大人数的限制，得到约束条件函数：
26 X1+24X2+23X3+18X4+16X5<=65                 （1）
同理，根据工段2、工段3、工段4和工段5最大人数的限制，得到约束条件函数：
20X1+15X2+10X3+30X4+6X5 <=60                  （2）
35X1+31X2+20X3+22X4+23X5<=80                  （3）
30 X1+27X2+28X3+9X4+25X5 <=70                 （4）
19 X1+26X2+35X3+18X4+10X5 <=100               （5）
根据产品1、产品2、产品3和产品5有销售比例的限制，得到约束条件函数：

同时，X1、 X2 、X3 、X4、 X5应当为非负数，得到约束条件函数：

X1>=0，X2>=0，X3>=0，X4>=0， X5>=0               （10）
通过简化变形上述约束条件函数，得到约束条件函数：

四、建立模型并求解
（一）模型建立
根据上述目的函数和约束条件函数，在EXCEL上建立模型。如图1。

图1 最大利润模型
（二）参数设立
运用EXCEL上的规划求解工具，对各参数进行设立。如图2。

图2 参数设立
（三）求解
运营求解后得到求解结果如图3所示。

图3 规划求解结果
五、规划求解结果分析
根据图3的结果，我们可以看出公司要实现利润最大化，五种产品的产量分别如图中最优结果所示。由于本模型计算的是单位产量下利润最大化，所以最优结果出现小数是允许存在的。

案例中是要保持公司利润最大化的情况下如何实现人力资源的优化配置，图3中可以看出各工段最小需求人数，我们可以建立一个表格更直观的看到结果，如图4。

图4 人员需求情况表
根据图4可以看到，在保持利润最大化的情况下公司生产人员是欠缺的，需要再增长32人。分析各工段人员需求情况我们还会发现工段2的人员是富余的，需要调整11人到其它工段。

六、结论
上述情况是根据最优结果得出的人员需求情况，总体需要招聘，1个工段人员需要调整。我们建立的模型是计算得出最优的结果，但是市场经济下，公司的行为受市场经济调控，公司不也许完全按最优结果生产和销售其产品，并且技术工艺的改善、人员出勤率等影响，生产人员的优化配置会和模型计算的结果有偏差，但通过线性规划求解，为案例中的人力资源经理提供了人员配置的最优结果，为他的人员配置提供了依据。假如有更多的约束条件，我们可以得出更趋向于实际的结果。