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

本书以Excel 2021版本为基础，系统全面地介绍了Excel的表格制作、数据分析、图表处理、数据透视表应用和函数与公式等使用技巧，是一本全面介绍Excel应用的实用教程，也是一本Excel实用技巧的速查手册。

全书共22章，内容包括工作簿操作与打印、工作表基本操作、数据输入与填充、数据定位应用、数据查找替换与复制粘贴、规范表格结构与数据、数据验证与自定义格式、表格美化设置、条件格式应用、数据筛选应用、数据排序应用、数据透视表应用、图表应用、高级数据分析、数据合并计算与分类汇总、了解公式与函数、逻辑判断与数学函数应用、统计函数应用、文本处理函数应用、日期与时间函数应用、查找与引用函数应用、财务函数应用。在具体讲解过程中，重要知识点均配有实例操作和同步视频教学，并提供源文件，便于读者高效学习，学练结合，快速将知识转化为技能。

本书内容丰富，实用性强，是提升Excel技能的必备指南，非常适合Excel各层次的读者学习使用，Excel初学者、职场人士、数据分析师等均可将此书作为案头速查参考手册。此书也可作为相关院校的教材。本书在Excel 2021版本基础上编写，也适用于Excel 2019/2013/2010等低版本软件的读者学习。

第1章 工作簿操作与打印1

1.1工作簿创建技巧1

1.将Excel2021固定到开始屏幕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

例：将表达式的名称定义为X524

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与COLUMN561

1.ROW（返回引用的行号）561

例：生成批量序列562

2.COLUMN（返回引用的列号）564

21.2LOOK类函数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经典组合INDEXMATCH585

1.MATCH（查找并返回指定值所在位置）585

例：用MATCH函数判断某数据是否包含在另一组数据中586

2.INDEX（从引用或数组中返回指定位置的值）587

例1：MATCHINDEX的搭配使用588

例2：查询总金额最高的销售员（逆向查找）589

例3：查找报销次数最多的员工590

3.XMATCH（查找并返回值所在位置）591

例：XMATCH搭配INDEX592

21.4Excel新增的查找函数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