Excel高效人力资源管理从入门到精通 实用案例版 修订版PDF电子书下载

第一篇 RH人员应具备的Excel基本技能 2

Chapter1高效输入数据 2

1.1处理文本型数字 2

1.1.1输入文本型数字的基本方法 2

1.1.2如何将数字转换为文本 3

1.2快速输入文本序列 5

1.2.1快速填充默认的文本序列 5

1.2.2使用函数快速填充文本序列 6

1.2.3输入自定义的文本序列 8

1.3快速准确输入日期和时间 11

1.3.1 Excel对日期和时间的处理方式 11

1.3.2输入日期和时间的正确方法 12

1.3.3如何判断单元格的数据是否为日期和时间 13

1.3.4快速输入当前日期 15

1.3.5使用函数输入动态的当前日期和时间 16

1.3.6快速输入有规律的日期序列 16

1.3.7利用函数输入特殊的有规律的日期序列 17

1.4利用有效性准确快速输入数据 18

1.4.1 Excel 2003有效性的基本使用方法 18

1.4.2 Excel 2010有效性的基本使用方法 21

1.4.3快速输入规定的序列数据 21

1.4.4禁止输入重复的数据 23

1.4.5多重限制只能输入其他工作表的某列数据 25

1.4.6保证数据记录的完整性 27

1.5其他高效输入数据的小技巧 28

Chapter 2现有HR表格数据的整理与规范 31

2.1不规范表格的重新整理与组织 31

2.1.1取消合并单元格并填充数据 31

2.1.2多行表头表格的重新架构搭建 33

2.2高效核对数据 35

2.2.1快速圈释错误数据 35

2.2.2对比分析两个表格的差异：特殊情况 36

2.2.3对比分析两个表格的差异：一般情况 44

2.3修改非法日期 48

2.3.1查找非法日期 48

2.3.2快速批量修改非法日期 50

2.3.3设置合同日期格式 52

2.4数据分列和分行 54

2.4.1数据分列 54

2.4.2数据分行 56

2.5处理重复数据 57

2.5.1从单列数据中找出重复的数据并用颜色标识出来 57

2.5.2从多列数据中找出重复的数据并用颜色标识出来 58

2.5.3从有重复数据的区域中整理出没有重复的数据清单 59

2.6其他数据整理技巧 62

2.6.1复制可见单元格数据 62

2.6.2对单元格区域数据进行批量修改 62

2.6.3将工作表或者指定区域的所有公式错误值删除 64

2.6.4将行、列数据进行对调 64

2.6.5快速删除工作表中所有的图形对象 65

Chapter 3设计科学、高效的HR管理表单 66

3.1认识两类基本的Excel表格 66

3.1.1报告型表格 66

3.1.2清单型表格 67

3.2设计表格的基本原则 68

3.3设计表格的基本方法和技巧 69

3.4案例研究：办公用品管理表格 70

3.4.1办公用品管理表格的基本要求 70

3.4.2设计表格结构 71

3.4.3防止输入非法日期 71

3.4.4选择输入办公用品名称和单位 72

3.4.5选择输入部门名称 72

3.4.6准确输入数量和金额 73

3.4.7设置条件格式以美化表格 73

3.4.8定义动态数据区域名称 74

3.4.9保护工作表，防止输入垃圾数据 74

Chapter 4公式基础及常用的HR函数 76

4.1创建高效计算公式 76

4.1.1巧妙使用单元格引用方式快速输入大量公式 76

4.1.2如何快速准确输入嵌套函数 78

4.1.3在公式中使用表达式 81

4.1.4在公式中使用名称 83

4.1.5创建数组公式 90

4.1.6处理公式的错误值 94

4.2数据的逻辑判断 95

4.2.1计算工龄工资 95

4.2.2判断是否正常出勤、迟到和早退 95

4.2.3删除重复刷卡数据 97

4.3处理日期和时间 98

4.3.1处理动态日期和时间 98

4.3.2拆分日期 98

4.3.3合并日期 99

4.3.4确定一段时间之后或之前的日期 100

4.3.5判断是星期几 101

4.3.6判断日期是某年的第几周 102

4.3.7计算时间 102

4.3.8关于合同管理中的几个问题 103

4.4处理文本 103

4.4.1从文本字符串中取出部分字符 103

4.4.2合并多个文本 105

4.4.3清理文本的特殊元素 106

4.4.4文本函数综合应用：从身份证号码中提取信息 107

4.5查找数据 108

4.5.1使用MATCH/INDEX函数进行精确查询 108

4.5.2使用VLOOKUP/HLOOKUP函数进行精确查询 109

4.5.3使用VLOOKUP/HLOOKUP函数进行模糊查询 111

4.5.4多列数据查询问题 113

4.5.5从多个数据区域中查询数据 113

4.5.6问题研究：如何从有重名重姓的数据区域中查询数据 114

4.5.7实用的多条件查询工具：Microsoft Query 115

4.5.8利用查找函数建立动态的滚动查询表模板 117

4.5.9利用查找函数建立动态的滚动汇总表模板 120

4.6计数与求和 122

4.6.1简单条件下的计数：统计在职和离职人数 122

4.6.2复杂条件下的计数：员工信息的多维度分析 124

4.6.3简单条件下的求和 126

4.6.4复杂条件下的求和 127

4.6.5多项目汇总求和 129

4.6.6复杂条件下的求极值 129

Chapter 5快速汇总多个工作表数据 132

5.1使用函数 132

5.1.1使用函数汇总个数不定的工作表数据 132

5.1.2使用函数汇总几个有关联的工作表数据 135

5.2使用合并计算工具 136

5.2.1 Excel 2003的操作方法 137

5.2.2 Excel 2010的操作方法 141

5.3使用多重合并计算数据区域的数据透视表 141

5.3.1 Excel 2003的操作方法 141

5.3.2 Excel 2010的操作方法 147

5.4使用导入数据+SQL语言 150

5.4.1 Excel 2003的操作方法 151

5.4.2 Excel 2010的操作方法 154

5.5使用Microsoft Query工具 158

5.6汇总多个工作簿数据 161

Chapter 6用图表展示HR管理中的问题 162

6.1绘制图表的基本方法 162

6.1.1 Excel 2003绘制图表的基本方法 162

6.1.2 Excel 2010/2010绘制图表的基本方法 162

6.2使用组合图表和变形图表表达多维信息 163

6.2.1同比增长分析图表 163

6.2.2业绩达成率分析图表 165

6.2.3显示变化区间的浮动变化图 167

6.3使用动态图表灵活分析数据 168

6.3.1绘制动态交互图表的基本原理 169

6.3.2关于窗体控件（表单控件） 169

6.3.3常用表单控件的功能和控制属性 170

6.3.4绘制动态交互图表的辅助绘图区域方法 172

6.3.5 Excel 2010/2010中动态图表的一大缺陷 175

第二篇 Excel人力资源应用 178

Chapter 7招聘管理 178

7.1应聘人员基本信息管理 178

7.1.1建立应聘人员基本信息表 178

7.1.2应聘人员信息筛选分析 180

7.2招聘面试信息管理 181

7.2.1面试评估表结构设计 182

7.2.2使用有效性控制数据的输入 183

7.2.3使用单选按钮对应聘人员的各方面进行评价 183

7.2.4计算评价结果 187

7.2.5保护工作表 187

7.2.6打印面试评估表 187

7.2.7自动保存面试评估结果 188

7.2.8系统完善：与应聘人员基本信息结合 192

7.2.9筛选分析面试评估信息 194

Chapter 8培训管理 195

8.1制作新员工培训计划表 195

8.1.1制作培训计划表 195

8.1.2在Excel 2003中制作培训计划图 196

8.1.3在Excel 2010中制作培训计划图 200

8.2设计培训评估表 203

8.3培训信息管理 204

8.3.1设计员工信息管理表格 205

8.3.2设计课程信息管理表格 206

8.3.3设计培训信息管理表格 207

8.3.4利用数据透视表编制各种统计汇总分析报表 212

8.3.5设计一个系统管理界面，便于操作管理 215

Chapter 9员工信息管理 217

9.1建立员工基本信息表 217

9.1.1设计员工基本信息表格结构 217

9.1.2表格固定数据的输入 218

9.1.3通过身份证号码自动输入员工相关信息 218

9.1.4计算动态年龄 218

9.1.5计算动态工龄 218

9.2编制员工信息统计分析月报 219

9.2.1编制员工属性情况表 219

9.2.2编制月度人员增减汇总表 220

9.2.3编制月度员工流失率统计表 221

9.3员工信息的多维度动态分析 223

9.3.1创建基本的数据透视表 223

9.3.2分析在职员工的男女构成 224

9.3.3分析在职员工的年龄构成 224

9.3.4分析在职员工的工龄构成 225

9.3.5分析在职员工的文化程度构成 225

9.3.6分析离职原因 226

9.4用图表更加直观地分析员工信息 228

9.4.1员工男女构成分析图 228

9.4.2员工年龄分布图 232

9.4.3员工流动分析图 233

Chapter 10合同管理 238

10.1试用期合同管理 238

10.1.1计算试用期到期日 238

10.1.2试用期提前提醒 239

10.2正式合同管理 241

10.2.1不规范的合同管理表格 241

10.2.2设计合同管理表格 242

10.2.3计算合同到期日 243

10.2.4合同提前提醒 243

Chapter11考勤与休假管理 246

11.1完整月份的考勤管理 246

11.1.1设计完整月份的动态考勤表 246

11.1.2制作完整月份日常考勤的月汇总表 249

11.1.3设计辅助区域，以备进行考勤统计汇总分析 250

11.1.4复制工作表，得到全年12个月的日常考勤表和月汇总表 251

11.1.5制作各种考勤汇总统计报表 251

11.2跨月份的考勤管理 255

11.3请假统计与分析 256

11.3.1利用函数进行请假统计与分析 257

11.3.2利用数据透视表进行请假统计与分析 258

11.3.3请假记录表格的改进 259

11.4计算加班时间 260

11.4.1计算每周的加班时间 261

11.4.2分类计算工作日、双休日和国家法定节假日的加班时间 262

11.5休假管理 265

11.5.1关于工龄的计算 265

11.5.2计算国家法定年休假天数 266

11.5.3根据社会工龄和公司工龄计算年休假天数 267

11.5.4根据工龄和岗位级别计算年休假天数 268

11.5.5根据员工请假条的起止日期确定休假天数 269

11.5.6年假动态管理表格 270

Chapter 12薪酬管理与统计分析 274

12.1计算个税的几个实用公式 274

12.1.1使用IF函数 275

12.1.2使用数组公式 275

12.1.3使用查找函数 275

12.1.4使用自定义函数 276

12.1.5将自定义函数设置成加载宏，在任何工作簿上都能使用 279

12.2根据税后工资反算税前工资和个税 282

12.2.1根据税后工资反算税前工资 282

12.2.2根据税后工资反算个税 283

12.3建立工资清单工资表 283

12.4制作打印工资单 284

12.4.1制作打印工资条 285

12.4.2制作打印工资表 288

12.4.3制作打印工资保密信封 291

12.4.4群发工资条邮件 292

12.5填报代扣代缴个人所得税报告表 292

12.5.1代扣代缴个人所得税报告表格式 292

12.5.2填报代扣代缴个人所得税报告表 293

12.6工资统计分析 294

12.6.1全年工资汇总 295

12.6.2全方位统计每个部门和岗位的工资分布 296

12.6.3统计分析全年各月的工资变化 308

12.6.4制作社会保险汇总表 312

12.6.5制作住房公积金汇总表 313

12.6.6制作每个员工的全年个人所得税汇总表 313

12.6.7制作每个员工的全年工资明细表 314

12.6.8制作工资的四分位分析图 315

12.7人工成本滚动分析模板 319

12.7.1人工成本的构成 319

12.7.2制作人工成本滚动汇总表 321

12.7.3制作动态的人工成本分析图 321