Excel公式、函数、图表与数据处理应用大全（全新版）

本书分别从数据处理需要使用的Excel操作、通过Excel 公式和函数计算数据、通过Excel中的各种工具来管理数据、对需要分析的数据用图形展示、在局域网中和OneDrive 中共享数据五个部分对数据处理的各种知识进行系统讲解。通过对本书的讲解，力求让读者快速掌握使用Excel 进行处理各种数据的操作技能。
本书主要定位于希望快速掌握Excel 数据处理技能的所有读者，特别适用于不同年龄段的办公人员、文秘、财务人员、公务员，也可作为大中专院校、职业院校和各类培训机构学习Excel 数据处理课程的教材。

第一章 数据处理的基本操作1
1.1 认识Excel 2016操作环境2
1.1.1 “文件”选项卡 2
1.1.2 快速访问工具栏 3
1.1.3 标题栏 3
1.1.4 功能区 4
1.1.5 工作表编辑区 5
1.1.6 状态栏和视图栏 6
1.2 自定义Excel操作环境.6
1.2.1 自定义快速访问工具栏 6
1.2.2 自定义功能区 7
1.2.3 禁用或启用浮动工具栏 9
1.2.4 自定义状态栏中的显示项目 10
1.2.5 更换Excel的界面颜色10
1.2.6 自定义视图模式 10
1.2.7 使用文档属性 11
1.3 认识工作簿、工作表和单元格 12
1.3.1 工作簿、工作表和单元格概述 12
1.3.2 工作簿、工作表和单元格的相互关系 13
1.4 工作簿的基本操作 13
1.4.1 创建空白工作簿 14
1.4.2 创建有样式的工作簿 14
1.4.3 打开Excel文件15
1.4.4 关闭Excel文件16
1.4.5 批量关闭工作簿 17
1.4.6 保存工作簿文件 17
1.4.7 工作簿文件窗口的保护与撤销保护 19
1.4.8 为工作簿设置密码保护 20
1.5 工作表的基本操作 21
1.5.1 选择工作表21
1.5.2 插入工作表22
1.5.3 重命名工作表23
1.5.4 移动和复制工作表24
1.5.5 隐藏和显示工作表25
1.5.6 彻底隐藏工作表25
1.5.7 保护工作表26
1.5.8 删除工作表27
1.5.9 限定工作表的可用范围28
1.6 多窗口操作28
1.6.1 并排查看多个工作表28
1.6.2 拆分工作表窗口29
1.6.3 冻结工作表窗口29
1.6.4 快速切换工作簿窗口30
1.7 Excel 2016的新功能介绍.31
1.7.1 获取和转换（查询）31
1.7.2 五种新图表类型31
1.7.3 一键式预测32
1.7.4 数据透视图表功能增强33
1.7.5 多选切片器33
1.7.6 快速形状格式33
1.7.7 Power BI发布和共享分析 34
1.7.8 使用“操作说明搜索”快速执行34
1.7.9 智能查找35
1.7.10 墨迹公式35

第2章 单元格效果的设置操作36
2.1 单元格的基本操作37
2.1.1 选择单元格37
2.1.2 轻松选择特殊区域39
2.1.3 插入单元格39
2.1.4 删除单元格40
2.1.5 单元格的合并与拆分 40
2.2 设置单元格行和列 42
2.2.1 调整单元格行高和列宽 42
2.2.2 隐藏或显示行或列 43
2.3 移动和复制单元格 44

第3章 向表格中录入并编辑数据.46
3.1 了解Excel中的数据类型47
3.1.1 文本型 47
3.1.2 数值型 47
3.1.3 日期和时间型 47
3.1.4 货币型 48
3.1.5 符号 49
3.2 输入普通数据 49
3.3 输入特殊的符号和数据 50
3.3.1 输入特殊符号 50
3.3.2 输入以“0”开头的数据 51
3.3.3 输入11位以上的数字数据 51
3.3.4 输入分数 52
3.4 快速填充数据 53
3.4.1 利用控制柄填充数据 53
3.4.2 使用对话框填充序列数据 55
3.4.3 通过“快速填充”按钮将数据合并为一列 56
3.5 编辑数据57
3.5.1 修改和删除错误的数据 57
3.5.2 更改数据的类型 58
3.5.3 自定义数据格式 58
3.6 查找和替换数据 59
3.6.1 数据的常规查找和替换 60
3.6.2 数据的高级查找和替换 60
3.7 移动和复制数据 61
3.7.1 什么是移动和复制数据 62
3.7.2 拖动鼠标移动和复制数据 62
3.7.3 通过剪贴板移动和复制数据 63
3.7.4 选择性粘贴数据63
3.8 撤销和恢复操作65
3.8.1 撤销操作65
3.8.2 恢复操作65

第4章 几种高级的数据处理方式67
4.1 使用记录单输入数据68
4.1.1 添加记录单按钮68
4.1.2 输入数据记录68
4.1.3 修改和删除数据记录70
4.1.4 查找符合条件的数据记录71
4.2 记忆式输入71
4.3 设置数据输入有效性72
4.3.1 允许范围内的有效性设定72
4.3.2 来源于序列的有效性设定74
4.3.3 自定义有效性74
4.3.4 只允许在连续单元格中录入75
4.3.5 设置输入信息和出错警告76
4.3.6 清除数据验证77

第5章 表格外观样式的美化操作78
5.1 格式化单元格79
5.1.1 单元格格式包括的内容79
5.1.2 使用组设置单元格格式81
5.1.3 使用对话框设置单元格格式82
5.1.4 使用浮动工具栏设置单元格格式84
5.2 美化工作表84
5.2.1 设置工作表标签颜色85
5.2.2 设置工作表背景85
5.3 使用图形对象86
5.3.1 使用剪贴画和图片86
5.3.2 使用形状92
5.3.3 使用艺术字94
5.3.4 使用SmartArt图形 97

第6章 表格数据的打印输出101
6.1 设置页面格式 102
6.1.1 页面设置 102
6.1.2 设置页眉与页脚 104
6.1.3 使用水印 107
6.2 打印预览108
6.2.1 预览表格 108
6.2.2 预览模式下调整页面格式 109
6.3 打印输出109
6.3.1 设置打印区域 109
6.3.2 打印指定区域的表格内容 110
6.3.3 打印整个表格内容 110
6.3.4 重复打印表头 111

第7章 名称的使用114
7.1 名称使用的基础知识 115
7.1.1 为什么要使用单元格名称 115
7.1.2 全局名称和局部名称 116
7.2 为单元格定义名称的几种方法 117
7.2.1 通过编辑栏快速定义名称 117
7.2.2 通过对话框逐个定义名称 118
7.2.3 通过对话框批量定义名称 119
7.3 使用名称121
7.3.1 通过名称快速选择单元格区域 121
7.3.2 引用名称中的数据 121
7.3.3 查看单元格名称的引用内容 122
7.4 管理名称124
7.4.1 修改名称基本信息 124
7.4.2 删除指定名称 125
7.5 使用单元格名称的注意事项 126
7.5.1 定义单元格名称的注意事项 126
7.5.2 工作表复制时的名称问题 126
7.5.3 相同全局和局部名称的编辑问题 127

第8章 公式与函数应用基础 129
8.1 了解引用单元格的类型130
8.1.1 Excel 2016中的单元格引用类型130
8.1.2 各种引用方式介绍130
8.1.3 各种引用范围介绍131
8.2 使用公式快速计算数据131
8.2.1 公式的结构132
8.2.2 公式的运算符及其优先级132
8.2.3 输入公式的方法135
8.2.4 编辑公式136
8.3 认识函数139
8.3.1 函数的结构140
8.3.2 可指定为函数的参数140
8.3.3 函数的类型141
8.4 在Excel中使用函数.141
8.4.1 输入函数141
8.4.2 快速查找需要的函数143
8.4.3 查找函数的具体帮助143
8.5 Excel中常见错误及解决方法.144

第9章 处理逻辑与信息数据 146
9.1 逻辑数据的处理147
9.1.1 判断逻辑真值和逻辑假值147
9.1.2 使用IF()函数对结果进行条件判断148
9.1.3 计算逻辑并集与交集151
9.1.4 使用NOT()函数对数据进行求反操作155
9.1.5 使用IFERROR()函数处理公式中的错误156
9.2 信息数据的处理158
9.2.1 使用CELL()函数返回单元格的信息158
9.2.2 使用ISBLANK()函数判断单元格是否为空白160
9.2.3 使用ISERR()函数和ISERROR()函数判断单元格错误值 162
9.2.4 使用ISNUMBER()函数判断数字163

第10章 处理文本数据.164
10.1 字符串数据的转换 165
10.1.1 使用VALUE()函数将文本转换为数值 165
10.1.2 使用TEXT()函数将数值转换为文本 166
10.1.3 使用LOWER()函数将大写字母转换为小写字母 168
10.1.4 使用UPPER()函数将小写字母转换为大写字母 168
10.1.5 使用PROPER()函数转换字母大小写 169
10.2 字符串数据的合并与比较 170
10.2.1 使用CONCATENATE()函数将多个字符进行合并 170
10.2.2 使用EXACT()函数对多个字符进行比较 171
10.3 字符串数据的查找与替换 172
10.3.1 使用FIND()函数查找指定文本 172
10.3.2 使用SEARCH()函数查找指定文本 173
10.3.3 使用REPLACE()函数替换指定文本 174
10.3.4 使用SUBSTITUTE()函数替换指定文本 175
10.4 获取指定位置的字符 175
10.5 使用REPT()函数快速录入重复字符 177

第11章 处理日期和时间数据179
11.1 了解Excel中的日期系统180
11.2 年、月、日数据的处理181
11.2.1 使用YEAR()函数返回年份182
11.2.2 使用MONTH()函数返回月份183
11.2.3 使用DAY()函数和DAYS360()函数返回日期天数184
11.2.4 使用TODAY()函数返回系统当前日期185
11.2.5 使用DATEDIF()函数计算时间间隔186
11.3 时、分、秒数据的处理187
11.3.1 随意获取时、分、秒188
11.3.2 使用NOW()函数获取当前系统的日期和时间189
11.4 星期数据的处理189
11.4.1 使用WEEKDAY()函数返回指定日期的星期值190
11.4.2 使用WEEKNUM()函数返回指定日期为全年的第几周191
11.4.3 使用NETWORKDAYS.INTL()函数返回除星期日的工作日192

第12章 数据的各种数学和三角处理 194
12.1 数据的求和处理195
12.1.1 使用SUM()函数快速对指定数据求和195
12.1.2 使用SUMIF()函数对数据按指定条件求和196
12.1.3 使用SUMIFS()函数对数据按多条件求和197
12.2 数据的取舍处理198
12.2.1 使用ROUND()函数按位四舍五入199
12.2.2 使用CEILING()函数按条件向上舍入199
12.2.3 使用FLOOR()函数按条件向下舍入 200
12.2.4 使用INT()函数向下取整201
12.3 数据的其他数学处理 201
12.3.1 使用PRODUCT()函数对数据求积 201
12.3.2 使用MOD()函数返回相除两数的余数 202
12.3.3 使用ABS()函数对数据求绝对值 204
12.3.4 使用RAND()函数返回随机数 205
12.4 三角数据的处理 206
12.4.1 正三角函数 206
12.4.2 反三角函数 207

第13章 数据的统计处理 208
13.1 数据的常规统计处理 209
13.1.1 直接获取最值数据 209
13.1.2 返回指定大小位置的最值 211
13.1.3 使用RANK.EQ()函数对数据进行排名 213
13.1.4 使用AVERAGE()函数计算平均值 215
13.1.5 使用COUNT()函数统计数据 216
13.1.6 使用COUNTIF()函数按条件进行统计 217
13.2 数据的数理统计处理 218
13.2.1 使用STDEV.S()函数计算样本标准偏差 218
13.2.2 使用VAR.S()函数估算样本方差 219

第14章 查询、引用与工程数据处理.221
14.1 数据的查询与引用处理 222
14.1.1 使用LOOKUP()函数在指定行/列查找数据222
14.1.2 使用LOOKUP()函数在首行/列查找数据223
14.1.3 按方向查找数据225
14.1.4 使用ADDRESS()函数获取单元格地址226
14.1.5 分别获取单元格的行号和列标227
14.1.6 统计引用区域的行/列数228
14.1.7 使用INDEX()函数在单区域中返回指定内容228
14.1.8 使用INDEX()函数在多区域中引用指定内容229
14.1.9 使用OFFSET()函数从给定的引用参照和偏移量返回新的引用230
14.2 工程数据的处理231
14.2.1 使用GESTEP()函数检测某个数据是否达到临界值231
14.2.2 使用DELTA()函数检测两个数据是否完全相等233
14.2.3 使用CONVERT()函数进行度量转换234

第15章 处理财务数据 235
15.1 处理投资预算数据236
15.1.1 使用FV()函数预测投资效果236
15.1.2 使用PV()函数计算投资现值238
15.1.3 使用NPV()函数计算非固定回报投资239
15.1.4 使用NPER()函数计算投资或贷款的期数241
15.1.5 使用RATE()函数返回年金的各期利率242
15.2 处理本金和利息数据243
15.2.1 使用PMT()函数计算每期还贷额243
15.2.2 使用PPMT()函数计算还款额中的本金 244
15.2.3 使用IPMT()函数计算还款金额中的利息 245
15.3 处理报酬率数据 246
15.3.1 使用IRR()函数计算现金流的内部收益率 246
15.3.2 使用MIRR()函数计算现金流的修正内部收益率 247
15.4 处理资产折旧数据 248
15.4.1 使用DB()函数按工作量法计算折旧值 248
15.4.2 使用DDB()函数按双倍余额递减法计算折旧值 250
15.4.3 使用SYD()函数按年限总和折旧法计算折旧值 252

第16章 常规的数据管理操作253
16.1 按条件进行数据排序 254
16.1.1 单列排序 254
16.1.2 多列排序 255
16.2 对数据进行序列排序 256
16.2.1 应用系统内置的序列排序 256
16.2.2 自定义序列排序 258
16.3 数据排序的高级操作 259
16.3.1 数字和字母混合字段的数据排序 259
16.3.2 快速返回排序前的状态 260
16.3.3 对合并单元格数据进行排序 260
16.4 按条件进行数据筛选 262
16.4.1 自动筛选 262
16.4.2 自定义筛选 263
16.4.3 高级筛选 264
16.5 数据筛选的高级操作 266
16.5.1 按照日期的特征筛选 266
16.5.2 包含多重标题行的自动筛选268
16.5.3 对合并单元格数据进行筛选268
16.5.4 对自动筛选结果重新编号271
16.6 数据分类汇总272
16.6.1 快速创建分类汇总并按级别查看汇总结果273
16.6.2 创建多个汇总字段的分类汇总274
16.6.3 创建多个汇总方式的分类汇总276
16.6.4 将汇总数据分页276
16.6.5 隐藏或显示明细数据277
16.6.6 删除分类汇总279
16.7 删除表格中的重复项280
16.7.1 直接删除重复项280
16.7.2 利用筛选功能删除重复项281
16.8 数据的假设分析282
16.8.1 使用方案管理器配置方案282
16.8.2 使用模拟运算表分析数据285
16.8.3 一键式预测287
16.9 合并计算数据288
16.9.1 按位置合并计算289
16.9.2 按类别合并计算290
16.9.3 在合并计算中使用多种计算方式291
16.9.4 在合并计算中使用通配符294
16.9.5 利用合并计算核对文本型数据295

第17章 利用数据分析工具管理数据 298
17.1 载入分析工具库299
17.2 常用分析工具的应用300
17.2.1 方差分析300
17.2.2 相关系数305
17.2.3 回归分析 306
17.2.4 移动平均预测 309
17.2.5 指数平滑预测 311
17.3 规划求解数据 311
17.3.1 规划求解线性拟合 312
17.3.2 求解最佳数字组合 314
17.3.3 规划求解常见问题及解决方法 315

第18章 将数据图形化的基础317
18.1 不同的图表类型展示不同的关系数据 318
18.1.1 图表与数量的关系 318
18.1.2 图表与趋势的关系 319
18.1.3 图表与占比的关系 321
18.1.4 其他关系的图表应用 322
18.2 创建一个完整的图表 326
18.2.1 图表的基本组成 326
18.2.2 创建图表 327
18.2.3 为图表添加标题 329
18.2.4 调整图表的大小 329
18.3 迷你图的使用 330
18.3.1 迷你图与图表的区别 330
18.3.2 创建迷你图 331
18.3.3 迷你图应用样式 331
18.3.4 设置迷你图的显示选项 332
18.3.5 更改迷你图类型 332
18.3.6 清除迷你图 333

第19章 设置图表格式.335
19.1 编辑图表的常规操作 336
19.1.1 更改图表类型 336
19.1.2 添加数据标签 337
19.1.3 添加和隐藏网格线 338
19.2 设置坐标轴格式 339
19.2.1 添加坐标轴标题 339
19.2.2 自定义坐标轴刻度340
19.2.3 添加次要坐标轴341
19.3 操作图表数据源341
19.3.1 更改图表数据源342
19.3.2 切换行列343
19.3.3 向图表中添加数据343
19.4 在图表中使用趋势线345
19.4.1 哪些图表类型可以添加趋势线345
19.4.2 在图表中添加趋势线346
19.4.3 自定义趋势线格式347
19.5 美化图表的外观348
19.5.1 套用自动内置图表样式348
19.5.2 填充图表形状349
19.5.3 设置图表形状效果350
19.5.4 更改布局样式350
19.5.5 设置艺术效果的图表标题351

第20章 图表的高级操作352
20.1 处理图表353
20.1.1 制作不可编辑的图表353
20.1.2 将多张图表放到同一图表工作表中354
20.1.3 制作组合图表355
20.2 数据系列和坐标轴的操作356
20.2.1 利用图片更换数据系列356
20.2.2 处理负数357
20.2.3 除去分类轴上的空白日期358
20.3 处理折线图中的数据358
20.3.1 断裂折线图的处理358
20.3.2 始终显示最值数据359
20.4 饼图的高级设置360
20.4.1 分离饼图361
20.4.2 定义复合饼图第二绘图区显示数据362

第21章 Excel图表与其他Office组件的应用.365
21.1 Excel图表与Word结合366
21.1.1 在Word中嵌入Excel图表 366
21.1.2 让Word中的图表与数据源同步更新 370
21.2 Excel图表与PPT结合371
21.2.1 在PPT中嵌入Excel图表 371
21.2.2 直接在幻灯片中创建Excel图表372
21.2.3 以可编辑的形状引入Excel图表372
21.2.4 使用PPT动画逐步播放Excel图表374

第22章 数据的透视处理.377
22.1 使用数据透视表 378
22.1.1 数据透视表基础 378
22.1.2 创建数据透视表 378
22.1.3 设计布局和格式 382
22.1.4 更改数据透视表的汇总方式 384
22.1.5 数据明细的显示和隐藏 384
22.1.6 刷新数据透视表中的数据 385
22.2 使用切片器385
22.2.1 在数据透视表中创建切片器 386
22.2.2 设置切片器的格式 387
22.2.3 断开切片器 388
22.2.4 删除切片器 389
22.3 使用数据透视图 389
22.3.1 数据透视图的作用 390
22.3.2 数据透视表和数据透视图之间的关系 390
22.3.3 根据数据透视表创建数据透视图 390
22.3.4 直接创建数据透视图 391
22.3.5 设置数据透视图 391
22.3.6 使用数据透视图中的按钮动态显示指定数据393
22.4 使用PowerView动态图表.394
22.4.1 添加PowerView加载项 394
22.4.2 认识PowerView主界面的结构396
22.4.3 向画布区添加PowerView数据397
22.4.4 设置PowerView表样式 398
22.4.5 切换可视化效果401
22.4.6 通过筛选器控制可视化区域中的内容404
22.4.7 调整PowerView图表的布局 405
22.4.8 创建组合图形408

第23章 外部数据的导入与导出411
23.1 获取外部数据412
23.1.1 导入文本文件数据412
23.1.2 导入Access数据414
23.1.3 导入网站数据415
23.1.4 导入SQL Server数据 416
23.1.5 导入XML数据 417
23.1.6 利用数据连接向导导入数据418
23.1.7 利用现有连接导入数据419
23.2 将Excel数据导出到其他程序.420
23.2.1 使用剪贴板将Excel数据导出420
23.2.2 将映射的XML数据导出 422

第24章 共享和审阅数据423
24.1 Excel文件的访问权限424
24.2 共享工作簿424
24.2.1 设置工作簿共享424
24.2.2 对共享工作簿设置密码保护425
24.2.3 实现工作簿的部分共享426
24.2.4 撤销共享工作簿 427
24.3 使用批注428
24.3.1 插入批注 428
24.3.2 查看批注 428
24.3.3 编辑批注 429
24.3.4 删除批注 429
24.4 使用修订430
24.4.1 突出显示修订 430
24.4.2 接受或拒绝修订 432

第25章 Excel协同工作的高级操作434
25.1 超链接435
25.1.1 创建超链接 435
25.1.2 打开超链接 438
25.1.3 编辑超链接 438
25.1.4 删除超链接 440
25.2 电子邮件440
25.2.1 以电子邮件方式发送工作簿文件 440
25.2.2 在邮件中添加附加项目442
25.2.3 对邮件进行签名443

第26章 OneDrive云端共享 445
26.1 注册Microsoft账户446
26.1.1 通过IE浏览器注册446
26.1.2 通过登录界面注册448
26.2 将文件上传到OneDrive.449
26.2.1 登录Microsoft账户449
26.2.2 直接在网站进行资源上传450
26.2.3 通过Excel程序另存到OneDrive451
26.2.4 Microsoft OneDrive桌面应用上传452
26.3 通过OneDrive实现共享.453
26.3.1 以邀请他人的方式共享：454
26.3.2 以电子邮件的方式共享454

附录：Excel 2016中的特有函数456