告别无效学习：Excel人力资源精细化管理和量化分析

引子阅读本书之前，先测试一下自己的Excel 水平 1

I 现在动手，自我测试.........................................................................1

测试题目01 你会设计Excel 表单吗？ ....................................................1

测试题目02 怎样快速整理基础数据？ ....................................................2

测试题目03 合同管理如何做到提前自动提醒？ ......................................2

测试题目04 你有最基本的逻辑判断处理能力吗？ ..................................3

测试题目05 如何快速引用指定条件的数据？ .........................................3

测试题目06 如何引用满足多个条件的数据？ .........................................4

测试题目07 如何对海量数据进行快速统计分析？ ..................................4

测试题目08 如何将枯燥的数据变为直观易懂的结果？ ...........................5

测试题目09 需要每个月都做相同的数据处理分析吗？ ..........................5

测试题目10 如何为老板解释数据的差异及其背后的原因？ ...........................5

II 评估自己的Excel 水平......................................................................6

III 如何快速掌握和提升自己的Excel 水平？.........................................6

CHAPTER 01 如何正确使用Excel——先从了解Excel 规则入手 8

1.1 如何使用Excel ...............................................................................8

1.1.1 先了解Excel 的重要规则................................................................9

1.1.2 掌握日常数据处理的基本工具和技能..............................................9

1.1.3 掌握最核心的几个常用核心函数 .....................................................9

1.1.4 学会做数据量化分析.....................................................................10

1.1.5 HR 乱用Excel 的十宗罪...............................................................10

1.2　正确区分基础表格和分析报告.......................................................12

1.2.1 基础表单.......................................................................................13

1.2.2 分析报告.......................................................................................13

1.2.3 两个表格的关系............................................................................14

 

1.3　正确输入文本、日期和数字..........................................................14

1.3.1 关于文本.......................................................................................15

1.3.2 关于日期和时间............................................................................16

1.3.3 关于数字.......................................................................................16

1.4　逻辑思维是Excel 的核心..............................................................17

1.4.1 表单设计，是对数据管理的逻辑思考............................................17

1.4.2 数据分析，是对数据背后信息的逻辑思考....................................17

1.4.3 函数公式，是对表格计算的逻辑思考............................................18

1.5　最终是数据的科学管理和量化分析...............................................19

1.5.1 对数据要做好科学管理.................................................................19

1.5.2 对数据要做好量化分析.................................................................20

CHAPTER 02 HR 如何做到高效数据管理——先整理好目前的乱表 21

2.1　现有表格结构整理：让数据流动顺畅............................................21

2.1.1 实际案例剖析................................................................................21

2.1.2 不要再犯这样的错误了.................................................................22

2.2　数据整理之一：修改非法日期和时间............................................23

2.2.1 让人忍无可忍的日期输入陋习......................................................23

2.2.2 几秒完成非法日期修改 .................................................................23

2.2.3 刷卡考勤机导出的日期和时间都要整理........................................25

2.3　数据整理之二：将无法计算的文本型数字转换为纯数字 ...............25

2.3.1 为什么我表格里的数字无法求和？...............................................25

2.3.2 快速转换法宝之一：利用智能标记...............................................26

2.3.3 快速转换法宝之二：选择性粘贴...................................................26

2.3.4 快速转换法宝之三：利用分列工具...............................................27

2.4　数据整理之三：快速删除眼睛看不见的字符.................................27

2.4.1 你为什么非要在名字中间加空格呢？............................................27

2.4.2 不知从何而来的特殊字符..............................................................28

2.4.3 刷卡考勤数据中会出现换行符......................................................29

2.5　数据整理之四：数据分列 ..............................................................29

2.5.1 分列法宝之一：分列工具，根据分隔符号分.................................29

2.5.2 分列法宝之二：分列工具，根据固定宽度分.................................30

2.5.3 分列法宝之三：使用函数..............................................................31

2.5.4 分列法宝之四：使用快速填充工具...............................................34

 

2.6　处理合并单元格及填充数据..........................................................34

2.6.1 取消数据列中的合并单元格并填充数据........................................34

2.6.2 取消数据行中的合并单元格并填充数据........................................36

2.7　其他数据整理技巧.........................................................................37

2.7.1 复制可见单元格数据.....................................................................37

2.7.2 对单元格区域数据进行批量修改...................................................37

2.7.3 将行列数据进行对调.....................................................................38

CHAPTER 03 HR 如何做到高效数据管理——设计规范的基础数据表单 40

3.1　要掌握数据高效输入的重要技能：数据验证.................................40

3.1.1 重要的数据验证工具.....................................................................40

3.1.2 限制只能输入规定的日期..............................................................41

3.1.3 为单元格设置下拉菜单.................................................................42

3.1.4 为单元格设置二级下拉菜单..........................................................43

3.1.5 控制不能输入重复数据.................................................................46

3.2　从身份证号码自动提取信息..........................................................47

3.2.1 身份证号码的编码规则 .................................................................47

3.2.2 提取办理身份证所在地的地址......................................................48

3.2.3 获取真正的生日数字.....................................................................48

3.2.4 计算年龄（周岁） ..........................................................................48

3.2.5 判断性别.......................................................................................49

3.2.6 判断星座.......................................................................................50

3.3　利用函数从其他基本资料表自动获取基本数据.............................50

3.3.1 谁也离不开的最基本取数函数......................................................51

3.3.2 固定标准数据用公式来解决..........................................................51

3.3.3 变动数据用函数自动计算..............................................................51

3.4　HR 基础表单设计练习之一：员工基本信息管理 ...........................52

3.4.1 绘制数据管理逻辑架构图..............................................................52

3.4.2 字段详细描述................................................................................53

3.4.3 表格架构设计................................................................................53

3.4.4 规范工号输入（只允许4 位编码，不允许重复）...........................53

3.4.5 规范姓名输入（不允许在姓名中输入空格）...................................54

3.4.6 快速输入部门名称........................................................................55

3.4.7 快速输入学历名称........................................................................55

 

3.4.8 快速输入婚姻状况........................................................................55

3.4.9 输入不重复的18 位身份证号码....................................................55

3.4.10 自动输入性别..............................................................................55

3.4.11 自动输入出生日期.......................................................................56

3.4.12 自动计算年龄..............................................................................56

3.4.13 规范输入入职时间......................................................................56

3.4.14 自动计算本公司工龄...................................................................56

3.4.15 保证员工基本信息的完整性........................................................57

3.4.16 规范输入离职时间......................................................................57

3.4.17 规范输入离职原因......................................................................58

3.4.18 设置条件格式，自动美化表格....................................................58

3.4.19 保护公式.....................................................................................58

3.4.20 数据输入与数据维护...................................................................59

3.5　HR 基础表单设计练习之二：动态考勤表 ......................................60

3.5.1 动态考勤表样式............................................................................60

3.5.2 设计动态标题................................................................................61

3.5.3 自动标识双休日............................................................................62

3.5.4 快速输入请假类型........................................................................63

3.5.5 自动统计各类请假天数.................................................................63

3.6　HR 基础表单设计练习之三：休假管理 .........................................64

3.6.1 建立员工休假标准信息表..............................................................64

3.6.2 建立员工休假记录表单.................................................................65

3.6.3 统计每个人的休假情况.................................................................65

CHAPTER 04 HR 如何高效批量采集数据——快速获取外部数据 67

4.1　从其他工作表导入数据.................................................................67

4.1.1 使用Microsoft Query 工具导出其他工作表数据...........................67

4.1.2 使用Power Query 工具导出其他工作表数据................................70

4.2　获取几个表的数据.........................................................................71

4.2.1 利用Mrosoft Query 获取几个表都存在的数据：根据年初年末.........

信息表抓取在职人员信息 .............................................................72

4.2.2 利用Power Query 只获取某个表格存在的数据：根据年初年末 .......

信息表抓取离职人员信息 .............................................................74

 

4.2.3 利用现有连接 SQL 语句获取两个表都存在的数据：在职 .............

老员工信息.............................................................................76

4.2.4 利用现有连接 SQL 语句获取只存在某个表的数据：当年离职 ........

员工信息......................................................................................78

4.2.5 利用现有连接 SQL 语句获取只存在某个表的数据：当年新进 ........

员工信息......................................................................................78

4.3　快速汇总全年12 个月的工资表....................................................78

4.3.1 利用现有连接 SQL 方法汇总当前工作簿的12 个月工资表.........78

4.3.2 利用Power Query 汇总当前工作簿的12 个月工资表...................81

4.3.3 利用Power Query 汇总12 个月工作簿工资表.............................84

4.4　快速汇总集团各个分公司全年数百个工资表.................................92

4.4.1 每个月工资表中有月份名称列......................................................92

4.4.2 每个月工资表中没有月份名称列...................................................93

4.5　导入文本数据..............................................................................101

4.5.1 导入全部数据：CSV 格式文本文件.............................................101

4.5.2 导入全部数据：其他格式文本文件 .............................................102

4.5.3 获取文本文件中满足条件的数据.................................................103

CHAPTER 05 HR 数据自动高效处理和分析——掌握核心函数公式 109

5.1　HR 人必须了解和掌握的函数公式基础知识 ................................109

5.1.1 常见的几种公式运算...................................................................109

5.1.2 绝对引用和相对引用................................................................... 110

5.1.3 公式中如何输入常量................................................................... 111

5.1.4 公式和函数中的标点符号都必须是半角字符............................... 112

5.1.5 如何快速准确地输入函数............................................................112

5.1.6 如何快速准确地输入嵌套............................................................112

5.2　HR 必须掌握和熟练运用的逻辑判断函数 .................................... 115

5.2.1 常用逻辑判断函数IF、AND 和OR............................................ 115

5.2.2 逻辑函数HR 应用之一：计算带薪年休假...................................116

5.2.3 逻辑函数HR 应用之二：计算工龄工资...................................... 116

5.2.4 逻辑函数HR 应用之三：计算正常出勤...................................... 116

5.2.5 逻辑函数HR 应用之四：计算上班迟到区间............................... 117

5.3　常用计数统计函数COUNTIF、COUNTIFS................................ 118

5.3.1 函数基本使用方法...................................................................... 118

 

5.3.2 精确条件计数..............................................................................119

5.3.3 模糊条件计数..............................................................................119

5.4　常用求和汇总函数SUMIF、SUMIFS.........................................120

5.4.1 函数基本使用方法......................................................................120

5.4.2 精确条件求和..............................................................................121

5.4.3 模糊条件求和..............................................................................121

5.5　用SUMPRODUCT 来高效统计..................................................122

5.5.1 函数基本原理..............................................................................122

5.5.2 条件计数.....................................................................................123

5.5.3 条件求和.....................................................................................125

5.6　最常用的查找函数VLOOKUP....................................................125

5.6.1 VLOOKUP 函数基本应用...........................................................125

5.6.2 与MATCH 函数联合使用............................................................127

5.6.3 反向查找.....................................................................................128

5.6.4 根据关键词来匹配查找...............................................................129

5.6.5 模糊定位查找..............................................................................129

5.7　联合使用MATCH 和INDEX 做更灵活查找.................................131

5.8　用IFERROR 处理公式的错误值.................................................132

5.9　常用日期函数及其HR 应用........................................................133

5.9.1 获取当天日期..............................................................................133

5.9.2 组合日期 .....................................................................................134

5.9.3 拆分日期.....................................................................................134

5.9.4 计算到期日.................................................................................134

5.9.5 计算年龄和工龄..........................................................................135

5.9.6 判断星期几.................................................................................135

5.10　常用HR 公式集锦.....................................................................136

5.10.1 计算年龄...................................................................................136

5.10.2 计算工龄...................................................................................136

5.10.3 计算带薪年休假 ........................................................................136

5.10.4 计算个税...................................................................................136

5.10.5 根据税后反算税前工资.............................................................136

5.10.6 从身份证号码提取信息.............................................................136

5.10.7 判断星期几...............................................................................137

5.10.8 一个月的天数............................................................................137

5.10.9 当月月底日期............................................................................137

5.10.10 当月月初日期..........................................................................137