Excel函数与公式应用大全for Excel 365 & Excel 2021

本书以 Excel 2021 为蓝本，全面系统地介绍了 Excel 365 & Excel 2021 函数与公式的技术原理、应用技巧与实战案例。内容包括函数与公式基础，文本处理、查找引用、统计求和、Web 类函数、宏表函数、自定义函数、数据库函数等常用函数的应用，以及数组公式、动态数组、多维引用等。
本书采用循序渐进的方式，由易到难地介绍各个知识点，适合各个水平的 Excel 用户，既可作为初学者的入门指南，又可作为中、高级用户的参考手册。

Excel Home是微软在线社区联盟成员，全球华语Excel资源网站，拥有大量原创技术文章、视频教程、加载宏及模板。

Excel Home是一个学习氛围浓厚的技术交流社区，中国大陆及中国港台各行各业身怀绝技的Office高手都汇聚于此，

目前已有三百多万办公人士选择成为它的注册会员。 Excel Home已精心编写并出版Office领域的图书60余种，

截至2020年春节，图书销量累计达200多万册。

经典：Excel Home团队策划，多位微软全球有价值专家（MVP）通力打造。

升级：上一版长期雄踞Excel函数类图书销量前列，《Excel 2019函数与公式应用大全》重磅升级版。

全面：详尽而又系统地介绍了Excel函数与公式的核心技术。

实战 精选Excel Home的海量案例，零距离接触Excel专家级使用方法。

深入：对一些常常困扰学习者的功能深入揭示背后的原理，让读者知其然，还能知其所以然。

揭秘：独家讲授Excel多项绝密应用，披露Excel专家多年研究成果！

资源：赠送视频教学资源及书中相关案例文件，供读者参考练习、快速上手。

非常感谢您选择《Excel函数与公式应用大全for Excel 365 & Excel 2021》。

本书全面系统地介绍了Excel函数与公式的应用方法，深入揭示其背后的原理，并配有大量典型实用的应用案例，帮助读者借助Excel函数与公式更好地完成各种数据计算与分析。

全书从公式与函数基础开始，逐步展开到文本处理、查找引用、统计求和等常用函数的应用，以及数组公式、动态数组、多维引用等。除此之外，还详细介绍了LAMBDA函数、Web类函数、宏表函数、自定义函数、数据库函数及Excel

365中有代表性的新函数，另外还包括在数据验证、条件格式、高级图表制作中的函数与公式综合应用，最后介绍了公式常见错误及处理方法，形成一套结构清晰、内容丰富的Excel函数与公式知识体系。附录中还提供了Excel的规范与限制、Excel的快捷键等，方便读者随时查阅。

本书采用循序渐进的方式，由易到难地介绍各个知识点。除了原理和基础性的讲解，还配以大量的典型示例帮助读者加深理解，适合不同层次的Excel用户，既可作为初学者的入门指南，又可作为中、高级用户的参考手册。

读者对象

本书面向的读者群是所有需要使用Excel的用户。无论是初学者，中、高级用户还是IT技术人员，都能从本书找到值得学习的内容。当然，希望读者在阅读本书以前至少对Windows操作系统有一定的了解，并且知道如何使用键盘与鼠标。

本书约定

在正式开始阅读本书之前，建议读者花上几分钟时间来了解一下本书在编写和组织上使用的一些惯例，这会对您的阅读有很大的帮助。

软件版本

本书的写作基础是安装于Windows 10专业版操作系统上的中文版Excel 2021和Excel

365。由于高版本Excel可以兼容低版本Excel，因此本书中的许多内容也适用于Excel的早期版本，如Excel

2007、2010、2013、2016、2019，或者其他语言版本的Excel，如英文版、繁体中文版。但是为了能顺利学习本书介绍的全部功能，仍然强烈建议读者在中文版Excel

2021或Excel 365的环境下学习。

菜单命令

我们会这样来描述在Excel或Windows及其他程序中的操作，比如在讲到对某张Excel工作表进行隐藏时，通常会写成：在Excel功能区中单击【开始】选项卡中的【格式】下拉按钮，在其扩展菜单中依次选择【隐藏和取消隐藏】→【隐藏工作表】。

鼠标指令

本书中表示鼠标操作的时候都使用标准方法：“指向”“单击”“右击”“拖动”“双击”“选中”等，您可以很清楚地知道它们表示的意思。

键盘指令

当读者见到类似这样的键盘指令时，表示同时按下键和键。

Win表示Windows键，就是键盘上画着窗口图标的键。本书还会出现一些特殊的键盘指令，表示方法相同，但操作方法可能会有稍许不一样，有关内容会在相应的章节中详细说明。

Excel函数与单元格地址

本书中涉及的Excel函数与单元格地址将全部使用大写，如SUM()、A1:B5。但在讲到函数的参数时，为了和Excel中显示一致，函数参数全部使用小写，如SUM(number1,number2,

...)。

阅读技巧

不同水平的读者可以使用不同的方式阅读本书，以求在相同的时间和精力之下能获得最大的回报。

Excel初级用户或者任何一位希望全面熟悉Excel函数与公式的读者，可以从头开始阅读，因为本书是按照函数与公式的使用频度及难易程度来组织章节顺序的。

Excel中高级用户可以挑选自己感兴趣的主题来有侧重地学习，虽然各知识点之间有千丝万缕的联系，但通过我们在本书中提供的交叉参考，可以轻松顺藤摸瓜。

如果遇到困惑的知识点不必烦躁，可以先跳过，保留个印象即可，今后遇到具体问题时再来研究。当然，更好的方式是与其他爱好者进行探讨。如果读者身边没有这样的人选，可以登录Excel

Home技术论坛，这里有无数Excel爱好者正在积极交流。

另外，本书中为读者准备了大量的示例，它们都有相当的典型性和实用性，并能解决特定的问题。因此，读者也可以直接从目录中挑选自己需要的示例开始学习，然后快速应用到自己的工作中去，就像查字典那么简单。

写作团队及致谢

本书由周庆麟组织与策划，第1~8章、第33章由祝洪忠编写，第9章、第14章、第17章、第19~20章由郭新建编写，第11~13章、第27~28章、第32章由方洁影编写，第15章、第18章、第21~24章、第26章由翟振福编写，第25章由邵武编写，第29~31章由郑晓芬编写，第10章由祝洪忠、邵武共同编写，第16章由郭新建、邵武共同编写。最后由祝洪忠和周庆麟完成统稿。

感谢Excel

Home全体专家作者团队成员对本书的支持和帮助，尤其是本书较早版本的原作者——方骥、朱明、冯海、胡建学、李锐、余银、巩金玲等，他们为本系列图书的出版贡献了重要的力量。

Excel Home论坛管理团队和培训团队长期以来都是Excel Home图书的坚实后盾，他们是Excel

Home中最可爱的人，在此向这些最可爱的人表示由衷的感谢。

衷心感谢Excel Home论坛的五百万会员，是他们多年来不断的支持与分享，才营造出热火朝天的学习氛围，并成就了今天的Excel Home系列图书。

衷心感谢Excel Home微博的所有粉丝和Excel Home微信公众号及视频号的所有关注者，你们的“赞”和“转”是我们不断前进的动力。

后续服务

在本书的编写过程中，尽管我们的每一位团队成员都未敢稍有疏虞，但纰缪和不足之处仍在所难免。敬请读者提出宝贵的意见和建议，您的反馈将是我们继续努力的动力，本书的后继版本也将会更臻完善。

您可以访问https://club.excelhome.net，我们开设了专门的版块用于本书的讨论与交流。您也可以发送电子邮件到[email protected]，我们将尽力为您服务。

同时，欢迎您关注我们的官方微博（@ExcelHome）和微信公众号（iexcelhome），我们每日都会更新很多优秀的学习资源和实用的Office技巧，并与大家进行交流。

绪论 如何学习函数公式1
1. 学习函数很难吗1
2. 从哪里学起1
3. 如何深入学习2
第一篇 函数与公式基础
第1章 认识公式6
1.1 公式和函数的概念6
1.2 公式的输入、编辑与删除7
1.3 公式的复制与填充8
1.4 设置公式保护9
1.5 浮点运算误差11
练习与巩固13
第2章 公式中的运算符和数据类型14
2.1 认识运算符14
2.2 认识数据类型16
2.3 数据类型的转换19
练习与巩固20
第3章 单元格引用类型21
3.1 A1引用样式和R1C1引用样式21
3.2 相对引用、绝对引用和混合引用22
3.3 单元格引用中的“隐式交集”25
3.4 单元格变动对单元格引用的影响26
练习与巩固26
第4章 跨工作表引用和跨工作簿引用27
4.1 引用其他工作表区域27
4.2 引用其他工作簿中的单元格28
4.3 引用连续多工作表的相同区域28
练习与巩固29
第5章 表格和结构化引用30
5.1 创建表格30
5.2 表格的特点31
5.3 表格应用范围的变化31
5.4 表格中的计算32
练习与巩固34
第6章 认识Excel函数36
6.1 Excel函数的概念36
6.2 常用函数的分类38
6.3 函数的易失性39
练习与巩固39
第7章 函数的输入与查看函数帮助40
7.1 输入函数的几种方式40
7.2 查看函数帮助文件42
练习与巩固43
第8章 公式结果的验证和限制44
8.1 公式结果的验证44
8.2 函数与公式的限制47
练习与巩固48
第9章 使用命名公式——名称49
9.1 认识名称49
9.2 定义名称的方法50
9.3 名称的级别53
9.4 名称命名的限制55
9.5 名称可使用的对象56
9.6 名称的管理57
9.7 名称的使用59
9.8 定义名称的技巧60
9.9 使用名称的注意事项62
9.10 使用INDIRECT函数创建不变的名称63
9.11 定义动态引用的名称63
练习与巩固66
第二篇 常用函数
第10章 文本处理技术68
10.1 认识文本型数据68
10.2 文本函数应用69
练习与巩固100
第11章 信息提取与逻辑判断101
11.1 使用CELL函数获取单元格信息101
11.2 常用IS类判断函数105
11.3 其他信息类函数106
11.4 逻辑判断函数107
11.5 屏蔽错误值112
练习与巩固116
第12章 数学计算117
12.1 序列函数117
12.2 四则运算117
12.3 幂运算与对数运算120
12.4 其他数学计算122
12.5 取舍函数123
12.6 数学转换函数127
12.7 随机数函数128
12.8 数学函数的综合应用130
练习与巩固131
第13章 日期和时间计算133
13.1 输入日期和时间数据133
13.2 日期时间格式的转换136
13.3 处理日期时间的函数139
13.4 星期函数142
13.5 季度运算146
13.6 日期时间间隔147
13.7 工作日间隔156
13.8 日期函数的综合运用161
练习与巩固165
第14章 查找与引用函数167
14.1 基础查找与引用函数167
14.2 用VLOOKUP函数查询数据171
14.3 用HLOOKUP函数查询数据176
14.4 用MATCH函数返回查询值的相对位置176
14.5 认识INDEX函数179
14.6 认识LOOKUP函数180
14.7 使用XLOOKUP函数查询数据184
14.8 用FILTER函数筛选符合条件的数据187
14.9 认识OFFSET函数189
14.10 认识INDIRECT函数194
14.11 使用UNIQUE函数去重197
14.12 使用SORT和SORTBY函数排序199
14.13 用HYPERLINK函数生成超链接201
14.14 用FORMULATEXT函数提取公式字符串203
14.15 用TRANSPOSE函数转置数组或单元格区域203
14.16 查找引用函数的综合应用204
练习与巩固207
第 15 章 统计与求和 209
15.1 基础统计函数 209
15.2 不同状态下的求和计算 210
15.3 其他常用统计函数 211
15.4 条件统计函数 218
15.5 平均值统计 231
15.6 能计数、能求和的SUMPRODUCT 函数 235
15.7 方差与标准差 240
15.8 筛选和隐藏状态下的统计与求和 242
15.9 使用FREQUENCY函数计算频数（频率） 248
15.10 排列与组合 250
15.11 线性趋势预测 254
15.12 概率分布函数 257
练习与巩固 261
第 16 章 数组运算与数组公式 263
16.1 理解数组 263
16.2 数组公式的概念 271
16.3 数组的重构 275
16.4 数组公式应用综合实例 291
16.5 数组公式的优化 300
练习与巩固 301
第 17 章 多维引用 302
17.1 多维引用的概念 302
17.2 多维引用实例 305
练习与巩固 309
第 18 章 财务函数 310
18.1 财务、投资相关的基本概念与常见计算 310
18.2 基本借贷和投资类函数FV、PV、RATE、NPER和PMT 314
18.3 与本金和利息相关的财务函数 315
18.4 名义利率函数NOMINAL与实际利率函数EFFECT 321
18.5 投资评价函数 322
18.6 用SLN、SYD、DB、DDB和VDB函数计算折旧 326
练习与巩固 328
第 19 章 工程函数 330
19.1 贝塞尔（Bessel）函数 330
19.2 数字进制转换函数 330
19.3 度量衡转换函数 332
19.4 误差函数 333
19.5 处理复数的函数 333
练习与巩固 335
第 20 章 Web类函数 336
20.1 用ENCODEURL函数对URL地址编码 336
20.2 用WEBSERVICE函数从Web服务器获取数据 336
20.3 用FILTERXML函数获取XML结构化内容中的信息 337
练习与巩固 339
第 21 章 数据透视表函数 340
21.1 初识数据透视表函数 340
21.2 提取数据透视表不同计算字段数据 344
21.3 提取各学科平均分前三名的班级 344
21.4 从多个数据透视表中提取数据 345
练习与巩固 346
第 22 章 数据库函数 347
22.1 数据库函数基础 347
22.2 数据库函数的基础用法 348
22.3 比较运算符和通配符的使用 351
22.4 使用公式作为筛选条件 353
22.5 认识DGET函数 355
22.6 跨工作表统计 356
练习与巩固 358
第 23 章 宏表函数 359
23.1 什么是宏表 359
23.2 用GET.DOCUMENT函数得到工作表信息 361
23.3 用FILES函数得到文件名清单信息 365
23.4 用GET.WORKBOOK函数返回工作簿信息 368
23.5 用GET.CELL函数返回单元格信息 370
23.6 用EVALUATE函数计算文本算式 372
练习与巩固 373
第 24 章 自定义函数 374
24.1 自定义函数的特点 374
24.2 自定义函数的工作环境 374
24.3 自定义函数实例 376
练习与巩固 380
第 25 章 LAMBDA与迭代函数 381
25.1 使用LAMBDA函数创建自定义函数 381
25.2 使用BYROW函数和BYCOL函数实现逐行逐列汇总 383
25.3 用MAKEARRAY函数生成指定行列的数组 384
25.4 使用MAP函数遍历数组中的每一个值 385
25.5 使用SCAN函数遍历数组并设置累加器 388
25.6 使用REDUCE函数遍历数组并设置累加器 392
练习与巩固 396
第 26 章 循环引用 398
26.1 认识循环引用和迭代计算398
26.2 控制循环引用的开启与关闭 399
练习与巩固 405
第27章 条件筛选技术406
27.1 按条件筛选406
27.2 提取不重复值415
27.3 综合运用418
练习与巩固419
第28章 排名与排序420
28.1 使用函数与公式进行排名420
28.2 使用函数与公式进行排序425
练习与巩固433
第四篇 其他功能中的函数应用
第29章 函数与公式在条件格式中的应用436
29.1 条件格式中使用函数公式的方法436
29.2 函数公式在条件格式中的应用实例444
练习与巩固454
第30章 函数与公式在数据验证中的应用455
30.1 数据验证中使用函数与公式的方法455
30.2 函数与公式在数据验证中的应用实例459
练习与巩固468
第31章 函数与公式在图表中的应用469
31.1 认识图表中的SERIES函数469
31.2 为图表添加参考线472
31.3 使用逻辑函数辅助创建图表475
31.4 使用FILTER函数与数据验证制作动态图表494
31.5 使用SORT函数制作自动排序的条形图497
31.6 使用SQRT函数制作气泡图500
31.7 使用OFFSET函数结合定义名称、控件制作动态图表503
31.8 用REPT函数制作旋风图515
31.9 用HYPERLINK函数制作动态图表516
练习与巩固518
第五篇 函数与公式常见错误指南
第32章 常见不规范表格导致的问题及处理建议520
32.1 统一字段属性520
32.2 使用单元格对象备注特殊数据523
32.3 减少使用合并单元格524
32.4 使用一维表存放数据526
32.5 保持数据表的完整526
32.6 正确区分数据源表、统计报表及表单530
练习与巩固532
第33章 公式常见错误指南533
33.1 函数名称或参数输入错误533
33.2 循环引用534
33.3 显示公式本身534
33.4 参数设置错误534
33.5 函数自身限制536
33.6 引用错误537
33.7 空格或不可见字符的影响538
33.8 数据类型的影响539
33.9 溢出错误539
33.10 开启了手动重算540
练习与巩固540
附录541
附录A Excel 2021主要规范与限制541
附录B Excel 2021常用快捷键544
附录C 高效办公必备工具——Excel易用宝549