领悟专家技巧，释放Excel潜能 如果只是使用Excel来创建简单的表格和图表，现在是时候深挖这个软件的真正功能了：公式。本书是一本理想的指南，介绍了公式的方方面面，向读者展示了Excel的强大功能。 利用本书的提示和技巧，读者的工作流程将变得更快、更轻松、更精确。本书介绍的专家技巧是在别的地方找不到的。无论是初学者还是高级用户，这本书都是一本重要参考，可带领读者深入Excel，*程度地利用Excel 2016版本的功能。
本书能够为你提供帮助。我们将带你进入Excel公式的世界，详细介绍Excel的各种内置函数。每章都以前一章为基础，带领你从学习基本的数学函数，一直到能够实际创建自定义的函数。
公式是Excel真正的动力引擎，这一点无可争议。通过运用Excel中的各种函数，公式让Excel分析人员能够创建聚合报表、复杂的计算引擎、巧妙的仪表板模型等。实际上，随着对Excel函数和公式熟悉程度的增加，Excel分析人员的效率会越来越高。但是，增加对Excel函数和公式的熟悉程度是需要时间的。Excel中包含超过400个函数，可能需要用几个月甚至几年的时间不断学习，才能知道哪些函数最适合特定任务，哪些函数可以与其他函数结合使用。本书能够为你提供帮助。我们将带你进入Excel公式的世界，详细介绍Excel的各种内置函数。每章都以前一章为基础，带领你从学习基本的数学函数，一直到能够实际创建自定义的函数。你会发现，运用Excel函数不仅能够提高效率，还能够完成一些你原本不知道能用Excel处理的任务。本书读者对象书中涉及的层面较深，适合有一定Excel基础的用户使用。如果读者没有任何使用Excel的经验，那么本书可能不是很适合你，除非你是一个可以在极短的时间内学会一款新软件的天才。如果希望掌握本书的大部分内容，读者必须对Excel的使用具有一定的背景。本书假定读者已掌握以下内容：● 创建工作簿、插入工作表、保存文件和其他基本操作● 在各个工作簿之间浏览● 会使用Excel功能区和对话框● 会使用Windows基本功能，例如文件管理、复制和粘贴操作等使用本书的条件本书是为Excel 2016编写的，但其中大部分内容也适用于Excel 2007-Excel 2013。如果读者使用的是Excel 2016之前的版本，会发现无法使用一些函数(例如，预测函数是在Excel 2016中新增的)。每章中会指出那些无法在其他版本中使用的函数。如果读者使用的是Excel 2007之前的版本，那么需要放下这本书，选择本书的早期版本了。Excel 2007是一个分水岭，之前和之后版本的差别非常大。如果用Excel 2007之前的版本对着本书学习，会被本书的内容弄糊涂。要下载本书的示例，需要能够访问Internet。稍后的本书示例文件下载部分将更详细地说明这一点。注意 本书的示例是在Windows平台上创建的。对于Mac用户，我们不能保证本书的示例都能用于Mac电脑上的Excel。虽然Excel的跨平台兼容性很好，但还没有做到完全兼容。
对于硬件来说，当然是运行速度越快越好，同时，系统的内存越大，使用起来就越方便。本书约定下面，我们将花点时间来介绍本书采用的排版上的约定。键盘约定公式是由键盘输入的。另外，菜单和对话框也可以用键盘操作，特别是如果手正好在这些操作键上时就显得更为方便。公式书中的公式通常用等宽字体以单独一行显示。例如，下面就显示了一个公式：=VLOOKUP(StockNumber,PriceList,2,False)Excel支持特殊的公式形式，即数组公式。输入数组公式时，需要按下Ctrl Shift Enter组合键(不能只按Enter键)。Excel会自动为数组公式添加括号，表示这是一个数组公式。本书中在列出数组公式时，会使用括号来清楚地显示这实际上是一个数组公式，如下所示：{=SUM(LEN(A1:A10))}注意 不必专门输入数组公式的括号，Excel会自动添加。VBA代码本书还包含许多VBA代码示例。这些代码都使用等宽字体，且每行代码占据单独一行。为了便于代码的阅读，通常会使用Tab来缩排代码。虽然缩进不是必需的，但这有助于将一组语句对齐。如果实际中的一行代码在本书中用一行放不下，本书就会使用标准的VBA续行符：一个空格后跟下划线，这表示该行代码将延续到下一行。例如，下面两行代码构成一条VBA语句：If Right(cell.Value, 1) = "!" Then cell.Value _ =Left(cell.Value, Len(cell.Value) 1)在实际使用中，可以用这种方式分两行输入代码，也可以不使用下划线直接将代码写在同一行中。快捷键快捷键要用到一些功能键，例如Alt、Home、PgDn和Ctrl。本书会用加号连接两个键来表示这两个按键需要同时按下，例如，Ctrl G表明要同时按下Ctrl和G键来打开定位对话框。函数、过程和命名范围Excel的工作表函数均使用大写字母来表示，例如使用SUM函数累加A列中的值。而宏和过程名会使用一般的表示方法，例如执行InsertTotals过程，书中使用大小写混用的方式，以方便阅读。命名范围使用斜体表示，如选择InputArea范围。除了引号内的文本，一般情况下Excel不区分大小写。换言之，下面两个公式的结果相同：=SUM(A1:A50)=sum(a1:a50)不过Excel会自动把第二个公式中的字符转换为大写。鼠标约定关于鼠标，本书中使用的都是标准术语：指向、单击、右击、拖动等。从字面就知道是如何操作的。图标含义在本书中，下面这些图标用来强调某些重要的内容。注意 注意图标用来表示这些内容很重要，这些内容会对手头的任务，或对后续内容的理解产生至关重要的影响提示 提示图标用来表示某些高效的操作方式或一些隐晦的技术。掌握这些提示会令人刮目相看。在线资源 这个图标表示示例文件位于本书的网站中(参见稍后的本书示例文件下载)。警告 警告图标用于警示那些不注意就会导致问题的操作。交叉参考 交叉参考图标用于告诉读者其他章节对这个主题也有所介绍。本书的组织结构本书分为7大部分，每部分关注Excel函数的一个关键方面。第Ⅰ部分：基础知识这部分介绍基本的操作和功能(第1章～第3章)。第1章简要介绍Excel的功能，主要是为使用过其他电子表格产品的Excel新用户准备的。第2章介绍Excel公式的基础知识，如果读者想对本书有一个基本了解，那么本章是必读的。第3章介绍名称，如果以为名称就是单元格和范围，就过于片面了。第Ⅱ部分：在公式中使用函数这部分包含第4章～第10章。第4章介绍在公式中使用工作表函数的基础知识，后面的章节还会更详细地介绍这些知识；第5章介绍文本操作；第6章介绍日期和时间；第7章探讨各种计数方法；第8章讨论几种不同形式的查找公式；第9章阐述表格和工作表数据库的内容；第10章涵盖一些杂项计算方法，例如单位转换和舍入等。第Ⅲ部分：财务公式这部分含有3章(第11章～第13章)，探讨创建财务公式的问题。这部分介绍许多能满足实际需要的有用的公式。第Ⅳ部分：数组公式这部分含有第14章和第15章。大多数Excel用户对数组公式知之甚少，但数组公式是非常有用的。因此，这里单独用了一个部分来介绍此功能强大的特性。第Ⅴ部分：其他公式这部分包括第16章～第22章。这一部分涵盖了许多主题，其中也介绍了一部分从表面上看与公式没有关联但却很重要的内容。第16章介绍许多有关数据清洁的有用信息；第17章介绍使用图表时公式的重要性；第18章介绍与数据透视表相关的公式；第19章包含一些非常有趣且有用的公式，这些公式可与Excel的条件格式特性联合使用；第20章介绍数据验证这个特性；第21章介绍大公式，所谓大公式，就是很大的公式，这个公式代替了多个中间公式。如果公式不正确，该怎么办？这时可以阅读第22章介绍的调试技术。第Ⅵ部分：开发自定义工作表函数这部分包括第23章～第26章，主要介绍VBA，以及创建自定义工作表函数的相关知识。第23章介绍VBA和VB编辑器；第24章介绍一些编程概念；第25章介绍自定义工作表函数相关的背景知识；第26章提供很多自定义工作表函数的示例，读者可以照原样使用，也可以根据自己的需要做些改动。第Ⅶ部分：附录本书包含两个附录。附录A是一份Excel工作表函数速查表，附录B是一些自定义数字格式的技巧。本书示例文件下载本书含有许多例子，可在下面的这个链接中下载含有这些例子的工作簿：www.wiley.com/go/excel2016formulas扩展名为*.xlsm的文件包含VBA宏。为了使用其中的宏，打开文件时必须启用宏(也可以将文件放到受信任位置)。
Michael Alexander拥有MCAD(微软认证应用开发专家)认证资格，有超过15年的Microsoft Office解决方案咨询与开发经验。Michael还是一名Microsoft MVP，他运营着一个免费课程网站www.datapigtechnologies.com。 Dick Kusleika已连续12年荣膺Microsoft MVP称号，拥有逾20年的Microsoft Office使用经验，为客户开发基于Access和Excel的解决方案。Dick撰写了一个有关Excel的热门博客www.dailydoseofexcel.com。
第Ⅰ部分 基础知识
第1章 Excel用户界面概述 3
1.1
Excel的工作方式 3
1.1.1
工作表 4
1.1.2
图表工作表 5
1.1.3
宏表和对话表 5
1.2
Excel用户界面 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.5
Excel创建的名称 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.7
Excel维护单元格和
范围名称的方式 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.1
Excel如何处理日期和时间 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.1
VLOOKUP函数 171
8.3.2
HLOOKUP函数 173
8.3.3
LOOKUP函数 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
第10章 其他计算 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
第Ⅲ部分 财务公式
第11章 借贷和投资公式 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
第12章 贴现和折旧计算公式 259
12.1
使用NPV函数 259
12.1.1
NPV的定义 260
12.1.2
NPV函数示例 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
第13章 财务计划 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
第Ⅳ部分 数组公式
第14章 数组 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
第15章 使用数组公式的技巧 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
第Ⅴ部分 其他公式
第16章 导入和清洗数据 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
第17章 图表技术 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
第18章 数据透视表 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
第19章 条件格式 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.3
Excel的审核工具 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.4
Excel中宏的安全性 534
23.1.5
受信任位置 535
23.1.6
在个人宏工作簿中保存宏 535
23.1.7
将宏指定给按钮和其他
窗体控件 536
23.1.8
将宏放到快速访问工具栏 537
23.2
使用Visual Basic编辑器 538
23.2.1
了解VBE的组件 538
23.2.2
使用工程窗口 539
23.2.3
使用代码窗口 541
23.2.4
自定义VBA环境 543
第24章 VBA编程概念 549
24.1
Excel对象模型简述 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.1
If-Then结构 560
24.4.2
Select Case结构 562
24.4.3
循环指令块 563
24.5
使用范围 567
24.5.1
For Each-Next结构 567
24.5.2
引用范围 568
24.5.3
一些有用的范围属性 570
24.5.4
Set关键字 573
24.5.5
lntersect函数 573
24.5.6
Union函数 574
24.5.7
UsedRange属性 574
第25章 函数过程基础 577
25.1
为什么要创建自定义函数 577
25.2
VBA函数的示例 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.1
LASTINCOLUMN函数 618
26.9.2
LASTINROW函数 619
26.10
多表函数 619
26.10.1
返回所有工作表中的
最大值 619
26.10.2
SHEETOFFSET函数 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
第Ⅶ部分 附录
附录A Excel函数参考 637
附录B 使用自定义数字格式
653
新书资讯