第一篇 Excel基本功能 2
第1章 Excel简介 2
1.1 Excel的起源与历史 2
1.2 Excel的主要功能 4
1.3 Excel 2016的主要新特性 7
第2章 Excel工作环境 11
2.1启动Excel程序 11
2.2其他特殊的启动方式 13
2.3使用Microsoft账户登录Excel 14
2.4理解Excel文件的概念 15
2.5 Office Open XML文件格式 18
2.6理解工作簿和工作表的概念 19
2.7认识Excel的工作窗口 19
2.8认识Ribbon功能区 20
2.9通过选项设置调整窗口元素 31
2.10自定义功能区 32
2.11快速访问工具栏 34
2.12快捷菜单和快捷键 36
第3章 工作簿和工作表操作 38
3.1工作簿的基本操作 38
3.2工作表的基本操作 56
3.3工作窗口的视图控制 61
第4章 认识行、列及单元格区域 70
4.1行与列的概念 70
4.2行与列的基本操作 73
4.3单元格和区域 81
第5章 在电子表格中输入和编辑数据 88
5.1数据类型的简单认识 88
5.2输入和编辑数据 91
5.3数据输入实用技巧 101
5.4填充与序列 105
第6章 整理电子表格中的数据 114
6.1为数据应用合适的数字格式 114
6.2处理文本型数字 118
6.3自定义数字格式 121
6.4单元格和区域的复制与粘贴 131
6.5查找和替换 139
6.6单元格的隐藏和锁定 143
第7章 格式化工作表 146
7.1单元格格式 146
7.2单元格样式 154
7.3使用主题 156
7.4工作表背景 158
7.5清除格式 159
7.6数据表格美化 160
第8章 创建和使用模板 162
8.1理解模板的含义 162
8.2默认启动文件夹和模板文件夹 162
8.3自定义启动文件夹 163
8.4更改默认工作簿模板 163
8.5更改默认工作表模板 164
8.6创建自定义模板 165
8.7使用内置模板创建工作簿 166
第9章 打印文件 168
9.1页面设置 168
9.2对象打印设置 175
9.3在工作表之间复制页面设置 176
9.4打印预览 176
9.5分页预览视图和页面布局视图 178
第二篇 使用公式和函数 181
第10章 公式和函数基础 181
10.1认识公式 181
10.1.1公式的输入和编辑 181
10.1.2公式的复制与填充 181
10.2公式中的运算符 183
10.2.1认识运算符 183
10.2.2运算符的优先顺序 183
10.2.3嵌套括号 184
10.3认识单元格引用 184
10.3.1 A1引用样式和R1C1引用样式 185
10.3.2相对引用、绝对引用和混合引用 185
10.3.3跨工作表引用和跨工作簿引用 187
10.3.4表格中的结构化引用 189
10.4认识Excel函数 190
10.4.1函数的概念和特点 190
10.4.2函数的结构 190
10.4.3可选参数与必需参数 190
10.4.4常用函数类型 191
10.4.5函数的易失性 191
10.5输入函数的几种方式 191
10.5.1使用【自动求和】按钮插入函数 191
10.5.2使用函数库插入已知类别的函数 192
10.5.3使用“插入函数”向导搜索函数 192
10.5.4手工输入函数 193
10.5.5活用函数屏幕提示工具 193
10.6查看函数帮助文件 194
10.7使用公式的常见问题 195
10.7.1常见错误列表 195
10.7.2检查公式中的错误 196
10.7.3使用公式审核 196
10.7.4循环引用 198
10.7.5显示公式本身 198
10.7.6自动重算和手动重算 198
10.8公式结果的检验和验证 199
10.8.1简单统计公式结果的验证 199
10.8.2使用〈F9〉键查看运算结果 199
10.9函数与公式的限制 200
10.9.1计算精度限制 200
10.9.2公式字符限制 200
10.9.3函数参数的限制 200
10.9.4函数嵌套层数的限制 200
第11章 使用命名公式——名称 201
11.1认识名称 201
11.1.1名称的概念 201
11.1.2名称的用途 201
11.1.3名称的级别 202
11.2定义名称的方法 202
11.2.1使用名称框定义名称 202
11.2.2使用【新建名称】命令定义名称 202
11.2.3根据所选内容批量创建名称 202
11.2.4在名称管理器中新建名称 203
11.3名称命名的限制 204
11.4名称的管理 204
11.4.1修改名称 204
11.4.2筛选和删除错误名称 205
11.4.3在单元格中粘贴名称列表 205
11.4.4查看命名范围 206
11.5名称的使用 206
11.5.1输入公式时使用名称 206
11.5.2现有公式中使用名称 206
11.6定义名称技巧 208
11.6.1名称中的相对引用和混合引用 208
11.6.2引用位置始终指向当前工作表内的单元格 209
11.7使用名称的注意事项 209
11.7.1工作表复制时的名称问题 209
11.7.2有关删除操作引起的名称问题 209
11.8利用“表”区域动态引用 210
第12章 文本处理技术 211
12.1文本数据的概念 211
12.1.1认识文本数据 211
12.1.2空单元格与空文本 211
12.1.3文本型数字与数值 212
12.2常用文本函数 213
12.2.1文本运算 213
12.2.2使用CHAR函数和CODE函数转换字符与编码 213
12.2.3 CLEAN函数和TRIM函数 214
12.2.4用FIND函数和SEARCH函数查找字符串 214
12.2.5用LEN函数和LENB函数计算字符串长度 216
12.2.6用LEFT、RIGHT和MID函数提取字符串 217
12.2.7用SUBSTITUTE和REPLACE函数替换字符串 219
12.2.8用TEXT函数将数值转换为指定数字格式的文本 221
12.3文本函数综合应用 225
12.3.1合并字符串 225
12.3.2提取子字符串 227
第13章 信息提取与逻辑判断 233
13.1用INFO函数获取当前操作环境信息 233
13.2用CELL函数获取单元格信息 233
13.3 IS类判断函数 236
13.3.1用ISODD函数和ISEVEN函数判断数值奇偶性 236
13.3.2用ISNUMBER函数判断参数是否为数值 237
13.4逻辑判断函数 237
13.4.1逻辑函数与乘法、加法运算 237
13.4.2使用IF函数进行条件判断 238
13.4.3用IFNA函数和IFERROR函数屏蔽错误值 239
第14章 数学计算 240
14.1取余函数 240
14.1.1利用MOD函数计算余数 240
14.1.2用MOD函数生成有规律的循环序列 241
14.2数值取舍函数 241
14.2.1 INT和TRUNC函数 242
14.2.2 ROUNDUP函数和ROUNDDOWN函数 243
14.2.3 CEILING函数和FLOOR函数 243
14.3四舍五入函数 244
14.3.1用ROUND函数对数值四舍五入 244
14.3.2用MROUND函数实现特定条件下的舍入 245
14.3.3四舍六入五成双法则 246
14.4用RAND函数和RANDBETWEEN函数生成随机数 246
14.5数学函数的综合应用 248
14.5.1计扣个人所得税 248
14.5.2数字校验应用 249
14.5.3指定有效数字 250
14.5.4生成不重复随机序列 250
第15章 日期和时间计算 252
15.1认识日期及时间 252
15.1.1日期及时间的本质 252
15.1.2标准日期格式 252
15.1.3快速生成日期或时间 252
15.2常规日期及时间函数 252
15.2.1基本日期函数TODAY、YEAR、MONTH和DAY函数 253
15.2.2日期之间的天数 254
15.2.3返回月末日期 254
15.2.4利用DATE函数返回月末日期 254
15.2.5将英文月份转化为数字 254
15.2.6基本时间函数NOW、TIME、HOUR、MINUTE和SECOND 255
15.2.7计算90分钟之后的时间 255
15.2.8使用鼠标快速填写当前时间 256
15.3星期函数 257
15.3.1用WEEKDAY函数计算某个日期是星期几 257
15.3.2用WEEKNUM函数计算指定日期位于当年第几周 258
15.4用EDATE和EOMONTH函数计算几个月之后的日期 259
15.4.1计算退休日期 259
15.4.2计算合同到期日 260
15.4.3计算每月的天数 260
15.5认识DATEDIF函数 261
15.5.1函数的基本用法 261
15.5.2计算法定年假天数 262
15.5.3计算员工工龄 263
15.5.4生日到期日提醒 263
15.6日期和时间函数的综合运用 264
15.6.1分别提取单元格中的日期和时间 264
15.6.2计算加班时长 264
15.6.3计算母亲节与父亲节日期 265
15.7计算工作日 265
15.7.1计算日期属性 266
15.7.2工作日统计 266
第16章 查找与引用 270
16.1用ROW函数和COLUMN函数返回行号列号信息 270
16.1.1返回当前单元格的行列号 270
16.1.2返回指定单元格的行列号 270
16.1.3返回单元格区域的自然数数组序列 271
16.1.4其他行列函数 271
16.1.5生成等差数列 271
16.2 INDEX函数 272
16.2.1 INDEX函数基础应用 272
16.2.2制作分数条 274
16.3 MATCH函数 275
16.3.1 MATCH函数精确匹配基础应用 275
16.4 INDEX＋MATCH函数组合 276
16.4.1 INDEX＋MATCH常规精确查找 276
16.4.2动态引用照片 277
16.4.3多条件查找 279
16.4.4一对多查询 279
16.4.5目标值含有通配符的查找 280
16.4.6 MATCH函数模糊查找 281
16.5 LOOKUP函数 283
16.5.1 LOOKUP函数常规查找应用 283
16.5.2查询员工最近一次合同签订日期 284
16.5.3使用LOOKUP函数实现多条件查找 285
16.6 VLOOKUP函数 285
16.7 INDIRECT函数 288
16.7.1数据区域转置引用 288
16.7.2根据工作表名称跨工作表查询 289
16.8 OFFSET函数 289
16.8.1 OFFSET偏移方式 290
16.8.2创建动态数据区域 291
第17章 统计与求和 292
17.1基础统计函数SUM、COUNT、COUNTA、AVERAGE、MAX和MIN 292
17.1.1基础统计函数应用 292
17.1.2使用SUM函数实现累计求和 293
17.1.3合并单元格统计 294
17.1.4跨工作表求和 295
17.1.5用MAX结合MIN函数设置上下限 296
17.2其他常用统计函数 296
17.2.1认识LARGE与SMALL函数 296
17.2.2用RANK.EQ和RANK.AVG函数对数据排名 298
17.3条件统计函数 299
17.3.1单条件计数COUNTIF函数 299
17.3.2某单字段同时满足多条件的计数 301
17.3.3验证身份证号是否重复 301
17.3.4中国式排名 302
17.3.5统计非重复值数量 302
17.3.6多条件计数COUNTIFS函数 303
17.3.7多字段同时满足多条件的计数 304
17.3.8单条件求和SUMIF函数 305
17.3.9某单字段同时满足多条件的求和 306
17.3.10多条件求和SUMIFS函数 307
17.3.11多字段同时满足多条件的求和 308
17.3.12 AVERAGEIF函数与AVERAGEIFS函数 308
17.4 SUMPRODUCT函数 309
17.4.1认识SUMPRODUCT函数 309
17.4.2 SUMPRODUCT条件统计计算 311
17.5多种平均值统计 312
17.5.1内部平均值TRIMMEAN函数 312
17.5.2几何平均值GEOMEAN函数 313
17.5.3调和平均值HARMEAN函数 314
17.6筛选和隐藏状态下的统计与求和 314
17.6.1认识SUBTOTAL函数 314
17.6.2认识AGGREGATE函数 317
17.7频率函数FREQUENCY 319
第18章 财务金融函数 321
18.1财务基础相关知识 321
18.1.1货币时间价值 321
18.1.2单利和复利 321
18.1.3现金的流入与流出 321
18.2借贷和投资函数 322
18.2.1未来值函数FV 322
18.2.2现值函数PV 324
18.2.3利率函数RATE 325
18.2.4期数函数NPER 326
18.2.5付款额函数PMT 326
18.3计算本金与利息函数 327
18.3.1每期还贷本金函数PPMT和利息函数IPMT 327
18.3.2累计还贷本金函数CUMPRINC和利息函数CUMIPMT 328
18.3.3制作贷款计算器 329
18.4投资评价函数 330
18.4.1净现值函数NPV 330
18.4.2内部收益率函数IRR 332
18.4.3不定期净现值函数XNPV 333
18.4.4不定期内部收益率函数XIRR 333
第19章 工程函数 335
19.1贝赛尔（Bessel）函数 335
19.2数字进制转换函数 336
19.3用CONVERT函数转换度量衡 338
19.4用ERFC函数计算误差 339
19.5用IMSUM函数处理复数计算 339
第20章 Web类函数 341
20.1 Web类函数简介 341
20.1.1 ENCODEURL函数 341
20.1.2 WEBSERVICE函数 342
20.1.3 FILTERXML函数 342
20.2 Web类函数综合应用 342
20.2.1将有道翻译装进Excel 342
20.2.2将天气预报装进Excel 343
第21章 数组公式 345
21.1理解数组 345
21.1.1 Excel中数组的相关定义 345
21.1.2 Excel中数组的存在形式 345
21.2数组公式与数组运算 346
21.2.1认识数组公式 346
21.2.2多单元格数组公式 347
21.2.3单个单元格数组公式 347
21.2.4数组公式的编辑 348
21.2.5数组的直接运算 348
21.2.6数组的矩阵运算 351
21.3数组构建及填充 351
21.3.1行列函数生成数组 351
21.3.2一维数组生成二维数组 352
21.3.3提取子数组 352
21.3.4填充带空值的数组 354
21.3.5二维数组转换一维数组 355
21.4条件统计应用 355
21.4.1单条件不重复统计 355
21.4.2多条件统计应用 356
21.4.3条件查询及定位 357
21.5数据筛选技术 358
21.5.1一维区域取得不重复记录 358
21.5.2条件提取唯一记录 359
21.5.3二维数据表提取不重复记录 360
21.6利用数组公式排序 361
21.6.1快速中文排序 361
21.6.2根据产品产量进行排序 362
21.7数据表处理技术 363
21.7.1总表拆分应用 363
21.7.2分表合并总表应用 364
第22章 多维引用 367
22.1多维引用的工作原理 367
22.1.1认识引用的维度和维数 367
22.1.2引用函数生成的多维引用 368
22.1.3函数生成的多维引用和“跨多表区域引用”的区别 370
22.2多维引用的应用 370
22.2.1支持多维引用的函数 370
22.2.2多表单条件统计 370
22.2.3多表多条件统计 372
22.2.4另类多条件汇总技术 373
22.2.5筛选条件下提取不重复记录 374
22.2.6根据比赛评分进行动态排名 375
22.2.7先进先出法应用 376
第三篇 创建图表和图形 379
第23章 创建迷你图 379
23.1认识迷你图 379
23.2创建迷你图 380
23.3更改迷你图类型 382
23.4设置迷你图样式 383
23.5清除迷你图 387
第24章 创建图表入门 389
24.1图表及其特点 389
24.2图表的组成 390
24.3创建图表 393
24.4标准图表类型 396
24.5设置图表格式 399
24.6图表模板 413
24.7图表布局与样式 414
第25章 高级图表制作 416
25.1 2016新增图表制作 416
25.2数据重构 422
25.3组合图表 439
25.4动态图表 451
第26章 使用图形和图片增强工作表效果 466
26.1形状 466
26.2图片 468
26.3艺术字 472
26.4 SmartArt 473
26.5条形码 474
26.6文件对象 475
第四篇 使用Excel进行数据分析 478
第27章 在数据列表中简单分析数据 478
27.1了解Excel数据列表 478
27.2数据列表的使用 479
27.3创建数据列表 479
27.4使用“记录单”添加数据 479
27.5删除重复值 480
27.6数据列表排序 483
27.7更多排序方法 487
27.8筛选数据列表 495
27.9使用高级筛选 503
27.10分级显示和分类汇总 511
27.11 Excel的“表格”工具 519
27.12合并计算 526
第28章 使用多样性的数据源 534
28.1了解外部数据库文件 534
28.2利用文本文件获取数据 534
28.3从Access获取外部数据 539
28.4利用Microsoft Query创建查询 541
28.5自网站获取数据 547
28.6 PowerPivot for Excel 2016 549
28.7 Power Query 552
第29章 使用数据透视表分析数据 554
29.1关于数据透视表 554
29.2改变数据透视表的布局 561
29.3设置数据透视表的格式 571
29.4数据透视表的刷新 573
29.5在数据透视表中排序 576
29.6数据透视表的切片器 578
29.7数据透视表的日程表 583
29.8数据透视表的项目组合 586
29.9在数据透视表中执行计算 591
29.10使用透视表函数获取数据透视表数据 596
29.11创建动态的数据透视表 597
29.12利用多种形式的数据源创建数据透视表 599
29.13利用Excel数据模型进行多表分析 607
29.14钻取数据透视表数据 609
29.15创建数据透视图 611
第30章 使用Power View分析数据 615
30.1利用Power View制作仪表盘式报表 615
30.2在Power View中使用自定义图片筛选数据 619
30.3在Power View中创建可自动播放的动画报表 624
第31章 模拟分析和预测工作表 627
31.1模拟运算表 627
31.2使用方案 634
31.3借助单变量求解进行逆向模拟分析 638
31.4预测工作表 641
第32章 规划求解 644
32.1规划求解加载项 644
32.2单纯线性规划 645
32.3投资收益最大化问题 647
32.4规划求解常见问题及解决方法 650
第五篇 使用Excel的高级功能 656
第33章 使用分析工具库分析数据 656
33.1加载分析工具库 656
33.2描述统计分析 658
33.3相关系数分析 659
33.4移动平均分析 660
33.5排位与百分比排位 662
33.6直方图分析 664
33.7双因素方差分析 666
第34章 条件格式 670
34.1认识条件格式 670
34.2设置条件格式 671
34.3管理条件格式 682
第35章 数据验证 685
35.1认识数据验证 685
35.2数据验证的典型应用 687
35.3数据验证的个性化设置 697
35.4修改和清除数据验证规则 698
第36章 链接和超链接 700
36.1链接工作簿 700
36.2超链接 703
第37章 使用Excel高级文本服务 711
37.1语音朗读表格 711
37.2中文简繁体转换 712
37.3多国语言翻译 713
37.4智能查找 714
第六篇 使用Excel进行协同 716
第38章 信息安全控制 716
38.1借助IRM进行信息安全控制 716
38.2保护工作表 716
38.3保护工作簿 718
38.4标记为最终状态 719
38.5数字签名 720
38.6借助“检查文档”保护私有信息 724
38.7发布为PDF或XPS 726
38.8发布为HTML文件 728
第39章 与其他应用程序共享数据 730
39.1 Windows剪贴板和Office剪贴板 730
39.2将Excel数据复制到其他Office应用程序中 731
39.3在其他Office应用程序文档中插入Excel对象 734
39.4在Excel中使用其他Office应用程序的数据 736
39.5使用Excel工作簿作为外部数据源 736
第40章 协同处理Excel数据 737
40.1远程打开或保存Excel文件 737
40.2共享工作簿 738
40.3审阅 743
40.4 Excel工作簿内容比对 745
第41章 云时代的Excel 747
41.1 Excel Online 747
41.2 Excel应用程序 754
第七篇 Excel自动化 757
第42章 初识VBA 757
42.1什么是宏 757
42.2 VBA的版本 757
42.3 VBA的应用场景 758
42.4 VBA与VSTO 758
42.5 Excel 2016中VBA的工作环境 759
42.6在Excel 2016中录制宏代码 767
42.7在Excel 2016中运行宏代码 768
第43章 VBA集成编辑环境 771
43.1 VBE界面介绍 771
43.2在VBE中运行宏代码 775
第44章 VBA编程基础 777
44.1常量与变量 777
44.2运算符 779
44.3过程 780
44.4程序结构 781
44.5对象与集合 783
44.6数组 785
44.7字典对象 785
第45章 与Excel进行交互 786
45.1使用MsgBox输出信息 786
45.2利用InputBox输入信息 789
第46章 自定义函数与加载宏 795
46.1什么是自定义函数 795
46.2函数的参数与返回值 795
46.3在VBA代码中引用工作表函数 796
46.4在工作表中引用自定义函数 797
46.5自定义函数的限制 798
46.6如何制作加载宏 799
第47章 如何操作工作簿、工作表和单元格 801
47.1 Workbook对象 801
47.2 Worksheet对象 806
47.3 Range对象 810
第48章 事件的应用 819
48.1事件过程 819
48.2工作表事件 819
48.3工作簿事件 823
48.4事件的优先级 825
48.5非对象相关事件 826
第49章 控件在工作表中的应用 827
第50章 窗体在Excel中的应用 828
50.1创建自己的第一个用户窗体 828
50.2在用户窗体中使用控件 830
50.3窗体的常用事件 834
附录 837
附录A Excel2016规范与限制 837
附录B Excel2016常用快捷键 840
附录C Excel2016术语简繁英文词汇对照表 845
附录D 高效办公必备工具——Excel易用宝 849