从逻辑思路到实战应用，轻松做Excel数据分析
作者：韩小良，于峰 著
出版社：中国铁道出版社
出版时间：2019-05-01
ISBN：9787113254032
定价：¥59.80
第 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 部分 分析结果可视化
第 15 章 一图抵万言，数据分析可视化图表 ............................................... 179
15.1 图表到底是什么 .......................................................................................... 179
15.1.1
15.1.2
对图表的困惑 ............................................................................................... 179
案例剖析 ...................................................................................................... 180
V
从逻辑思路到实战应用，轻松做 Excel 数据分析
15.1.3
15.2.1
15.2.2
15.2.3
15.2.4
15.2.5
15.3.1
15.3.2
15.3.3
15.3.4
15.3.5
15.3.6
15.3.7
15.3.8
数据充满思想，图表充满思考 ...................................................................... 183
阅读表格是基本功 ........................................................................................ 183
案例剖析举例之一 ........................................................................................ 184
案例剖析举例之二 ........................................................................................ 184
案例剖析举例之三 ........................................................................................ 185
案例剖析举例之四 ........................................................................................ 186
利用固定数据区域绘图................................................................................. 186
利用定义名称绘图 ........................................................................................ 187
为图表添加新数据系列................................................................................. 189
修改数据系列 ............................................................................................... 190
删除数据系列 ............................................................................................... 190
设置数据系列的坐标轴................................................................................. 190
设置数据系列的图表类型 ............................................................................. 191
图表的保存位置 ........................................................................................... 192
15.2 从阅读表格入手 .......................................................................................... 183
15.3 绘制图表的基本方法 ................................................................................... 186
15.3.9 复制图表 ...................................................................................................... 192
15.3.10 删除图表 .................................................................................................... 192
15.4 格式化图表 ................................................................................................. 193
15.4.1
15.4.2
15.4.3
15.4.4
15.4.5
15.4.6
15.4.7
15.4.8
图表结构及主要元素 .................................................................................... 193
为图表添加元素 ........................................................................................... 195
选择图表元素的方法 .................................................................................... 195
设置图表元素格式的工具 ............................................................................. 196
格式化图表区 ............................................................................................... 196
格式化绘图区 ............................................................................................... 197
格式化坐标轴 ............................................................................................... 197
格式化图表标题、坐标轴标题、图例 ........................................................... 197
15.4.9 格式化网格线 ............................................................................................... 197
15.4.10 格式化数据系列 ......................................................................................... 198
15.4.11 格式化数据标签.......................................................................................... 198
15.4.12 突出标识图表的重要信息 ........................................................................... 199
15.4.13 让图表元素显示为单元格数据 .................................................................... 199
15.4.14 让数据点显示为形状 .................................................................................. 199
15.4.15
15.5.1
15.5.2
15.5.3
15.5.4
VI
简单是美 .................................................................................................... 199
柱形图 .......................................................................................................... 199
条形图 .......................................................................................................... 200
折线图 .......................................................................................................... 202
XY 散点图 .................................................................................................... 203
15.5 常见分析图表及其基本应用案例 ................................................................. 199
目 录
15.5.5
15.5.6
15.5.7
15.5.8
15.5.9
饼图 ............................................................................................................. 204
圆环图 .......................................................................................................... 206
面积图 .......................................................................................................... 208
雷达图 .......................................................................................................... 209
气泡图 .......................................................................................................... 210
15.5.10 树状图 ........................................................................................................ 212
15.5.11 旭日图 ........................................................................................................ 213
15.5.12 直方图 ........................................................................................................ 213
15.5.13 箱形图 ........................................................................................................ 214
15.5.14 漏斗图 ........................................................................................................ 214
15.5.15
瀑布图 ........................................................................................................ 215
15.5.16 组合图 ........................................................................................................ 216
第 16 章 动态图表，让数据分析更灵活 ...................................................... 219
16.1 动态图表技术：概述 ................................................................................... 219
16.1.1
16.1.2
16.1.3
16.1.4
16.1.5
16.1.6
16.2.1
16.2.2
16.2.3
16.2.4
16.2.5
16.2.6
动态图的基本原理 ........................................................................................ 219
动态图表制作方法：辅助区域法 .................................................................. 220
动态图表制作方法：动态名称法 .................................................................. 222
动态图表制作方法：数据透视图法............................................................... 222
制作动态图表的六大步骤 ............................................................................. 223
制作动态图表需要注意的几个问题............................................................... 223
使用组合框制作动态图表 ............................................................................. 223
使用列表框制作动态图表 ............................................................................. 224
使用选项按钮制作动态图表 ......................................................................... 225
使用复选框制作动态图表 ............................................................................. 226
使用滚动条制作动态图表 ............................................................................. 228
使用数值调节钮制作动态图表 ...................................................................... 230
16.2 常见表单控件控制的动态图表制作 ............................................................. 223
第 17 章 数据透视图与数据透视表联合使用，建立个性化的小 BI ............. 232.
17.1 创建数据透视图 .......................................................................................... 232
17.1.1
17.1.2
17.1.3
17.1.4
17.2.1
17.2.2
17.3.1
创建数据透视图的基本方法 ......................................................................... 232
关于数据透视图的分类轴 ............................................................................. 233
数据透视图的美化 ........................................................................................ 234
利用切片器控制透视表和透视图 .................................................................. 234
建立多重合并计算数据区域透视表............................................................... 235
联合使用切片器和数据透视图进行分析 ....................................................... 236
创建普通的数据透视表................................................................................. 237
VII
17.2 二维表格的透视分析 ................................................................................... 235
17.3 一维表格的透视分析 ................................................................................... 237
从逻辑思路到实战应用，轻松做 Excel 数据分析
17.3.2
17.3.3
17.3.4
分析指定产品的客户销售额 ......................................................................... 238
分析指定客户的产品销售情况 ...................................................................... 238
分析客户销售排名 ........................................................................................ 238
第 18 章 Power View 报表，更加灵活的可视化 ....................................... 240.
18.1 创建报表 ..................................................................................................... 240
18.2 报表的可视化 .............................................................................................. 241
第 5 部分 实战案例精讲
第 19 章 精选案例讲解：手工台账数据管理与分析模板——日常考勤 ............
管理 ............................................................................................ 247
19.1 设计考勤表母版 .......................................................................................... 247
19.2 各月考勤记录表 .......................................................................................... 248
19.3 统计分析 ..................................................................................................... 248
第 20 章 精选案例讲解：基于系统导出数据的分析模板——两年销售 ...............
同比分析 ..................................................................................... 251.
20.1 制作分析底稿 .............................................................................................. 251
20.2 两年销售额、销售成本和毛利整体分析 ...................................................... 252
20.3 两年销售额、销售成本和毛利的月度同比分析 ........................................... 253
20.4 分析企业销售额同比增长原因：产品影响................................................... 253
20.5 分析指定产品销售额同比增长原因：量价分析 ........................................... 254
20.6 分析企业毛利同比增长原因：产品影响 ...................................................... 257
20.7 分析指定产品毛利同比增长原因：销量 - 单价 - 成本分析........................... 257
20.8 客户排名分析 .............................................................................................. 259
20.9 分析客户流动：新增客户 ............................................................................ 259
20.10 分析客户流动：流失客户 .......................................................................... 260
20.11 分析客户流动：存量客户 .......................................................................... 260
VIII
