Excel函数与公式标准教程

（实战微课版）

聂静 编著

内 容 简 介

本书以微型实用办公案例的形式，对Excel中常用的公式与函数进行全面细致的阐述。

全书共11章，首先介绍Excel公式与函数基础知识、公式的检查和错误值处理，然后逐步对各类函数展开介绍，包括统计函数的应用、查找与引用函数的应用、逻辑函数的应用、数学与三角函数的应用、日期与时间函数的应用、文本函数的应用、财务函数的应用、信息函数的应用等，最后介绍函数在条件格式和数据验证中的应用。为了提高可操作性，在每章安排了“动手练”和“案例实战”内容并对常见疑难问题及解决方法进行了汇总。

全书内容丰富，结构清晰，图文并茂，详略得当，让读者可以更轻松地学习和掌握公式与函数的应用。本书适合高等院校相关专业作为教材使用，也适合办公室“小白”作为首选读物，还适合那些希望提高办公效率的职场人士阅读使用。

本书封面贴有清华大学出版社防伪标签，无标签者不得销售。

版权所有，侵权必究。举报：010-62782989  beiqinquan@tup.tsinghua.edu.cn

图书在版编目（CIP）数据

Excel函数与公式标准教程 : 实战微课版 / 聂静编著. -- 北京 : 清华大学出版社, 2021.6

 （清华电脑学堂）

ISBN 978-7-302-57995-3

 Ⅰ. ①E…?Ⅱ. ①聂…?Ⅲ. ①表处理软件－教材?Ⅳ. ①TP391.13

中国版本图书馆CIP数据核字（2021）第071046号

责任编辑：袁金敏

封面设计：杨玉兰

责任校对：徐俊伟

责任印制：杨?艳

出版发行：清华大学出版社

?????网??址：http://www.tup.com.cn, http://www.wqbook.com

?????地??址：北京清华大学学研大厦A座 邮??编：100084

?????社?总?机：010-62770175 邮??购：010-83470235

?????投稿与读者服务：010-62776969, c-service@tup.tsinghua.edu.cn

?????质?量?反?馈：010-62772015, zhiliang@tup.tsinghua.edu.cn

印?装?者：大厂回族自治县彩虹印刷有限公司

经??销：全国新华书店

开??本：170mm×240mm?????印??张：16?????字??数：351千字

版??次：2021年6月第1版   ????印??次：2021年6月第1次印刷

定??价：59.80元

--------------------------------------------

产品编号：089018-01

前 言

本书以理论与实际应用相结合的方式，从易学、易会的角度出发，全面介绍Excel函数与公式相关的内容。

本书特色

案例紧贴实际。本书采用案例的形式对知识点进行讲解，所选案例紧贴工作实际，读者甚至可以在自己的工作中直接借鉴。

重视动手操作。本书安排了大量的“动手练”内容，并在每章的结尾增加了“案例实战”，读者可以边学习边动手操作。

编写科学、合理。本书选取工作中常用的函数进行介绍，先讲解函数的基本原理，再用实例引导，最后进行公式解析，使读者可以快速入门。

知识浅显易懂。本书主要讲解函数的基础用法，不涉及非常复杂的函数公式，读者很容易理解，学习起来不吃力。

内容概述

全书共11章，各章内容如下。

章 内?容?导?读 难 点 指 数

第1章 主要介绍Excel公式与函数基础知识，包括公式的组成、公式中的运算符、输入公式、修改公式、单元格的引用、复制和填充公式、名称的使用、函数的类型和输入方法等 ★ ★ ☆

第2章 主要介绍公式的检查和错误值处理，包括检查错误公式、查看公式求值步骤、显示或隐藏公式、追踪引用或从属单元格、屏蔽错误值等 ★ ☆ ☆

第3章 主要介绍统计函数的应用，包括AVERAGE、COUNT、COUNTIF、MAX、MIN、RANK等函数的应用 ★ ★  ☆

第4章 主要介绍查找与引用函数的应用，包括VLOOKUP、HLOOKUP、MATCH、ADDRESS、INDIRECT、ROW等函数的应用 ★ ★ ★

第5章 主要介绍逻辑函数的应用，包括AND、OR、NOT、IF等函数的应用 ★ ★ ☆

第6章 主要介绍数学与三角函数的应用，包括SUM、SUMIF、MOD、INT、ROUND、TRUNC、RAND、RANDBETWEEN等函数的应用 ★ ★ ☆

第7章 主要介绍日期与时间函数的应用，包括NOW、TODAY、DATE、TIME、HOUR、MINUTE、SECOND、WEEKDAY、WORKDAY等函数的应用 ★ ★ ☆

第8章 主要介绍文本函数的应用，包括LEN、LEFT、RIGHT、MID、UPPER、LOWER、TEXT、FIND、REPLACE、CONCAT等函数的应用 ★ ★ ☆

第9章 主要介绍财务函数的应用，包括DB、DDB、SYD、SLN、VDB、FV、PV、NPER、IRR等函数的应用 ★ ★ ★

第10章 主要介绍信息函数的应用，包括CELL、N、ISBLANK、ISTEXT、ISEVEN、ISODD、ISERR、ISERROR等函数的应用 ★ ★ ☆

第11章 主要介绍函数在条件格式和数据验证中的应用，包括标记重复数据、突出显示不达标业绩、突出显示周末日期、禁止输入重复值、禁止在单元格中输入空格等 ★ ★ ★

附赠资源

案例素材及源文件。附赠书中所用到的案例素材及源文件，方便读者实践学习。

扫码观看教学视频。本书涉及的疑难操作均配有高清视频讲解，共36个，读者可以边看边学。

其他附赠学习资源。附赠Excel办公模板1000个，Office办公学习视频100集，Excel小技巧动画演示120个，可进QQ群（群号在本书资源下载资料包中）下载。

作者在线答疑。为帮助读者快速掌握书中技能，全书配有专门的答疑QQ群（见本书资源下载资料包），随时为读者答疑解惑。

本书在编写过程中力求严谨细致，但由于时间与水平有限，疏漏之处在所难免，望广大读者批评指正。

编?者

目 录

第1章

Excel公式与函数基础知识

1.1 认识Excel公式 2

1.1.1 Excel公式的基础形式 2

1.1.2 公式的组成 2

1.1.3 公式中的运算符 3

1.1.4 输入公式 5

1.1.5 修改公式 6

1.2 单元格的引用 7

1.2.1 相对引用 7

1.2.2 绝对引用 7

1.2.3 混合引用 8

动手练 使用公式计算销售净利润 8 8

1.3 复制和填充公式 9

1.3.1 复制公式 9

1.3.2 填充公式 10

动手练 批量计算所有商品的销售额 11

1.4 名称的使用 12

1.4.1 定义名称 12

1.4.2 使用名称 13

1.4.3 删除名称 14

动手练 制作疫情发展动态图表 15

1.5 函数的类型和输入方法 17

1.5.1 函数的类型 17

1.5.2 函数的输入技巧 18

1.5.3 什么是数组 20

动手练 启用“公式记忆式键入”功能 22

案例实战：计算车间生产合计 23

新手答疑 24

第2章

公式的检查和错误值处理

2.1 公式审核 26

2.1.1 自动检查错误公式 26

2.1.2 手动检查错误公式 27

2.1.3 查看公式求值步骤 27

2.1.4 显示或隐藏公式 28

2.1.5 追踪引用或从属单元格 30

动手练 解决公式不能自动计算的问题 31

2.2 公式返回的错误值 33

2.2.1 分析错误值产生的原因 33

2.2.2 屏蔽错误值 35

动手练 设置不打印错误值 36

案例实战：检查销售明细表中的错误公式并修改 38

新手答疑 40

第3章

统计函数的应用

3.1 使用函数完成平均值计算 42

3.1.1 计算所有考生平均成绩 42

3.1.2 计算全班学生平均成绩 43

3.1.3 计算所有男生/女生的平均成绩 44

3.1.4 计算90分以上男生/女生平均成绩 44

3.1.5 计算过去一年产量的平均增长率 45

3.1.6 计算车辆从始发点到终点的平均速度 46

动手练 统计员工平均工资 47

3.2 按条件统计单元格数量 47

3.2.1 统计参加考试人数 47

3.2.2 统计活动报名人数 48

3.2.3 统计缺席活动的人数 48

3.2.4 统计销售额超过5万元的人数 49

3.2.5 统计指定商品销售额超过5万元的人数 50

动手练 统计员工考勤数据 50

3.3 统计最大值和最小值 53

3.3.1 统计单笔消费最高金额 53

.3.3 统计最低的商品销量 54

3.3.4 统计运动员最差成绩 55

动手练 统计景点不同时段最高和最低人流量 55

3.4 对数据进行排位统计 56

3.4.1 对员工销售业绩进行排位 56

3.4.2 提取业绩最佳的3位员工的销售额 57

3.4.3 提取业绩最差的3位员工的销售额 58

3.4.4 计算短跑中间成绩 58

动手练 对面试应聘成绩进行排名 59

3.5 计算概率分布 60

3.5.1 计算试验成功的概率 60

3.5.2 计算各概率下应包含合格产品个数 61

3.5.3 计算累积负二项式分布值 62

3.5.4 计算F概率分布 63

3.5.5 计算商品在一段时期内价格的峰值 63

3.5.6 计算数值在指定区间内的概率 64

动手练 计算各地区平均气温的峰值 65

3.6 使用函数进行数据预测 66

3.6.1 根据以往数据预测双十一成交额 66

3.6.2 预测未来利润 67

3.6.3 预测未来两个月的产品销售额 67

3.6.4 预测9月份的产品销售量 68

案例实战：统计出入库数据 69

新手答疑 72

第4章

查找与引用函数的应用

4.1 查找指定的数据 74

4.1.1 根据商品名称查询商品价格 74

4.1.2 根据所选价格查找相关售房信息 75

4.1.3 查找工号对应的职务 76

4.1.4 根据书名查找书的定价 77

4.1.5 根据客户名称查找订单信息 77

4.1.6 查找销售员对应的提成金额 78

4.1.7 检索书号所在的位置 79

4.1.8 查找指定月份指定员工的销售额 81

4.1.9 从所有销售记录中找出指定商品的销售记录 81

动手练 从销售订单表中查找需要的信息 82

4.2 引用单元格 83

4.2.1 查找最高订单数量所在单元格 84

动手练 通过下拉列表查询不同工作表中的营业额 85

4.2.2 统计分公司的数量 87

4.2.3 查询员工工资 87

4.2.4 合并多张工作表中的数据 88

4.2.5 快速为表格填充序号 89

4.2.6 计算项目的数量 90

4.2.7 根据列标提取对应的列数 90

4.2.8 计算项目需要开支的数量 91

案例实战：查询客户基本信息 92

新手答疑 94

第5章 

逻辑函数的应用

5.1 真假判断函数 96

5.1.1 判断两个单元格中的内容是否相同 96

动手练 提取手机最新报价 96

5.1.2 判断员工是否符合晋升条件 97

5.1.3 判断学生是否符合贫困生 98

5.1.4 判断游戏玩家是否未成年 99

根据年龄判断员工是否退休 99

5.2 IF条件判断函数 100

5.2.1 判断实际产量是否达标 100

5.2.2 判断员工培训成绩是否及格 101

5.2.3 根据笔试成绩和面试成绩判断是否有证书 102

5.2.4 根据报销类型输入报销费用 102

5.2.5 判断参赛选手是否被淘汰 103

5.2.6 根据业绩计算奖金总额 103

5.2.7 计算合计金额时忽略错误值 104

5.2.8 根据考核总分判断考核结果 105

5.2.9 根据公司代码返回公司名称 106

动手练 根据工龄计算提成金额 107

案例实战：根据出生日期判断员工生肖和星座 109

新手答疑 110

第6章

数学与三角函数的应用

6.1 常用的求和函数 112

6.1.1 统计所有商品销售总额 112

6.1.2 统计指定商品的销售总额 112

动手练 对超过1000的销量进行汇总 113

6.1.3 统计姓“李”的员工的销售额 114

动手练 统计手机的销售数量 115

6.1.4 统计3月份台式电脑的入库数量 115

动手练 统计销售员销售指定商品的金额 116

6.1.5 计算购买商品的合计金额 117

动手练 统计销售部女员工人数 118

6.1.6 计算所有数值的平方和 119

6.2 其他数学函数的应用 120

6.2.1 计算自然数的阶乘 120

6.2.2 计算产品的总产值 120

6.2.3 计算产品不同售价时的利润 121

6.2.4 计算两数相除的余数 122

动手练 从身份证号码中提取性别 123

6.2.5 计算两个或两个以上整数的最大公约数 123

6.2.6 计算两个或两个以上整数的最小公倍数 124

6.3 四舍五入函数的应用 125

6.3.1 计算合计支出时忽略小数 125

6.3.2 将收入金额保留到分位 126

6.3.3 将收入金额向上舍入到角位 126

6.3.4 将支出金额向下舍入到整数 127

6.3.5 将销售金额的小数部分去掉 128

动手练 根据员工入职日期计算年假天数 128

6.3.6 统计销售员提成金额，不足10000元忽略 129

6.3.7 活动分组时保证每组人数为偶数 130

6.3.8 随机从奇数行中抽取值班人员 131

统计指定单元格区域中偶数的个数 132

6.4 随机函数的应用 133

6.4.1 产生1～10之间的不重复随机整数 133

6.4.2 生成-10～10之间的随机整数 134

动手练 随机抽取中奖人员 134

案例实战：制作工资条 136

新手答疑 138

第7章

日期与时间函数的应用

7.1 日期函数 140

7.1.1 快速录入当前日期和时间 140

动手练 计算距离项目完工还有多少天 140

7.1.2 快速录入当前日期 141

动手练 统计即将到期的合同数量 142

7.1.3 计算6个月后的日期 142

7.1.4 提取日期中的年份 143

动手练 计算公司成立多少周年 144

7.1.5 提取日期中的月份 144

动手练 计算5月份的销售额 145

7.1.6 提取日期中的某一天 146

动手练 计算今年平均每月天数 146

7.1.7 计算指定日期之前/之后两个月的最后一天 147

7.1.8 计算机器工作天数和月数 148

7.1.9 计算工程历时天数 149

7.1.10 计算请假天数占全年天数百分比 150

7.1.11 根据借款日期和借款期限计算最后还款日期 150

动手练 设置合同到期提醒 151

7.2 时间函数 152

7.2.1 返回某一特定时间 152

7.2.2 计算迟到时长 153

7.2.3 计算加班时间 153

7.2.4 从时间值中提取秒数 154

计算共享单车骑行时间  154

7.3 星期函数 155

7.3.1 计算指定日期是星期几 155

动手练 计算今天是星期几 156

7.3.2 计算指定日期是当年的第几周 157

动手练 计算本学期的周数 158

7.4 工作日的计算 158

7.4.1 统计实际工作日 158

动手练 根据辞职报告批准日期计算离职日期 159

7.4.2 计算兼职天数 160

7.4.3 计算项目完成日期 160

7.4.4 计算两个日期值之间的工作天数 162

7.5 将文本日期与文本时间转换成标准格式 162

7.5.1 解决日期无法计算的问题 162

7.5.2 解决时间无法计算的问题 163

案例实战：计算员工相关信息 164

新手答疑 166

第8章

文本函数的应用

8.1 计算字符长度 168

8.1.1 根据歌曲名称长短排序 168

8.1.2 计算字符串一共有多少字节 168

动手练 计算文本字符串中包含多少个数字 169

8.2 提取字符 170

8.2.1 从产品简介中提取产品名称 170

8.2.2 从摘要中提取订单号 171

8.2.3 从学号中提取班级 171

动手练 从身份证号码中提取出生日期 172

8.3 转换文本格式 172

8.3.1 将单元格中所有字母转换为大写 172

8.3.2 将所有字母转换为小写 173

8.3.3 将所有英文句子转换成首字母大写 173

8.3.4 将全角字符转换为半角字符 174

8.3.5 将半角字符转换为全角字符 174

8.3.6 将文本型数字转换成真正的数字 175

8.3.7 判断给定的值是否是文本 176

8.3.8 将手机号码分段显示 176

8.3.9 为销售业绩自动划分等级 178

8.3.10 判断公司盈亏情况 178

8.3.11 转换日期的显示方式 178

8.3.12 为金额添加单位“元” 179

8.3.13 将数字代码转换成文本 179

8.3.14  为金额添加$符号 180

8.3.15 为金额添加千位分隔符和￥符号 180

8.3.16 将数字显示为千分位格式并转换为文本 181

将数字金额显示为中文大写金额 182

8.4 查找与替换文本 183

8.4.1 提取空格之前的字符 183

8.4.2 查找（??学历）的字符位置 184

动手练  提取括号中的内容 185

8.4.3 在字符串的指定位置插入固定内容 185

8.4.4?  删除字符串中多余的文本 186

对不规范的日期进行计算 187

8.5 合并文本 187

8.5.1?  将多个字符串合并成一个字符串 187

8.5.2?  将销售人员名单按销售部门进行显示 188

动手练 统计员工销量并判断是否达标 189

8.6 删除文本 190

8.6.1 将多个字符串合并成一个字符串 190

8.6.2 删除不能打印的字符 190

动手练 显示12位及以上的数值 191

案例实战：隐藏手机号码中间4位 192

新手答疑 194

第9章

财务函数的应用

9.1 计算固定资产折旧费 196

9.1.1 用余额递减法计算固定资产的年度折旧值 196

9.1.2 用双倍余额递减法计算资产折旧值 197

9.1.3 使用年限总和折旧法计算折旧值 198

9.1.4 计算折旧期限为8年的固定资产的折旧值 199

9.1.5 使用双倍余额递减法计算任何期间的资产折旧值 199

动手练 计算前200天的折旧值 200

9.2 计算存款和利息 201

9.2.1 计算某项投资的未来值 201

动手练 计算银行存款总额 202

9.2.2 计算贷款的现值 203

9.2.3 计算贷款需多少年才能还清 204

9.2.4 计算贷款的年利率 205

9.2.5 计算给定时间段内累计偿还的利息 206

9.2.6 计算贷款在第一个月偿还的本金 207

9.3 计算内部收益率 208

9.3.1 计算投资的内部收益率 208

9.3.2 计算基金定投的年化收益率 209

9.3.3 计算四年后投资的修正收益率 210

动手练 计算实际年利率 211

案例实战：计提固定资产折旧额 212

新手答疑 214

第10章

信息函数的应用

10.1?    数据类型信息的获得 216

10.1.1   提取当前工作表的名称 216

10.1.2   判断错误类型 217

10.1.3   按类别单独设置序号 218

         动手练判断成绩等级 219

10.2?    使用IS函数进行各种判断 220

10.2.1   统计缺勤人数 220

10.2.2   统计缺考人数 220

10.2.3   判断数值是否为偶数 221

10.2.4   判断员工性别 222

10.2.5   屏蔽单元格中数值的错误 222

10.2.6   计算项目的完成率 223

         计算男、 女员工人数 224

案例实战： 提取某运动员比赛成绩 225

新手答疑                   226

第11章

函数在条件格式和数据验证中的应用

11.1?    函数在条件格式中的应用 228

11.1.1   标记重复数据 228

11.1.2   突出显示每个商品最高销售量 229

11.1.3   突出显示不达标业绩 230

11.1.4   突出显示周末日期 231

         动手练 将低于60分的考核成绩标为“不合格” 233

11.2?    使用函数设置数据验证 234

11.2.1   禁止输入重复值 234

11.2.2   禁止在单元格中输入空格 235

11.2.3   禁止修改指定区域数据 236

         动手练 保证输入的银行卡号位数正确 237

案例实战： 制作多级下拉菜单 238

新手答疑  240