从小白到高手 Excel人力资源精细化管理和量化分析PDF|Epub|txt|kindle电子书版本网盘下载

从小白到高手 Excel人力资源精细化管理和量化分析PDF格式电子书版下载

注意：本站所有压缩包均有解压码： 点击下载压缩包解压工具

引子 阅读本书之前，先测试一下自己的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 如何使用Excel8

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和OR115

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

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

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

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

5.3 常用计数统计函数COUNTIF、COUNTIFS118

5.3.1 函数基本使用方法118

5.3.2 精确条件计数119

5.3.3 模糊条件计数119

5.4 常用求和汇总函数SUMIF、SUMIFS120

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 最常用的查找函数VLOOKUP125

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 间接引用函数INDIRECT179

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