从原始数据到分析报告 Excel数据透视表高效达人养成记PDF电子书下载

PART 01 数据透视表篇 3

CHAPTER 01 制作数据透视表的准备工作 3

1.1 不规范表格结构的重新搭建 3

1.1.1 把多行标题的数据区域转换为数据清单（案例1-1） 3

1.1.2 将二维表格转换为数据清单（案例1-2） 6

1.1.3 将多列文字描述转换为一个列表清单（案例1-3） 8

1.2 数据分列 9

1.2.1 利用分隔符对数据分列（案例1-4、案例1-5） 10

1.2.2 利用固定宽度对数据分列（案例1-6） 11

1.2.3 利用文本函数对数据分列（案例1-7） 12

1.2.4 利用全角字符和半角字符特征对数据分列（案例1-8） 13

1.2.5 利用文本和数字特征对数据分列（案例1-9） 14

1.2.6 利用关键词对数据分列（案例1-10） 14

1.3 清除数据中的垃圾 16

1.3.1 快速删除文本中的空格 16

1.3.2 快速删除数据中眼睛看不见的特殊字符（案例1-11） 16

1.4 修改非法日期和非法时间 17

1.4.1 使用分列工具快速修改非法日期（案例1-12） 17

1.4.2 使用函数处理非法时间（案例1-13） 18

1.4.3 考勤数据的快速处理（案例1-14） 18

1.5 快速填充空单元格 19

1.5.1 快速填充上一行数据（案例1-15） 20

1.5.2 快速填充下一行数据（案例1-16） 21

1.5.3 快速往空单元格填充数字0 22

1.5.4 快速取消合并单元格并填充数据 23

CHAPTER 02 制作数据透视表的基本方法 25

2.1 以一个表格数据创建数据透视表 25

2.1.1 以一个固定的数据区域制作数据透视表（案例2-1） 25

2.1.2 以一个变动的数据区域制作数据透视表 27

2.2 以多个二维表格数据创建数据透视表 28

2.2.1 创建单页字段的多重合并计算数据区域透视表（案例2-2） 29

2.2.2 创建多页字段的多重合并计算数据区域透视表（案例2-3） 33

2.3 以多个多维表格数据创建数据透视表 37

2.3.1 利用现有连接+SQL数据查询汇总多个多维工作表（案例2-4） 37

2.3.2 小知识：关于SQL语句 40

2.3.3 利用现有连接+SQL数据查询创建数据透视表应注意的问题 42

2.4 以多个关联工作表数据创建数据透视表 43

2.4.1 使用Query工具（案例2-5） 43

2.4.2 注意事项 47

2.5 以工作表部分数据创建数据透视表（案例2-6） 47

2.6 在不打开工作簿的情况下创建数据透视表 50

2.6.1 利用现有连接工具创建数据透视表 50

2.6.2 利用Query工具创建数据透视表 51

2.7 以数据库数据创建数据透视表 51

2.7.1 以Access数据库的一个数据表制作数据透视表（案例2-7） 51

2.7.2 以Access数据库的多个数据表制作数据透视表（案例2-8） 53

2.7.3 以Access数据库的部分数据表制作数据透视表 54

2.8 以文本文件数据直接创建数据透视表（案例2-9） 54

2.9 查看／更改数据源 58

2.9.1 以一个Excel表格制作的数据透视表 58

2.9.2 以多个Excel表格制作的数据透视表（现有连接方法） 58

2.9.3 以数据库或文本文件制作的数据透视表（Query方法） 58

CHAPTER 03 布局数据透视表 60

3.1 数据透视表字段窗格 60

3.2 数据透视表布局的几种方法 61

3.3 恢复经典的数据透视表布局方式 61

3.4 数据透视表工具 62

3.4.1 “分析”选项卡中各功能组介绍 63

3.4.2 “设计”选项卡中各功能组介绍 64

CHAPTER 04 数据透视表的设计与美化 66

4.1 设计透视表的样式 66

4.1.1 套用数据透视表样式（案例4-1） 66

4.1.2 细雕数据透视表样式 67

4.1.3 删除默认的数据透视表样式 67

4.2 设计透视表的布局 68

4.2.1 以压缩形式显示 68

4.2.2 以大纲形式显示 68

4.2.3 以表格形式显示 69

4.2.4 重复、不重复项目标签（案例4-2） 70

4.2.5 在每个项目后插入、删除空行 71

4.2.6 显示、隐藏报表的行总计和列总计 72

4.3 设置透视表的选项 72

4.3.1 合并字段的项目标签单元格 72

4.3.2 处理透视表中的错误值（案例4-3） 73

4.4 设置字段 74

4.4.1 修改值字段名称 74

4.4.2 设置值字段的汇总依据 74

4.4.3 设置值字段的数字格式 74

4.4.4 设置值字段的条件格式（案例4-4） 75

4.4.5 显示、隐藏字段的分类汇总 76

4.4.6 对分类字段项目进行自动排序 77

4.4.7 对分类字段项目进行手动排序（案例4-5） 77

4.4.8 对分类字段项目进行自定义排序（案例4-6） 78

4.4.9 显示没有数据的分类字段项目（案例4-7） 80

4.4.10 显示、隐藏分类字段标题 81

4.5 数据透视表的其他设置 81

4.5.1 刷新数据透视表 81

4.5.2 显示、隐藏折叠／展开按钮 82

4.5.3 显示、隐藏数据透视表字段窗格 82

4.5.4 选择数据透视表 82

4.5.5 复制数据透视表 83

4.5.6 移动数据透视表 83

4.5.7 清空数据透视表 83

4.5.8 将数据透视表转换为普通报表 83

CHAPTER 05 自定义计算字段和计算项 85

5.1 自定义计算字段 85

5.1.1 添加计算字段（案例5-1） 85

5.1.2 修改计算字段 86

5.1.3 删除计算字段 86

5.1.4 列示出所有自定义字段信息 87

5.1.5 可以改变计算字段的汇总方式吗 87

5.2 自定义计算项 87

5.2.1 添加计算项（案例5-2） 87

5.2.2 自定义计算项的几个重要说明 89

5.2.3 修改自定义计算项 89

5.2.4 删除自定义计算项 89

5.2.5 列示出所有自定义计算项信息 90

5.3 添加计算字段和计算项的几个注意问题 90

5.3.1 什么时候添加计算字段，什么时候添加计算项 90

5.3.2 同时添加计算字段和计算项的几个问题（案例5-3） 90

5.3.3 哪些情况下不能添加自定义计算字段和计算项 91

5.3.4 自定义计算字段是否能使用工作簿函数 91

5.3.5 自定义计算字段是否能使用单元格引用和名称 91

5.4 综合应用案例 92

5.4.1 预算与实际执行情况的对比分析（案例5-4） 92

5.4.2 动态进销存管理（案例5-5） 93

CHAPTER 06 通过设置字段显示方式分析数据 97

6.1 占比分析 97

6.1.1 总计的百分比（案例6-1） 98

6.1.2 列汇总的百分比 98

6.1.3 行汇总的百分比 99

6.1.4 百分比（案例6-2） 99

6.1.5 父行汇总的百分比 100

6.1.6 父列汇总的百分比（案例6-3） 101

6.1.7 父级汇总的百分比 101

6.2 差异分析 102

6.2.1 环比分析（案例6-4） 102

6.2.2 同比分析（案例6-5） 103

6.2.3 字段下只有两个项目的差异分析（案例6-6） 103

6.3 累计分析 104

6.3.1 按某一字段汇总（案例6-7） 104

6.3.2 按某一字段汇总的百分比 105

6.4 排名分析：升序和降序显示方式（案例6-8） 106

6.5 恢复默认的显示方式 106

CHAPTER 07 通过组合字段分析数据 108

7 1 组合日期和时间 108

7.1.1 制作年、季度、月度汇总报告（案例7-1） 108

7.1.2 制作日汇总报告（案例7-2） 110

7.1.3 制作时间段汇总报告 111

7.1.4 组合日期时应注意的问题 112

7.2 组合数字 112

7.2.1 汇总各个年龄段的人数（案例7-3） 113

7.2.2 汇总各个工资区间的人数和占比（案例7-4） 114

7.2.3 组合数字时应注意的问题 115

7.3 组合文本 115

7.3.1 组合地区和商品 115

7.3.2 组合季度（案例7-5） 117

CHAPTER 08 通过设置字段汇总方式分析数据 121

8.1 工资分析（案例8-1） 121

8.2 员工信息分析（案例8-2） 123

8.3 销售分析（案例8-3） 124

CHAPTER 09 在透视表中筛选数据 126

9.1 基本筛选操作 126

9.2 筛选值最大（最小）的前N个项目（案例9-1） 127

9.3 使用切片器快速筛选报表（案例9-2） 129

9.4 快速获取明细数据 130

9.5 快速批量制作明细表（案例9-3） 131

CHAPTER 10 从原始数据到分析报告：透视表、图表和函数的综合应用 134

10.1 综合应用案例1：历年经营数据分析（案例10-1） 134

10.1.1 归集数据，制作分析底稿（案例10-1源数据） 134

10.1.2 分析各年收入、成本、利润、利润率的变化趋势 135

10.1.3 分析进口业务和出口业务的各年变化 137

10.1.4 客户排名分析 138

10.1.5 业务员业绩统计跟踪 139

10.1.6 其他分析（案例10-1分析报告） 140

10.2 综合应用案例2：年度经营综合分析（案例10-2） 140

10.2.1 案例数据 140

10.2.2 创建数据透视表 141

10.2.3 整体分析 141

10.2.4 客户排名分析 142

10.2.5 客户下产品结构分析 144

10.2.6 产品排名分析 146

10.2.7 产品下客户结构分析 146

10.2.8 业务员排名分析 147

10.3 综合应用案例3：店铺月度经营分析（案例10-3） 147

10.3.1 案例数据 147

10.3.2 66家店铺数据的快速汇总 147

10.3.3 制作基本的数据透视表 148

10.3.4 指定项目下客户排名分析 148

10.3.5 指定店铺的净利润影响因素分析 149

10.4 综合应用案例4：预算执行分析（案例10-4） 151

10.4.1 案例数据 151

10.4.2 制作预算执行情况分析表 152

10.4.3 分析指定科目各个月的预算执行情况 152

10.4.4 分析指定月份下各个科目的预算执行情况 154

10.5 综合应用案例5：考勤数据统计分析（案例10-5） 154

10.5.1 案例数据 154

10.5.2 整理考勤数据 155

10.5.3 制作数据透视表，进行汇总分析 157

PART 02 公式与函数篇 161

CHAPTER 11 必须掌握的函数和公式基本技能 161

11.1 梳理表格数据的逻辑关系 161

11.1.1 搞清楚自己要做什么 161

案例11-1 管理费用的趋势分析和结构分析 161

11.1.2 分析表格数据之间的逻辑关系 162

案例11-2 销售收入达成分析 162

案例11-3 计算物料最新采购价格和加权平均采购价格 163

11.1.3 学会画逻辑流程图 164

案例11-4 根据工龄工资标准计算工龄工资 164

案例11-5 根据不同城市、不同工龄计算补贴标准 165

11.1.4 设计好分析报告的结构和标题 166

案例11-6 重新设计报告标题以简化计算公式 166

案例11-7 设计二级分层表格结构 167

11.2 学会创建嵌套函数公式 168

11.2.1 分解综合法 168

案例11-8 根据商品名称查找对应编码 169

11.2.2 函数对话框——名称框法 170

案例11-9 应用单流程嵌套IF公式计算工龄工资 170

11.3 学会使用条件表达式解决复杂问题 177

11.3.1 什么是条件表达式 177

11.3.2 了解逻辑运算符 178

11.3.3 简单的条件表达式 178

11.3.4 复杂的条件表达式 178

11.3.5 很多问题使用条件表达式来解决更方便 179

11.4 学点数组公式的知识 180

11.4.1 数组的概念 180

11.4.2 数组公式的概念 181

11.4.3 输入数组公式 182

11.4.4 数组公式应用案例剖析 182

11.4.5 常见数组公式应用举例 183

案例11-10 计算数据区域内N个最大数或最小数之和 183

案例11-11 获取数据列中最后一个非空单元格数据 183

11.5 适当使用名称 184

11.5.1 定义名称的规则 184

11.5.2 定义名称的三种方法 185

11.5.3 编辑、修改和删除名称 187

11.5.4 名称应用 187

11.6 不可忽略的绝对引用和相对引用知识 187

11.6.1 单元格引用方式 187

11.6.2 单元格引用方式对公式的影响 188

11.6.3 引用其他工作表单元格 189

11.6.4 单元格引用的两种表示法A1和R1C1有什么不同 189

11.7 公式和函数其他应掌握的小技巧 191

11.7.1 复制公式本身 192

11.7.2 复制公式的一部分 192

11.7.3 查看公式中的某些表达式计算结果 192

11.7.4 将公式分行输入，以便使公式更加容易理解和查看 192

CHAPTER 12 逻辑判断函数及其实际应用 194

12.1 只使用IF函数的逻辑判断 194

12.1.1 IF函数的基本语法 194

案例12-1 计算含有错误值的单元格区域 195

12.1.2 嵌套IF函数 195

12.1.3 使用条件表达式来代替嵌套IF函数 196

12.2 逻辑函数、信息函数联合使用的复杂逻辑判断 196

12.2.1 使用AND函数组合“与”条件 196

案例12-2 将逾期数据按时间不同标识出来 196

12.2.2 使用OR函数组合“或”条件 197

案例12-3 计算公司年休假天数 197

12.2.3 使用IFERROR函数或者ISERROR函数处理公式的错误值 198

12.2.4 使用ISNUMBER函数处理特殊问题 199

12.3 实际应用案例解析 199

案例12-4 统计员工的出勤情况（固定时间） 199

案例12-5 统计员工的出勤情况（阶段时间） 201

案例12-6 统计18:00～20:00以及20:00以后的刷卡人数 203

案例12-7 处理重复刷卡数据 206

案例12-8 按成本中心分析工资的四分位值 207

案例12-9 根据税后工资反算税前工资 210

案例12-10 制作个人所得税计算表 210

案例12-11 调整信用期 211

案例12-12 判断应收账款账龄区间 211

12.4 逻辑函数和信息函数的其他应用 212

12.4.1 自定义数据有效性 212

案例12-13 在员工信息表中输入身份证号码 213

12.4.2 复杂的条件格式 213

案例12-14 自动跟踪不同的业绩变动率 213

CHAPTER 13 日期函数及其实际应用 216

13.1 常用日期函数的基本使用方法 216

13.1.1 TODAY函数：动态日期计算 217

13.1.2 EDATE函数：计算几个月之前或之后的日期 217

13.1.3 EOMONTH函数：计算几个月之前或之后的月底日期 217

13.1.4 DATEDIF函数：计算两个日期之间的期限 218

13.1.5 DATE函数：把代表年、月、日的三个数字组合成一个日期 218

13.1.6 YEAR、MONTH和DAY函数：把日期拆分成年、月、日三个数字 219

13.1.7 WEEKDAY函数：判断日期为星期几 219

13.1.8 WEEKNUM函数：判断日期为当年的第几周 220

13.1.9 YEARFRAC函数：计算小数点年数 220

13.2 常用日期函数的实际应用案例 220

案例13-1 指定月数或年数后的具体日期 220

案例13-2 指定天数、月数或年数后的月底日期 221

案例13-3 根据出生日期计算年龄 222

案例13-4 根据入职日期计算工龄 223

案例13-5 设计动态的日程表和考勤表 223

案例13-6 计算应付款截止日，遇双休日顺延 227

案例13-7 设计动态的固定资产折旧表 227

案例13-8 编制销售周报表 230

13.3 日期函数的其他应用案例 231

案例13-9 确定某日期所在年已经过去的天数和剩余天数 231

案例13-10 获取某个月最后一天的日期以及该月有多少天 232

案例13-11 确定某个日期在该年的第几季度 232

CHAPTER 14 文本函数及其实际应用 234

14.1 获取文本的部分数据 234

案例14-1 从文本字符串左边往右取指定长度的字符 235

案例14-2 从文本字符串右边往左取指定长度的字符 235

案例14-3 从文本字符串中间指定位置往右取指定长度的字符 236

案例14-4 从文本字符串右边指定位置往左取指定长度的字符 236

案例14-5 从某个特定字符位置取指定长度的字符 237

案例14-6 将前部分是数字、后部分是汉字的字符串分成两列 239

案例14-7 将前部分是汉字、后部分是数字的字符串分成两列 240

案例14-8 将数字、汉字混合的字符串中的数字取出 240

案例14-9 将前部分是字母、后部分是数字的字符串分成两列 242

案例14-10 将前部分是数字、后部分是字母的字符串分成两列 242

案例14-11 将金额的各个数字依次填入到连续的单元格 243

案例14-12 从身份证号码获取出生日期和性别 244

14.2 把数字和日期转换为指定格式的文本 245

14.2.1 TEXT函数的基本用法 245

14.2.2 将一个单元格的日期和时间分成两列 246

14.2.3 将日期转换成中文月份或者英文月份名称 246

案例14-13 根据日期流水账制作月份（英文标题）汇总表 246

14.3 获取当前文件的有关信息 246

案例14-14 获取文件路径、文件名和工作表名称等信息 246

CHAPTER 15 分类汇总函数及其实际应用 249

15.1 常见的数据分类汇总问题概述 249

15.1.1 两种常见的分类汇总方式：计数与求和 249

15.1.2 条件下的分类汇总：单条件与多条件 250

15.1.3 匹配模式下的分类汇总：精确匹配与模糊匹配 250

15.1.4 分类汇总函数的基本使用语法 250

15.2 单条件计数与单条件求和 251

15.2.1 精确匹配下的单条件计数 251

案例15-1 COUNTIF函数进行单条件计数：简单应用 251

案例15-2 使用COUNTIF函数进行自定义有效性设置：不允许输入重复数据 252

案例15-3 使用COUNTIF函数核对两个表格的数据 252

15.2.2 模糊匹配下的单条件计数 254

案例15-4 根据明细表统计每种材料的购买次数 254

15.2.3 精确匹配下的单条件求和 255

案例15-5 计算每个供应商的采购次数和采购总金额 255

15.2.4 模糊匹配下的单条件求和 256

案例15-6 计算每个部门和每个费用的总金额 256

15.3 多条件计数与多条件求和 257

15.3.1 精确匹配下的多条件计数 257

案例15-7 按部门统计总人数、男女人数、已婚未婚人数、各个学历的人数 257

15.3.2 模糊匹配下的多条件计数 258

15.3.3 精确匹配下的多条件求和 259

案例15-8 按地区分别汇总自营店和加盟店的销售总额和销售成本 259

15.3.4 模糊匹配下的多条件求和 260

案例15-9 按客户、按产品大类进行汇总 260

15.4 SUMPRODUCT函数的综合应用 260

15.4.1 SUMPRODUCT函数的基本用法 261

案例15-10 计算所有产品的销售总额、折扣额、销售净额 261

15.4.2 SUMPRODUCT函数的变形用法 262

案例15-11 通过销售表计算每个客户的销售总额 262

15.4.3 SUMPRODUCT函数用来进行条件计数 263

案例15-12 制作能够查看任意指定年份员工流动率变化的模板 263

15.4.4 SUMPRODUCT函数用来进行多条件求和 264

案例15-13 计算每个产品类别、每个月的总金额 264

案例15-14 根据管理费用科目余额表汇总每个费用项目每个月的总金额 265

案例15-15 从销售及回款清单汇总每个客户每个账龄的应收账款总额 266

15.4.5 SUMPRODUCT函数的其他应用 267

案例15-16 从采购数据计算物料的加权平均采购价格和算术平均采购价格 267

案例15-17 按照客户编码和存货名称核对从两套软件中导出的数据是否一致 268

15.4.6 SUMPRODUCT函数的优点和缺点 269

15.5 条件计数函数与条件求和函数的一个特殊问题 269

CHAPTER 16 查找引用函数及其实际应用 272

16.1 查找引用问题概述 272

16.1.1 单条件查找和多条件查找 273

16.1.2 精确匹配数据查找和模糊匹配数据查找 274

16.1.3 精确定位单元格查找和模糊定位单元格查找 274

16.1.4 单一数据查找和重复数据查找 275

16.1.5 单区域查找和多区域查找 275

16.1.6 固定区域查找和滚动区域查找 276

16.1.7 常用查找引用函数的使用语法 277

16.2 VLOOKUP函数各种实际应用 280

16.2.1 精确匹配下的单条件查询 280

案例16-1 查询每个人的实发工资 280

16.2.2 模糊匹配下的单条件查询 280

案例16-2 查找指定城市价格 280

16.2.3 多个区域的数据查找 280

案例16-3 根据编码从“低值品”和“原材料”中查询对应的数据 280

16.2.4 从不同列取数的数据查找 281

案例16-4 随意指定一个工资项目就能把每个员工的工资项目取出来 281

案例16-5 查询指定员工的所有工资项目（工资项目横向排列，顺序与工资表一致） 282

案例16-6 查询指定员工的所有工资项目（工资项目垂直排列，顺序与工资表一致） 282

16.2.5 模糊定位单元格查找 282

案例16-7 在应收账款清单中通过逾期天数判断账龄 283

案例16-8 统计各个税率下的人数、税前工资总额、个税总额 283

16.3 MATCH函数和INDEX函数的各种实际应用 285

16.3.1 核对两个表格 285

案例16-9 使用MATCH函数核对两个表格数据 285

16.3.2 精确定位指定数据的单元格地址 286

案例16-10 使用MATCH函数和ADDRESS函数查找指定数据的单元格地址 286

16.3.3 模糊定位指定数据所在单元格 287

案例16-11 利用查找函数进行数学插值计算 287

16.3.4 定位最大值／最小值的单元格地址 287

案例16-12 从一个数据区域中查找最大值所属的地区和产品 287

16.3.5 多条件精确查找 288

案例16-13 根据数据表计算每个员工的小时津贴 289

案例16-14 从流水账整理成分类汇总表 290

16.3.6 模糊查找 290

案例16-15 分列汉字、字母和数字 290

案例16-16 自动根据工作令号把真正的成本对象编码查询出来 291

16.3.7 财务中的典型应用：成本费用分析 292

案例16-17 从管理费用数据中按照部门和费用项目分类汇总 293

案例16-18 制作动态成本分析报告，查看任意产品的成本构成 293

16.3.8 人力资源中的典型应用：制作员工简历表 294

案例16-19 设计动态查询表，查询指定员工的基本信息 294

16.4 INDIRECT函数的各种实际应用 296

16.4.1 滚动循环查找技术：制作明细表 296

案例16-20 制作对账单表格，把指定客户的所有销售明细查找出来 296

16.4.2 滚动汇总技术：快速汇总大量工作表 300

案例16-21 各月利润表数据的动态滚动汇总 300

案例16-22 制作动态员工工资单查询表 301

案例16-23 制作每个部门各个月的预算执行情况跟踪分析表 302

案例16-24 制作查看指定部门、指定费用各个月的跟踪分析表 302

案例16-25 按部门汇总每个月的应发工资合计数 303

案例16-26 按部门、按费用汇总跟踪分析每个月的预算执行情况 304

案例16-27 指定部门、指定费用的各个月汇总分析 305

16.4.3 工作表间的累计值自动计算 306

案例16-28 自动计算每天的累计数 306

16.4.4 利用INDIRECT函数与ROW函数构建常量数组 307

16.5 OFFSET函数的各种实际应用 307

案例16-29 获取动态数据区域，制作动态数据源的数据透视表 307

案例16-30 获取动态数据区域，作为数据有效性序列来源 308

案例16-31 计算截至指定月份的累计值 309

案例16-32 从任意指定项目列中进行分类汇总 310

案例16-33 复杂的考勤汇总 311

16.6 其他查找函数的实际应用 313

案例16-34 LOOKUP函数实际应用 313

案例16-35 CHOOSE函数实际应用 314

案例16-36 TRANSPOSE函数实际应用 315

案例16-37 使用HYPERLINK函数自动建立超链接 315

案例16-38 使用HYPERLINK函数设计产品图片查看系统 316

16.7 查询数据几个应注意的问题 316

16.7.1 查不到数据出现错误怎么办 316

16.7.2 为什么会查不到数据 317

16.7.3 如何区别对待零值与空值 317