从逻辑思路到实战应用，轻松做Excel数据分析
￥59.80定价
作者： 韩小良、于峰
出版时间：2019年5月
出版社：中国铁道出版社
- 中国铁道出版社
- 9787113254032
- 1版
- 285266
- 61202195-6
- 平装
- 16开
- 2019年5月
- 434
- 276
- 工学
- 计算机科学与技术
- 电商
- 本科、高职高专
内容简介
韩小良国内实战型Excel培训讲师和应用解决方案专家。出版了40余部关于Excel企业管理应用方面的实用书籍。从事Excel研究及应用近20年，有着丰富的Excel高级应用培训和咨询实战经验，具有丰富的实际管理经验和极强的Excel应用开发能力；已经为数千家国内外大中型企业进行了Excel培训和应用方案解决咨询服务，在业界享有很高的评价，深得客户认可。韩小良老师从管理和决策的视角阐释和应用Excel，注重实际问题的解决思路，为企业提供解决方案。通过案例教学，紧贴实际工作，今天学习，明天应用，真正授人以渔！授课诙谐幽默，用幽默通俗的语言把枯燥的Excel技能展现出来，课堂气氛好，互动性强。于峰：实战型财务培训师、咨询师，曾任某财务软件公司高管、某外企高管，现任江苏某上市公司财务总监。有着丰富的财务培训和咨询经验，对财务经营分析理念、方法、实务、以及财务工具Excel应用、业财融合分析仪表盘等，有着较深的研究和独特的认识，在工作中积累了丰富的财务管理和企业经营数据分析经验。
目录
目录
第 1 部分 数据采集与汇总
第 1 章 快速汇总大量的一维表单 ................................................................... 3
1.1 ”现有连接”命令 SQL 语句 ............................................................................ 3
1.1.1
1.1.3
1.1.4
1.1.5
1.1.6
1.2.1
1.2.3
SQL 语句的基本知识 ......................................................................................... 3
汇总全部列、部分行数据 ................................................................................... 8
汇总部分列、全部行数据 ................................................................................... 9
汇总部分列、部分行数据 ................................................................................... 9
汇总不同工作簿里的工作表 ............................................................................. 10
汇总当前工作簿里的多个工作表 ...................................................................... 10
汇总不同工作簿里的多个工作表 ...................................................................... 12
1.1.2 汇总全部列数据 ................................................................................................. 5
1.2 VBA 方法 ........................................................................................................ 10
1.2.2 汇总不同工作簿里的一个工作表 .......................................................................11
1.2.4 VBA 基本知识概述 ........................................................................................... 14
1.3 Power Query 方法 .......................................................................................... 24
1.3.1
汇总当前工作簿的多个工作表 .......................................................................... 24
1.3.2 汇总多个工作簿，每个工作簿仅有一个工作表................................................. 28
1.3.3 汇总多个工作簿，每个工作簿有多个工作表 .................................................... 34
1.3.4 汇总多个工作簿里满足条件的数据................................................................... 38
第 2 章 快速汇总大量二维表格 ..................................................................... 39
2.1 灵活汇总结构完全相同的工作表 ..................................................................... 39
2.1.1
使用 SUM 函数快速汇总 .................................................................................. 39
2.1.2 使用合并计算工具快速汇总 ............................................................................. 40
2.1.3 使用 INDIRECT 函数 ....................................................................................... 42
2.2 大量二维表格的快速汇总 ................................................................................ 43
2.2.1
2.2.3
利用多重合并计算数据区域透视表................................................................... 44
利用 Power Query ........................................................................................... 48
I
2.2.2 利用 INDIRECT 函数 ....................................................................................... 47
从逻辑思路到实战应用，轻松做 Excel 数据分析
第 3 章 关联工作表的快速汇总 ..................................................................... 52
3.1 Microsoft Query 工具 ...................................................................................... 52
3.1.1
Microsoft Query 汇总的基本步骤 ..................................................................... 52
3.1.2 Microsoft Query 汇总的注意事项 ..................................................................... 55
3.2 Power Query 工具 .......................................................................................... 57
3.2.1
建立基本查询 ................................................................................................... 57
3.2.2 合并已有查询 ................................................................................................... 58
3.2.3 添加自定义列 ................................................................................................... 61
3.2.4 加载为数据模型，便于以后随时调用 ............................................................... 62
第 4 章 个数不定工作表的自动滚动汇总 ...................................................... 64
4.1 单独使用 INDIRECT 函数 ............................................................................... 64
4.2 INDIRECT 函数与查找函数联合使用 .............................................................. 65
4.3 INDIRECT 函数与分类汇总函数联合使用 ....................................................... 66
第 5 章 获取其他类型文件的数据 ................................................................. 68
5.1 从文本文件中获取数据 ................................................................................... 68
5.2 从数据库中获取数据 ....................................................................................... 70
5.3 从网页上获取数据........................................................................................... 72
第 2 部分 数据查询与提取
第 6 章 从一个工作表获取部分数据 ............................................................. 77.
6.1 利用透视表快速制作明细表 ............................................................................ 77
6.1.1
每次制作一个明细表 ........................................................................................ 77
6.1.2 一次批量制作多个明细表 ................................................................................. 79
6.2 利用函数制作自动化明细表 ............................................................................ 81
6.2.1
制作满足一个条件的明细表 ............................................................................. 81
6.2.2 制作满足多个条件的明细表 ............................................................................. 83
6.2.3 综合练习 .......................................................................................................... 84
6.3 利用 VBA 一键完成明细表 .............................................................................. 84
6.3.1
通过录制宏将普通的筛选 / 复制 / 粘贴工作自动化 ........................................... 85
6.3.2 使用 ADO 技术快速筛选查询数据 .................................................................... 86
6.3.3 在当前工作簿中批量制作明细表 ...................................................................... 87
6.3.4 批量制作明细表工作簿..................................................................................... 88
6.4 制作与源数据链接的动态明细表 ..................................................................... 89
第 7 章 从大量工作表中提取部分数据 .......................................................... 90
7.1 从一个工作簿的多个工作表中提取数据 .......................................................... 90
II
目 录
7.1.1
多个工作表的合并查询..................................................................................... 90
7.1.2 其他查询操作 ................................................................................................... 95
7.2 从多个工作簿的多个工作表中提取数据 .......................................................... 96
7.2.1
多个工作簿的合并查询..................................................................................... 97
7.2.2 其他查询操作 ................................................................................................. 102
第 8 章 从其他类型文件中提取部分数据 .................................................... 103
8.1 从文本文件中提取数据 ................................................................................. 103
8.1.1
8.1.2
利用 Microsoft Query ..................................................................................... 103
利用 Power Query ......................................................................................... 106
8.2 从其他数据库中提取数据 .............................................................................. 108
第 3 部分 数据分析与挖掘
第 9 章 数据分析是对数据的思考 ............................................................... 111
9.1 案例剖析：毛利预算分析 ...............................................................................111
9.1.1
9.1.2
9.1.3
9.1.4
9.1.5
9.1.6
9.2.1
9.2.2
9.2.3
9.2.4
9.2.5
9.2.6
9.2.7
失败的分析报告 ..............................................................................................111
醒目标识需要特别关注的异常数据..................................................................112
用仪表盘直观表达出整体预算完成情况 ..........................................................112
分析各个产品毛利预算偏差影响 .....................................................................112
分析各个产品销量、单价、成本对毛利预算偏差影响 .....................................113
进一步分析问题产品 .......................................................................................113
价值树的原理 ..................................................................................................113
营业利润价值树分析 .......................................................................................114
毛利价值树分析——依照计算逻辑流程 ..........................................................114
毛利价值树分析——依照业务单元（详细）.....................................................115
毛利价值树分析——依照业务单元（简略）.....................................................116
净利润率价值树分析 .......................................................................................116
净资产收益率价值树分析 ................................................................................117
9.2 利用价值树分析企业经营 ...............................................................................113
第 10 章 管理者驾驶舱，一目了然的经营分析与决策报告 ......................... 118
10.1 经营成果分析驾驶舱仪表盘示例 ..................................................................118
10.1.1
10.1.2
10.2.1
10.2.2
10.2.3
预算执行分析仪表盘 .....................................................................................119
同比分析仪表盘 ............................................................................................119
使用数据透视表建立简单的销售分析仪表盘 ................................................ 120
使用函数建立自动化销售分析仪表盘 ........................................................... 121
联合使用函数和透视表建立销售分析仪表盘 ................................................ 121
10.2 销售分析驾驶舱仪表盘示例 ........................................................................ 120
III
从逻辑思路到实战应用，轻松做 Excel 数据分析
10.2.4
利用 VBA 自动对客户进行排名 .................................................................... 123
10.3 人工成本分析驾驶舱仪表盘示例 ................................................................. 123
10.4 利用 Power 工具建立经营分析仪表盘......................................................... 124
第 11 章 智能表格，日常数据的基本分析 .................................................. 125
11.1 智能表格的创建与维护................................................................................ 125
11.1.1
11.1.3
创建智能表格 ............................................................................................... 125
手工调整表格大小 ........................................................................................ 126
11.1.2 智能表格的格式化 ........................................................................................ 126
11.1.4 为表格添加列、插入行 ................................................................................. 127
11.1.5 删除表格的列或行 ........................................................................................ 128
11.2 利用智能表格快速分析数据 ........................................................................ 128
11.2.1
11.2.3
为表格添加汇总行 ........................................................................................ 128
使用快速分析工具来分析表格 ...................................................................... 130
11.2.2 使用切片器筛选数据 .................................................................................... 129
第 12 章 固定格式报表：函数的综合运用 .................................................. 131
12.1 示例数据 ..................................................................................................... 131
12.2 从原始数据直接制作底稿 ............................................................................ 132
12.2.1
12.2.2
12.3.1
12.3.2
从原始数据表单中直接汇总计算 .................................................................. 132
快速得到结构相同的汇总表 ......................................................................... 132
分析各个产品指定项目的当月数和累计数 .................................................... 133
分析指定产品、指定项目的各月预算执行情况............................................. 134
12.3 从底稿中直接进行计算分析 ........................................................................ 133
第 13 章 多维汇总分析，数据透视表工具的灵活运用 ................................ 136
13.1 制作数据透视表的常用方法 ........................................................................ 136
13.1.1
13.1.2
13.1.3
13.1.4
13.1.5
以一个表格数据创建数据透视表 .................................................................. 136
以多个二维表格数据创建数据透视表 ........................................................... 138
以多个一维表单数据创建数据透视表 ........................................................... 138
在不打开工作簿的情况下创建数据透视表 .................................................... 139
以表格的部分数据创建数据透视表............................................................... 140
13.2 布局数据透视表 .......................................................................................... 140
13.2.1 ”数据透视表字段”窗格 ............................................................................... 140
13.2.2
13.2.3
13.3.1
13.3.2
13.3.3
数据透视表布局的几种方法 ......................................................................... 141
数据透视表工具 ........................................................................................... 141
设计透视表的样式 ........................................................................................ 141
设计透视表的布局 ........................................................................................ 142
重复 / 不重复项目标签 ................................................................................. 143
13.3 美化数据透视表 .......................................................................................... 141
IV
目 录
13.3.4
13.3.5
13.3.7
13.3.9
13.3.10
13.3.12
显示 / 隐藏报表的行总计和列总计 ............................................................... 143
合并项目标签单元格 .................................................................................... 144
设置值字段的汇总依据................................................................................. 145
显示 / 隐藏字段的分类汇总 .......................................................................... 145
对分类字段项目自动排序 ........................................................................... 146
显示没有数据的分类字段项目 .................................................................... 146
13.3.6 修改值字段名称 ........................................................................................... 144
13.3.8 设置值字段的数字格式................................................................................. 145
13.3.11 对分类字段项目手动排序 ........................................................................... 146
13.3.13 刷新数据透视表 ......................................................................................... 147
13.4 利用透视表分析数据 ................................................................................... 147
13.4.1
13.4.2
13.4.3
13.4.4
13.4.5
13.4.6
13.4.7
13.4.8
13.4.9
13.5.1
13.5.2
13.5.3
通过设置字段汇总方式分析数据 .................................................................. 148
通过设置字段显示方式分析数据 .................................................................. 148
通过组合字段分析数据................................................................................. 149
自定义计算字段 ........................................................................................... 151
自定义计算项 ............................................................................................... 152
计算字段和计算项的区别 ............................................................................. 153
透视表的基本数据筛选................................................................................. 154
筛选值最大（最小）的前 N 个项目 .............................................................. 154
使用切片器快速筛选报表 ............................................................................. 156
费用预实分析 ............................................................................................... 157
动态进销存管理 ........................................................................................... 158
客户销售同比分析与流入流出分析............................................................... 160
13.5 透视表综合应用案例 ................................................................................... 157
第 14 章 Power Pivot 报表，更加强大的数据分析工具 ............................ 162.
14.1 加载 Power Pivot ........................................................................................ 162
14.2 建立单个工作表的 Power Pivot .................................................................. 163
14.3 建立多个关联工作表的 Power Pivot ........................................................... 167
14.4 以大量工作表数据创建 Power Pivot ........................................................... 171
14.5 以文本文件数据创建 Power Pivot ............................................................... 172
14.6 以数据库数据创建 Power Pivot .................................................................. 174
第 4 部分 分析结果可视化
第 1 部分 数据采集与汇总
第 1 章 快速汇总大量的一维表单 ................................................................... 3
1.1 ”现有连接”命令 SQL 语句 ............................................................................ 3
1.1.1
1.1.3
1.1.4
1.1.5
1.1.6
1.2.1
1.2.3
SQL 语句的基本知识 ......................................................................................... 3
汇总全部列、部分行数据 ................................................................................... 8
汇总部分列、全部行数据 ................................................................................... 9
汇总部分列、部分行数据 ................................................................................... 9
汇总不同工作簿里的工作表 ............................................................................. 10
汇总当前工作簿里的多个工作表 ...................................................................... 10
汇总不同工作簿里的多个工作表 ...................................................................... 12
1.1.2 汇总全部列数据 ................................................................................................. 5
1.2 VBA 方法 ........................................................................................................ 10
1.2.2 汇总不同工作簿里的一个工作表 .......................................................................11
1.2.4 VBA 基本知识概述 ........................................................................................... 14
1.3 Power Query 方法 .......................................................................................... 24
1.3.1
汇总当前工作簿的多个工作表 .......................................................................... 24
1.3.2 汇总多个工作簿，每个工作簿仅有一个工作表................................................. 28
1.3.3 汇总多个工作簿，每个工作簿有多个工作表 .................................................... 34
1.3.4 汇总多个工作簿里满足条件的数据................................................................... 38
第 2 章 快速汇总大量二维表格 ..................................................................... 39
2.1 灵活汇总结构完全相同的工作表 ..................................................................... 39
2.1.1
使用 SUM 函数快速汇总 .................................................................................. 39
2.1.2 使用合并计算工具快速汇总 ............................................................................. 40
2.1.3 使用 INDIRECT 函数 ....................................................................................... 42
2.2 大量二维表格的快速汇总 ................................................................................ 43
2.2.1
2.2.3
利用多重合并计算数据区域透视表................................................................... 44
利用 Power Query ........................................................................................... 48
I
2.2.2 利用 INDIRECT 函数 ....................................................................................... 47
从逻辑思路到实战应用，轻松做 Excel 数据分析
第 3 章 关联工作表的快速汇总 ..................................................................... 52
3.1 Microsoft Query 工具 ...................................................................................... 52
3.1.1
Microsoft Query 汇总的基本步骤 ..................................................................... 52
3.1.2 Microsoft Query 汇总的注意事项 ..................................................................... 55
3.2 Power Query 工具 .......................................................................................... 57
3.2.1
建立基本查询 ................................................................................................... 57
3.2.2 合并已有查询 ................................................................................................... 58
3.2.3 添加自定义列 ................................................................................................... 61
3.2.4 加载为数据模型，便于以后随时调用 ............................................................... 62
第 4 章 个数不定工作表的自动滚动汇总 ...................................................... 64
4.1 单独使用 INDIRECT 函数 ............................................................................... 64
4.2 INDIRECT 函数与查找函数联合使用 .............................................................. 65
4.3 INDIRECT 函数与分类汇总函数联合使用 ....................................................... 66
第 5 章 获取其他类型文件的数据 ................................................................. 68
5.1 从文本文件中获取数据 ................................................................................... 68
5.2 从数据库中获取数据 ....................................................................................... 70
5.3 从网页上获取数据........................................................................................... 72
第 2 部分 数据查询与提取
第 6 章 从一个工作表获取部分数据 ............................................................. 77.
6.1 利用透视表快速制作明细表 ............................................................................ 77
6.1.1
每次制作一个明细表 ........................................................................................ 77
6.1.2 一次批量制作多个明细表 ................................................................................. 79
6.2 利用函数制作自动化明细表 ............................................................................ 81
6.2.1
制作满足一个条件的明细表 ............................................................................. 81
6.2.2 制作满足多个条件的明细表 ............................................................................. 83
6.2.3 综合练习 .......................................................................................................... 84
6.3 利用 VBA 一键完成明细表 .............................................................................. 84
6.3.1
通过录制宏将普通的筛选 / 复制 / 粘贴工作自动化 ........................................... 85
6.3.2 使用 ADO 技术快速筛选查询数据 .................................................................... 86
6.3.3 在当前工作簿中批量制作明细表 ...................................................................... 87
6.3.4 批量制作明细表工作簿..................................................................................... 88
6.4 制作与源数据链接的动态明细表 ..................................................................... 89
第 7 章 从大量工作表中提取部分数据 .......................................................... 90
7.1 从一个工作簿的多个工作表中提取数据 .......................................................... 90
II
目 录
7.1.1
多个工作表的合并查询..................................................................................... 90
7.1.2 其他查询操作 ................................................................................................... 95
7.2 从多个工作簿的多个工作表中提取数据 .......................................................... 96
7.2.1
多个工作簿的合并查询..................................................................................... 97
7.2.2 其他查询操作 ................................................................................................. 102
第 8 章 从其他类型文件中提取部分数据 .................................................... 103
8.1 从文本文件中提取数据 ................................................................................. 103
8.1.1
8.1.2
利用 Microsoft Query ..................................................................................... 103
利用 Power Query ......................................................................................... 106
8.2 从其他数据库中提取数据 .............................................................................. 108
第 3 部分 数据分析与挖掘
第 9 章 数据分析是对数据的思考 ............................................................... 111
9.1 案例剖析：毛利预算分析 ...............................................................................111
9.1.1
9.1.2
9.1.3
9.1.4
9.1.5
9.1.6
9.2.1
9.2.2
9.2.3
9.2.4
9.2.5
9.2.6
9.2.7
失败的分析报告 ..............................................................................................111
醒目标识需要特别关注的异常数据..................................................................112
用仪表盘直观表达出整体预算完成情况 ..........................................................112
分析各个产品毛利预算偏差影响 .....................................................................112
分析各个产品销量、单价、成本对毛利预算偏差影响 .....................................113
进一步分析问题产品 .......................................................................................113
价值树的原理 ..................................................................................................113
营业利润价值树分析 .......................................................................................114
毛利价值树分析——依照计算逻辑流程 ..........................................................114
毛利价值树分析——依照业务单元（详细）.....................................................115
毛利价值树分析——依照业务单元（简略）.....................................................116
净利润率价值树分析 .......................................................................................116
净资产收益率价值树分析 ................................................................................117
9.2 利用价值树分析企业经营 ...............................................................................113
第 10 章 管理者驾驶舱，一目了然的经营分析与决策报告 ......................... 118
10.1 经营成果分析驾驶舱仪表盘示例 ..................................................................118
10.1.1
10.1.2
10.2.1
10.2.2
10.2.3
预算执行分析仪表盘 .....................................................................................119
同比分析仪表盘 ............................................................................................119
使用数据透视表建立简单的销售分析仪表盘 ................................................ 120
使用函数建立自动化销售分析仪表盘 ........................................................... 121
联合使用函数和透视表建立销售分析仪表盘 ................................................ 121
10.2 销售分析驾驶舱仪表盘示例 ........................................................................ 120
III
从逻辑思路到实战应用，轻松做 Excel 数据分析
10.2.4
利用 VBA 自动对客户进行排名 .................................................................... 123
10.3 人工成本分析驾驶舱仪表盘示例 ................................................................. 123
10.4 利用 Power 工具建立经营分析仪表盘......................................................... 124
第 11 章 智能表格，日常数据的基本分析 .................................................. 125
11.1 智能表格的创建与维护................................................................................ 125
11.1.1
11.1.3
创建智能表格 ............................................................................................... 125
手工调整表格大小 ........................................................................................ 126
11.1.2 智能表格的格式化 ........................................................................................ 126
11.1.4 为表格添加列、插入行 ................................................................................. 127
11.1.5 删除表格的列或行 ........................................................................................ 128
11.2 利用智能表格快速分析数据 ........................................................................ 128
11.2.1
11.2.3
为表格添加汇总行 ........................................................................................ 128
使用快速分析工具来分析表格 ...................................................................... 130
11.2.2 使用切片器筛选数据 .................................................................................... 129
第 12 章 固定格式报表：函数的综合运用 .................................................. 131
12.1 示例数据 ..................................................................................................... 131
12.2 从原始数据直接制作底稿 ............................................................................ 132
12.2.1
12.2.2
12.3.1
12.3.2
从原始数据表单中直接汇总计算 .................................................................. 132
快速得到结构相同的汇总表 ......................................................................... 132
分析各个产品指定项目的当月数和累计数 .................................................... 133
分析指定产品、指定项目的各月预算执行情况............................................. 134
12.3 从底稿中直接进行计算分析 ........................................................................ 133
第 13 章 多维汇总分析，数据透视表工具的灵活运用 ................................ 136
13.1 制作数据透视表的常用方法 ........................................................................ 136
13.1.1
13.1.2
13.1.3
13.1.4
13.1.5
以一个表格数据创建数据透视表 .................................................................. 136
以多个二维表格数据创建数据透视表 ........................................................... 138
以多个一维表单数据创建数据透视表 ........................................................... 138
在不打开工作簿的情况下创建数据透视表 .................................................... 139
以表格的部分数据创建数据透视表............................................................... 140
13.2 布局数据透视表 .......................................................................................... 140
13.2.1 ”数据透视表字段”窗格 ............................................................................... 140
13.2.2
13.2.3
13.3.1
13.3.2
13.3.3
数据透视表布局的几种方法 ......................................................................... 141
数据透视表工具 ........................................................................................... 141
设计透视表的样式 ........................................................................................ 141
设计透视表的布局 ........................................................................................ 142
重复 / 不重复项目标签 ................................................................................. 143
13.3 美化数据透视表 .......................................................................................... 141
IV
目 录
13.3.4
13.3.5
13.3.7
13.3.9
13.3.10
13.3.12
显示 / 隐藏报表的行总计和列总计 ............................................................... 143
合并项目标签单元格 .................................................................................... 144
设置值字段的汇总依据................................................................................. 145
显示 / 隐藏字段的分类汇总 .......................................................................... 145
对分类字段项目自动排序 ........................................................................... 146
显示没有数据的分类字段项目 .................................................................... 146
13.3.6 修改值字段名称 ........................................................................................... 144
13.3.8 设置值字段的数字格式................................................................................. 145
13.3.11 对分类字段项目手动排序 ........................................................................... 146
13.3.13 刷新数据透视表 ......................................................................................... 147
13.4 利用透视表分析数据 ................................................................................... 147
13.4.1
13.4.2
13.4.3
13.4.4
13.4.5
13.4.6
13.4.7
13.4.8
13.4.9
13.5.1
13.5.2
13.5.3
通过设置字段汇总方式分析数据 .................................................................. 148
通过设置字段显示方式分析数据 .................................................................. 148
通过组合字段分析数据................................................................................. 149
自定义计算字段 ........................................................................................... 151
自定义计算项 ............................................................................................... 152
计算字段和计算项的区别 ............................................................................. 153
透视表的基本数据筛选................................................................................. 154
筛选值最大（最小）的前 N 个项目 .............................................................. 154
使用切片器快速筛选报表 ............................................................................. 156
费用预实分析 ............................................................................................... 157
动态进销存管理 ........................................................................................... 158
客户销售同比分析与流入流出分析............................................................... 160
13.5 透视表综合应用案例 ................................................................................... 157
第 14 章 Power Pivot 报表，更加强大的数据分析工具 ............................ 162.
14.1 加载 Power Pivot ........................................................................................ 162
14.2 建立单个工作表的 Power Pivot .................................................................. 163
14.3 建立多个关联工作表的 Power Pivot ........................................................... 167
14.4 以大量工作表数据创建 Power Pivot ........................................................... 171
14.5 以文本文件数据创建 Power Pivot ............................................................... 172
14.6 以数据库数据创建 Power Pivot .................................................................. 174
第 4 部分 分析结果可视化
