Excel 2016应用大全PDF电子书下载

第一篇 Excel基本功能 2

第1章 Excel简介 2

1.1 Excel的起源与历史 2

1.2 Excel的主要功能 4

1.3 Excel 2016的主要新特性 7

第2章 Excel工作环境 11

2.1启动Excel程序 11

2.2其他特殊的启动方式 13

2.3使用Microsoft账户登录Excel 14

2.4理解Excel文件的概念 15

2.5 Office Open XML文件格式 18

2.6理解工作簿和工作表的概念 19

2.7认识Excel的工作窗口 19

2.8认识Ribbon功能区 20

2.9通过选项设置调整窗口元素 31

2.10自定义功能区 32

2.11快速访问工具栏 34

2.12快捷菜单和快捷键 36

第3章 工作簿和工作表操作 38

3.1工作簿的基本操作 38

3.2工作表的基本操作 56

3.3工作窗口的视图控制 61

第4章 认识行、列及单元格区域 70

4.1行与列的概念 70

4.2行与列的基本操作 73

4.3单元格和区域 81

第5章 在电子表格中输入和编辑数据 88

5.1数据类型的简单认识 88

5.2输入和编辑数据 91

5.3数据输入实用技巧 101

5.4填充与序列 105

第6章 整理电子表格中的数据 114

6.1为数据应用合适的数字格式 114

6.2处理文本型数字 118

6.3自定义数字格式 121

6.4单元格和区域的复制与粘贴 131

6.5查找和替换 139

6.6单元格的隐藏和锁定 143

第7章 格式化工作表 146

7.1单元格格式 146

7.2单元格样式 154

7.3使用主题 156

7.4工作表背景 158

7.5清除格式 159

7.6数据表格美化 160

第8章 创建和使用模板 162

8.1理解模板的含义 162

8.2默认启动文件夹和模板文件夹 162

8.3自定义启动文件夹 163

8.4更改默认工作簿模板 163

8.5更改默认工作表模板 164

8.6创建自定义模板 165

8.7使用内置模板创建工作簿 166

第9章 打印文件 168

9.1页面设置 168

9.2对象打印设置 175

9.3在工作表之间复制页面设置 176

9.4打印预览 176

9.5分页预览视图和页面布局视图 178

第二篇 使用公式和函数 181

第10章 公式和函数基础 181

10.1认识公式 181

10.1.1公式的输入和编辑 181

10.1.2公式的复制与填充 181

10.2公式中的运算符 183

10.2.1认识运算符 183

10.2.2运算符的优先顺序 183

10.2.3嵌套括号 184

10.3认识单元格引用 184

10.3.1 A1引用样式和R1C1引用样式 185

10.3.2相对引用、绝对引用和混合引用 185

10.3.3跨工作表引用和跨工作簿引用 187

10.3.4表格中的结构化引用 189

10.4认识Excel函数 190

10.4.1函数的概念和特点 190

10.4.2函数的结构 190

10.4.3可选参数与必需参数 190

10.4.4常用函数类型 191

10.4.5函数的易失性 191

10.5输入函数的几种方式 191

10.5.1使用【自动求和】按钮插入函数 191

10.5.2使用函数库插入已知类别的函数 192

10.5.3使用“插入函数”向导搜索函数 192

10.5.4手工输入函数 193

10.5.5活用函数屏幕提示工具 193

10.6查看函数帮助文件 194

10.7使用公式的常见问题 195

10.7.1常见错误列表 195

10.7.2检查公式中的错误 196

10.7.3使用公式审核 196

10.7.4循环引用 198

10.7.5显示公式本身 198

10.7.6自动重算和手动重算 198

10.8公式结果的检验和验证 199

10.8.1简单统计公式结果的验证 199

10.8.2使用〈F9〉键查看运算结果 199

10.9函数与公式的限制 200

10.9.1计算精度限制 200

10.9.2公式字符限制 200

10.9.3函数参数的限制 200

10.9.4函数嵌套层数的限制 200

第11章 使用命名公式——名称 201

11.1认识名称 201

11.1.1名称的概念 201

11.1.2名称的用途 201

11.1.3名称的级别 202

11.2定义名称的方法 202

11.2.1使用名称框定义名称 202

11.2.2使用【新建名称】命令定义名称 202

11.2.3根据所选内容批量创建名称 202

11.2.4在名称管理器中新建名称 203

11.3名称命名的限制 204

11.4名称的管理 204

11.4.1修改名称 204

11.4.2筛选和删除错误名称 205

11.4.3在单元格中粘贴名称列表 205

11.4.4查看命名范围 206

11.5名称的使用 206

11.5.1输入公式时使用名称 206

11.5.2现有公式中使用名称 206

11.6定义名称技巧 208

11.6.1名称中的相对引用和混合引用 208

11.6.2引用位置始终指向当前工作表内的单元格 209

11.7使用名称的注意事项 209

11.7.1工作表复制时的名称问题 209

11.7.2有关删除操作引起的名称问题 209

11.8利用“表”区域动态引用 210

第12章 文本处理技术 211

12.1文本数据的概念 211

12.1.1认识文本数据 211

12.1.2空单元格与空文本 211

12.1.3文本型数字与数值 212

12.2常用文本函数 213

12.2.1文本运算 213

12.2.2使用CHAR函数和CODE函数转换字符与编码 213

12.2.3 CLEAN函数和TRIM函数 214

12.2.4用FIND函数和SEARCH函数查找字符串 214

12.2.5用LEN函数和LENB函数计算字符串长度 216

12.2.6用LEFT、RIGHT和MID函数提取字符串 217

12.2.7用SUBSTITUTE和REPLACE函数替换字符串 219

12.2.8用TEXT函数将数值转换为指定数字格式的文本 221

12.3文本函数综合应用 225

12.3.1合并字符串 225

12.3.2提取子字符串 227

第13章 信息提取与逻辑判断 233

13.1用INFO函数获取当前操作环境信息 233

13.2用CELL函数获取单元格信息 233

13.3 IS类判断函数 236

13.3.1用ISODD函数和ISEVEN函数判断数值奇偶性 236

13.3.2用ISNUMBER函数判断参数是否为数值 237

13.4逻辑判断函数 237

13.4.1逻辑函数与乘法、加法运算 237

13.4.2使用IF函数进行条件判断 238

13.4.3用IFNA函数和IFERROR函数屏蔽错误值 239

第14章 数学计算 240

14.1取余函数 240

14.1.1利用MOD函数计算余数 240

14.1.2用MOD函数生成有规律的循环序列 241

14.2数值取舍函数 241

14.2.1 INT和TRUNC函数 242

14.2.2 ROUNDUP函数和ROUNDDOWN函数 243

14.2.3 CEILING函数和FLOOR函数 243

14.3四舍五入函数 244

14.3.1用ROUND函数对数值四舍五入 244

14.3.2用MROUND函数实现特定条件下的舍入 245

14.3.3四舍六入五成双法则 246

14.4用RAND函数和RANDBETWEEN函数生成随机数 246

14.5数学函数的综合应用 248

14.5.1计扣个人所得税 248

14.5.2数字校验应用 249

14.5.3指定有效数字 250

14.5.4生成不重复随机序列 250

第15章 日期和时间计算 252

15.1认识日期及时间 252

15.1.1日期及时间的本质 252

15.1.2标准日期格式 252

15.1.3快速生成日期或时间 252

15.2常规日期及时间函数 252

15.2.1基本日期函数TODAY、YEAR、MONTH和DAY函数 253

15.2.2日期之间的天数 254

15.2.3返回月末日期 254

15.2.4利用DATE函数返回月末日期 254

15.2.5将英文月份转化为数字 254

15.2.6基本时间函数NOW、TIME、HOUR、MINUTE和SECOND 255

15.2.7计算90分钟之后的时间 255

15.2.8使用鼠标快速填写当前时间 256

15.3星期函数 257

15.3.1用WEEKDAY函数计算某个日期是星期几 257

15.3.2用WEEKNUM函数计算指定日期位于当年第几周 258

15.4用EDATE和EOMONTH函数计算几个月之后的日期 259

15.4.1计算退休日期 259

15.4.2计算合同到期日 260

15.4.3计算每月的天数 260

15.5认识DATEDIF函数 261

15.5.1函数的基本用法 261

15.5.2计算法定年假天数 262

15.5.3计算员工工龄 263

15.5.4生日到期日提醒 263

15.6日期和时间函数的综合运用 264

15.6.1分别提取单元格中的日期和时间 264

15.6.2计算加班时长 264

15.6.3计算母亲节与父亲节日期 265

15.7计算工作日 265

15.7.1计算日期属性 266

15.7.2工作日统计 266

第16章 查找与引用 270

16.1用ROW函数和COLUMN函数返回行号列号信息 270

16.1.1返回当前单元格的行列号 270

16.1.2返回指定单元格的行列号 270

16.1.3返回单元格区域的自然数数组序列 271

16.1.4其他行列函数 271

16.1.5生成等差数列 271

16.2 INDEX函数 272

16.2.1 INDEX函数基础应用 272

16.2.2制作分数条 274

16.3 MATCH函数 275

16.3.1 MATCH函数精确匹配基础应用 275

16.4 INDEX＋MATCH函数组合 276

16.4.1 INDEX＋MATCH常规精确查找 276

16.4.2动态引用照片 277

16.4.3多条件查找 279

16.4.4一对多查询 279

16.4.5目标值含有通配符的查找 280

16.4.6 MATCH函数模糊查找 281

16.5 LOOKUP函数 283

16.5.1 LOOKUP函数常规查找应用 283

16.5.2查询员工最近一次合同签订日期 284

16.5.3使用LOOKUP函数实现多条件查找 285

16.6 VLOOKUP函数 285

16.7 INDIRECT函数 288

16.7.1数据区域转置引用 288

16.7.2根据工作表名称跨工作表查询 289

16.8 OFFSET函数 289

16.8.1 OFFSET偏移方式 290

16.8.2创建动态数据区域 291

第17章 统计与求和 292

17.1基础统计函数SUM、COUNT、COUNTA、AVERAGE、MAX和MIN 292

17.1.1基础统计函数应用 292

17.1.2使用SUM函数实现累计求和 293

17.1.3合并单元格统计 294

17.1.4跨工作表求和 295

17.1.5用MAX结合MIN函数设置上下限 296

17.2其他常用统计函数 296

17.2.1认识LARGE与SMALL函数 296

17.2.2用RANK.EQ和RANK.AVG函数对数据排名 298

17.3条件统计函数 299

17.3.1单条件计数COUNTIF函数 299

17.3.2某单字段同时满足多条件的计数 301

17.3.3验证身份证号是否重复 301

17.3.4中国式排名 302

17.3.5统计非重复值数量 302

17.3.6多条件计数COUNTIFS函数 303

17.3.7多字段同时满足多条件的计数 304

17.3.8单条件求和SUMIF函数 305

17.3.9某单字段同时满足多条件的求和 306

17.3.10多条件求和SUMIFS函数 307

17.3.11多字段同时满足多条件的求和 308

17.3.12 AVERAGEIF函数与AVERAGEIFS函数 308

17.4 SUMPRODUCT函数 309

17.4.1认识SUMPRODUCT函数 309

17.4.2 SUMPRODUCT条件统计计算 311

17.5多种平均值统计 312

17.5.1内部平均值TRIMMEAN函数 312

17.5.2几何平均值GEOMEAN函数 313

17.5.3调和平均值HARMEAN函数 314

17.6筛选和隐藏状态下的统计与求和 314

17.6.1认识SUBTOTAL函数 314

17.6.2认识AGGREGATE函数 317

17.7频率函数FREQUENCY 319

第18章 财务金融函数 321

18.1财务基础相关知识 321

18.1.1货币时间价值 321

18.1.2单利和复利 321

18.1.3现金的流入与流出 321

18.2借贷和投资函数 322

18.2.1未来值函数FV 322

18.2.2现值函数PV 324

18.2.3利率函数RATE 325

18.2.4期数函数NPER 326

18.2.5付款额函数PMT 326

18.3计算本金与利息函数 327

18.3.1每期还贷本金函数PPMT和利息函数IPMT 327

18.3.2累计还贷本金函数CUMPRINC和利息函数CUMIPMT 328

18.3.3制作贷款计算器 329

18.4投资评价函数 330

18.4.1净现值函数NPV 330

18.4.2内部收益率函数IRR 332

18.4.3不定期净现值函数XNPV 333

18.4.4不定期内部收益率函数XIRR 333

第19章 工程函数 335

19.1贝赛尔（Bessel）函数 335

19.2数字进制转换函数 336

19.3用CONVERT函数转换度量衡 338

19.4用ERFC函数计算误差 339

19.5用IMSUM函数处理复数计算 339

第20章 Web类函数 341

20.1 Web类函数简介 341

20.1.1 ENCODEURL函数 341

20.1.2 WEBSERVICE函数 342

20.1.3 FILTERXML函数 342

20.2 Web类函数综合应用 342

20.2.1将有道翻译装进Excel 342

20.2.2将天气预报装进Excel 343

第21章 数组公式 345

21.1理解数组 345

21.1.1 Excel中数组的相关定义 345

21.1.2 Excel中数组的存在形式 345

21.2数组公式与数组运算 346

21.2.1认识数组公式 346

21.2.2多单元格数组公式 347

21.2.3单个单元格数组公式 347

21.2.4数组公式的编辑 348

21.2.5数组的直接运算 348

21.2.6数组的矩阵运算 351

21.3数组构建及填充 351

21.3.1行列函数生成数组 351

21.3.2一维数组生成二维数组 352

21.3.3提取子数组 352

21.3.4填充带空值的数组 354

21.3.5二维数组转换一维数组 355

21.4条件统计应用 355

21.4.1单条件不重复统计 355

21.4.2多条件统计应用 356

21.4.3条件查询及定位 357

21.5数据筛选技术 358

21.5.1一维区域取得不重复记录 358

21.5.2条件提取唯一记录 359

21.5.3二维数据表提取不重复记录 360

21.6利用数组公式排序 361

21.6.1快速中文排序 361

21.6.2根据产品产量进行排序 362

21.7数据表处理技术 363

21.7.1总表拆分应用 363

21.7.2分表合并总表应用 364

第22章 多维引用 367

22.1多维引用的工作原理 367

22.1.1认识引用的维度和维数 367

22.1.2引用函数生成的多维引用 368

22.1.3函数生成的多维引用和“跨多表区域引用”的区别 370

22.2多维引用的应用 370

22.2.1支持多维引用的函数 370

22.2.2多表单条件统计 370

22.2.3多表多条件统计 372

22.2.4另类多条件汇总技术 373

22.2.5筛选条件下提取不重复记录 374

22.2.6根据比赛评分进行动态排名 375

22.2.7先进先出法应用 376

第三篇 创建图表和图形 379

第23章 创建迷你图 379

23.1认识迷你图 379

23.2创建迷你图 380

23.3更改迷你图类型 382

23.4设置迷你图样式 383

23.5清除迷你图 387

第24章 创建图表入门 389

24.1图表及其特点 389

24.2图表的组成 390

24.3创建图表 393

24.4标准图表类型 396

24.5设置图表格式 399

24.6图表模板 413

24.7图表布局与样式 414

第25章 高级图表制作 416

25.1 2016新增图表制作 416

25.2数据重构 422

25.3组合图表 439

25.4动态图表 451

第26章 使用图形和图片增强工作表效果 466

26.1形状 466

26.2图片 468

26.3艺术字 472

26.4 SmartArt 473

26.5条形码 474

26.6文件对象 475

第四篇 使用Excel进行数据分析 478

第27章 在数据列表中简单分析数据 478

27.1了解Excel数据列表 478

27.2数据列表的使用 479

27.3创建数据列表 479

27.4使用“记录单”添加数据 479

27.5删除重复值 480

27.6数据列表排序 483

27.7更多排序方法 487

27.8筛选数据列表 495

27.9使用高级筛选 503

27.10分级显示和分类汇总 511

27.11 Excel的“表格”工具 519

27.12合并计算 526

第28章 使用多样性的数据源 534

28.1了解外部数据库文件 534

28.2利用文本文件获取数据 534

28.3从Access获取外部数据 539

28.4利用Microsoft Query创建查询 541

28.5自网站获取数据 547

28.6 PowerPivot for Excel 2016 549

28.7 Power Query 552

第29章 使用数据透视表分析数据 554

29.1关于数据透视表 554

29.2改变数据透视表的布局 561

29.3设置数据透视表的格式 571

29.4数据透视表的刷新 573

29.5在数据透视表中排序 576

29.6数据透视表的切片器 578

29.7数据透视表的日程表 583

29.8数据透视表的项目组合 586

29.9在数据透视表中执行计算 591

29.10使用透视表函数获取数据透视表数据 596

29.11创建动态的数据透视表 597

29.12利用多种形式的数据源创建数据透视表 599

29.13利用Excel数据模型进行多表分析 607

29.14钻取数据透视表数据 609

29.15创建数据透视图 611

第30章 使用Power View分析数据 615

30.1利用Power View制作仪表盘式报表 615

30.2在Power View中使用自定义图片筛选数据 619

30.3在Power View中创建可自动播放的动画报表 624

第31章 模拟分析和预测工作表 627

31.1模拟运算表 627

31.2使用方案 634

31.3借助单变量求解进行逆向模拟分析 638

31.4预测工作表 641

第32章 规划求解 644

32.1规划求解加载项 644

32.2单纯线性规划 645

32.3投资收益最大化问题 647

32.4规划求解常见问题及解决方法 650

第五篇 使用Excel的高级功能 656

第33章 使用分析工具库分析数据 656

33.1加载分析工具库 656

33.2描述统计分析 658

33.3相关系数分析 659

33.4移动平均分析 660

33.5排位与百分比排位 662

33.6直方图分析 664

33.7双因素方差分析 666

第34章 条件格式 670

34.1认识条件格式 670

34.2设置条件格式 671

34.3管理条件格式 682

第35章 数据验证 685

35.1认识数据验证 685

35.2数据验证的典型应用 687

35.3数据验证的个性化设置 697

35.4修改和清除数据验证规则 698

第36章 链接和超链接 700

36.1链接工作簿 700

36.2超链接 703

第37章 使用Excel高级文本服务 711

37.1语音朗读表格 711

37.2中文简繁体转换 712

37.3多国语言翻译 713

37.4智能查找 714

第六篇 使用Excel进行协同 716

第38章 信息安全控制 716

38.1借助IRM进行信息安全控制 716

38.2保护工作表 716

38.3保护工作簿 718

38.4标记为最终状态 719

38.5数字签名 720

38.6借助“检查文档”保护私有信息 724

38.7发布为PDF或XPS 726

38.8发布为HTML文件 728

第39章 与其他应用程序共享数据 730

39.1 Windows剪贴板和Office剪贴板 730

39.2将Excel数据复制到其他Office应用程序中 731

39.3在其他Office应用程序文档中插入Excel对象 734

39.4在Excel中使用其他Office应用程序的数据 736

39.5使用Excel工作簿作为外部数据源 736

第40章 协同处理Excel数据 737

40.1远程打开或保存Excel文件 737

40.2共享工作簿 738

40.3审阅 743

40.4 Excel工作簿内容比对 745

第41章 云时代的Excel 747

41.1 Excel Online 747

41.2 Excel应用程序 754

第七篇 Excel自动化 757

第42章 初识VBA 757

42.1什么是宏 757

42.2 VBA的版本 757

42.3 VBA的应用场景 758

42.4 VBA与VSTO 758

42.5 Excel 2016中VBA的工作环境 759

42.6在Excel 2016中录制宏代码 767

42.7在Excel 2016中运行宏代码 768

第43章 VBA集成编辑环境 771

43.1 VBE界面介绍 771

43.2在VBE中运行宏代码 775

第44章 VBA编程基础 777

44.1常量与变量 777

44.2运算符 779

44.3过程 780

44.4程序结构 781

44.5对象与集合 783

44.6数组 785

44.7字典对象 785

第45章 与Excel进行交互 786

45.1使用MsgBox输出信息 786

45.2利用InputBox输入信息 789

第46章 自定义函数与加载宏 795

46.1什么是自定义函数 795

46.2函数的参数与返回值 795

46.3在VBA代码中引用工作表函数 796

46.4在工作表中引用自定义函数 797

46.5自定义函数的限制 798

46.6如何制作加载宏 799

第47章 如何操作工作簿、工作表和单元格 801

47.1 Workbook对象 801

47.2 Worksheet对象 806

47.3 Range对象 810

第48章 事件的应用 819

48.1事件过程 819

48.2工作表事件 819

48.3工作簿事件 823

48.4事件的优先级 825

48.5非对象相关事件 826

第49章 控件在工作表中的应用 827

第50章 窗体在Excel中的应用 828

50.1创建自己的第一个用户窗体 828

50.2在用户窗体中使用控件 830

50.3窗体的常用事件 834

附录 837

附录A Excel2016规范与限制 837

附录B Excel2016常用快捷键 840

附录C Excel2016术语简繁英文词汇对照表 845

附录D 高效办公必备工具——Excel易用宝 849