Excel 2019公式与函数应用大全

本书通过大量的实例详细讲解Excel 2019的常用函数，并且介绍公式与函数相关基础知识以及函数在职场各个领域的具体使用方法。读者在工作时，若有自己不明白的函数，只需要翻开本书查阅相关的讲解即可。 本书共16章，前3章介绍公式和函数操作基础，分别是公式技巧、函数基础、单元格引用、数组的应用、公式的审核与修正、分析与解决公式返回错误值；中间8章介绍8类函数的实际应用，包括逻辑函数、日期和时间函数、数学函数、文本函数、统计函数、财务函数、查找与引用函数、信息函数；*后5章结合实际介绍函数在职场各个领域的应用，包括员工档案管理、员工考勤管理、员工薪资管理、公司销售管理、公司固定资产管理。

前言

第1章 认识Excel公式

1.1 认识公式2

1.1.1 公式的组成2

实战实例：根据面试和笔试成绩计算总成绩2

1.1.2 公式中运算符的使用3

实战实例1：计算总销售额（算术运算符）3

实战实例2：比较各店面的销售额（比较运算符）4

实战实例3：统计各季度分店的总销售额（引用运算符）4

1.1.3 公式输入与修改5

实战实例1：统计各产品的折扣后金额5

实战实例2：重新计算每月总销售额6

1.1.4 公式的复制7

实战实例1：统计学生总成绩7

实战实例2：统计各店铺兼职人员工资9

1.2 认识函数10

1.2.1 函数的巨大作用10

实战实例1：计算全年总支出11

实战实例2：筛选应聘者的年龄11

1.2.2 参数设置要符合规则12

实战实例1：判断面试成绩是否合格12

实战实例2：统计指定产品的总销量13

1.2.3 初学者如何学习函数14

实战实例1：打开“插入函数”对话框逐步了解参数14

实战实例2：使用帮助功能学习函数15

1.2.4 正确输入函数的方法16

实战实例1：使用编辑栏中的“插入函数”按钮输入公式16

实战实例2：使用“公式”选项卡中的“插入函数”按钮输入公式17

1.2.5 修改与保存函数公式19

实战实例1：修改函数参数19

实战实例2：保存未输入完的公式20

1.3 嵌套函数21

实战实例1：两项测试数据都达标时才予以验收合格21

实战实例2：根据职位调整基本工资23

1.4 了解数组公式24

1.4.1 普通公式与数组公式的区别25

实战实例：计算商品折后总金额25

1.4.2 多个单元格数组公式26

实战实例：统计前三名分数26

1.4.3 单个单元格数组公式27

实战实例：计算所有商品的总销售额27

1.4.4 修改或删除数组公式28

实战实例：修改或删除数组公式28

1.5 公式的保护与设置28

实战实例1：设置公式保护29

实战实例2：设置公式隐藏31

第2章 数据源的引用方式

2.1 不同的单元格引用方式34

2.1.1 相对引用34

实战实例：相对引用34

2.1.2 绝对引用36

实战实例：绝对引用36

2.1.3 混合引用38

实战实例：混合引用38

2.1.4 引用当前工作表之外的单元格40

实战实例：引用当前工作表之外的单元格40

2.1.5 引用多个工作表中的同一单元格41

实战实例：引用多个工作表中的同一单元格41

2.1.6 引用其他工作簿中的单元格43

实战实例：引用其他工作簿中的单元格43

2.2 了解名称定义功能45

2.2.1 定义名称的作用45

实战实例1：定义名称简化公式45

实战实例2：快速输入序列46

实战实例3：快速跳转至指定工作表46

2.2.2 定义名称的方法47

实战实例1：使用“定义名称”功能48

实战实例2：在名称框中直接定义名称50

2.2.3 公式中应用名称50

实战实例：公式中应用名称51

2.2.4 定义公式为名称52

实战实例：定义公式为名称52

2.2.5 创建动态名称54

实战实例：创建动态名称54

2.2.6 管理定义的名称57

实战实例1：重新修改名称的引用位置57

实战实例2：删除不再使用的名称58

第3章 常见错误公式修正及错误值分析

3.1 公式检测与审核60

3.1.1 利用“公式求值”按钮解析公式60

实战实例：利用“公式求值”按钮解析公式60

3.1.2 使用【F9】键快速查看公式指定部分计算结果62

实战实例：使用【F9】键快速查看公式指定部分计算结果62

3.1.3 查看从属与引用单元格63

实战实例1：查看从属引用单元格—使用“追踪从属单元格”63

实战实例2：查看从属引用单元格—使用“追踪引用单元格”64

3.1.4 使用“错误检查”功能65

实战实例：使用“错误检查”功能65

3.1.5 利用“监视窗口”监视数据66

实战实例：利用“监视窗口”监视数据66

3.1.6 利用“显示公式”功能查看公式68

实战实例：利用“显示公式”功能查看公式68

3.2 常见错误公式的修正69

3.2.1 修正文本数据参与计算的问题70

实战实例1：数据中带有中文单位70

实战实例2：单元格格式是文本格式70

3.2.2 修正日期计算时差值总为日期问题71

实战实例：修正日期计算时差值总为日期问题71

3.2.3 空白单元格不为空问题72

实战实例1：公式返回的空值再参与计算时造成出错72

实战实例2：单元格中有英文单引号造成出错74

实战实例3：自定义单元格格式为隐藏格式造成出错75

3.2.4 实际的值与显示的值不同77

实战实例：实际的值与显示的值不同77

3.2.5 修正循环引用不能计算的公式78

实战实例：修正循环引用不能计算的公式79

3.3 分析与解决公式返回的错误值80

3.3.1 分析与解决 “####”错误值80

实战实例：分析与解决 “####”错误值80

3.3.2 分析与解决“#DIV/0!”错误值81

实战实例：分析与解决“#DIV/0!”错误值81

3.3.3 分析与解决“#N/A” 错误值82

实战实例1：数据源引用错误82

实战实例2：行数与列数引用不一致83

3.3.4 分析与解决“#NAME?”错误值84

实战实例1：公式中的文本要添加引号84

实战实例2：公式中使用没有定义的名称85

实战实例3：引用单元格区域时缺少冒号87

3.3.5 分析与解决“#NUM!”错误值88

实战实例：分析与解决“#NUM!”错误值88

3.3.6 分析与解决“#VALUE!” 错误值89

实战实例：分析与解决“#VALUE!”错误值89

3.3.7 分析与解决“#REF!” 错误值90

实战实例：分析与解决“#REF!”错误值90

3.3.8 分析与解决“#NULL!” 错误值91

实战实例：分析与解决“#NULL!”错误值91

第4章 逻辑函数

4.1 逻辑判断函数94

4.1.1 AND：判断指定的多个条件是否全部成立94

实战实例1：判断学生各科成绩是否都合格94

实战实例2：判断是否通过职称评定95

4.1.2 OR：判断指定的多个条件是否有一个成立96

实战实例1：判断员工考核是否合格96

实战实例2：判断是否通过职称评定98

4.1.3 NOT：判断指定的条件不成立98

实战实例1：判断员工是否符合发放工龄工资的要求99

实战实例2：筛选出高中学历的应聘人员100

4.2 根据逻辑判断结果返回值101

4.2.1 IF：根据逻辑测试值返回指定值101

实战实例1：判断学生成绩是否优秀101

实战实例2：分区间判断成绩并返回不同结果103

实战实例3：判断能够获得公司年终福利的员工104

实战实例4：判断是否通过职称评定105

实战实例5：根据双条件筛选出符合发放赠品条件的消费者106

实战实例6：根据员工的职位和工龄调整工资107

实战实例7：只为满足条件的商品提价109

4.2.2 IFS：多条件判断110

实战实例：计算销售员提成金额110

4.2.3 SWITCH：返回匹配的结果112

实战实例1：根据代码返回到具体的星期112

实战实例2：分区间判断成绩并返回不同结果113

4.2.4 IFERROR：根据错误值返回指定值114

实战实例：解决被除数为空值（或0值）时返回错误值问题114

第5章 日期与时间函数

5.1 构建与提取日期、时间118

5.1.1 TODAY：返回当前日期118

实战实例1：统计书稿还有多少天截稿118

实战实例2：判断借书证是否到期119

5.1.2 DATE：构建标准日期120

实战实例1：将不规范日期转换为标准日期120

实战实例2：合并年月日并得到完整日期122

5.1.3 TIME：构建标准时间122

实战实例：计算促销商品的结束时间123

5.1.4 YEAR：返回某日期中的年份值124

实战实例：计算员工年龄124

5.1.5 MONTH：返回某日期中的月份值125

实战实例1：判断员工是否是本月过生日125

实战实例2：统计指定月份的总入库量126

5.1.6 DAY：返回以序列号表示的某日期中的天数128

实战实例：按本月缺勤天数计算缺勤扣款金额128

5.1.7 EOMONTH：返回某个月份最后一天的序列号129

实战实例1：计算实习生转正日期130

实战实例2：根据促销开始日期计算促销天数130

5.1.8 WEEKDAY：返回日期对应的星期数132

实战实例1：判断加班日期是星期几132

实战实例2：判断加班日期是工作日还是双休日133

5.1.9 HOUR：返回时间值的小时数134

实战实例：确定客户来访时间区间134

5.1.10 MINUTE：返回时间值的分钟数135

实战实例1：计算商品的促销用时（分钟数）136

实战实例2：计算停车分钟数137

5.1.11 SECOND：返回时间值的秒数138

实战实例：计算比赛总秒数138

5.2 期间差140

5.2.1 DAYS360：返回两日期间相差的天数（按照一年360天的算法）140

实战实例1： 计算账龄140

实战实例2： 计算合同到期天数141

5.2.2 NETWORKDAYS：计算某时段中的工作日天数142

实战实例：计算临时工的实际工作天数142

5.2.3 NETWORKDAYS.INTL函数143

实战实例：计算临时工的实际工作天数（指定只有星期日为休息日）144

5.2.4 WORKDAY：根据起始日期计算出指定工作日之后的日期145

实战实例：计算工程的竣工日期145

5.2.5 WORKDAY.INTL函数146

实战实例：根据项目各流程所需工作日计算项目结束日期147

5.2.6 ISOWEEKNUM：返回日期的ISO周数147

实战实例：判断项目开工日期位于第几周148

5.2.7 EDATE：计算出间隔指定月份数后的日期148

实战实例1：计算食品过期日期149

实战实例2：计算员工退休日期149

5.3 文本日期与文本时间的转换150

5.3.1 DATEVALUE：将文本日期转换为可计算的日期序列号150

实战实例：计算商品促销天数151

5.3.2 TIMEVALUE：将文本时间转换为可计算的小数值152

实战实例：根据下班打卡时间计算加班时长152

第6章 数学函数

6.1 数据计算函数155

6.1.1 SUM：求和155

实战实例1：统计总营业额155

实战实例2：统计总销售额156

实战实例3：统计产品A的总入库量157

6.1.2 SUMIF：根据指定条件对若干单元格求和158

实战实例1：统计指定部门的费用支出总额158

实战实例2：统计某个时段的销售业绩总金额159

实战实例3：使用通配符对某一类数据求和160

6.1.3 SUMIFS：对区域中满足多个条件的单元格求和161

实战实例1：用SUMIF函数实现满足多条件的求和运算162

实战实例2：统计上半个月的总销售额163

实战实例3：统计指定部门指定费用类别的总支出额164

6.1.4 PRODUCT：求所有参数的乘积166

实战实例：计算指定数值的阶乘166

6.1.5 SUMPRODUCT：求数组间对应的元素乘积的和167

实战实例1：计算所有容器的总容量168

实战实例2：使用SUMPRODUCT函数实现满足多条件的求和运算169

实战实例3：使用SUMPRODUCT函数实现满足多条件的计数运算170

实战实例4：统计各部门的工资总额172

实战实例5：统计工作日总销售额173

实战实例6：按月统计销售额174

6.2 舍入函数 176

6.2.1 INT：返回实数向下取整后的整数值176

实战实例：对平均销售量取整176

6.2.2 ROUND：按指定位数对数值四舍五入177

实战实例：为超出的完成量计算奖金177

6.2.3 ROUNDUP：向上舍入数值179

实战实例1：计算材料长度（材料只能多不能少）179

实战实例2：计算上网费用180

实战实例3：计算物品的快递费181

6.2.4 ROUNDDOWN：向下舍入数值182

实战实例：商品折后价格舍尾取整183

6.2.5 CEILING.PRECISE：向上舍入到最接近指定数字的某个值的倍数值183

实战实例：按指定计价单位计算总话费184

6.2.6 MROUND：按照指定基数的倍数对参数四舍五入185

实战实例：计算商品运送车次185

6.2.7 EVEN：将数值向上舍入到最接近的偶数186

实战实例：将数值向上舍入到最接近的偶数186

6.2.8 ODD：将数值向上舍入到最接近的奇数187

实战实例：将数值向上舍入到最接近的奇数187

6.3 随机数函数188

6.3.1 RAND：返回大于等于0及小于1的均匀分布随机数188

实战实例：随机获取选手编号188

6.3.2 RANDBETWEEN：产生整数的随机数189

实战实例：自动生成两位数货号189

第7章 文本函数

7.1 文本提取函数192

7.1.1 FIND：返回字符串在另一个字符串中的起始位置 192

实战实例1：从商品名称中查找空格的位置192

实战实例2：从商品名称中提取品牌名称193

7.1.2 SEARCH：查找字符串中指定字符起始位置（不区分大小写）194

实战实例：从商品订单号中提取交易日期195

7.1.3 LEFT：从最左侧开始提取指定个数的字符196

实战实例1：提取出类别编码196

实战实例2：从学历中提取毕业院校197

实战实例3：统计各个年级参赛的总人数198

7.1.4 RIGHT：从最右侧开始提取指定个数的字符199

实战实例1：提取员工的入职时间199

实战实例2：从文字与金额合并显示的字符串中提取金额数据200

实战实例3：从学历中提取专业名称201

7.1.5 MID：提取文本字符串中从指定位置开始的特定个数的字符202

实战实例1：从货号中提取商品出厂时间202

实战实例2：提取公司所属地203

7.2 文本新旧替换函数204

7.2.1 REPLACE：将一个字符串中的部分字符用另一个字符串替换204

实战实例1：屏蔽中奖号码的后四位205

实战实例2：完善班级名称205

7.2.2 SUBSTITUTE：用新字符串替换字符串中的部分字符串206

实战实例1：删除不规范的空格符号206

实战实例2：将日期规范化再进行求差207

实战实例3：查找特定文本并且将第一次出现的删除，其他保留208

7.3 文本格式转换函数209

7.3.1 ASC：将全角字符更改为半角字符210

实战实例：修正全半角字符不统一导致数据无法统计问题210

7.3.2 DOLLAR：四舍五入数值并添加千分位符号和$符号211

实战实例：将金额转换为美元货币格式211

7.3.3 LOWER：将文本字符串中所有大写字母转换为小写字母212

实战实例：将英文文本转换为小写形式212

7.3.4 PROPER：将英文文本字符串中的首字母转换成大写213

实战实例：将英文单词的首字母转换为大写213

7.3.5 RMB：四舍五入数值并添加千分位符号和￥符号214

实战实例：将数字转换为人民币格式214

7.3.6 TEXT：将数值转换为按指定数字格式表示的文本 215

实战实例1：返回值班日期对应的星期数216

实战实例2：将数值转换为万元显示单位216

实战实例3：解决日期计算返回日期序列号问题217

实战实例4：将员工加班时长转换为指定时间格式219

7.3.7 UPPER： 将小写英文文本转换为大写形式220

实战实例：将小写英文文本转换为大写形式220

7.3.8 VALUE： 将代表数字的文本字符串转换为数值220

实战实例：将文本型数字转换为数值221

7.4 文本的其他操作222

7.4.1 CONCAT：将多个区域和/或字符串的文本组合起来 222

实战实例：合并所有文本数据222

7.4.2 CONCATENATE：将多个文本字符串合并成一个文本字符串 223

实战实例1：合并所有文本数据223

实战实例2：合并业绩总额和达标情况224

7.4.3 TRIM：删除文本中的多余空格225

实战实例：删除产品名称中多余的空格225

7.4.4 CLEAN：删除文本中不能打印的字符226

实战实例：删除不规范的换行符226

7.4.5 EXACT：比较两个文本字符串是否完全相同227

实战实例：比较用户密码设置是否相同227

7.4.6 TEXTJOIN：将多个区域的文本组合起来并指定分隔符228

实战实例：合并商品信息228

7.4.7 REPT：按照给定的次数重复显示文本229

实战实例：快速输入多个相同符号229

第8章 统计函数

8.1 基础统计函数232

8.1.1 AVERAGE：计算平均值232

实战实例1：计算财务部平均工资232

实战实例2：统计大于平均分数的人数233

实战实例3：动态统计网站平均点击率234

8.1.2 AVERAGEA：计算平均值（包括文本、逻辑值）235

实战实例：计算业务员的平均业绩（包含文本值）235

8.1.3 AVERAGEIF：对区域中满足条件的单元格求平均值236

实战实例1：统计各分店平均销售业绩236

实战实例2：使用通配符对某一类数据求平均值238

8.1.4 AVERAGEIFS：对区域中满足多个条件的单元格求平均值239

实战实例1：计算满足双条件的平均值240

实战实例2：计算五中英语作文的平均分数241

8.1.5 COUNT：统计单元格区域中含有数值数据的单元格个数242

实战实例1：统计本月获得满勤奖的总人数242

实战实例2：统计销售部人数243

8.1.6 COUNTA：统计单元格区域中含有数据的单元格个数244

实战实例：统计出参加考试的人数244

8.1.7 COUNTIF：统计满足给定条件的数据个数245

实战实例1：统计某课程的报名人数245

实战实例2：统计业绩小于等于1万的人数246

8.1.8 COUNTIFS：统计满足多重条件的单元格数目246

实战实例1：统计销售1部业绩大于1万元人数247

实战实例2：统计指定品牌每日的销售记录数248

8.1.9 MAX（MIN）：返回一组值中的最大值、最小值249

实战实例1：返回最高产量249

实战实例2：统计8月份最高用电量250

实战实例3：忽略0值求出最低分数251

实战实例4：计算出单日最高的销售额252

实战实例5：根据部门与工龄计算应发奖金253

8.1.10 MAXIFS（MINIFS）：返回一组给定条件或标准指定的单元格中的最大值、最小值255

实战实例1：统计上海地区女装的最高销售额255

实战实例2：统计一车间女性员工的最低工资256

8.1.11 LARGE（SMALL）：返回某数据集的某个最大值、最小值257

实战实例1：返回排名前三的销售额257

实战实例2：分班级统计各班级的前三名成绩258

实战实例3：返回倒数第一名的成绩与对应姓名259

8.1.12 TRIMMEAN：求一组数据集的内部平均值261

实战实例：通过6位评委打分并计算选手的最后得分261

8.1.13 GEOMEAN：返回数据集的几何平均值262

实战实例：判断两组数据的稳定性262

8.1.14 RANK.EQ：返回数组的最高排位263

实战实例：对每月产量进行排名263

8.2 方差、协方差与偏差264

8.2.1 VAR.S：计算基于样本的方差265

实战实例：估算产品质量的方差265

8.2.2 VAR.P：计算基于样本总体的方差266

实战实例：以样本值估算总体的方差266

8.2.3 STDEV.S：计算基于样本估算标准偏差267

实战实例：估算班级女生身高的标准偏差267

8.2.4 STDEV.P：计算样本总体的标准偏差268

实战实例：以样本值估算总体的标准偏差268

8.2.5 COVARIANCE.S：返回样本协方差269

实战实例：计算甲状腺与碘食用量的协方差270

8.2.6 COVARIANCE.P：返回总体协方差271

实战实例：以样本值估算总体的协方差271

8.2.7 AVEDEV：计算数值的平均绝对偏差272

实战实例：判断哪个班级学生被保送的可能性最高272

8.3 数据预测273

8.3.1 LINEST：对已知数据进行最佳直线拟合273

实战实例：根据生产数量预测产品的单个成本273

8.3.2 TREND：构造线性回归直线方程274

实战实例：根据前几次模拟考试成绩预测后期考试分数275

8.3.3 LOGEST：回归拟合曲线返回该曲线的数值276

实战实例：预测网站专题的点击量276

8.3.4 GROWTH：根据现有的数据预测指数增长值277

实战实例：预测销售量277

8.3.5 FORECAST：根据已有的数值计算或预测未来值278

实战实例：预测未来值279

8.3.6 CORREL：返回两变量相关系数279

实战实例：分析温度和空调销量的相关性280

8.4 其他常用指标统计函数280

8.4.1 MODE.SNGL：返回数组中的众数280

实战实例：返回频率最高的身高281

8.4.2 MEDIAN：求一组数的中值281

实战实例：返回中间值的身高282

8.4.3 MODE.MULT：返回一组数据集中出现频率最高的数值282

实战实例：统计出现次数最多的错误代码283

8.4.4 FREQUENCY：频数分布统计284

实战实例：统计考试分数的分布区间284

8.4.5 PROB：返回数值落在指定区间内的概率285

实战实例：计算出中奖概率285

8.4.6 KURT：返回数据集的峰值286

实战实例：计算员工在一段时期内薪酬的峰值286

第9章 财务函数

9.1 投资计算函数289

9.1.1 FV：固定利率及等额分期付款方式返回投资未来值289

实战实例1：计算住房公积金的未来值289

实战实例2：计算投资的未来值290

实战实例3：计算某项保险的未来值290

9.1.2 FVSCHEDULE：计算投资在变动或可调利率下的未来值291

实战实例：计算投资在可变利率下的未来值291

9.1.3 IPMT：返回贷款的给定期间内利息偿还额292

实战实例：计算贷款每年偿还额中的利息额（等额分期付款方式）292

9.1.4 ISPMT：等额本金还款方式的利息计算293

实战实例：计算贷款每年偿还额中的利息额（等额本金付款方式）293

9.1.5 PMT：基于固定利率返回贷款的每期等额付款额294

实战实例1：计算贷款的每年偿还额295

实战实例2：按季度（月）支付时计算每期偿还额295

9.1.6 PPMT：计算指定期间内本金偿还额296

实战实例：计算指定期间的本金偿还额297

9.1.7 NPV：返回投资的净现值297

实战实例：计算一笔投资的净现值298

9.1.8 PV：返回投资的现值299

实战实例：计算一笔投资的现值299

9.1.9 XNPV：返回一组不定期现金流的净现值300

实战实例：计算出一笔不定期投资额的净现值300

9.1.10 EFFECT：计算实际年利率301实战实例：计算债券的年利率301

9.1.11 NOMINAL：计算名义利率302

实战实例：将实际年利率转换为名义年利率30