领悟专家技巧，释放Excel潜能
如果只是使用Excel来创建简单的表格和图表，现在是时候深挖这个软件的真正功能了：公式。本书是一本理想的指南，介绍了公式的方方面面，向读者展示了Excel的强大功能。
利用本书的提示和技巧，读者的工作流程将变得更快、更轻松、更准确。本书介绍的专家技巧是在别的地方找不到的。无论是初学者还是不错用户，这本书都是一本重要参考，可带领读者深入Excel，优选地利用Excel 2016版本的功能。

迈克尔·亚历山大，拥有MCAD(微软认证应用开发专家)认证资格，有超过15年的Microsoft Office解决方案咨询与开发经验。Michael还是一名Microsoft MVP。
迪克·库斯莱卡，已连续12年荣膺Microsoft MVP称号，拥有逾20年的Microsoft Office使用经验，为客户开发基于Access和Excel的解决方案。

第Ⅰ部分基础知识
章Excel用户界面概述3
1.1Excel的工作方式3
1.1.1工作表4
1.1.2图表工作表5
1.1.3宏表和对话表5
1.2Excel用户界面5
1.2.1功能区5
1.2.2后台视图7
1.2.3快捷菜单和浮动工具栏7
1.2.4对话框7
1.2.5自定义用户界面7
1.2.6任务窗格8
1.2.7自定义屏幕显示8
1.2.8数字格式化8
1.2.9样式格式化9
1.3保护选项9
1.3.1保护整个工作簿的访问9
1.3.2限制对特定工作表范围的
访问12
1.3.3保护工作簿结构15
第2章公式基本内容17
2.1输入和编辑公式17
2.1.1公式元素17
2.1.2输入公式18
2.1.3粘贴名称19
2.1.4空格和空行20
2.1.5公式的限制20
2.1.6公式示例20
2.1.7编辑公式21
2.2在公式中使用运算符22
2.2.1引用运算符23
2.2.2运算符的优先级24
2.3计算公式26
2.4单元格和范围引用27
2.4.1创建绝对引用或混合引用27
2.4.2引用其他工作表或工作簿29
2.5复制或移动公式30
2.6准确地复制公式31
2.7把公式转换成值32
2.8隐藏公式34
2.9公式中的错误35
2.10处理循环引用35
2.11单变量求解37
2.11.1单变量求解示例37
2.11.2更多有关单变量求解的内容38
第3章使用名称39
3.1名称的定义39
3.2名称的作用域40
3.2.1引用名称41
3.2.2引用其他工作簿的名称41
3.2.3名称冲突42
3.3名称管理器42
3.3.1创建名称43
3.3.2编辑名称43
3.3.3删除名称43
3.4创建单元格和范围名称的
快捷方法44
3.4.1“新建名称”对话框44
3.4.2使用名称框创建名称45
3.4.3从单元格中的文本创建名称46
3.4.4命名整个行和列46
3.4.5Excel创建的名称47
3.5创建多表名称48
3.6使用范围和单元格名称49
3.6.1创建一个名称列表49
3.6.2在公式中使用名称50
3.6.3对名称使用的交叉运算符51
3.6.4使用带名称的范围运算符52
3.6.5引用多单元格命名范围中的
单个单元格53
3.6.6在现有公式中使用名称53
3.6.7创建公式时自动应用名称54
3.6.8取消名称的应用54
3.6.9有错误的名称55
3.6.10查看命名范围55
3.6.11在图表中使用名称55
3.7Excel维护单元格和
范围名称的方式56
3.7.1插入行或列56
3.7.2删除行或列56
3.7.3剪切和粘贴56
3.8名称中的潜在问题56
3.8.1复制工作表时的名称问题57
3.8.2删除工作表时的名称问题57
3.9理解名称的奥秘58
3.9.1命名常量59
3.9.2命名文本常量59
3.9.3在命名公式中使用
工作表函数60
3.9.4在命名公式中使用单元格和
范围引用61
3.9.5使用包含相对引用的
命名公式62
3.10使用名称的不错技术64
3.10.1使用INDIRECT函数
处理命名范围64
3.10.2在命名公式中使用数组66
3.10.3创建一个动态的命名公式67
3.10.4在命名公式中使用XLM宏68
第Ⅱ部分在公式中使用函数
第4章工作表函数入门73
4.1什么是函数73
4.1.1简化公式73
4.1.2执行其他方法无法实现的计算74
4.1.3提高编辑任务的速度74
4.1.4实现判断功能74
4.1.5其他函数75
4.2函数参数类型75
4.2.1将名称用作参数76
4.2.2把整行或整列作为参数76
4.2.3把字面值作为参数77
4.2.4把表达式作为参数77
4.2.5把其他函数作为参数77
4.2.6把数组作为参数78
4.3在公式中输入函数的方法78
4.3.1手工输入函数78
4.3.2使用“函数库”命令79
4.3.3使用“插入函数”对话框
输入函数80
4.3.4输入函数的其他技巧82
第5章处理文本85
5.1文本的概念85
5.1.1一个单元格可以容纳的
字符数85
5.1.2把数字作为文本85
5.2文本函数87
5.2.1确定单元格中是否包含文本87
5.2.2使用字符代码88
5.2.3确定两个字符串是否相同90
5.2.4连接两个或多个单元格90
5.2.5把格式化的值显示成文本91
5.2.6把格式化的货币值显示为
文本93
5.2.7删除额外的空格和非打印字符93
5.2.8计算字符串中的字符个数93
5.2.9重复字符或字符串94
5.2.10创建文本直方图94
5.2.11填充数字95
5.2.12改变文本的大小写96
5.2.13从字符串中提取字符97
5.2.14用其他文本替换文本97
5.2.15在字符串中查找和搜索98
5.2.16在字符串中进行查找和替换99
5.3不错文本公式99
5.3.1计算单元格中指定字符的
个数99
5.3.2计算单元格中指定子串的
个数100
5.3.3删除尾部的减号100
5.3.4把数字表示成序数词100
5.3.5为列数确定列字母101
5.3.6从具体路径中提取文件名101
5.3.7提取字符串的个词102
5.3.8提取字符串的最后一个单词102
5.3.9提取字符串中除个词
以外的所有词102
5.3.10提取名字的名、中间
名和姓103
5.3.11删除姓名中的称谓104
5.3.12计算单元格中词的数量105
第6章处理日期和时间107
6.1Excel如何处理日期和时间107
6.1.1了解日期序列号107
6.1.2输入日期108
6.1.3理解时间序列号110
6.1.4输入时间111
6.1.5日期和时间的格式化112
6.1.6有关日期的问题114
6.2日期函数115
6.2.1显示当前日期116
6.2.2使用函数显示任意日期117
6.2.3生成一系列日期117
6.2.4把非日期字符串转换为日期119
6.2.5计算两个日期之间的天数119
6.2.6计算两日期之间的工作日数120
6.2.7计算指定工作日天数的日期121
6.2.8计算两日期之间的年数121
6.2.9计算人的年龄122
6.2.10确定在某年中的天数123
6.2.11确定某天是星期几123
6.2.12确定在一年中的周数124
6.2.13确定最近星期日的日期124
6.2.14确定某个日期后面的个
星期的某一天的日期124
6.2.15确定某月中第n个星期的
某一天的日期125
6.2.16计算星期日期的出现次数125
6.2.17把日期表示为序数126
6.2.18计算节假日期127
6.2.19确定某月的最后一天129
6.2.20确定某年是否是闰年129
6.2.21确定某个日期的季度130
6.2.22把年份转换成罗马数字130
6.3时间函数130
6.3.1显示当前时间131
6.3.2使用函数显示任何时间131
6.3.3计算两个时间的差132
6.3.4合计超过24小时的时间133
6.3.5转换军事时间135
6.3.6把带小数点的小时、分钟
或秒转换成时间136
6.3.7在时间中添加小时数、
分钟数或秒数136
6.3.8时区之间的转换137
6.3.9时间值的舍入138
6.3.10计算期间值138
第7章计数与求和141
7.1工作表单元格的计数与求和141
7.2其他计数方法143
7.3基本计数公式143
7.3.1统计单元格的总数144
7.3.2统计空单元格的个数144
7.3.3非空单元格计数145
7.3.4数字单元格计数145
7.3.5文本单元格计数145
7.3.6非文本单元格计数145
7.3.7逻辑值计数145
7.3.8某个范围中的错误值计数145
7.4不错计数公式146
7.4.1使用COUNTIF函数统计
单元格数量146
7.4.2统计满足多个条件的
单元格数量147
7.4.3出现频率最高的项的计数150
7.4.4确定特定文本的出现频率151
7.4.5统计唯一值的数目153
7.4.6创建频率分布154
7.5求和公式160
7.5.1对范围内的所有单元格求和160
7.5.2对包含错误的范围求和161
7.5.3计算累计和161
7.5.4求n个优选值的和162
7.6使用单个条件求和163
7.6.1只对负值求和164
7.6.2根据范围Difference中的
值求和165
7.6.3基于文本的比较结果求和165
7.6.4基于日期的比较结果求和165
7.7使用多重条件求和165
7.7.1使用AND条件166
7.7.2使用OR条件167
7.7.3使用AND和OR条件167
第8章使用查找函数169
8.1什么是查找公式169
8.2与查找相关的函数170
8.3基本查找函数171
8.3.1VLOOKUP函数171
8.3.2HLOOKUP函数173
8.3.3LOOKUP函数173
8.3.4组合使用MATCH和
INDEX函数175
8.4专业查找公式176
8.4.1精确查找177
8.4.2查找值的左侧178
8.4.3进行区分大小写的查找179
8.4.4在多个查找表中进行选择179
8.4.5确定考试分数的等级180
8.4.6计算等级平均分(GPA)181
8.4.7进行双向查找182
8.4.8进行双列查找183
8.4.9确定范围中值的地址184
8.4.10使用最接近匹配查找
一个值184
8.4.11使用线性插值法查找
一个值185
第9章表格和列表189
9.1表格和术语189
9.1.1列表示例190
9.1.2表格示例190
9.2处理表格191
9.2.1创建表格192
9.2.2改变表格的外观193
9.2.3在表格中导航和选择193
9.2.4添加新行或新列194
9.2.5删除行或列194
9.2.6移动表格195
9.2.7从表格中删除重复的行195
9.2.8表格的排序和筛选196
9.2.9处理汇总行201
9.2.10在表格中使用公式202
9.2.11引用表格中的数据204
9.2.12将表格转换为列表208
9.3使用不错筛选功能208
9.3.1设置条件范围209
9.3.2应用不错筛选209
9.3.3清除不错筛选211
9.4指定不错筛选条件211
9.4.1指定单一条件211
9.4.2指定多重条件213
9.4.3指定计算条件215
9.5使用数据库函数216
9.6插入分类汇总217
0章其他计算221
10.1单位转换221
10.2舍入数字224
10.2.1基本舍入公式225
10.2.2舍入到最近倍数226
10.2.3舍入货币值226
10.2.4处理以分数表示的
美元值227
10.2.5使用INT和TRUNC
函数228
10.2.6舍入为一个偶数或奇数228
10.2.7舍入为n位有效数字229
10.3解直角三角形229
10.4面积、表面积、周长和
体积的计算231
10.4.1计算正方形的面积和周长231
10.4.2计算矩形的面积和周长232
10.4.3计算圆的面积和周长232
10.4.4计算梯形的面积232
10.4.5计算三角形的面积232
10.4.6计算球体的表面积和体积233
10.4.7计算立方体的表面积和
体积233
10.4.8计算长方体的表面积和
体积233
10.4.9计算圆锥体的表面积和
体积233
10.4.10计算圆柱体的体积234
10.4.11计算棱椎的体积234
10.5解联立方程组234
10.6处理正态分布235
第Ⅲ部分财务公式
1章借贷和投资公式241
11.1货币时值241
11.2贷款计算242
11.2.1用于计算贷款信息的
工作表函数242
11.2.2贷款计算示例245
11.2.3信用卡还款246
11.2.4创建贷款分期偿还时间表248
11.2.5计算不定期还款的贷款249
11.3投资计算251
11.3.1单笔存款的未来值251
11.3.2一系列收益的现值256
11.3.3一系列存款的未来值256
2章贴现和折旧计算公式259
12.1使用NPV函数259
12.1.1NPV的定义260
12.1.2NPV函数示例261
12.2使用IRR函数265
12.2.1回报率266
12.2.2几何增长率267
12.2.3检查结果267
12.3不定期的现金流268
12.3.1净现值269
12.3.2内部回报率270
12.4折旧计算270
3章财务计划275
13.1创建财务计划275
13.2创建分期偿还计划275
13.2.1简单的分期偿还计划276
13.2.2动态的分期偿还计划278
13.2.3信用卡计算280
13.3使用数据表汇总贷款项282
13.3.1创建单变量数据表282
13.3.2创建双变量数据表283
13.4财务决算和比率285
13.4.1基本财务决算285
13.4.2比率分析288
13.5创建指数290

第Ⅳ部分数组公式
4章数组295
14.1数组公式295
14.1.1多单元格数组公式296
14.1.2单个单元格数组公式297
14.1.3创建数组常量298
14.1.4数组常量元素299
14.2理解数组的维数299
14.2.1一维横向数组299
14.2.2一维纵向数组300
14.2.3二维数组300
14.3命名数组常量301
14.4使用数组公式303
14.4.1输入数组公式303
14.4.2选择数组公式范围303
14.4.3编辑数组公式303
14.4.4扩展或压缩多单元格
数组公式305
14.5使用多单元格数组公式305
14.5.1从一个范围的值中
创建数组305
14.5.2从一个范围的值中创建
数组常量306
14.5.3对数组执行运算306
14.5.4对数组使用函数307
14.5.5数组转置308
14.5.6生成一个连续整数的数组309
14.6使用单个单元格数组公式310
14.6.1范围中的字符计数310
14.6.2范围中最小的三个
数值求和311
14.6.3范围中文本单元格的计数312
14.6.4省略中间公式313
14.6.5使用数组代替范围引用315
5章使用数组公式的技巧317
15.1使用单个单元格数组公式317
15.1.1对包含错误的范围
进行求和317
15.1.2范围中错误值的计数318
15.1.3范围中n个优选值的求和319
15.1.4计算非零数的平均值319
15.1.5确定范围中是否包含
特定值320
15.1.6两个范围中不同单元格的
计数321
15.1.7返回范围中优选值的位置323
15.1.8查找范围中某个值第n次
出现的行323
15.1.9返回范围中的最长文本323
15.1.10确定一个范围是否包含
有效值324
15.1.11整数数字求和325
15.1.12舍入值求和326
15.1.13对范围中相隔n个数的
数值求和327
15.1.14从字符串中删除非
数字字符328
15.1.15确定范围中最接近的
数值329
15.1.16返回一列中的最后
一个数值330
15.1.17返回一行中的最后
一个数值330
15.2使用多单元格数组公式331
15.2.1只返回范围中的正数331
15.2.2返回范围中的非空单元格332
15.2.3反转范围中单元格的顺序333
15.2.4对一个范围的值动态排序334
15.2.5返回范围中唯一元素的
列表334
15.2.6在范围中显示日历335
第Ⅴ部分其他公式
6章导入和清洗数据341
16.1关于数据341
16.2导入数据341
16.2.1从文件导入342
16.2.2将文本文件导入到
指定范围344
16.2.3复制并粘贴数据345
16.3数据清洗技术346
16.3.1删除重复行346
16.3.2标识重复行347
16.3.3拆分文本348
16.3.4改变文本大小写353
16.3.5删除多余的空格354
16.3.6删除乱字符354
16.3.7转换值355
16.3.8对值进行分类355
16.3.9连接列357
16.3.10重新安排列357
16.3.11随机化行358
16.3.12在列表中匹配文本358
16.3.13将纵向数据转换为
横向数据359
16.3.14填充导入报告中的空隙361
16.3.15拼写检查362
16.3.16替换或删除单元格中的
文本362
16.3.17向单元格添加文本364
16.3.18修复尾部的负号364
16.4数据清洗检查表365
16.5导出数据365
16.5.1导出到文本文件366
16.5.2导出到其他文件格式366
7章图表技术369
17.1理解SERIES公式369
17.1.1在SERIES公式中
使用名称371
17.1.2断开图表系列与其数据
范围的链接371
17.2创建与单元格的链接372
17.2.1添加图表标题链接372
17.2.2添加坐标轴标题链接373
17.2.3添加文本链接373
17.2.4为图表添加链接的图片374
17.3图表示例374
17.3.1单数据点图表374
17.3.2在柱形图表中显示带
条件的颜色376
17.3.3创建比较直方图378
17.3.4创建甘特图379
17.3.5创建箱式图381
17.3.6绘出每隔n个点的数据383
17.3.7在图表中标识优选值和
最小值384
17.4创建时间轴385
17.4.1绘制数学函数386
17.4.2绘制圆390
17.4.3创建钟表图393
17.4.4创建卓越的设计394
17.5使用趋势线396
17.5.1线性趋势线396
17.5.2非线性趋势线402
17.5.3趋势线方程总结403
17.6创建交互式图表404
17.6.1从下拉列表中选择系列404
17.6.2绘制最后n个数据点404
17.6.3选择起始日期和点的数量405
17.6.4显示人口数据406
17.6.5显示天气数据407
8章数据透视表411
18.1数据透视表简介411
18.2数据透视表示例412
18.3适合数据透视表的数据414
18.4自动创建数据透视表416
18.5手工创建数据透视表417
18.5.1指定数据417
18.5.2指定数据透视表的位置418
18.5.3数据透视表的布局420
18.5.4格式化数据透视表421
18.5.5修改数据透视表423
18.6更多的数据透视表示例425
18.6.1问题1：每个支行每天的
新存款金额有多少？425
18.6.2问题2：一星期中哪天的
存款最多？425
18.6.3问题3：按账户类型分，每
个支行开了多少账户？426
18.6.4问题4：不同账户类型的
金额分布情况如何？426
18.6.5问题5：柜员新开的哪类
账户最多？427
18.6.6问题6：“中心”支行与其他两
个支行相比，情况怎样？428
18.6.7问题7：哪个支行为新客户
开的支票账户最多？429
18.7组合数据透视表中的项429
18.7.1手工组合示例430
18.7.2查看组合的数据431
18.7.3自动组合示例432
18.8创建频率分布436
18.9创建计算字段或计算项437
18.9.1创建计算字段439
18.9.2插入计算项441
18.10使用切片器筛选数据
透视表443
18.11使用日程表筛选数据
透视表444
18.12引用数据透视表中的
单元格446
18.13另一个数据透视表示例447
18.14使用数据模型450
18.15创建数据透视图453
18.15.1数据透视图示例453
18.15.2关于数据透视图的
更多内容455
9章条件格式457
19.1条件格式简介457
19.2指定条件格式459
19.2.1可以应用的格式类型459
19.2.2定义自己的规则460
19.3使用图形的条件格式461
19.3.1使用数据条461
19.3.2使用色阶462
19.3.3使用图标集465
19.4创建基于公式的规则468
19.4.1理解相对和绝对引用469
19.4.2条件格式公式示例470
19.5使用条件格式476
19.5.1管理规则477
19.5.2复制包含条件格式的
单元格477
19.5.3删除条件格式477
19.5.4定位包含条件格式的
单元格478
第20章数据验证479
20.1数据验证简介479
20.2指定验证条件480
20.3可应用的验证条件类型481
20.4创建下拉列表483
20.5将公式用于数据验证规则483
20.6理解单元格引用484
20.7数据验证公式示例485
20.7.1只接受文本485
20.7.2接受比前一个单元格大的
数值486
20.7.3只接受不重复的输入项486
20.7.4接受以特定字符开头的
文本486
20.7.5只接受是星期几的日期487
20.7.6只接受不超过总和的值487
20.7.7创建有依赖关系的列表487
20.7.8使用结构化的表引用489
第21章创建大公式491
21.1什么是大公式491
21.2创建大公式：一个简单的
示例492
21.3大公式示例494
21.3.1使用大公式删除中间名494
21.3.2使用大公式返回字符串中最
后一个空格字符的位置498
21.3.3使用大公式判断信用卡
号码的有效性501
21.3.4使用中间命名公式505
21.3.5生成随机姓名506
21.4大公式的优缺点507
第22章调试公式的工具和方法509
22.1什么是公式调试509
22.2公式的问题及其解决办法510
22.2.1括号不匹配510
22.2.2用#填充的单元格511
22.2.3空白单元格并非空白511
22.2.4额外的空格字符512
22.2.5返回错误的公式512
22.2.6绝对/相对引用问题517
22.2.7运算符优先级问题517
22.2.8公式不能计算518
22.2.9实际数值与显示的数值519
22.2.10浮点数错误520
22.2.11幻影链接错误520
22.2.12逻辑值错误521
22.2.13循环引用错误522
22.3Excel的审核工具522
22.3.1识别特定类型的单元格522
22.3.2查看公式523
22.3.3跟踪单元格关系524
22.3.4跟踪错误值526
22.3.5纠正循环引用错误526
22.3.6使用后台错误检查特性526
22.3.7使用Excel的公式求值器527
第Ⅵ部分开发自定义工作表函数
第23章VBA简介531
23.1宏的基本概念531
23.1.1激活“开发工具”选项卡531
23.1.2录制宏532
23.1.3理解启用宏的扩展名534
23.1.4Excel中宏的安全性534
23.1.5受信任位置535
23.1.6在个人宏工作簿中保存宏535
23.1.7将宏指定给按钮和其他
窗体控件536
23.1.8将宏放到快速访问工具栏537
23.2使用VisualBasic编辑器538
23.2.1了解VBE的组件538
23.2.2使用“工程”窗口539
23.2.3使用代码窗口541
23.2.4自定义VBA环境543
第24章VBA编程概念549
24.1Excel对象模型简述549
24.1.1理解对象550
24.1.2理解集合550
24.1.3理解属性551
24.1.4理解方法551
24.1.5变量概述552
24.1.6错误处理554
24.1.7在代码中使用注释556
24.2一个函数过程示例557
24.3使用VBA的内置函数559
24.4控制执行560
24.4.1If-Then结构560
24.4.2SelectCase结构562
24.4.3循环指令块563
24.5使用范围567
24.5.1ForEach-Next结构567
24.5.2引用范围568
24.5.3一些有用的范围属性570
24.5.4Set关键字573
24.5.5lntersect函数573
24.5.6Union函数574
24.5.7UsedRange属性574
第25章函数过程基础577
25.1为什么要创建自定义函数577
25.2VBA函数的示例578
25.3关于函数过程579
25.3.1声明函数579
25.3.2选择函数名580
25.3.3在公式中使用函数581
25.3.4使用函数参数582
25.4使用“插入函数”对话框582
25.4.1添加函数说明583
25.4.2指定函数类别584
25.4.3添加参数说明586
25.5测试和调试函数586
25.5.1使用VBA的MsgBox语句587
25.5.2在代码中使用Debug.Print
语句588
25.5.3从子过程中调用函数589
25.5.4在函数中设置断点591
24.6为函数创建加载项592
第26章VBA自定义函数示例595
26.1简单函数595
26.1.1确定单元格是否隐藏596
26.1.2返回工作表名称596
26.1.3返回工作簿名称597
26.1.4返回应用程序名称597
26.1.5返回Excel的版本号597
26.1.6返回单元格的格式信息598
26.2确定单元格的数据类型599
26.3多功能函数600
26.4生成随机数603
26.4.1生成不变的随机数603
26.4.2随机选择单元格604
26.5计算销售佣金605
26.5.1用于简单佣金结构的函数606
26.5.2用于复杂佣金结构的函数607
26.6文本处理函数608
26.6.1反转字符串608
26.6.2搅乱文本608
26.6.3返回首字母缩写词609
26.6.4检查文本是否与模式匹配609

26.6.5检查单元格中是否包含
某个特定单词610
26.6.6检查单元格是否包含文本611
26.6.7从字符串中提取第n个
元素612
26.6.8拼写一个数字613
26.7计数函数613
26.7.1计算与模式匹配的
单元格数614
26.7.2计算工作簿中的工作表
个数614
26.7.3计算范围中的单词数614
26.8日期函数615
26.8.1计算下一个星期一的日期615
26.8.2计算下一个星期几的日期616
26.8.3指出日期是月份中的
第几个星期616
26.8.4处理1900年以前的日期617
26.9返回列或行中的最后一个
非空单元格618
26.9.1LASTINCOLUMN函数618
26.9.2LASTINROW函数619
26.10多表函数619
26.10.1返回所有工作表中的
优选值619
26.10.2SHEETOFFSET函数620
26.11不错函数技术621
26.11.1返回错误值621
26.11.2从函数中返回一个数组623
26.11.3返回一个没有重复随机
整数的数组624
26.11.4随机排列一个范围626
26.11.5使用可选参数628
26.11.6使用不确定数量的参数629
第Ⅶ部分附录
附录AExcel函数参考637
附录B使用自定义数字格式653