Excel 2003/2007公式与函数的使用艺术PDF电子书下载

第一部分　Excel公式篇 2

Chapter 1　公式入门 2

1.1　公式和函数 2

1.2　公式的构成 3

1.2.1　公式的元素 3

1.2.2　公式的运算符 4

1.2.3　公式运算符的优先顺序 5

1.3　单元格的引用 5

1.3.1　什么是引用 5

1.3.2　引用方式 5

1.3.3　引用方式对公式的影响 7

1.3.4　绝对引用应用实例 9

1.3.5　相对引用应用实例 9

1.3.6　混合引用应用实例 9

1.3.7　单元格引用的两种表示法A1和R1C1的区别 10

1.4　输入公式 13

1.4.1　直接用键盘输入公式 13

1.4.2　输入复杂公式 13

1.4.3　输入公式的快捷方法 14

1.5　自动更正公式的错误 15

1.5.1　Excel的自动更正公式错误功能 15

1.5.2　Excel的错误检查规则 17

1.5.3　常见错误的自动检查 18

1.6　公式返回的错误信息 20

1.6.1　错误提示 20

1.6.2　错误信息 20

1.7　公式审核 21

1.7.1 Excel 2003的“公式审核”工具栏 21

1.7.2　Excel 2003的“工具审核”菜单 22

1.7.3　追踪引用单元格和从属单元格 22

1.7.4　错误检查 23

1.7.5　以公式求值检查错误 24

1.7.6　Excel 2007的“公式审核”工具 24

1.8　编辑公式 25

1.8.1　在编辑栏中编辑公式 25

1.8.2　在单元格中编辑公式 25

1.8.3　禁止在单元格内编辑公式 25

1.9　复制和移动公式 26

1.9.1　复制公式的基本方法 26

1.9.2　复制公式的快捷方法 26

1.9.3　移动公式的基本方法 27

1.9.4　移动公式的快捷方法 27

1.10　原封不动复制公式本身或公式的一部分 27

1.10.1　复制公式本身 28

1.10.2　复制公式的一部分 28

1.11　将公式转换为值 28

1.11.1　将整个公式转换为值 28

1.11.2　将公式中的某些表达式转换为值 30

1.11.3　查看公式中的某些表达式计算结果 31

1.12　隐藏、显示和保护公式 31

1.12.1　显示和隐藏公式 31

1.12.2　真正地隐藏公式 32

1.12.3　保护公式 33

1.13　处理公式的循环引用 36

1.14　计算公式 39

1.14.1　将工作表设置为手动重算 39

1.14.2　手动计算公式 39

1.14.3　只对工作表的某些单元格区域进行重新计算 40

1.15　关于公式的其他操作和说明 40

1.15.1　将公式分行输入 40

1.15.2　在公式表达式中插入空格 41

1.15.3　公式字符长度的限制 41

Chapter 2　使用名称 42

2.1　使用名称 42

2.1.1　使用名称的好处 42

2.1.2　定义名称的规则 43

2.1.3　可定义名称的对象 44

2.2　为单元格或单元格区域定义名称 44

2.2.1　使用“定义名称”对话框 44

2.2.2　使用名称框 45

2.2.3　自动建立名称 46

2.2.4 自动建立名称时需要注意的问题 46

2.2.5　查看定义的名称 47

2.3　为固定单元格区域定义名称 47

2.3.1　为单个单元格定义名称 47

2.3.2　为连续的单元格区域定义名称 48

2.3.3　为不连续的单元格区域定义名称 48

2.3.4　为整列或整行定义名称 50

2.3.5　以多个工作表的3D单元格区域为对象定义名称 50

2.4 为可变单元格区域定义名称 52

2.4.1　使用相对引用定义名称 53

2.4.2　定义以单元格A1为起点，以A列最后一个单元格为终点的动态列区域名称 54

2.4.3　定义以单元格A1为起点，以第1行有数据的最后一个单元格为终点的动态行区域名称 54

2.4.4　定义以单元格A1为起点，向下偏移M个单元格、高度为H的动态单元格区域名称 55

2.4.5　定义以单元格A1为起点，向右偏移N个单元格、宽度为W的动态单元格区域名称 56

2.4.6　定义以单元格A1为起点，以A列最后一个有数据的单元格和第1行最后一个有数据的单元格为终点的动态单元格区域名称 56

2.4.7　定义以单元格A1为起点，向下偏移M个单元格、向右偏移N个单元格、高度为H、宽度为W的动态单元格区域名称 57

2.5　为常数定义名称 58

2.5.1 为数值常数定义名称 58

2.5.2　为数组常量定义名称 59

2.5.3　为文本常数定义名称 60

2.6　在名称中使用函数和表达式 60

2.6.1 在名称中使用工作表函数 60

2.6.2　在名称中使用自定义函数 61

2.6.3　在名称中使用表达式 62

2.6.4　在名称中使用Excel 4.0宏函数 64

2.6.5　在名称中使用其他的名称 64

2.6.6　定义带有函数的名称的小诀窍 64

2.7　名称的级别和有效范围 65

2.7.1　定义工作簿级的名称 65

2.7.2　定义工作表级的名称 65

2.7.3　定义和使用工作簿级名称和工作表级名称应注意的事项 66

2.7.4　引用其他工作簿中定义的名称 66

2.7.5　将多个已有的名称合并为一个新名称 67

2.8　获取名称信息 68

2.8.1 快速查看名称所代表的单元格区域 68

2.8.2　获取当前工作簿的所有名称及其引用位置清单 68

2.9　名称应用案例 69

2.9.1　利用名称进行计算 69

2.9.2　利用名称的交集功能查询数据 69

2.9.3　绘制动态图表 71

2.9.4　使用INDEX和名称查询数据 72

2.9.5　在现有的公式上套用事后定义的名称 72

2.9.6　建立公式时自动套用名称 73

2.9.7　利用名称快速定位单元格区域 73

2.10　重命名和删除名称 74

2.10.1　重命名现有的名称 74

2.10.2　重新定义名称 74

2.10.3　删除名称 75

2.11 编辑工作表对名称的影响 75

2.11.1 插入行、列或单元格对名称的影响 75

2.11.2　删除行、列或单元格对名称的影响 75

2.11.3　移动单元格区域对名称的影响 76

2.11.4　复制工作表对名称的影响 76

2.11.5　删除工作表对名称的影响 76

2.12　使用INDIRECT函数建立一个固定范围的名称 76

2.13　在Excel 2007中定义和使用名称 77

2.13.1　定义名称 77

2.13.2　使用名称 79

2.13.3　使用“名称管理器”对话框管理名称 80

Chapter 3　公式高级应用 81

3.1　在公式中使用嵌套函数 81

3.1.1　什么是嵌套函数 81

3.1.2　如何在公式中使用嵌套函数 81

3.1.3　如何克服嵌套函数层数的限制 82

3.2　在公式中使用条件表达式 82

3.2.1　什么是条件表达式 83

3.2.2　简单的条件表达式 83

3.2.3　复杂的条件表达式 84

3.3　利用循环引用解决特殊问题 84

3.3.1　如何实现循环引用 84

3.3.2　应用案例：计算奖励基金 85

3.3.3　应用案例：输入时间标志 85

3.3.4　应用案例：求解方程的根 86

3.3.5　应用案例：产生唯一的随机整数 86

3.4　如何解决公式计算误差引起的错误 87

3.4.1 解决计算误差造成的判断错误 88

3.4.2　解决明细加总与总和不符的问题 89

3.4.3　解决单项百分比数字加总不等于100％的问题 90

3.5　创建高效计算公式的几点建议 91

3.5.1　科学、合理地设计表格 91

3.5.2　问题的解法不止一种 97

3.5.3　合理使用绝对引用和相对引用 97

3.5.4　巧妙使用名称 97

3.5.5　使用分析工具库函数 98

3.5.6　使用自定义函数来解决复杂问题 99

3.5.7　联合使用VBA和函数 99

Chapter 4　神奇的数组公式 100

4.1　数组概述 100

4.1.1　一维数组 100

4.1.2　二维数组 101

4.1.3　数组与工作表单元格区域的对应关系 101

4.2　数组公式概述 102

4.2.1　什么是数组公式 102

4.2.2　数组公式的特征 103

4.2.3　关于数组常量 103

4.3　输入数组公式 104

4.3.1　数组公式的输入方法和基本步骤 104

4.3.2　连续单元格区域的数组公式 104

4.3.3　单个单元格的数组公式 105

4.3.4　在工作表中输入数组常量 106

4.4　编辑数组公式 107

4.4.1　选择数组公式的单元格区域 107

4.4.2　连续单元格区域数组公式的操作规则 108

4.4.3　修改数组公式 108

4.4.4　扩充或缩小数组公式 109

4.4.5　删除数组公式 109

4.5　数组公式典型应用案例 109

4.5.1　计算数据区域内文本单元格的个数 109

4.5.2　计算数据区域内数字单元格的个数 110

4.5.3　计算数据区域内错误单元格的个数 110

4.5.4　计算数据区域内逻辑值单元格的个数 111

4.5.5　计算数据区域内前N个最大数之和 111

4.5.6　计算数据区域内前N个最小数之和 112

4.5.7　计算数据区域内某个范围内的数字之和 113

4.5.8　对含有错误值的数据区域求和 115

4.5.9　计算数据区域除去零值后的平均值 116

4.5.10　判断数据区域内是否有特定的数据 117

4.5.11　计算某个整数数字的所有位数字 117

4.5.12　获取数据区域的所有正数清单 118

4.5.13　获取数据区域的所有负数清单 119

4.5.14　获取数据列中的不重复数据清单 119

4.5.15　获取数据行中的不重复数据清单 122

4.5.16　颠倒数据列中的数据顺序 122

4.5.17　边输入数据边进行动态排序 122

4.5.18　获取数据行中最后一个数据 122

4.5.19　获取数据列中最后一个数据 123

4.5.20　获取数据区域内最接近指定值的数值 124

4.5.21　构建连续正数的数组 124

4.6　数组公式应用的其他问题 125

4.6.1 哪些工作表函数需要数组输入 125

4.6.2　输入单元格区域数组公式后有些单元格出现＃N/A是怎么回事 125

4.6.3　能否在数组公式中使用AND函数和OR函数 126

4.6.4　数组公式有哪些缺点 126

第二部分　Excel工作表函数篇Chapter 5　函数入门 130

5.1 函数概述 130

5.1.1　什么是函数 131

5.1.2　使用函数有什么好处 131

5.1.3　函数的基本语法 131

5.1.4　函数参数的类型 132

5.2　在公式中输入函数的方法 133

5.2.1　手工输入函数 133

5.2.2　采用“插入函数”对话框输入函数 134

5.2.3　插入嵌套函数 135

5.2.4　如何调出“函数参数”对话框以检查函数的参数设置情况 136

5.3　获取函数的帮助信息 136

5.4　函数种类 138

5.4.1　财务函数 138

5.4.2　日期和时间函数 138

5.4.3　查找与引用函数 138

5.4.4　数学与三角函数 138

5.4.5　统计函数 138

5.4.6　文本函数 139

5.4.7　信息函数 139

5.4.8　逻辑函数 139

5.4.9　数据库函数 139

5.4.10　工程函数 139

5.4.11 自定义函数 139

5.4.12　分析工具库函数 139

5.4.13　Excel 4.0宏函数 139

5.5　使用分析工具库的函数 140

5.6　关于Excel的易变函数 140

5.7　关于Excel 2007函数的说明 140

5.7.1 “函数库”组中名列按钮的功能 140

5.7.2　在Excel 2007工作表快速输入函数 144

5.7.3　Excel 2007的新增函数 144

Chapter 6　财务函数及其应用 145

6.1　Excel的财务函数 145

6.2　关于财务计算的几个基本概念 147

6.2.1　资金的时间价值 147

6.2.2　资金流的方向 147

6.2.3　常用的术语 147

6.3　现值计算 148

6.3.1　计算现值的函数 148

6.3.2　计算一次性存入银行多少钱才能在5年后购买一套房子 149

6.3.3　计算原始本金 149

6.3.4　计算承受的贷款限额 150

6.3.5　如果年通货膨胀率是5％，该如何理财 150

6.3.6　计划5年后每月末从银行取出6000元，连续取10年，那么现在应一次性存入银行多少钱 151

6.3.7　每年要多交业主500元，计算租金现值 152

6.3.8　已知工资年增长率是8％，计算10年间工资总额的现值 153

6.4　终值的计算 153

6.4.1　计算终值的函数 153

6.4.2　现在存入银行10万元，计算3年后的金额 154

6.4.3　现在每月初存入银行3000元，计算10年后的金额 154

6.4.4　今年1月存入银行3000元，以后每月多存20元，10年后是多少钱 155

6.4.5　工资年增长率是8％，10年间工资总额在10年后是多少 155

6.4.6　如果年通货膨胀率是5％，今天存入银行20万元在10年后的金额 156

6.4.7　如果年通货膨胀率是5％，今天开始每月末存入银行2000元，在10年后是多少 156

6.5　个人理财计划 157

6.5.1　从现在开始为养老作打算 157

6.5.2　从现在开始为孩子准备学费 157

6.6　等值年金的计算 158

6.6.1　若干年后一次从银行取出10万元，现在开始每年应存入银行的金额 158

6.6.2　现在一次性存入银行10万元，从现在开始每年可从银行取出的金额 159

6.6.3　现在一次性存入银行10万元，若干年后每年可从银行取出的金额 159

6.7　贷款计算 160

6.7.1　贷款计算的有关函数 160

6.7.2　每月应还多少贷款本息 161

6.7.3　每年应还多少贷款本息 161

6.7.4　编制贷款偿还计划表 163

6.7.5　另外一种还贷方式 165

6.7.6　提前还贷合适吗 166

6.8　期数计算 167

6.8.1　计算期数的函数 167

6.8.2　多少年后能成为百万富翁 167

6.8.3　多少年才能还清贷款 167

6.8.4　股票需要多少个涨停板才能翻番 168

6.8.5　股票多少个跌停板就会跌回原值 169

6.9　利率计算 170

6.9.1　计算利率的函数 170

6.9.2　年利率是多少才能满足投资收益要求 170

6.9.3　贷款的真实利率是多少 170

6.9.4　揭开无息分期付款的真面目 171

6.10　固定资产折旧计算 171

6.10.1 影响固定资产折旧的基本因素 172

6.10.2　常用的折旧计算函数 172

6.10.3　折旧计算一般方法 173

6.10.4　编制折旧表：考虑折旧年度与会计年度的不同 173

6.11 固定资产投资决策 175

6.11.1 固定资产投资决策方法 175

6.11.2　利用净现值进行投资决策 176

6.11.3　利用内部收益率进行投资决策 177

6.12　债券投资计算 178

6.12.1　计算债券价值 178

6.12.2　计算债券收益率 179

6.12.3　计算债券利息 180

6.13　财务函数PV、FV和RATE的其他应用 181

6.13.1　PV函数的综合应用 181

6.13.2　FV函数的综合应用 181

6.13.3　RATE函数的综合应用 182

6.13.4　制作复利现值系数表 182

6.13.5　制作年金现值系数表 183

6.13.6　制作复利终值系数表 183

6.13.7　制作年金终值系数表 184

Chapter 7　日期和时间函数及其应用 185

7.1　Excel的日期和时间函数 185

7.2　Excel如何处理日期和时间 186

7.2.1　Excel对日期的处理方式 186

7.2.2　Excel对时间的处理方式 187

7.2.3　判断单元格的数据是否为日期和时间 187

7.3　输入日期 188

7.3.1　快速输入当前日期 188

7.3.2　输入日期的简单方法 188

7.3.3　在工作表中输入了错误的日期怎么办 189

7.4　输入时间 190

7.4.1　快速输入当前时间 190

7.4.2　输入时间的简单方法 190

7.4.3　输入没有小时只有分钟和秒的时间 190

7.4.4　输入带日期限制的时间 190

7.4.5　输入超过24小时的时间 190

7.5 日期和时间的计算规则 191

7.5.1　计算日期的规则 191

7.5.2　计算时间的规则 191

7.6　设置日期的显示格式 192

7.6.1 将日期显示为中文的年月日 193

7.6.2　将日期显示为中文的星期 193

7.6.3　将日期显示为中文的年月日和星期 194

7.6.4　将日期显示为英文的年月日 194

7.6.5　将日期显示为英文的星期 195

7.6.6　将日期显示为英文的年月日和星期 195

7.6.7　将日期显示为纯数字格式 195

7.7　设置时间的显示格式 196

7.7.1　将时间显示为24小时制的标准格式 196

7.7.2　将时间显示为12小时制的格式 196

7.7.3　将时间显示为中文格式 196

7.7.4　将时间显示为计时格式 197

7.7.5　显示超过24小时的累计时间 197

7.8　日期函数及其应用 198

7.8.1　TODAY函数及其应用 198

7.8.2　DATE函数及其应用 199

7.8.3　YEAR、MONTH、DAY函数及其应用 200

7.8.4　WEEKDAY函数及其应用 200

7.8.5　DATEVALUE函数及其应用 202

7.8.6　DAYS360函数及其应用 202

7.8.7　EDATE函数及其应用 202

7.8.8　EOMONTH函数及其应用 202

7.8.9　WEEKNUM函数及其应用 203

7.8.10　WORKDAY函数及其应用 203

7.8.11 NETWORKDAYS函数及其应用 203

7.8.12　YEARFRAC函数及其应用 204

7.8.13　DATEDIF函数及其应用 204

7.9　时间函数及其应用 205

7.9.1　NOW函数及其应用 205

7.9.2　HOUR、MINUTE、SECOND函数及其应用 205

7.9.3　TIME函数及其应用 205

7.9.4　TIMEVALUE函数及其应用 206

7.10　日期与时间函数综合应用案例 206

7.10.1　计算年龄和工龄 206

7.10.2　确定某日期所在年已经过去的天数和剩余天数 206

7.10.3　确定刚刚过去的星期日的日期 207

7.10.4　确定某个日期后下一周的各个日期 207

7.10.5　确定某个月的第几个星期几的日期 208

7.10.6　确定某个月有几个星期几 208

7.10.7　获取某个月最后一天的日期 209

7.10.8　确定某个日期在该年的第几季度 209

7.10.9　设计完整月份的考勤表 210

7.10.10　设计跨月份的考勤表 212

7.11 输入一组有规律的日期数据序列 214

7.11.1　利用填充工具输入一组有规律的日期数据序列 214

7.11.2　利用函数和公式输入一组有规律的日期数据序列 216

Chapter 8　文本函数及其应用 217

8.1　Excel的文本函数 217

8.2　文本数据概述 218

8.2.1　输入和显示文本数据 218

8.2.2　判断看似数值的数据是否为文本数据 219

8.3　将数字型文本转换为数值 220

8.3.1　利用智能标记 220

8.3.2　重新设置格式 220

8.3.3　选择性粘贴 220

8.3.4　利用VALUE函数 221

8.3.5　利用两个减号 221

8.3.6　将文本数字乘以数字1 222

8.4　将数值转换为文本 222

8.4.1　利用TEXT函数 222

8.4.2　利用DOLLAR函数或RMB函数 223

8.4.3　利用FIXED函数 224

8.4.4　使用哪种数字格式化函数好 224

8.5　获取文本的部分数据 224

8.5.1　获取文本部分数据的有关函数 224

8.5.2　从文本字符串的左边取指定长度的字符 226

8.5.3　从文本字符串的右边取指定长度的字符 226

8.5.4　从文本字符串的指定位置取指定长度的字符 226

8.5.5　从某个特定字符位置取指定长度的字符 227

8.5.6　将前部分是数字后部分是文本的字符串分成两列文本 228

8.5.7　从身份证号码获取出生日期和性别 229

8.5.8　将金额的各个数字依次填入到连续的单元格 230

8.6　比较两个文本是否完全相同 230

8.7　文本合并与替换 230

8.7.1　将几个文本字符串合并为一个文本字符串 231

8.7.2　替换文本字符串中指定个数的文本字符 231

8.7.3　将文本中指定的文本字符替换为新文本字符 231

8.8　清理文本的特殊元素 231

8.8.1　清除文本两侧的空格 232

8.8.2　删除文本中所有打印不出的字符 232

8.9　重复复制文本 232

8.9.1 关于REPT函数 232

8.9.2　制作简易的直方图 232

8.10　数字代码与字符转换 233

8.10.1 获取由代码数字指定的字符 233

8.10.2　将不同单元格的数据合并为一个单元格的多行文本 233

8.10.3　将数字转换为列标字母 234

8.10.4　获取文本字符串中第一个字符的数字代码 235

8.11　英文字母的大小写转换 235

8.12　文本函数综合应用案例 235

8.12.1　获取当前文件的有关信息 235

8.12.2　将阿拉伯数字转换为中文大写 236

8.12.3　计算单元格内文本字符串中特定字符串出现的次数 237

8.12.4　获取英文名字的名、中间名和姓 237

8.12.5　删除英文名称中的称谓 238

8.12.6　将数字1、2、3...改为序列数1st、2nd、3rd 238

8.12.7　将单元格的多行数据分割成多列数据 239

Chapter 9　查找与引用函数及其应用 241

9.1　Excel的查找与引用函数 241

9.2　查找与引用函数及其基本应用 242

9.2.1　ADDRESS函数及其应用 242

9.2.2　AREAS函数及其应用 244

9.2.3　CHOOSE函数及其应用 244

9.2.4　COLUMN函数和ROW函数及其应用 247

9.2.5　COLUMNS函数和ROWS函数及其应用 251

9.2.6　HLOOKUP函数及其应用 251

9.2.7　VLOOKUP函数及其应用 253

9.2.8　LOOKUP函数及其应用 255

9.2.9　INDEX函数及其应用 256

9.2.10　MATCH函数及其应用 258

9.2.11　INDIRECT函数及其应用 262

9.2.12　OFFSET函数及其应用 263

9.2.13　TRANSPOSE函数及其应用 265

9.2.14　HYPERLINK函数及其应用 266

9.3　精确查询数据 266

9.3.1　利用INDEX函数和MATCH函数进行精确查找 266

9.3.2　利用HLOOKUP函数和VLOOKUP函数进行精确查找 267

9.3.3　利用名称进行精确查找 267

9.3.4　区分字母大小写的精确查找 267

9.4　近似查询数据 268

9.4.1　查找最接近于查找值的最大数据 268

9.4.2　查找最接近于平均值的数据 268

9.5　多列数据条件下的数据查询 269

9.5.1　使用数组公式 269

9.5.2　使用辅助列 270

9.5.3　在Excel 2003中使用数据透视表进行数据查询 270

9.5.4　在Excel 2007中使用数据透视表进行数据查询 273

9.6　从多个数据区域中查询数据 276

9.6.1 从当前工作表的多个区域中查找数据：简单情况 276

9.6.2　从当前工作表的多个区域中查找数据：复杂情况 277

9.6.3　查询汇总多个工作表中的数据 278

9.7　利用查阅向导工具查询数据 280

9.8　查询数据的几个应注意的问题 284

9.8.1 查不到数据出现错误怎么办 284

9.8.2　如何区别对待零值与空值 284

Chapter 10　逻辑函数与信息函数及其应用 285

10.1 Excel的逻辑函数与信息函数 285

10.1.1　Excel的逻辑函数 285

10.1.2　Excel的信息函数 286

10.2　逻辑函数及其应用 286

10.2.1 常用逻辑函数简介 286

10.2.2　如何快速准确地创建嵌套的多层IF公式 287

10.2.3　IF函数应用案例：计算个人所得税 289

10.2.4　IF函数应用案例：根据税后工资反算税前工资 290

10.2.5　使用乘号和加号替代IF函数：突破嵌套层数的限制 291

10.3　信息函数及其应用 292

10.3.1　IS类函数 292

10.3.2　N函数 293

10.3.3　TYPE函数 294

10.3.4　INFO函数 294

10.3.5　CELL函数 295

Chapter 11　数学和三角函数及其应用 297

11.1 Excel的数学和三角函数 297

11.2　三角函数计算 299

11.2.1 正三角函数SIN、COS和TAN 299

11.2.2　反三角函数ASIN、ACOS和ATAN 300

11.2.3 角度和弧度转换函数DEGREES和RADIANS 300

11.3　基本数学计算 301

11.3.1　绝对值函数 301

11.3.2 对数和指数函数 301

11.3.3　除法运算中的商和余数 304

11.3.4　排列与组合 305

11.3.5　计算最大公约数和最小公倍数 306

11.3.6　计算平方根函数 306

11.3.7　产生随机数 306

11.4　数值的舍入 307

11.4.1　取整数部分 307

11.4.2　以特定值为基准的进位和舍位 308

11.4.3　四舍五入 310

11.4.4　综合应用案例：产品价格标定 311

11.4.5　综合应用案例：话吧收费计算 312

11.4.6　综合应用案例：确定元以上的纸币张数 312

11.4.7　综合应用案例：确定小数点数字 315

11.4.8　解决单项加总不等于项目总和的问题 316

11.5　矩阵计算函数 316

11.5.1　计算行列式 316

11.5.2　求矩阵的逆矩阵 317

11.5.3　矩阵乘法运算 318

11.5.4　转置矩阵 318

11.5.5　矩阵函数应用案例：计算原材料需求量 319

11.5.6　矩阵函数应用案例：求解线性方程组 319

11.5.7　矩阵函数应用案例：计算学生成绩 320

11.6　数据汇总计算 321

11.6.1　无条件求和 321

11.6.2　单条件求和 322

11.6.3　多项目汇总求和 322

11.6.4　分类汇总求和 323

11.6.5　多条件求和 324

11.6.6　使用Excel 2007多条件求和函数SUMIFS进行多条件求和 326

11.6.7　计算平方和 326

11.6.8　计算一元多项式的函数值 328

11.6.9　求和函数综合应用：快速汇总全年的预算数和实际数 328

11.6.10　求和函数综合应用：动态汇总当日数据和以前累计数据 329

11.6.11 求和函数综合应用：统计一个月内周一到周五的加班小时数 330

11.6.12　求和函数综合应用：按照月份累加求和 330

11.6.13　求和函数综合应用：动态向下或向右累加计算 331

11.6.14　求和函数综合应用：对含有特殊字符的单元格区域进行求和 331

11.6.15　求和函数综合应用：统计公司员工各个年龄段的男女员工人数 332

11.7　使用条件求和向导进行多条件求和 334

11.8　关于数值的说明 338

11.8.1　数值的特性 338

11.8.2　输入数值的方法及注意事项 339

11.8.3　输入分数 340

11.8.4　判断输入的是否为数字 340

11.9　设置数值的各种显示样式 341

11.9.1　缩小至1/10显示数字 341

11.9.2　缩小至1/100显示数字 342

11.9.3　缩小至1/1000显示数字 342

11.9.4　缩小至万分之一显示数字 343

11.9.5　缩小至十万分之一显示数字 343

11.9.6　缩小至百万分之一显示数字 344

11.9.7　缩小至亿分之一显示数字 344

11.9.8　将负数显示为正数 344

11.9.9　将正数显示为负数 345

11.9.10　为数字添加单位及说明文字 345

11.9.11　将数字显示为其他字符 346

11.9.12　隐藏单元格的数据 347

11.9.13　关于自定义数字格式代码 349

Chapter 12　统计函数及其应用 350

12.1　统计函数及其应用 350

12.2　计算最大值和最小值 353

12.2.1　MAX函数和MAXA函数：求最大值 353

12.2.2　MIN函数和MINA函数：求最小值 353

12.2.3　无条件求最大值和最小值 353

12.2.4　单条件求最大值和最小值 354

12.2.5　多条件求最大值和最小值 354

12.2.6　综合应用案例：每隔多行求最大值和最小值 355

12.3　计算平均值 355

12.3.1　AVERAGE函数和AVERAGEA函数：算术平均值 356

12.3.2　GEOMEAN函数：几何平均值 356

12.3.3　TRIMMEAN函数：内部平均值 356

12.3.4　HARMEAN函数：调和平均值 357

12.3.5　AVERAGEIF函数：Excel 2007的单条件平均值 357

12.3.6　AVERAGEIFS函数：Excel 2007的多条件平均值 357

12.3.7　无条件求平均值 357

12.3.8　单条件求平均值 357

12.3.9　多条件求平均值 358

12.3.10　应用案例：每隔多行求平均值 359

12.3.11　应用案例：对含有零值的单元格区域求平均值 359

12.3.12　应用案例：除去一个最大值和一个最小值后的平均值 359

12.4　分析数据的离散程度：计算中位数、众数和四分位数 360

12.4.1　计算中位数：MEDIAN函数 360

12.4.2　计算众数：MODE函数 361

12.4.3　计算四分位数：QUARTILE函数 361

12.4.4　QUARTILE函数应用案例：绘制四分位点图 362

12.5　统计单元格个数 367

12.5.1　COUNT函数和COUNTA函数：无条件计数 367

12.5.2　COUNTIF函数：单条件计数 367

12.5.3　Excel 2003中的多条件下的计数问题 368

12.5.4　Excel 2007中的多条件下的计数问题 368

12.5.5　计数函数的应用公式集锦 369

12.6　获取第几个最大和最小的值 370

12.6.1　LARGE函数：获取数据区域的第k个最大值 370

12.6.2　SMALL函数：获取数据区域的第n个最小值 371

12.6.3　应用案例：选最好的N次成绩算平均值 371

12.7 对数据进行排位 372

12.7.1　RANK函数：确定绝对等级排名 372

12.7.2　PERCENTRANK函数：确定百分比等级排名 373

12.7.3　PERCENTILE函数：确定第几个百分比的数值 373

12.7.4　利用排位与百分比排位工具进行排名 374

12.8　考察数据变化的离散程度及风险评估 376

12.8.1　VARP函数和VARPA函数：计算基于整个样本总体的方差 376

12.8.2　VAR函数和VARA函数：计算基于给定样本的方差 376

12.8.3　STDEVP函数和STDEVPA函数：计算基于整个样本总体的标准差 377

12.8.4　STDEV函数和STDEVA函数：计算基于给定样本的标准差 377

12.8.5　COVAR函数：计算协方差 378

12.8.6　应用案例：某只股票投资收益与风险分析 379

12.8.7　应用案例：某几只股票投资组合收益与风险分析 379

12.9　概率分布分析 382

12.9.1　频数分布与直方图 382

12.9.2　正态分布 384

12.9.3　二项式分布 385

12.9.4　泊松分布 386

12.9.5　偏态与峰度 386

12.10　相关与回归概述 388

12.10.1　相关系数：判断两组数据的相关性 388

12.10.2　置信区间：进行可信区间估计 388

12.11　线性回归与预测 389

12.11.1　一元线性回归模型 389

12.11.2　多元线性回归模型 389

12.11.3　进行线性回归及预测的几个函数及说明 389

12.11.4　LINEST函数 390

12.11.5　SLOPE函数 392

12.11.6　INTERCEPT函数 392

12.11.7　FORECAST函数 392

12.11.8　TREND函数 393

12.11.9　利用FORECAST函数进行线性插值计算 393

12.12　指数回归与预测 395

12.12.1　指数回归模型 395

12.12.2　进行指数回归及预测的几个函数 395

12.12.3　LOGEST函数 395

12.12.4　GROWTH函数 395

12.13　非线性回归与预测 395

12.13.1　通过线性转换的方法解决非线性回归与预测 395

12.13.2　利用规划求解工具解决非线性回归与预测 396

12.14　利用回归工具进行回归与预测 399

Chapter 13　数据库函数及其应用 401

13.1　Excel的数据库函数使用方法 401

13.1.1　Excel的数据库函数 401

13.1.2　数据库函数的语法 402

13.2　使用数据库函数应的规则 402

13.2.1　必须使用数据清单或数据库 402

13.2.2　必须建立一个条件区域 403

13.3　建立条件区域 403

13.3.1　同一行的条件构成与关系条件 403

13.3.2　同一列的条件构成或关系条件 403

13.3.3　不同列、同行构成与关系条件 404

13.3.4　不同列、不同行构成或关系条件 404

13.3.5　不同列、不同行的复杂条件 404

13.3.6　将公式结果用作条件 404

13.4　常用数据库函数及应用 405

13.4.1　DSUM函数 405

13.4.2　DMAX函数 406

13.4.3　DMIN函数 406

13.4.4　DAVERAGE函数 407

13.4.5　DCOUNT函数 407

13.4.6　DCOUNTA函数 408

13.4.7　DGET函数 408

13.5　个人对数据库函数的看法 409

13.5.1　数据库函数的缺点：灵活性太差 409

13.5.2　利用其他的函数要比数据库函数更加灵活 409

13.5.3　利用数据透视表对数据清单进行汇总计算是最好的方法 409

13.5.4　利用筛选对数据清单进行查询要简单得多 411

Chapter 14　工程函数及其应用 412

14.1　Excel的工程函数 412

14.2　进制转换 413

14.2.1　BIN2DEC函数：将二进制数转换为十进制数 414

14.2.2　BIN2HEX函数：将二进制数转换为十六进制数 414

14.2.3　BIN2OCT函数：将二进制数转换为八进制数 414

14.2.4　DEC2BIN函数：将十进制数转换为二进制数 414

14.2.5　DEC2HEX函数：将十进制数转换为十六进制数 415

14.2.6　DEC2OCT函数：将十进制数转换为八进制数 415

14.2.7　HEX2BIN函数：将十六进制数转换为二进制数 415

14.2.8　HEX2DEC函数：将十六进制数转换为十进制数 415

14.2.9　HEX2OCT函数：将十六进制数转换为八进制数 416

14.2.10　OCT2BIN函数：将八进制数转换为二进制数 416

14.2.11　OCT2DEC函数：将八进制数转换为十进制数 416

14.2.12　OCT2HEX函数：将八进制数转换为十六进制数 416

14.3　复数运算 417

14.3.1　COMPLEX函数：将实系数和虚系数转换为复数 417

14.3.2　IMABS函数：返回复数的绝对值（模数） 417

14.3.3　IMREAL函数：返回复数的实系数 417

14.3.4　IMAGINARY函数：返回复数的实系数 417

14.3.5　IMDIV函数：返回两个复数的商 418

14.3.6　IMPRODUCT函数：返回两个复数的积 418

14.3.7　IMSUB函数：返回两个复数的差 418

14.3.8　IMSUM函数：返回多个复数的和 418

14.3.9　IMCONJUGATE函数：返回复数的共轭复数 418

14.3.10　IMEXP函数：返回复数的指数 418

14.3.11　IMLN函数：返回复数的自然对数 418

14.4　度量转换 419

14.4.1 CONVERT函数 419

14.4.2　重量和质量转换 419

14.4.3　距离和长度转换 420

14.4.4　日期转换 420

14.4.5　压力转换 421

14.4.6　力转换 421

14.4.7　能量转换 421

14.4.8　功率转换 422

14.4.9　磁力转换 422

14.4.10　温度转换 422

14.4.11　液体度量转换 422

Chapter 15　Excel 4.0宏函数 424

15.1 常用的宏函数 424

15.1.1 GET.CELL函数 424

15.1.2　GET.DOCUMENT函数 425

15.1.3　EVALUATE函数 425

15.2　Excel 4.0宏函数应用 425

15.2.1　将没有等号的表达式进行计算 425

15.2.2　将公式的计算结果显示为公式表达式 426

15.2.3　根据单元格填充颜色求和 426

15.2.4　根据单元格字体颜色求和 427

15.2.5　根据单元格字形求和 428

15.3　注意事项 429

第三部分　Excel自定义函数篇Chapter 16　设计自定义函数必须具备的Excel VBA知识 432

16.1　了解VBE工具 432

16.1.1　VBE窗口的结构 432

16.1.2　VBE窗口的菜单栏和工具栏 434

16.1.3　工程资源管理器窗口的结构 435

16.1.4　代码窗口的结构 436

16.1.5　属性窗口 436

16.1.6　插入模块、修改模块名称和删除模块 437

16.1.7　使用立即窗口查看计算结果和执行命令 438

16.1.8　为程序设置断点 438

16.1.9　获取VBA帮助信息 439

16.2　定义和声明变量和常量 439

16.2.1　变量的数据类型 439

16.2.2　强制声明变量 440

16.2.3　定义变量应注意的事项 440

16.2.4　定义变量 441

16.2.5　使用类型标识符定义变量 441

16.2.6　定义和使用对象变量 441

16.2.7　定义模块级变量 442

16.2.8　定义公共变量 442

16.2.9　定义常量 443

16.2.10　如何使用VBA内置常量 443

16.3　定义数组 444

16.3.1　什么是数组 444

16.3.2　定义静态数组 444

16.3.3　定义动态数组 444

16.3.4　将数组的默认下界设置为1 445

16.3.5　获取数组的最小下标和最大下标 445

16.4　数据运算符 446

16.4.1　赋值运算符 446

16.4.2　数学运算符 446

16.4.3　比较运算符 446

16.4.4　逻辑运算符 446

16.5　语句基本知识及语句书写技巧 446

16.5.1 声明语句 447

16.5.2　赋值语句 447

16.5.3　可执行语句 447

16.5.4　输入VBA代码 447

16.5.5　语句书写技巧 451

16.6　条件控制语句 452

16.6.1　If...Then条件语句 452

16.6.2　If...Then...Else条件语句 453

16.6.3　If...Then...Elself...Then条件语句 453

16.6.4　Select Case语句 454

16.6.5　GoTo语句 455

16.6.6　On...GoTo语句 455

16.6.7　On Error语句 456

16.7　循环语句 457

16.7.1　For...Next循环语句 457

16.7.2　Do...Loop循环语句 458

16.7.3　For Each...Next循环语句 460

16.8　使用单元格对象 461

16.8.1 引用单元格和单元格区域 461

16.8.2　获取单元格和单元格区域的基本信息 462

16.8.3　设置单元格和单元格区域的格式 463

16.8.4　单元格和VBA变量之间的数据交换 463

Chapter 17　设计自定义函数 464

17.1 自定义函数概述 464

17.1.1　什么是自定义函数 464

17.1.2　为什么要使用自定义函数 464

17.2　建立自定义函数 464

17.2.1 了解自定义函数的基本语法 464

17.2.2　不带参数的自定义函数 465

17.2.3　带必需参数的自定义函数 466

17.2.4　带可选参数的自定义函数 466

17.3　为自定义函数添加说明信息和分类管理 467

17.3.1 为自定义函数添加说明信息 467

17.3.2　指定自定义函数的类别 468

17.4　调用自定义函数 469

17.4.1　在工作表中调用自定义函数 469

17.4.2　在过程中调用自定义函数 470

17.5　自定义函数的测试和除错 470

Chapter 18　常用自定义函数集锦 471

18.1 自定义财务函数 471

18.1.1　计算个人所得税 471

18.1.2　根据税后工资反算税前工资 472

18.1.3　将阿拉伯数字转换为中文大写 473

18.2　自定义求和函数 474

18.2.1　对可见的数据行单元格求和 474

18.2.2　对隐藏的数据行单元格求和 475

18.2.3　根据单元格填充颜色对单元格求和 476

18.2.4　根据单元格字体颜色对单元格求和 477

18.3　获取单元格信息的自定义函数 477

18.3.1 判断单元格是否有普通公式 477

18.3.2　判断单元格是否有数组公式 477

18.3.3　判断单元格是否设置有条件格式 478

18.3.4　判断单元格是否设置有数据有效性 478

18.3.5　判断单元格是否引用了其他工作表数据 478

18.3.6　判断单元格是否引用了外部工作簿数据 478

18.3.7　判断单元格是否定义了名称 478

18.3.8　判断当前工作簿中是否存在指定名称的工作表 478

18.3.9　判断单元格数据类型 478

Chapter 19 共享自定义函数 480

19.1 准备使用自定义函数 480

19.2　将自定义函数复制到其他工作簿 481

19.3　共享自定义函数：导入与导出 482

19.4　共享自定义函数：创建加载宏 483

第四部分　公式与函数其他应用篇Chapter 20 公式和函数在数据管理中的应用 486

20.1 控制数据输入：数据有效性 486

20.1.1 数据有效性的使用方法 486

20.1.2　不允许输入重复数据 491

20.1.3　只能输入数字 491

20.1.4 只能输入文本 492

20.1.5　只能输入规定格式的数字 493

20.1.6　只能输入比上行数字大的数字 493

20.1.7　只能输入比上行数字小的数字 494

20.1.8　当在A列输入数据后才能在其他列输入数据 495

20.1.9　禁止在输入数据时出现空行 496

20.1.10　禁止在几列同时输入数据 497

20.1.11　只能输入其他工作表规定的数据 498

20.1.12　对数据输入范围进行多重限制 499

20.2　高效管理数据：条件格式 501

20.2.1　条件格式的使用方法 501

20.2.2　对工作表每隔一行填充为不同的颜色 503

20.2.3　将工作表的单元格设置为棋盘样式 504

20.2.4　根据某列是否有数据设置边框 507

20.2.5　标示每行的最大值和最小值 508

20.2.6　标示每列的最大值和最小值 509

20.2.7　标示数据区域内第N大或第N小的数值 510

20.2.8　标示大于平均值的单元格 512

20.2.9　不经排序在原始数据内将最小的3个数标示出来 514

20.2.10　不经排序在原始数据内将最大的3个数标示出来 515

20.2.11　标示数据区域内重复的数值 515

20.2.12　标示数据区域内未排序的数值 517

20.2.13　标示数据区域内数值的上升或下降趋势 518

20.2.14　标示含有特定字符的单元格 518

20.2.15　标示考试成绩有进步的学生 519

20.2.16　员工生日提前提醒 520

20.2.17　标示特定日期的单元格 521

20.2.18　隐藏单元格的错误值 521

20.2.19　隐藏单元格的零值 522

20.2.20　当数据区域内都输入数据后才显示汇总行 522

20.3　在条件格式中使用自定义函数 523

20.3.1　标示有普通公式的单元格 523

20.3.2　标示有数组公式的单元格 524

20.3.3　标示设置有条件格式的单元格 526

20.3.4　标示设置有数据有效性的单元格 526

20.3.5　标示引用其他工作表数据的单元格 528

20.3.6　标示引用外部工作簿数据的单元格 529

20.3.7　标示输入无效数据的单元格 530

20.3.8　标示定义了名称的单元格 531

20.4　Excel 2007强大的样式功能 532

20.4.1　在数据前面显示数据条 532

20.4.2　在单元格显示色阶 533

20.4.3　在数据前面显示图标 534

20.4.4　套用表格格式 534

20.4.5　使用单元格样式 535

Chapter 21　公式和函数在图表中的应用 536

21.1　利用SERIES函数编辑图表 536

21.1.1　认识SERIES函数 536

21.1.2　利用SERIES函数编辑修改图表 538

21.1.3　在SERIES函数中使用名称 540

21.1.4　将图表转换为静态图表 540

21.2　将图表元素与单元格链接起来 541

21.2.1　建立图表标题与单元格的链接 541

21.2.2　建立坐标轴标题与单元格的链接 542

21.2.3　建立数据标签与单元格的链接 542

21.2.4　在图表上添加单元格的说明文字 545

21.2.5　利用“照相机”工具将工作表数据拍摄下来并显示在图表上 546

21.3　绘制交互动态图表 548

21.3.1　绘制能够显示任务完成进度的图表 548

21.3.2　以每隔N个数据绘制图表 549

21.3.3　绘制能够随数据量变化而变化的图表 550

21.3.4　绘制能够显示最新N个数的图表 551

21.3.5　根据使用中的行或列绘制图表 554

21.3.6　通过窗体控件控制图表 556

21.4　绘制数学函数的图表 557

21.4.1　绘制一元变量的数学函数图表 557

21.4.2　绘制二元变量的数学函数图表 558

21.5　利用图表进行预测 559

21.6　在Excel 2007中绘制图表的基本方法介绍 560

21.6.1 以选定的单元格区域绘制图表 561

21.6.2　以定义的名称绘制图表 562

21.6.3　设计图表的布局和样式 565

附录A　Excel 2003的规范与限制 566

附录B　Excel 2007的规范与限制 569