引子 阅读本书之前，先测试一下自己的Excel水平 1
Ⅰ 现在动手，自我测试 1
测试题目01 你会设计Excel表单吗？ 1
测试题目02 怎样快速整理基础数据？ 2
测试题目03 合同管理如何做到提前自动提醒？ 2
测试题目04 你有最基本的逻辑判断处理能力吗？ 3
测试题目05 如何快速引用指定条件的数据？ 3
测试题目06 如何引用满足多个条件的数据？ 4
测试题目07 如何对海量数据进行快速统计分析？ 4
测试题目08 如何将枯燥的数据变为直观易懂的结果？ 5
测试题目09 需要每个月都做相同的数据处理分析吗？ 5
测试题目10 如何为老板解释数据的差异及其背后的原因？ 5
Ⅱ 评估自己的Excel水平 6
Ⅲ 如何快速掌握和提升自己的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
CHAPTER 06 HR数据快速汇总分析——熟练运用数据透视表 138
6.1 制作数据透视表的准备工作 138
6.1.1 规范表单结构 138
6.1.2 规范表单数据 139
6.2 制作数据透视表的基本方法 139
6.2.1 基本方法 140
6.2.2 认识数据透视表的结构及布局窗格 141
6.2.3 布局方法 141
6.3 数据透视表的美化 142
6.3.1 设置透视表样式 142
6.3.2 修改字段名称 143
6.3.3 调整项目的次序 143
6.3.4 设置数字格式 143
6.3.5 其他设置 144
6.4 利用数据透视表分析HR数据的基本技能 144
6.4.1 改变值字段的汇总依据，制作多种计算结果的报告 144
6.4.2 改变值字段的显示方式，制作结构分析报告 145
6.4.3 组合字段，制作分组报告 145
6.4.4 其他分析技能 147
6.5 数据透视表的HR综合应用 147
6.5.1 快速核对社保 147
6.5.2 快速汇总全年12个月工资表 151
6.5.3 快速批量制作明细表 152
CHAPTER 07 HR数据量化分析——建立高效的员工信息分析模板 156
7.1 在职员工属性分析报告 156
7.1.1 示例数据 156
7.1.2 建立属性整体分析报表架构 157
7.1.3 创建汇总计算公式 157
7.1.4 分析结果可视化：员工分布动态图 159
7.1.5 利用透视表建立动态分析报告 164
7.2 流动性分析报告 166
7.2.1 示例数据 166
7.2.2 制作动态的入职离职分析报表 167
7.2.3 分析指定部门各月入职和离职情况 167
7.2.4 分析指定月份各部门入职和离职情况 171
7.2.5 各部门期初期末人数变化分析 172
7.2.6 离职原因分析 173
7.3 制作人力资源月报 176
7.3.1 月报架构设计 176
7.3.2 创建计算公式 177
7.4 制作在职和离职人员明细表 178
7.4.1 创建在职人员明细表 178
7.4.2 创建离职人员明细表 179
7.5 制作动态明细表 179
7.5.1 间接引用函数INDIRECT 179
7.5.2 制作指定部门的在职员工明细表 183
7.5.3 制作任意指定字段下指定项目的在职员工明细表 185
CHAPTER 08 HR数据量化分析——建立高效的人工成本分析模板 188
8.1 月度工资的深度分析 188
8.1.1 工资的基本汇总分析 188
8.1.2 工资极值的基本分析 189
8.1.3 工资四分位值分析 193
8.1.4 工资点状分布分析 196
8.1.5 工资区间人数统计分析 201
8.2 年度工资的汇总与分析 202
8.2.1 年度工资的汇总与统计分析 202
8.2.2 制作每个员工的年度工资表 202
8.2.3 制作每个部门的年度工资表 203
8.2.4 制作各个分公司的工资汇总表 203
8.3 人工成本同比分析仪表盘 203
8.3.1 示例数据 204
8.3.2 两年数据汇总分析 204
8.3.3 两年人工成本整体同比分析报告 204
8.3.4 人工成本同比增减因素分析：按工资项目 207
8.3.5 人工成本同比增减因素分析：按部门 211
8.3.6 OFFSET函数的使用方法 213
8.4 当前人工成本滚动跟踪分析仪表盘 214
8.4.1 示例数据 215
8.4.2 建立人工成本滚动汇总表 216
8.4.3 建立环比跟踪分析仪表盘 217
8.4.4 建立预算跟踪分析仪表盘 219
8.4.5 建立同比分析仪表盘 227
8.4.6 附：制作指定员工的各月工资汇总表 228
CHAPTER09 HR数据量化分析——千变万化的考勤数据高效处理 229
9.1 处理考勤数据必备的技能 229
9.1.1 正确认识日期和时间是何物 229
9.1.2 正确区分单元格和数据 230
9.1.3 考勤机导出的数据基本上都是文本型数据 231
9.1.4 考勤机导出的日期和时间在一个单元格或分行保存 231
9.1.5 考勤机导出的每个人刷卡数据分行保存 232
9.1.6 牢牢掌握分列工具 232
9.1.7 熟练使用几个函数 232
9.1.8 培养自己的表格阅读能力 233
9.1.9 训练自己的逻辑思维能力 233
9.1.10 了解和制定考勤判断规则 234
9.2 不同打卡考勤数据的快速整理 235
9.2.1 刷卡日期和时间保存在一行的不同列：最多两次刷卡 235
9.2.2 刷卡日期和时间保存在一行的不同列：有多次刷卡记录 237
9.2.3 刷卡日期和时间保存在同一列：有多次刷卡记录 239
9.2.4 刷卡日期和时间保存在同一列的不同行：有多次刷卡记录 239
9.2.5 刷卡时间被分行保存在同一单元格：有多次刷卡记录 241
9.2.6 有签到签退标记的刷卡数据，分几行保存 244
9.2.7 12小时制的刷卡数据 246
9.2.8 每天早中晚4次刷卡制度，有重复刷卡记录 248
9.2.9 夜班考勤，有重复刷卡 250
9.3 考勤数据的统计分析 252
9.3.1 判断迟到早退 252
9.3.2 判断异常打卡、漏打卡 254
9.3.3 计算工作时长 255
9.3.4 计算加班时间 256
9.3.5 计算跨夜的加班时间 256
CHAPTER 10 HR其他数据处理和统计分析 257
10.1 合同管理 257
10.1.1 计算合同到期日 257
10.1.2 建立合同提醒模型 258
10.1.3 自动提取即将到期的合同明细表 260
10.2 年休假管理 261
10.2.1 国家法定年休假 261
10.2.2 公司规定的年休假 261
10.2.3 年假已休天数计算 263
10.3 加班时间计算 264
10.3.1 正常加班时间计算 265
10.3.2 跨夜加班时间计算 265
10.3.3 不规范加班时间记录的加班计算 266
10.4 员工简历管理 266
10.4.1 从员工信息表制作员工简历表 266
10.4.2 快速汇总员工简历表的信息 267