办公大师经典丛书 中文版Excel2016公式与函数应用宝典 第7版PDF|Epub|txt|kindle电子书版本网盘下载

办公大师经典丛书 中文版Excel2016公式与函数应用宝典 第7版PDF格式电子书版下载

注意：本站所有压缩包均有解压码： 点击下载压缩包解压工具

第Ⅰ部分 基础知识3

第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

第Ⅱ部分 在公式中使用函数73

第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

第Ⅲ部分 财务公式241

第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

第Ⅳ部分 数组公式295

第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

第Ⅴ部分 其他公式341

第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

第Ⅵ部分 开发自定义工作表函数531

第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 Intersect函数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

第Ⅶ部分 附录637

附录A Excel函数参考637

附录B使用自定义数字格式653