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

第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