Excel应用技巧秒查秒懂 486集视频课+425个应用技巧

第 1 章　工作簿操作与打印 1
1.1 工作簿创建技巧 1
1．将 Excel 2021固定到开始屏幕 1
2．保存新工作簿 2
3．创建模板工作簿 3
4．保存工作簿模板 5
5．打开*近使用的工作簿 6
6．固定*近常用的工作簿 7
1.2 工作簿优化技巧 8
1．一键快速选择常用命令 8
2．为工作簿定制默认字体格式 9
3．为工作簿定制默认表格数量 10
4．为工作簿定制常用的功能按钮 11
5．为工作簿定制默认的保存路径 12
6．快速恢复工作簿默认状态 13
例 1：工作表中的行号和列标恢复法 13
例 2：取消鼠标中键的智能缩放功能 14
7．启动 Excel 时自动打开某个工作簿 14
1.3 工作簿打印技巧 15
1．打印表格的指定页 15
2．只打印表格的指定区域 16
3．一次性打张工作表 17
4．超宽工作表打印技巧 18
5．在表格每一页显示指定行列 19
6．按数据透视表的字段分项打印 21
7．按数据透视表的筛选字段分项打印 22
8．分页打印分类汇总数据表 24
9．将打印内容显示到纸张中间 25
1.4 工作簿保护技巧 27
1．加密保护重要工作簿 27
2．设置工作表修改权限密码 28
3．保护工作表的指定区域 29
4．清除工作簿中的个人信息 30
5．隐藏“*近使用的工作簿” 31
6．阻止文档中的外部内容 32
1.5 工作簿打包技巧 33
1．将工作簿导出为 PDF 文件 33
2．将数据透视表输出为报表 35
3．将图表保存为图片 35
4．将 Excel 表格或图表应用到 Word 报告中 36
5．将 Excel 表格或图表应用到 PPT 幻灯片 37
第 2 章 工作表基本操作 39
2.1 工作表操作 39
1．添加新工作表 39
2．重命名工作表 39
3．重设工作表标签颜色 40
4．快速移动或复制工作表 41
5．跨工作簿移动复制工作表 42
6．将多张工作表建立为一个工作组 43
7．隐藏包含重要数据的工作表 44
8．隐藏工作表标签 44
2.2 工作表数据查看 45
1．查看数据时冻结窗格 46
2．在任意位置拆分表格 47
3．缩放表格窗口的显示比例 47
4．并排比较工作表 48
2.3 导入外部工作表 49
1．导入 Excel 表格数据 50
2．导入文本文件数据 51
3．导入网页中的表格 53 第３章 数据输入与填充 55
3.1 数据输入技巧 55
1．输入生僻字的技巧 55
2．运用“墨迹公式”手写数学公式 56
3．输入身份证号码或长编码 58
4．设置指定的日期格式 59
5．输入大写人民币金额 60
6．输入时在指定位置强制换行 61
7．记忆式输入提高数据录入效率 62
8．让数据自动保留两位小数位数 64
3.2 数据填充技巧 65
1．在连续单元格中填充相同数据 65
2．在不连续单元格中填充相同数据 66
3．在大范围区域中填充相同数据 66
4．在多张工作表中填充相同数据 67
3.3 自动填充选项技巧 68
1．快速填充递增序号 69
2．快速填充相同日期 70
3．快速填充工作日日期 70
4．按分钟数递增填充 71
5．快速填充大批量序号 71
6．自定义填充序列 72
7．解决填充柄消失问题 74 第 4 章 数据定位应用 76
1．定位大范围区域 76
2．超大范围公式复制的办法 77
3．忽略非空单元格填充数据 78
4．查看公式引用的所有单元格 79
5．快速定位合并的单元格 80
6．自定义视图快速定位区域 81
7．定位设置数据验证的单元格 83
8．核对两列数据是否一致 84 第 5 章 数据查找替换与复制粘贴 85
5.1 数据的查找和替换 85
1．为替换后的内容设置格式 85
2．单元格匹配替换数据 87
3．查找数据应用通配符 88
4．替换小于指定数值的数据 89
5．快速选中格式相同的单元格 91
5.2 复制粘贴及选择性粘贴 92
1．正确复制筛选后的数据 93
2．仅复制单元格的格式 94
3．粘贴数据匹配目标区域格式 95
4．同增同减数据时忽略空单元格 96
5．让粘贴数据随源数据自动更新 98
6．链接 Excel 与 Word 中的表格 99 第 6 章 规范表格结构与数据 100
6.1 表格结构调整 100
1．一次性插入多行、多列 100
2．一次性隔行插入空行 101
3．每隔 4 行（或任意指定行数）插入空行 102
4．整行整列数据调换 103
5．删除有空白单元格的所有行 105
6.2 规范表格数据 106
1．删除重复值 106
2．拆分不同类型的数据 107
3．解决分类汇总的合并单元格问题 109
4．巧用 Word 批量清除指定字符 111
6.3 处理无法计算的数据 113
1．为什么明明显示的是数值却不能计算 113
2．处理不规范的无法计算的日期 114
3．一次性处理文本中所有空格 116
4．谨防空值陷阱 117
第7 章 数据验证与自定义格式 120
7.1 设置数据验证规范输入 120
1．建立可选择输入序列 120
2．限制输入的数据范围 121
3．限制数据的*大值 123
4．限制输入指定范围日期 123
5．圈释无效数据 125
6．快速应用相同的数据验证 126
7.2 公式在数据验证中的应用 127
1．避免输入重复值 127
2．限制单元格只能输入文本 128
3．避免输入文本数据 129
4．限制输入空格 130
5．限定单元格内必须包含指定内容 132
6．禁止出库量大于库存量 133
7．轻松制作二级下拉菜单 134
7.3 自定义单元格格式 137
1．自定义日期数据格式 139
2．在考勤表中只显示日 140
3．为数据批量添加重量单位 141
4．数据部分重复时的快捷输入技巧 142
5．输入 1 显示“√”，输入 0 显示“×” 143
6．根据正负值自动显示前缀文字 144
7．约定数据宽度不足时用 0 补齐 145
8．设置数据以“万元”为显示单位 146
9．根据数值大小显示不同的颜色 148
10．设置业绩小于 5 万元时显示“业绩不达标” 149
11．隐藏单元格中所有或部分数据 150 第 8 章 表格美化设置 151
8.1 表格外观美化 151
1．合并居中显示表格的标题 151
2．设置标题分散对齐效果 152
3．为表格标题设置下画线 153
4．让表格部分数据竖向显示 154
5．批量调整行高、列宽 155
6．精确调整行高、列宽 157
7．为表格定制边框线条 158
8．快速套用表格样式 159
8.2 表格格式美化 160
1．设置文字对齐方式 161
2．使用艺术字标题 161
3．自定义艺术字样式 162
4．快速设置注释文本样式 164
5．自定义单元格样式 165
6．“格式刷”快速复制格式 167
8.3 表格页面美化 168
1．设置页面背景 168
2．调整默认页边距 169
3．在任意位置分页 170
4．应用页眉 172
5．应用页脚 173
8.4 用图形、图片、图示来美化表格 174
1．插入并调整图片 174
2．裁剪图片 176
3．删除图片背景 178
4．调整图片色调 180
5．快速应用图片样式 181
6．快速应用图标 181
7．应用图片版式 182
8．应用形状 184
9．为形状添加文本 184
10．设置形状边框线条 185
11．设置形状填充效果 186
12．设置对象叠放次序 188
13．应用 SmartArt 图形 188
14．SmartArt 图形不够时自定义添加 189
15．更改 SmartArt 图形布局和样式 190 第 9 章 条件格式应用 193
9.1 常用条件格式类型 193
1．突出显示大于指定数值的单元格 193
2．突出显示介于指定数值的单元格 194
3．设定包含某文本时显示特殊格式 195
4．按发生日期显示特殊格式 196
5．重复值（唯一值）显示特殊格式 197
6．排名靠前 / 后 N 位显示特殊格式 198
7．高 / 低于平均值时显示特殊格式 199
8．设定排除某文本时显示特殊格式 200
9．设定以某文本开头时显示特殊格式 202
9.2 使用图形的条件格式 203
1．应用数据条开关 204
2．给优秀成绩插红色旗帜 207
3．升降箭头标识数据 209
9.3 使用公式建立规则 210
1．自动标识周末日期 210
2．自动提醒值班日 212
3．标记出优秀学生姓名 213
4．突出显示每行的*大 / *小值 214
5．设置多个条件格式规则 216
6．自动标识指定值班人 218
7．查找数据后自动标记颜色 220 第 10 章 数据筛选应用 223
10.1 数值筛选 223
1．筛选大于特定值的记录 223
2．筛选介于指定值之间的记录 224
3．筛选小于平均值的记录 225
4．筛选出指定时间区域的记录 225
5．筛选出以指定数字开头的记录 226
10.2 文本筛选 227
1．模糊筛选获取同一类型的数据 227
2．筛选出开头不包含某文本的所有记录 228
3．利用搜索筛选器将筛选结果中的某类数据再次排除 229
10.3 日期筛选 230
1．将日期按年（月、周）筛选 230
2．按季度筛选数据记录 232
3．按任意日期区间筛选 233
4．筛选日期时不按年月日分组 234
10.4 高级筛选 235
1．使用高级筛选提取指定数据 235
2．筛选出不重复的记录 236
3．根据目标单元格进行快速筛选 237
4．对双行标题列表进行筛选 237
5．使用“表”功能筛选并汇总数据 238
6．高级筛选实现一对多查询 240
7．高级筛选出同时满足多个条件的记录 241
8．高级筛选出满足多个条件中一个条件的所有记录 243
9．在高级筛选条件区域中使用通配符 244 第 11 章 数据排序应用 246
11.1 简单排序 246
1．按列排序数据 246
2．按行排序数据 246
3．按汉字笔画排序 247
4．按单元格颜色排序 249
5．按单元格图标排序 250
11.2 按条件排序 251
1．按多条件排序 251
2．自定义排序规则 252
3．应用函数随机排序 253
4．恢复排序前的数据 254
第 12 章 数据透视表应用 256
12.1 按步骤创建数据透视表 256
1．整理好数据源表格 256
2．两步创建数据透视表 257
3．为透视表添加字段 258
4．重新设置值的汇总方式 259
5．添加多个值汇总方式 260
6．重新设置值的显示方式 261
7．应用外部数据表 262
8．重新更改数据透视表的数据源 263
9．更新数据时要刷新数据透视表 264
10．创建自动更新的数据透视表 265
11．美化数据透视表样式 267
12.2 数据透视表应用实例 268
1．统计指定时间段订单 268
2．应用多张数据透视表 271
3．按百分比统计各店铺数据 272
4．应用多张数据透视图 273
5．应用日程表查看数据 275
6．应用切片器查看数据 276
7．统计销售额占分类汇总的百分比 278
8．按日累计销售额 279
9．自定义公式求解各业务员奖金 280
10．显示项的明细数据 281
11．筛选销售额在前 5 名的产品 282
12．筛选介于指定日期区间的汇总值 283
13．按周分组汇总产量 284
14．统计指定分数区间的人数 284
15．按分数区间将成绩分为“优”“良”“差”等级 286
16．将支出金额按半年汇总 287
12.3 设置数据透视表、图格式 288
1．重设数据的显示格式 288
2．更改透视表的报表布局 289
3．添加空行让分级显示更清晰 290
4．在数据透视图中筛选查看部分数据 290 第 13 章 图表应用 292
13.1 按步骤创建图表 292
1．整理数据源表格 292
2．了解常用图表类型 294
3．了解图表布局元素 295
4．图表设计应当以简洁为主 296
5．两步快速创建*佳图表 297
6．选择部分数据源创建图表 298
7．快速向图表中添加新数据 299
8．快速更改图表为另一类型 299
9．正确添加数据标签 301
10．添加值标签以外的数据标签 301
11．为饼图添加百分比数据标签 302
12．隐藏元素以简化图表 303
13．快速美化图表 304
14．将图表保存为模板 305
13.2 其他图表类型应用 307
1．展示数据层级关系的旭日图 307
2．展示数据累计的瀑布图 308
3．展示数据分布区域的直方图 309
4．展示数据中*重要因素的排列图 311
5．展示数据环节的漏斗图 311
6．展示重要数据的复合条饼图 312
7．展示数据统计的迷你图 314
13.3 图表编辑技巧应用 315
1．快速改变图表展示重点 315
2．解决坐标轴被覆盖的问题 316
3．用垂直轴分割图表 317
4．设置数值刻度单位 318
5．解决刻度值次序颠倒问题 320
6．根据不连续日期建立柱形图 321
7．突出显示图表中的极值 321
8．左右对比的条形图 323
9．两项指标比较的温度计图 325
10．添加趋势线预测数据 327
13.4 公式辅助图表应用 328
1．制作柱形图均值比较线 328
2．为柱形图添加升降箭头 330
3．为柱形图添加动态排序 336
4．在条形图中显示进度 337
13.5 图表格式应用技巧 339
1．设置折线图数据点格式 339
2．图形、图片、文本框辅助设计 343
3．制作南丁格尔玫瑰图 345 第 14 章 高级数据分析 348
14.1 模拟运算与单变量求解 348
1．单变量求解示例 348
2．单变量模拟运算示例 349
3．双变量模拟运算示例 350
14.2 规划求解 351
1．加载规划求解功能 352
2．建立*佳报价方案 353
3．建立合理的生产方案 355
14.3 方案管理器 358
1．创建方案分析模型 358
2．建立方案管理器 359
3．应用方案管理器 361
14.4 分析工具库 362
1．单因素方差分析——分析学历层次对综合考评能力的影响 363
2．双因素方差分析——分析何种因素对生产量有显著性影响 364
3．相关系数——分析产量和施肥量是否有相关性 366
4．协方差——分析数据的相关性 367
5．描述统计——分析学生成绩的稳定性 368
6．移动平均——使用移动平均预测销售量 369
7．移动平均——指数平滑法预测产品的生产量 371
8．回归——一元线性回归预测 372
9．回归——多元线性回归预测 374
10．排位与百分比排位——对学生成绩进行排位 375
11．抽样——从数据库中快速抽取样本 376 第 15 章 数据合并计算与分类汇总 378
15.1 合并计算 378
1．合并计算统计总费用支出 378
2．合并计算时应用通配符 380
3．合并计算核对两列数据 382
4．统计各部门平均工资 383
5．统计员工值班次数 386
6．分析各班成绩的稳定程度 386
15.2 分类汇总 388
1．创建单级分类汇总 388
2．显示分类汇总结果 390
3．添加多种汇总方式 390
4．创建多级分类汇总 391
5．制作带页小计的工作表 393
6．复制分类汇总统计结果 395 第 16 章 了解公式与函数 397
16.1 公式操作技巧 397
1．手动编辑公式 397
2．复制公式完成批量计算 398
3．引用相对数据源 400
4．引用绝对数据源 401
5．引用当前工作表之外的单元格 402
6．定义名称简化公式引用 403
16.2 公式数组运算技巧 405
1．多个单元格数组公式 406
2．单个单元格数组公式 406
16.3 函数应用技巧 408
1．函数的优势作用 408
2．函数应用的结构 409
3．函数参数的编写技巧 410
4．Excel 搜索功能快速学习函数 413
5．利用提示列表快速选择函数 413
6．多种函数嵌套应用 414
7．利用“公式求值”理解公式 415 第 17 章 逻辑判断与数学函数应用 417
17.1 逻辑判断函数 417
1．AND 函数 417
2．OR 函数 419
3．IF 函数 420
例 1：根据销售额返回提成率（IF 函数嵌套） 420
例 2：只为满足条件的员工调整薪资（IF 函数嵌套其他函数） 421
4．IFS 函数 423
例 1：根据销售额返回提成率（IFS 函数） 423
例 2：根据工龄和职位统计年终奖 424
例 3：根据分数进行等级评定 425
17.2 求和函数应用 425
1．SUM（对给定的数据区域求和） 426
例 1：用“自动求和”按钮快速求和 427
例 2：对一个数据区域求和 428
例 3：求排名前三的产量总和 429
2．SUMIF（按照指定条件求和） 430
例 1：统计各销售员的销售业绩总和 430
例 2：统计指定时段的总销售额 431
例 3：用通配符对某一类数据求和 432
例 4：用通配符求所有车间工人的工资和 433
3．SUMIFS（对满足多重条件的单元格求和） 434
例 1：统计指定店面中指定品牌的总销售额 434
例 2：按月汇总出库量 435
4．SUMPRODUCT（将数组间对应的元素相乘并返回乘积之和） 437
例 1：计算商品的折后总销售额 440
例 2：满足多条件时求和运算 440
例 3：统计周末的总营业额 441
例 4：汇总某两种产品的总销售额 442
例 5：统计大于 12 个月的账款 443
例 6：统计指定部门工资大于指定数值的人数 444
17.3 数据的舍入 445
1．INT（将数字向下舍入到*接近的整数） 445
例：对平均销量取整 446
2．ROUND（对数据进行四舍五入） 447
例：为超出完成量的计算奖金 448
3．ROUNDUP（远离 0 值向上舍入数值） 449
例 1：计算材料长度（材料只能多不能少） 450
例 2：计算物品的快递费用 450
4．ROUNDDOWN（靠近 0 值向下舍入数值） 451
例：折后金额舍尾取整 452
5．RANDARRAY（返回一组随机数字） 452
例：返回一组 100 ～ 1000 之间的随机整数 453
6. SWITCH（根据表达式的返回值匹配结果） 454
例 1：只安排星期一至星期三值班 454
例 2：提取产品的规格容量 455 第 18 章 统计函数应用 457
18.1 求平均值计算 457
1．AVERAGE（计算算术平均值） 457
例 1：计算月平均支出费用 458
例 2：计算平均成绩（将空白单元格计算在内） 459
例 3：实现平均分数的动态计算 460
2．AVERAGEIF（返回满足条件的平均值） 461
例 1：按班级统计平均分 462
例 2：计算平均值时排除 0 值 463
例 3：使用通配符对某一类数据求平均值 463
例 4：排除部分数据计算平均值 464
3．AVERAGEIFS（返回满足多重条件的平均值） 465
例：计算指定班级指定性别的平均分钟数 466
4．GEOMEAN（返回几何平均值） 467
例：判断两组数据的稳定性 468
5．HARMEAN（返回数据集的调和平均值） 468
例：计算固定时间内几位学生的平均解题数 469
6．TRIMMEAN（截头尾返回数据集的平均值） 469
例：通过 10 位评委打分计算选手的*后得分 470
18.2 按条件计数统计 471
1．COUNT（统计含有数字的单元格个数） 471
例 1：统计出席会议的人数 471
例 2：统计 1 月份获取交通补助的总人数 472
例 3：统计出某一项成绩为满分的人数 473
2．COUNTIF（统计满足给定条件的单元格的个数） 474
例 1：统计指定学历的人数 474
例 2：统计成绩表中分数大于 90 分的人数 475
3．COUNTIFS（统计同时满足多个条件的单元格的个数） 476
例 1：统计指定部门销量达标人数 476
例 2：统计指定职位男、女应聘人数 477
4．COUNTBLANK（计算空白单元格的数目） 478
例：检查应聘者信息填写是否完善 479
18.3 *大 / *小值计数 480
1．MAX/MIN（返回数据集的*大 / *小值） 480
2．MAXIFS/MINIFS（返回给定条件指定单元格的*大 / *小值）481
例 1：求指定班级的*高分 481
例 2：忽略 0 值求出*低分数 482
3．LARGE（返回表格或区域中的值或值的引用） 483
例 1：返回排名前 3 的销售额 483
例 2：分班级统计各班级的前 3 名的成绩 484
4．SMALL（返回某数据集的某个*小值） 485
例：返回倒数**名的成绩与对应的姓名 486
18.4 排位统计 487
1．MEDIAN（返回中位数） 487
例：返回一个数据序列的中间值 488
2．RANK.EQ（返回数组的*高排位） 489
例 1：对销售业绩进行排名 489
例 2：对不连续的数据进行排名 490
3．RANK.AVG（返回数字列表中的排位） 491
例：对员工考核成绩排名次 491
4．QUARTILE.INC（返回四分位数） 492
例：四分位数偏度系数 493
5．PERCENTILE.INC（返回第 k 个百分点值） 495
例：返回一组数据 k 百分点处的值 495 第 19 章 文本处理函数应用 497
19.1 查找指定字符的位置 497
1．FIND（查找指定字符在字符串中的位置） 497
例 1：找出指定文本所在位置 497
例 2：提取指定位置的字符（配合文本提取函数） 498
例 3：查找位置是为了辅助提取（从产品名称中提取规格） 499
2．SEARCH（查找字符串的起始位置） 500
19.2 提取指定文本 501
1．LEFT（按指定字符数从*左侧提取字符串） 501
例 1：提取分部名称 501
例 2：从商品全称中提取产地 502
例 3：根据产品名称进行一次性调价 503
2．RIGHT（按指定字符数从*右侧提取字符串） 503
例：从文字与金额合并显示的字符串中提取金额数据 504
3．MID（从任意位置提取指定字符数的字符串） 504
例 1：从产品名称中提取货号 505
例 2：提取括号内的字符串 506
19.3 替换指定文本 507
1．REPLACE（用指定的字符和字符数替换文本字符串中的
部分文本） 507
例：对产品名称批量更改 507
2．SUBSTITUTE（替换旧文本） 508
例 1：快速批量删除文本中的多余空格 508
例 2：根据报名学员统计人数 509
19.4 文本格式转换 510
1．TEXT（设置数字格式并将其转换为文本） 510
例 1：返回值班日期对应的星期数 511
例 2：计算加班时长并显示为“* 时 * 分”形式 512
例 3：解决日期计算返回日期序列号问题 513
例 4：从身份证号码中提取性别 514
2．DOLLAR（四舍五入数值，并添加千分位符号和“$”符号） 515
例：将销售额转换为美元格式 516
3．RMB（四舍五入数值，并添加千分位符号和“￥”符号） 516
例：将销售额转换为人民币格式 516
4．UPPER（将文本转换为大写形式） 517
例：将文本转换为大写形式 517
5．VALUE（将文本数字转换成数值） 518
例：将文本型数字转换为可计算的数值 518
19.5 其他常用文本函数 519
1．CONCAT（合并两个或多个文本字符串） 519
例 1：将分散两列的数据合并为一列 519
例 2：合并面试人员的总分数与录取情况 520
2．LEN（返回文本字符串的字符数量） 521
例：检测员工编号位数是否正确 521
3．EXACT（比较两个文本字符串是否完全相同） 522
例：比较两次测试数据是否完全一致 522
4．LET（可以定义名称） 523
例：将表达式的名称定义为 X 524
5．TEXTJOIN（将多个区域中的文本组合起来） 525
例 1：获取离职人员的全部名单 526
例 2：获取各职位的录取人员名单 527 第 20 章 日期与时间函数应用 529
20.1 返回系统当前日期 529
1．NOW（返回当前日期与时间） 529
例：计算活动剩余时间 529
2．TODAY（返回当前的日期） 530
例：判断会员是否可以升级 530
20.2 构建与提取日期 531
1．DATE（构建标准日期） 531
例：将不规范的日期转换为标准日期 532
2．YEAR（返回某日对应的年份） 533
例：计算出员工的工龄 533
3．MONTH（返回日期中的月份） 535
例 1：判断是否为本月的应收账款 535
例 2：统计指定月份的总库存量 536
4．DAY（返回日期中的天数） 536
例 1：计算本月上旬的销量 537
例 2：按本月缺勤天数计算扣款金额 538
5．WEEKDAY（返回指定日期对应的星期数） 538
例 1：快速返回加班日期对应星期几 539
例 2：判断加班日期是工作日还是双休日 539
例 3：汇总周日总销售额 540
6．WEEKNUM（返回日期对应 1 年中的第几周） 541
例：计算培训课程的历时周数 541
7．EOMONTH（返回指定月份前 / 后几个月*后一天的序列号） 542
例 1：根据促销开始时间计算促销天数 543
例 2：计算食品的有效保质日期 544
20.3 日期计算 544
1．DATEDIF（用指定的单位计算起始日和结束日之间的天数） 544
例：计算借款逾期月份 546
2．DAYS360（按照一年 360 天的算法计算两个日期间相差
的天数） 547
例：计算借款的还款剩余天数 548
3．EDATE（计算间隔指定月份数后的日期） 548
例 1：计算借款的到期日期 549
例 2：根据出生日期与性别计算退休日期 549
20.4 关于工作日的计算 550
1．WORKDAY（获取间隔若干工作日后的日期） 550
例：根据项目开始日期计算项目结束日期 551
2．WORKDAY.INTL 函数 551
例：根据项目各流程所需工作日计算项目结束日期 553
3．NETWORKDAYS（计算两个日期间的工作日） 554
例：计算临时工的实际工作天数 554
4．NETWORKDAYS.INTL 函数 555
例：计算临时工的实际工作天数（指定只有周一为休息日） 556
20.5 时间函数 556
1．HOUR（返回时间值的小时数） 557
例：判断商场活动的整点时间区间 557
2．MINUTE（返回时间值的分钟数） 558
例：统计比赛分钟数 558
3．SECOND（返回时间值的秒数） 559
例：统计比赛历时秒数 559 第 21 章 查找与引用函数应用 561
21.1 返回行列号的 ROW 与 COLUMN 561
1．ROW（返回引用的行号） 561
例：生成批量序列 562
2．COLUMN（返回引用的列号） 564
21.2 LOOK 类函数 564
1．VLOOKUP（在数组第 1 列中查找并返回指定列中同一位置
的值） 565
例 1：按编号查询员工的工资明细 566
例 2：代替 IF 函数的多层嵌套（模糊匹配） 568
例 3：应用通配符查找数据 569
例 4：VLOOKUP 应对多条件匹配 570
2．LOOKUP（查找并返回同一位置的值） 571
例 1：LOOKUP 模糊查找 573
例 2：利用 LOOKUP 模糊查找动态返回*后一条数据 574
例 3：通过简称或关键字模糊匹配 575
例 4：LOOKUP 满足多条件查找 576
3．HLOOKUP（查找数组的首行，并返回指定单元格的值） 577
例：根据不同的返利率计算各笔订单的返利金额 578
4．XLOOKUP（查找区域或数组返回对应项） 580
例 1：XLOOKUP 实现多行多列查找 582
例 2：指定错误值返回空白 583
例 3：XLOOKUP 应对多条件匹配 584
21.3 经典组合 INDEX MATCH 585
1．MATCH（查找并返回指定值所在位置） 585
例：用 MATCH 函数判断某数据是否包含在另一组数据中 586
2．INDEX（从引用或数组中返回指定位置的值） 587
例 1：MATCH INDEX 的搭配使用 588
例 2：查询总金额*高的销售员（逆向查找） 589
例 3：查找报销次数*多的员工 590
3．XMATCH（查找并返回值所在位置） 591
例：XMATCH 搭配 INDEX 592
21.4 Excel 新增的查找函数 593
1．SORT（对区域或数组排序） 593
例：数据动态排序 594
2．SORTBY（多条件排序） 595
例：多条件排序 596
3．UNIQUE（提取唯一值） 596
例：按单列去重复值 597
4．FILTER（按条件筛选数据） 597
例 1：单条件查询 598
例 2：多条件查询 598 第 22 章 财务函数应用 600
22.1 本金和利息计算 600
1．PMT（返回贷款的每期还款额） 600
例 1：计算贷款的每年偿还额 600
例 2：按季（月）还款时计算每期应偿还额 601
2．PPMT（返回给定期间内的本金偿还额） 601
例 1：计算指定期间的本金偿还额 602
例 2：计算第 1 个月与*后一个月的本金偿还额 602
3．IPMT（返回给定期间内的利息偿还额） 603
例 1：计算每年偿还额中的利息金额 603
例 2：计算每月偿还额中的利息金额 603
4．ISPMT（等额本金还款方式下的利息计算） 604
例：计算投资期内需支付的利息额 604
22.2 投资计算 605
1．FV（返回某项投资的未来值） 605
例 1：计算投资的未来值 606
例 2：计算某项保险的未来值 606
例 3：计算住房公积金的未来值 607
2．FVSCHEDULE（计算投资在变动或可调利率下的未来值） 607
例：计算投资在可变利率下的未来值 607
3．PV（返回投资的现值） 608
例：判断购买某种保险是否合算 608
4．NPV（返回一笔投资的净现值） 609
例：计算一笔投资的净现值 609
5．XNPV（返回一组不定期现金流的净现值） 610
例：计算出一组不定期盈利额的净现值 610
6．NPER（返回某笔投资的总期数） 611
例 1：计算某笔贷款的清还年数 611
例 2：计算一笔投资的期数 611
7．EFFECT 函数（计算实际年利率） 612
例 1：计算投资的实际利率与本利和 612
例 2：计算信用卡的实际年利率 613
8．NOMINAL 函数（计算名义年利率） 613
例：根据实际年利率计算名义年利率 614
22.3 偿还率函数 614
1．IRR（计算内部收益率） 614
例：计算一笔投资的内部收益率 614
2．MIRR（计算修正内部收益率） 615
例：计算不同利率下的修正内部收益率 615
3．XIRR（计算不定期现金流的内部收益率） 616
例：计算一组不定期现金流的内部收益率 616
4．RATE（返回年金的各期利率） 617
例：计算一笔投资的收益率 617
22.4 资产折旧计算 617
1．SLN（直线法计提折旧值） 617
例 1：直线法计算固定资产的每年折旧额 618
例 2：直线法计算固定资产的每月折旧额 618
2．SYD（年数总和法计提折旧值） 619
例：年数总和法计算固定资产的年折旧额 620
3．DB（固定余额递减法计算折旧值） 621
例：固定余额递减法计算固定资产的每年折旧额 621
4．DDB（双倍余额递减法计算折旧值） 622
例：双倍余额递减法计算固定资产的每年折旧额 623
5．VDB（返回指定期间的折旧值） 624
例：计算任意指定期间的资产折旧值 624