Excel 2019公式与函数应用大全 视频教学版PDF电子书下载

第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