Excel 2019公式与函数应用大全（视频教学版）

前言

第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
......