Excel 2003/2007公式与函数的使用艺术PDF|Epub|txt|kindle电子书版本网盘下载

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　将文本数字乘以数字1222

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、3rd238

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和TAN299

11.2.2　反三角函数ASIN、ACOS和ATAN300

11.2.3 角度和弧度转换函数DEGREES和RADIANS300

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　将数组的默认下界设置为1445

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