《Excel 2019公式与函数应用大全 视频教学版》求取 ⇩

第1章认识Excel公式2

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章数据源的引用方式34

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章常见错误公式修正及错误值分析60

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章逻辑函数94

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章日期与时间函数118

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章数学函数155

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章文本函数192

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章统计函数232

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章财务函数289

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

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

9.1.12 NPER：返回某项投资的总期数302

实战实例：计算一笔投资的期数303

9.2折旧计算函数304

9.2.1 DB：使用固定余额递减法计算折旧值304

实战实例1：使用固定余额递减法计算出固定资产的每月折旧额304

实战实例2：使用固定余额递减法计算出固定资产的每年折旧额305

9.2.2 DDB：使用双倍余额递减法计算折旧值306

实战实例：使用双倍余额递减法计算固定资产的每年折旧额306

9.2.3 SLN：返回某项资产在一个期间中的线性折旧值307

实战实例1：使用直线折旧法计算固定资产的每月折旧额307

实战实例2：使用直线折旧法计算固定资产的每年折旧额308

9.2.4 SYD：使用年数总和折旧法计算折旧值308

实战实例：使用年数总和法计算固定资产的年折旧额309

9.3偿还率计算函数310

9.3.1 IRR：计算内部收益率310

实战实例：计算一笔投资的内部收益率310

9.3.2 MIRR：返回某一连续期间内现金流的修正内部收益率311

实战实例：计算不同利率下的修正内部收益率311

9.3.3 RATE：返回年金的各期利率312

实战实例：计算一笔投资的年增长率312

9.3.4 XIRR：计算不定期现金流的内部收益率313

实战实例：计算一组不定期盈利额的内部收益率313

第10章查找与引用函数315

10.1数据的引用315

10.1.1 CHOOSE：根据给定的索引值返回数值参数清单中的数值315

实战实例1：判断分店业绩是否达标315

实战实例2：统计参赛者的获奖结果（金、银、铜牌）316

实战实例3：返回成绩最好的三名学生姓名318

10.1.2 ROW：返回引用的行号函数319

实战实例1：自动生成大批量序号320

实战实例2：分科目统计平均分321

10.1.3 COLUMN：返回引用的列号函数322

实战实例：实现隔列求总支出额323

10.1.4 OFFSET：以指定引用为参照系，通过给定偏移量得到新引用324

实战实例1：对库存量累计求和326

实战实例2：实现数据动态查询327

实战实例3： OFFSET用于创建动态图表的数据源328

10.2数据的查找331

10.2.1 VLOOKUP：查找目标数据并返回当前行中指定列处的值331

实战实例1：查找指定产品的库存量331

实战实例2：根据货号自动查询相关信息332

实战实例3：代替IF函数的多层嵌套（模糊匹配）334

实战实例4：根据销售区域判断提成率335

实战实例5：查找并返回符合条件的多条记录337

实战实例6：VLOOKUP应对多条件匹配339

10.2.2 LOOKUP：查找目标数据并返回当前行中指定数组中的值340

实战实例1： LOOKUP模糊查找341

实战实例2：通过简称或关键字模糊匹配342

实战实例3： LOOKUP辅助数据查找344

实战实例4： LOOKUP满足多条件查找345

10.2.3 HLOOKUP：在首行查找指定的数值并返回当前行中指定列处的数值346

实战实例：根据不同的提成率计算业务员的奖金347

10.2.4 MATCH：查找并返回值所在位置348

实战实例1：判断某数据是否包含在另一组数据中348

实战实例2：查找指定店铺业绩是否达标349

10.2.5 INDEX：返回指定行列交叉处引用的值350

实战实例1：返回指定行列交叉处的值351

实战实例2：查找指定人员指定科目的分数352

实战实例3：反向查询总分最高的应聘人员353

实战实例4：返回值班次数最多的员工姓名355

第11章信息函数357

11.1信息获得函数357

11.1.1 CELL：返回单元格的信息357

实战实例：库存量过小时提示“补货”357

11.1.2 TYPE：返回单元格内的数值类型358

实战实例：测试数据是否是数值型359

11.2 IS函数360

11.2.1 ISBLANK：判断测试对象是否为空单元格360

实战实例1：统计停留车辆数360

实战实例2：标注“无业绩”的销售员361

11.2.2 ISERROR：检测一个值是否为错误值362

实战实例：错误值结果显示为空362

11.2.3 ISNA：检测一个值是否为#N/A错误值363

实战实例：查询员工错误时显示“无此人员”363

11.2.4 ISNUMBER：检测一个值是否为数值364

实战实例：快速统计出离开的车辆365

11.2.5 ISEVEN：检测一个值是否为偶数366

实战实例：根据身份证号码判断性别366

11.2.6 ISODD：检测一个值是否为奇数367

实战实例1：根据员工编号判断性别367

实战实例2：分奇偶月计算总销售量368

11.2.7 ISTEXT：检测一个值是否为文本370

实战实例：统计装修中的店铺数量370

11.2.8 ISNONTEXT：检测一个值是否为非文本371

实战实例：统计公司实到人数371

第12章员工档案管理374

12.1员工档案管理表374

12.1.1创建员工档案管理表374

12.1.2利用数据有效性防止工号重复输入376

12.1.3从身份证号码中提取有效信息377

12.1.4计算员工年龄和工龄379

12.2员工档案查询表381

12.2.1创建员工档案查询表381

12.2.2使用VLOOKUP函数查询人员信息382

12.3使用函数制作到期提醒385

12.3.1生日到期提醒385

12.3.2试用期到期提醒388

12.4员工学历层次分析390

12.4.1编制员工学历数据透视表390

12.4.2制作员工学历透视图393

12.5员工年龄段分析395

12.5.1编制员工年龄段数据透视表395

12.5.2制作员工学历透视图397

第13章员工考勤管理401

13.1员工考勤登记表401

13.1.1绘制员工基本考勤登记表401

13.1.2设置考勤日期和星期格式404

13.1.3冻结窗格显示固定数据408

13.1.4添加考勤符号409

13.2员工考勤统计表411

13.2.1创建员工考勤统计表411

13.2.2员工考勤扣款统计414

13.2.3员工满勤奖统计416

13.3员工出勤情况分析417

13.3.1员工出勤率统计417

13.3.2使用数据透视表统计各部门出勤情况419

第14章员工薪资管理424

14.1员工销售业绩奖金424

14.1.1创建销售记录表424

14.1.2计算本月业绩奖金424

14.2员工基本工资表426

14.2.1创建员工基本工资表426

14.2.2计算员工工龄及工龄工资427

14.3复制员工考勤扣款及满勤奖表格428

14.4员工福利补贴表429

14.4.1创建员工福利表429

14.4.2计算员工福利补贴430

14.5员工社保缴费表432

14.5.1创建员工社保缴费表432

14.5.2计算各项社保缴费明细432

14.6工资统计表434

14.6.1创建员工月度工资表434

14.6.2计算工资表中应发金额435

14.6.3计算工资表中应扣金额437

14.7员工工资单439

14.7.1创建员工的工资单439

14.7.2快速生成每位员工的工资单441

第15章公司销售管理444

15.1销售记录表444

15.1.1创建销售记录表444

15.1.2标记出排名前三的销售金额444

15.1.3筛选出指定的销售记录447

15.2按产品系列统计销售收入449

15.2.1创建基本表格449

15.2.2创建饼图显示各产品销售金额451

15.3销售收入变动趋势分析454

15.3.1创建基本表格454

15.3.2创建图表显示销售变动趋势455

15.4销售员业绩分析457

15.4.1创建数据透视表457

15.4.2创建数据透视图459

第16章固定资产管理464

16.1固定资产清单464

16.1.1创建固定资产清单464

16.1.2使用公式计算固定资产状态466

16.1.3查询部门固定资产清单468

16.2固定资产查询表469

16.2.1返回固定资产各项信息469

16.2.2固定资产折旧的计算473

16.3企业固定资产折旧计算476

16.3.1使用固定余额法进行折旧计算476

16.3.2使用双倍余额递减法进行折旧计算477

16.3.3使用年限总和法进行折旧计算479