动手学Excel数据分析与可视化

　　本书由资深数据分析师结合十余年从业经验精心打造，循序渐进地介绍使用Excel进行数据分析和可视化的专业技术。全书分为13章，第1章介绍数据分析的常用方法，第2章介绍如何用Excel获取分析数据，第3章介绍Excel常用的公式和函数，第4章介绍Excel日常数据分析，第5章介绍Excel数据透视表，第6章介绍使用Excel进行数据清洗的方法，第7章和第8章介绍Excel强大的统计分析工具，第9章和第10章介绍Excel的数据可视化图表和仪表板，第11章介绍如何从大数据集群中获取数据，第12章和第13章介绍数据分析的实际案例。 本书既适合使用Excel 进行数据分析的初学者，也适合有一定经验的数据分析从业者，还可以作为大专院校数据分析课程的教学用书。

　　王国平 毕业于上海海洋大学，硕士，从业10余年，主要从事数据可视化、数据挖掘和大数据分析与研究等工作，对于多类数据工具以及行业软件Tableau、SPSS、PyTorch、Power Bl等都有丰富的实践经验。

第1章  数据分析概述 1
1.1  数据分析简介 1
1.1.1  什么是数据分析 1
1.1.2  数据分析的方法 2
1.1.3  数据分析的流程 5
1.2  开始使用Excel 5
1.2.1  初识工作簿与工作表 6
1.2.2  快速录入数据的技巧 8
1.2.3  数据重复录入的验证 14
1.3  Excel快速操作技巧 16
1.3.1  一键快速分析数据 16
1.3.2  对比工作表的变化 17
1.3.3  Excel常用快捷键 18
1.4  动手练习：查看工作表之间的关系 22
1.5  实操练习 24
第2章  Excel连接数据源 25
2.1  导入本地离线数据 25
2.1.1  导入Excel工作簿文件 25
2.1.2  导入文本/CSV文件 26
2.1.3  JSON文件 27
2.2  导入关系型数据库中的数据 29
2.2.1  Excel连接SQL Server数据库 29
2.2.2  Excel连接Access数据库 31
2.2.3  Excel连接MySQL数据库 32
2.3  读取其他数据源的数据 34
2.3.1  使用“自表格/区域”读取数据 34
2.3.2  读取Web网站的数据 36
2.3.3  通过ODBC连接数据源 38
2.4  多表合并查询 39
2.4.1  多表合并的应用场景 39
2.4.2  不同工作簿的多表合并 39
2.4.3  同一工作簿的多表合并 42
2.5  动手练习：读取国家统计局的Web数据 44
2.5.1  连接到Web数据源 44
2.5.2  调整和清洗源数据 45
2.6  实操练习 51
第3章  使用公式与函数 52
3.1  公式与函数基础 52
3.1.1  认识Excel函数 52
3.1.2  公式及其输入方法 53
3.1.3  输入Excel内置函数 54
3.2  Excel单元格引用 55
3.2.1  单元格的相对引用 56
3.2.2  单元格的绝对引用 56
3.2.3  单元格的混合引用 57
3.3  必会Excel新函数 59
3.3.1  多条件判断 59
3.3.2  文本连接函数 60
3.3.3  日期处理函数 61
3.4  Excel函数举例 63
3.4.1  与文本相关的函数 64
3.4.2  日期和时间处理函数 66
3.4.3  数值计数与求和函数 68
3.5  动手练习：函数嵌套计算职位薪资 73
3.6  实操练习 75
第4章  Excel日常数据分析 76
4.1  Excel数据排序 76
4.1.1  按单个字段排序 76
4.1.2  按多个字段排序 77
4.1.3  自定义数据排序 78
4.2  Excel条件格式 80
4.2.1  突出显示单元格 80
4.2.2  自定义条件格式 81
4.2.3  单元格数据添加图标 82
4.3  Excel数据分类汇总 83
4.3.1  按单个字段分类汇总 83
4.3.2  按多个字段分类汇总 84
4.4  Excel数据筛选 85
4.4.1  自动筛选数据 85
4.4.2  自定义筛选数据 85
4.5  动手练习：Excel在学生管理中的运用 86
4.6  实操练习 90
第5章  Excel数据透视表 91
5.1  数据透视表入门 91
5.1.1  创建数据透视表 91
5.1.2  更改和刷新数据源 92
5.1.3  为数据透视表插入切片器 94
5.2  数据透视表布局 95
5.2.1  设置透视表报表分类汇总 96
5.2.2  设置透视表报表布局形式 96
5.3  数据透视表计算 98
5.3.1  在透视表中添加计算字段 98
5.3.2  在透视表中添加计算项 100
5.4  数据透视表显示 101
5.4.1  在数据透视表中添加货币符号 101
5.4.2  透视表字段列表的设置 102
5.5  动手练习：财政税收收入结构分析 103
5.5.1  union语句在财政历年税收收入结构分析中的应用 104
5.5.2  Excel数据透视表与SQL结合应用的注意事项 106
5.5.3  小结 106
5.6  实操练习 107
第6章  Excel数据清洗 108
6.1  重复数据检测与处理 108
6.1.1  重复数据的检测 108
6.1.2  重复数据的处理 110
6.2  缺失数据检测与处理 110
6.2.1  缺失数据的检测 110
6.2.2  缺失数据的处理 111
6.3  异常数据的检测与处理 112
6.3.1  异常数据的检测 112
6.3.2  异常数据的处理 113
6.4  数据清洗的其他方法 113
6.4.1  快速实现数据分列 113
6.4.2  更改文本的大小写 114
6.5  动手练习：Excel指纹考勤数据处理 116
6.6  实操练习 120
第7章  Excel基本统计分析 121
7.1  描述性统计 121
7.1.1  描述性统计概述 121
7.1.2  主要描述性指标 122
7.1.3  描述性统计分析实例 124
7.2  相关分析 125
7.2.1  皮尔逊相关系数 126
7.2.2  斯皮尔曼相关系数 127
7.2.3  肯德尔相关系数 128
7.3  模拟分析 129
7.3.1  单变量求解模拟 129
7.3.2  双变量模拟运算 130
7.4  方差分析 133
7.4.1  单因素方差分析 133
7.4.2  双因素方差分析 135
7.5  动手练习：企业投资决策敏感性分析 138
7.5.1  投资决策敏感性分析的分类 139
7.5.2  敏感性分析的主要模板设计 139
7.5.3  利用“单变量求解”功能分析各因素的临界值 140
7.5.4  案例小结 141
7.6  实操练习 142
第8章  Excel高级统计分析 143
8.1  回归分析 143
8.1.1  线性回归简介 143
8.1.2  线性回归建模 146
8.1.3  回归模型案例 147
8.2  时间序列分析 149
8.2.1  时间序列简介 150
8.2.2  移动平均法及案例 151
8.2.3  指数平滑法及案例 155
8.3  假设检验 158
8.3.1  Z检验及案例 158
8.3.2  T检验及案例 159
8.3.3  F检验及案例 160
8.4  线性规划 161
8.4.1  线性规划简介 162
8.4.2  案例：规划求解问题 163
8.4.3  案例：最短路径问题 165
8.5  动手练习：活跃用户数据建模与预测 168
8.5.1  分析思路 168
8.5.2  解决方案 169
8.6  实操练习 172
第9章  Excel数据可视化 173
9.1  Excel图表及案例 173
9.1.1  绘制对比型图表 173
9.1.2  绘制趋势型图表 175
9.1.3  绘制比例型图表 177
9.1.4  绘制分布型图表 178
9.1.5  绘制其他类图表 180
9.2  迷你图及案例 181
9.2.1  创建迷你图 182
9.2.2  迷你图设置 182
9.2.3  企业盈亏分析 185
9.3  数据透视图及案例 186
9.3.1  如何创建数据透视图 186
9.3.2  利用筛选器筛选数据 190
9.3.3  数据透视图样式设计 193
9.3.4  为透视图添加切片器 194
9.4  Excel图表绘制技巧 195
9.4.1  调整图表格式控制 195
9.4.2  图表标注突出数据 197
9.4.3  巧用图表样式模板 198
9.5  Excel图表美化方法 199
9.5.1  几招让表格不难看 200
9.5.2  如何使图形更美观 202
9.6  动手练习：Excel可视化插件——EasyCharts 205
9.6.1  示例9-1：绘制2021年第四季度销售额分割面积图 206
9.6.2  示例9-2：绘制2022年3月份销售业绩子弹图 206
9.7  实操练习 207
第10章  用仪表板展示数据 208
10.1  如何制作出高效精美的仪表板 208
10.1.1  熟悉业务合理规划 208
10.1.2  利用视图充分展示 209
10.1.3  完善视图避免错误 209
10.2  制作仪表板 209
10.2.1  创建数据透视表 210
10.2.2  创建数据透视图 211
10.2.3  添加图形切片器 212
10.3  共享仪表板 213
10.3.1  与他人直接共享 214
10.3.2  通过电子邮件共享 215
10.3.3  通过Power BI共享 217
10.4  动手练习：2021年商品销售额仪表板 218
10.5  实操练习 222
第11章  Excel连接Hadoop集群 223
11.1  了解Hadoop 223
11.1.1  Hadoop概述 223
11.1.2  Hadoop组件 224
11.1.3  Apache Hadoop发行版 227
11.2  连接Hadoop Hive 228
11.2.1  启动Hadoop Hive 228
11.2.2  连接Cloudera Hadoop 229
11.2.3  连接MapR Hadoop 231
11.3  连接Hadoop Spark 232
11.3.1  启动Hadoop Spark 232
11.3.2  配置SparkODBC 233
11.3.3  连接Hadoop Spark 234
11.4  动手练习：Excel连接Hadoop集群订单表 235
11.5  实操练习 236
第12章  电商客户关系管理实战 237
12.1  RFM分析法 237
12.2  数据分析与建模 238
12.2.1  数据清洗 239
12.2.2  数据整理 240
12.2.3  确定权重 241
12.3  数据分析与可视化 242
12.3.1  数据分析 242
12.3.2  数据可视化 244
12.3.3  案例小结 246
12.4  客户关系管理策略 246
12.5  实操练习 247
第13章  银行用户信用评分实战 248
13.1  数据准备 248
13.1.1  案例数据集介绍 248
13.1.2  描述性统计分析 249
13.2  数据清洗 250
13.2.1  重复值的处理 250
13.2.2  缺失值的处理 251
13.2.3  异常值的处理 251
13.3  客户特征分析 251
13.3.1  客户属性分析 251
13.3.2  还款能力分析 253
13.3.3  违约风险分析 255
13.4  客户违约率分析 256
13.4.1  违约率与月收入的关系 257
13.4.2  违约率与年龄的关系 257
13.4.3  违约率与逾期次数的关系 258
13.5  实操练习 258
附录A  Excel主要函数 259
A.1  数学和三角函数 259
A.2  统计函数 262
A.3  逻辑函数 266
A.4  日期和时间函数 267
A.5  查找和引用函数 268
A.6  文本函数 269
A.7  财务函数 270
A.8  工程函数 272
A.9  信息函数 274
A.10  数据库函数 275
A.11  Web函数 275
A.12  兼容性函数 276
A.13  多维数据集函数 277
附录B  Excel快捷键 278
附录C  Excel宏与VBA 284
C.1  宏及其操作 284
C.2  VBA基础知识 287
C.3  VBA编程案例：冒泡排序算法实现数据排序 289
附录D  认识SQL语句 292
D.1  CREATE语句 292
D.2  ALTER语句 293
D.3  INSERT语句 293
D.4  UPDATE语句 294
D.5  SELECT语句 294
D.6  DELETE语句 296