Excel数据分析与可视化一本通

数据分析篇 1
第1章 建立规范的数据源
1.1 必懂的3种数据类型 3
1.1.1 数值型数据 3
1.1.2 文本型数据 4
1.1.3 逻辑型数据 5
1.2 合理设置数据的格式 6
1.2.1 快速统一小数位数 6
1.2.2 将数值转换成货币格式 7
1.2.3 启用会计专用格式 8
1.2.4 设置负数的显示方式 9
1.2.5 输入百分比数值 9
1.2.6 转换日期的显示方式 10
1.2.7 让身份证号码完整显示 11
1.2.8 各种符号的输入方法 12
1.2.9 自定义数据格式 13
1.3 处理重复性工作的技巧 16
1.3.1 复制数据 16
1.3.2 自动填充增效百倍 17
1.3.3 批量完成数据更换 20
1.4 没有规矩不成方圆——数据验证 23
1.4.1 设置允许输入的数值范围 23
1.4.2 设置允许输入的文本长度 24
1.4.3 使用下拉列表输入数据 24
1.4.4 自定义验证条件 25
1.4.5 自定义出错警告对话框 26
1.4.6 删除数据验证条件 26
1.5 数据源的规范整理 27
1.5.1 去除多余标题行和表头 27
1.5.2 合理安排字段的位置 28
1.5.3 删除无用的空白行/列 29
1.5.4 取消合并单元格 30
1.5.5 补全空缺信息 30
1.5.6 数据属性行列分明 31
拓展练习：制作员工销售提成表 33
知识总结：用思维导图学习数据分析 36

第2章 公式与函数在数据分析中的应用
2.1 公式与函数的自我介绍 38
2.1.1 HI！我是Excel公式 38
2.1.2 HI！我是Excel函数 39
2.2 公式与函数的应用基础 41
2.2.1 公式的输入与编辑 41
2.2.2 填充与复制公式 42
2.2.3 单元格的引用形式 43
2.2.4 函数的输入技巧 44
2.2.5 常见的错误值分析及公式审核 46
2.3 数组公式让计算变得更简单 48
2.3.1 什么是数组 48
2.3.2 使用数组公式时的注意事项 49
2.4 逻辑函数实现自动判断 51
2.4.1 IF函数的应用 51
2.4.2 IF函数执行多重判断 51
2.4.3 AND函数的应用 52
2.4.4 OR函数的应用 52
2.5 快速统计和汇总数据 53
2.5.1 SUM函数的应用 53
2.5.2 SUMIF函数的应用 54
2.5.3 COUNT函数的应用 55
2.5.4 COUNTIF函数的应用 56
2.5.5 AVERAGE函数的应用 57
2.5.6 AVERAGEIF函数的应用 57
2.5.7 MOD函数的应用 58
2.6 数据的追踪与查询专业户 59
2.6.1 VLOOKUP函数的应用 59
2.6.2 MATCH函数的应用 60
2.6.3 INDEX函数的应用 60
2.6.4 ROW函数的应用 61
2.7 一键计算日期与时间差 61
2.7.1 TODAY函数的应用 62
2.7.2 DATEDIF函数的应用 62
2.7.3 WEEKDAY函数的应用 63
2.8 其他典型函数的应用 64
2.8.1 RANK函数的应用 64
2.8.2 ROUND函数的应用 65
2.8.3 LEFT、MID、RIGHT函数的应用 65
2.8.4 TEXT函数的应用 66
拓展练习：制作工作服统计表 67
知识总结：用思维导图学习数据分析 69

第3章 掌握日常数据分析与管理技能
3.1 轻松排序表格中的数据 71
3.1.1 执行升序或降序操作 71
3.1.2 多个字段同时排序 71
3.1.3 根据不同要求排序 72
3.1.4 让最顶端的数据不参与排序 73
3.1.5 自定义排序 73
3.2 快速筛选指定数据 74
3.2.1 启用筛选 74
3.2.2 快速筛选指定数据方法 75
3.2.3 根据数据类型选择筛选方式 75
3.2.4 在受保护的工作表中进行筛选 76
3.2.5 清除筛选 77
3.3 完成复杂条件下的筛选 77
3.3.1 高级筛选的条件设置原则 77
3.3.2 执行高级筛选 78
3.3.3 筛选不重复值 78
3.3.4 在其他区域显示筛选结果 79
3.4 察“颜”观“色”完成数据分析 79
3.4.1 突出显示符合条件的单元格 79
3.4.2 突出显示金额最高的3个值 80
3.4.3 使用数据条显示数值大小 81
3.4.4 制作销量对比旋风图 81
3.4.5 用颜色显示数据集中趋势 82
3.4.6 自定义色阶的渐变颜色 82
3.4.7 使用图标展示数据 83
3.4.8 重新分配图标的取值范围 83
3.4.9 隐藏数值只显示图标 84
3.5 数据分类汇总 84
3.5.1 单字段分类汇总 84
3.5.2 多字段分类汇总 86
3.5.3 复制分类汇总结果 86
3.5.4 清除分类汇总 87
3.6 多表合并计算 88
3.6.1 同一工作簿中的多表合并计算 88
3.6.2 选择合并计算的函数 89
3.6.3 让合并计算结果与数据源产生链接 89
拓展练习：分析发货明细表 89
知识总结：用思维导图学习数据分析 93

第4章 强大的多功能数据分析仪
4.1 创建数据透视表 95
4.1.1 数据透视表对数据源的要求 95
4.1.2 创建空白数据透视表 96
4.1.3 创建系统推荐的数据透视表 97
4.1.4 同时创建数据透视表和数据透视图 98
4.2 字段的添加和显示 98
4.2.1 了解数据透视表的核心概念 98
4.2.2 快速添加或删除字段 99
4.2.3 移动字段 100
4.2.4 调整数据透视表字段窗格 101
4.2.5 更改字段名称 101
4.2.6 展开或折叠字段 102
4.2.7 隐藏行字段中的折叠和展开按钮 102
4.2.8 清除数据透视表中的所有字段 102
4.3 数据透视表的基本操作 103
4.3.1 更改数据源 103
4.3.2 刷新数据源 103
4.3.3 更改数据透视表名称 104
4.3.4 快速获得多个数据透视表 104
4.3.5 移动数据透视表 104
4.3.6 删除数据透视表 105
4.4 以更佳的布局方式展示数据 105
4.4.1 数据透视表的3种布局形式 105
4.4.2 重复显示所有项目标签 106
4.4.3 在每个项目后插入空行 107
4.4.4 控制是否显示总计 107
4.4.5 调整分类汇总的显示位置 107
4.4.6 批量为分类汇总行添加底纹 108
4.4.7 查看指定汇总行的明细数据 108
4.5 让数据透视表看起来更美观 109
4.5.1 套用数据透视表样式 109
4.5.2 自定义数据透视表样式 110
4.5.3 清除数据透视表样式 110
拓展练习：创建薪酬管理数据透视表 111
知识总结：用思维导图学习数据分析 114

第5章 多维度动态分析数据
5.1 数据透视表中的计算和汇总 116
5.1.1 对字段分组 116
5.1.2 更改值字段汇总方式 117
5.1.3 更改值的显示方式 117
5.1.4 添加计算字段 118
5.1.5 使用函数创建新字段 119
5.1.6 用数据透视表合并多表数据 119
5.1.7 设置数值的小数位数 121
5.2 在数据透视表中执行排序 122
5.2.1 拖拽法手动排序 122
5.2.2 对指定字段进行排序 123
5.2.3 多个字段同时排序 123
5.2.4 更改文本字段的自动排序依据 124
5.3 在数据透视表中执行筛选 125
5.3.1 筛选行字段 125
5.3.2 筛选值字段 125
5.3.3 筛选分类汇总大于指定值的数据 126
5.3.4 添加筛选字段执行筛选 127
5.3.5 分页显示筛选字段项目 127
5.3.6 分页打印筛选结果 128
5.4 借助筛选工具完成筛选 128
5.4.1 创建切片器 128
5.4.2 设置切片器外观 129
5.4.3 使用切片器筛选数据 129
5.4.4 降序排列切片器中的项目 130
5.4.5 日期字段的专用筛选器 130
5.4.6 日程表的应用方法 131
拓展练习：用数据透视表分析电商销售数据 131
知识总结：用思维导图学习数据分析 136

第6章 高手都在用的数据分析工具
6.1 模拟运算 138
6.1.1 单变量求解 138
6.1.2 使用单变量模拟运算表 139
6.1.3 使用双变量模拟运算表 140
6.2 方案分析 141
6.2.1 方案的创建 141
6.2.2 方案的编辑和删除 142
6.2.3 创建方案摘要 142
6.2.4 方案的保护 143
6.3 规划求解 143
6.3.1 加载规划求解 143
6.3.2 建立规划求解模型 144
6.3.3 使用规划求解 144
拓展练习：计算贷款最佳偿还期限及每期偿还额 145
知识总结：用思维导图学习数据分析 148

数据可视化篇 149
第7章 用图表直观呈现数据分析结果
7.1 图表操作常识 151
7.1.1 认识常用的图表类型 151
7.1.2 创建图表 152
7.1.3 调整图表的大小 153
7.1.4 移动图表 154
7.1.5 更改图表类型 154
7.1.6 创建复合图表 155
7.2 图表的快速布局及样式设置 155
7.2.1 修改图表标题 155
7.2.2 快速更改图表系列颜色 156
7.2.3 设置指定图表系列的颜色 156
7.2.4 交换图表坐标轴 157
7.2.5 添加或删除图表系列 158
7.2.6 设置图表字体 159
7.2.7 快速调整图表布局 159
7.2.8 快速套用图表样式 159
7.3 图表中的重要元素 160
7.3.1 常见的图表元素 160
7.3.2 添加或删除图表元素 161
7.4 用微型图表展示数据趋势 161
7.4.1 迷你图的类型 161
7.4.2 创建迷你图 162
7.4.3 更改迷你图类型 163
7.4.4 设置迷你图样式 163
7.4.5 设置重要标记点的颜色 164
7.4.6 单独编辑一组迷你图中的其中一个 164
7.4.7 清除迷你图 164
拓展练习：制作渐变销售趋势分析图表 165
知识总结：用思维导图学习数据分析 168

第8章 编辑图表元素的不二法门
8.1 饼图系列的典型编辑法 170
8.1.1 设置扇形的填充及边框效果 170
8.1.2 旋转饼图角度 171
8.1.3 设置饼图分离 172
8.2 柱形图的经典设置 173
8.2.1 更改三维柱形图的柱体形状 173
8.2.2 调整柱形的间距和宽度 173
8.2.3 用实物图形替换柱形系列 174
8.2.4 设置透明柱形系列效果 175
8.3 坐标轴的编辑技巧 176
8.3.1 修改垂直轴的取值范围及刻度单位 176
8.3.2 翻转坐标轴 176
8.3.3 设置对数刻度 177
8.3.4 水平轴不显示标签内容的处理方法 177
8.3.5 调整坐标轴值的数字格式 179
8.3.6 以万为单位显示坐标轴值 179
8.4 数据标签的设置 180
8.4.1 将数据标签以百分比形式显示 180
8.4.2 在数据标签中显示单元格中的内容 181
8.4.3 添加数据标注 182
8.5 设置图表背景 182
8.5.1 设置纯色背景 182
8.5.2 设置渐变背景 182
8.5.3 设置图片背景 183
8.5.4 隐藏背景 184
拓展练习：制作销售分析玫瑰图 184
知识总结：用思维导图学习数据分析 189

第9章 数据在图表中的完美演绎
9.1 创意饼图的制作 191
9.1.1 制作阶梯式三维立体饼图 191
9.1.2 制作局部分离的复合饼图 193
9.1.3 制作双层复合饼图 195
9.1.4 制作双层补充说明圆环图 196
9.2 样式繁多的柱形图 197
9.2.1 制作三等分背景板 197
9.2.2 制作色阶对比效果图表 199
9.2.3 制作数据对比旋风图 200
9.2.4 制作具有重要参考线的柱形图 202
9.3 其他典型图表的制作 204
9.3.1 制作带中心发射线的渐变雷达图 204
9.3.2 制作始终显示最新信息的动态图表 206
9.3.3 制作能突出最大值和最小值的折线图 207
拓展练习：制作公司发展时间轴图表 209
知识总结：用思维导图学习数据分析 213

第10章 高大上的数据可视化看板
10.1 销售数据的录入和整理 215
10.1.1 用公式统计销售数据 215
10.1.2 用图标展示完成率和环比 216
10.2 制作实际完成率分析图 217
10.2.1 创建基础图表 217
10.2.2 设置柱形填充效果 218
10.2.3 设置数据标签 220
10.3 制作各店铺完成率分析图 220
10.3.1 制作完成率低于100%的圆环图 220
10.3.2 制作完成率高于100%的圆环图 221
10.4 制作两个季度的销售对比图 222
10.4.1 创建组合图表 222
10.4.2 设置标签格式 223
10.4.3 设置其他图表元素 224
10.5 美化销售数据分析可视化看板 225
10.5.1 设置数据源表格式 225
10.5.2 组合图表 226
拓展练习：制作上半年财务运营数据看板 228
知识总结：用思维导图学习数据分析 232

附录
附录A 完美输出数据报表的方法 234
A.1 打印数据报表和图表 234
A.1.1 设置黑白打印 234
A.1.2 打印网格线 235
A.1.3 设置打印份数 236
A.1.4 从指定位置分页打印 236
A.1.5 控制图表的打印 237
A.1.6 重复打印标题行 238
A.1.7 打印指定区域 238
A.1.8 设置打印范围 239
A.1.9 缩放打印 239
A.1.10 一张纸上打印一列超长内容 240
A.1.11 为打印区域设置背景 241
A.2 设置打印页面 242
A.2.1 设置纸张大小 242
A.2.2 设置横向打印 243
A.2.3 调整页边距 243
A.2.4 设置居中打印 244
A.3 添加打印信息 245
A.3.1 添加打印日期和时间 245
A.3.2 打印logo 246
A.3.3 添加预定义的页眉页脚 247
A.3.4 设置奇偶页不同的页眉页脚 248
A.4 导出数据分析结果 248
A.4.1 将报表导出成电子表格模板 248
A.4.2 将报表导出成PDF文件 249
A.4.3 以邮件形式发送数据报表 249

附录B 不可不知的Excel操作术语 250