【Excel高阶技巧】条件函数、查找函数、字符串相关函数、公式审核、保护工作表/工作簿、模拟分析、宏

Excel 高阶技巧

1. Excel 条件函数

1.1 使用Excel Name Range

选中目标区域→别名→今后要引用目标区域时就可以使用别名来引用了。注意别名不能有空格。

1.2 Name Range的优缺点

（1）优点：

• 比起xx:xx这样的区域，一个别名能给人展示更清晰的含义，即可读性更强

• 当作为函数参数使用，也能减少代码长度

• 别名可以作为导航使用，在同一个Excel文件中点击别名会快速跳转到指定区域
  

（2）缺点：

• 别名相当于绝对引用，不会通过自动填充更改区域！！！！

1.3 编辑Name Range

公式→名称管理器。

1.4 IF()函数

IF()函数的三个参数分别是：条件判断语句、如果为真输出的值、如果为假输出的值。

例如：判断总销售额有没有达到月度指标，达到了显示Yes，否则显示No。

1.5 IF()函数配合Name Range

先给34000所在单元格别名MonthlyGoal，之后IF()函数中就可以直接使用这个别名了，效果和绝对引用是一样的。

1.6 嵌套函数

AND()函数：当多个条件均成立时返回True，否则False，每个参数为一个条件判断语句。

例如：当总销售额达到了月度目标，且每周销售额都要大于等于8000（这里不要一周写一个条件语句，直接用MIN()函数找最小值），则返回True。

1.7 AND()和IF()嵌套

想要表示多条件均成立时才显示True，就把IF()函数的1号参数替换成AND()函数，然后在AND()里写条件。

例如：当总销售额达到了月度目标，且每周销售额都要大于等于8000，则返回True。当IF接收到True就显示“Bonus”，否则“No Bonus”。

1.8 COUNTIF()函数

COUNTIF()函数，判断某个单元格（注意是统计单元格个数，不是行数）是否满足条件，满足就计入，否则不计入，两个参数分别是：统计范围（可以是多列）、判断条件（如果条件是等于XX，等号必须省略）。

例如：在H5-H9的范围内所有单元格中，如果某个单元格里写的“Yes”，就计入，否则不计。

1.9 SUMIF()函数

SUMIF()函数，遍历判断给定列中的单元格是否满足给定条件，如果满足就去加另一个给定范围里的数，有三个参数，分别是：进行条件判断的范围（可以框选多列，但函数还是只会在第一列查找）、判断条件（如果是表示等于XX，同样要省略等号。）、求和区域（随便多少列）。

例如：查找商店ID，如果等于3000，就去求对应商品个数的和。

值得注意的是：判断条件可以有多个单元格，相当于写了多个SUMIF，最后的结果会以列表的形式挨个返回。

1.10 IFERROR()函数

IFERROR()函数，如果报错了则显示XX，有两个参数，分别是：需要判断是否报错的表达式、如果报错了返回的值。

2. Excel 查找函数

2.1 VLOOKUP()函数

VLOOKUP函数，在指定范围的第一列沿着垂直方向遍历查找指定值，每次找到一个就返回水平方向上的第N个值，有四个参数，分别是：要查找的值、要查找的范围、返回值在查找范围的第几列、精确查找（False）还是模糊查找（True），虽然第四个参数是可选参数，但是建议写上。

注意：（1）第二个参数查找区域的首列必须是要查找值所在的列；（2）查找区域可以直接写区域，也可以写别名，但要记得用绝对引用，因为查找区域一般是不会变的！查找值同理，也要想一想用相对还是绝对引用。

2.2 HLOOKUP()函数

和VLOOKUP类似，唯一的区别是变成了沿着水平方向遍历，找到后返回垂直方向上的第N个值。

2.3 INDEX()函数

INDEX()函数，用以在给定范围返回给定坐标的值，有3个参数，分别是：查找范围（记得用绝对引用）、返回值在第几排、返回值在第几列（如果不写第三个参数，就以列表形式返回一整排）。

2.4 MATCH()

MATCH()函数，用以在指定列/行查找指定值，找到后返回所在行/列数，有3个参数：要查找的值、查找范围（只能是单行或者单列）、匹配模式（0表示精确匹配，返回第一次找到的位置；1返回小于目标的值中最接近的那个值的位置；-1返回大于目标的值中最接近的那个值的位置）。

如果在某列中查找，返回值就是行数；如果在某行中查找，返回值就是列数。

2.5 INDEX()和MATCH()结合案例①

VLOOKUP的缺点是（HLOOKUP同理），只能在查找范围的首列查找目标值，或者说目标值必须在返回值的右边。而且VLOOKUP运行速度很慢。

而INDEX()和MATCH()配合使用可以弥补这种缺点，它的思想和Pandas中花式索引非常类似，都是先用内层函数定位到坐标，再用外层函数根据坐标去索引。

例如：内层的MATCH()函数在员工ID列中查找指定ID，找到了就返回行数N。N作为参数传给外层的INDEX()函数，它就会返回部门列中的第N行的值。两个函数共同完成了根据员工ID查找部门的功能。

2.6 INDEX()和MATCH()结合案例②

VLOOKUP()的第二个缺点是，第三个参数（要返回第几列），在向右自动填充后必须手动去更改，比如搜索First Name的VLOOKUP()函数，如果简单地复制过去用来搜索Last Name就会失效。

而使用INDEX()+MATCH()就能解决这个问题，可以任意自动填充。

例如：外层仍然使用INDEX()，第一个参数填写查找范围，后面两个参数行坐标和列坐标都使用MATCH()去查出来。

怎么说呢，原理不复杂，但怎么看怎么麻烦，尤其是一会儿要用绝对引用，一会儿要用相对引用，特别容易出错。

联表也能起到相同效果，而且代码简单得多：

2.7 HLOOKUP()和MATCH()结合

VLOOKUP和MATCH()结合道理一样，都是为了解决自动填充时必须手动改参数的问题。

例如：内层用MATCH()函数查找左侧目标字段的行数i。行数i作为参数传给HLOOKUP()函数，HLOOKUP()在查找范围的第j列找到目标值后，就会返回第j列i行单元格的值。

这里两边的字段名不完全对得上，用了LEFT()函数截取字符串。

3. Excel 字符串函数

3.1 LEFT()、RIGHT()、MID()函数

LEFT()和RIGHT()函数，给指定字符串截取指定长度，有两个参数，分别是：字符串、截取长度。

MID()函数，将指定字符串从起始位置截取指定长度，有三个参数，分别是：字符串、起点、指定长度。

3.2 LEN()函数

LEN()函数，计算字符串长度，只有一个参数：目标字符串。

3.3 SEARCH()函数

SEARCH()函数，在指定字符串中查找指定字符（也可以是子字符串），找到后返回它位于字符串的第几个，有3个参数，分别是：被查找的字符、字符串、起点。

例如：截取姓。先用SEARCH()找到空格的位置，由于这个位置是从左数的，所以用LEN()减去这个位置，就得到了姓的长度，最后再用RIGHT()截取。

注意：SEARCH()和FIND()的区别：SEARCH()不区分大小写，FIND()要区分；SEARCH()可以用通配符，FIND()不能。

3.4 CONCATENATE()函数

CONCATENATE()函数，将给定的字符串全部拼接起来，接受任意个数的参数，每个参数都是一个字符串，最后会将所有参数拼起来。

4. Excel 公式审核

4.1 监视窗口

有时原始数据和计算公式在两个sheet中，数据有变动时，来回切换查看很不方便。此时可以将公式放到监视窗口中，一旦原始数据有变动，监视窗口中计算出来的值也会同步变动，方便查看。

4.2 显示公式

有时需要一个个审核公式对不对，可以把公式显示出来，方便查看。当然教学时也能用上。

并且显示公式后，也可以就这样去打印，也可以把公式打印下来。

4.3 追踪引用单元格

有时公式引用的单元格不那么直观，无法一眼定位过去，可以使用公式→追踪引用单元格功能。

点一下显示父级，再点继续显示父级的父级，以此类推。点“删除箭头”可以去掉箭头。

4.3 追踪从属单元格

和4.2类似。

5. 保护工作表和工作簿

5.1 保护特定单元格

有时把Excel表格发送给别人，不希望他们更改某些单元格的内容，此时就会用到保护单元格功能。

Excel默认所有单元格都上了锁，所以第一步要先给不想保护（即允许更改）的单元格解锁，然后再点保护。

框选要解锁的区域→右键打开设置单元格格式→保护→取消勾选锁定→审阅→保护工作表→设置密码、设置权限→确认密码。解锁时只需要点“撤销工作表保护”，然后再输入一遍密码即可。

5.2 保护工作簿结构

有时不希望别人更改Excel里面各个sheet的名字，或是删除、移动，就可以使用保护工作簿功能。

审阅→保护工作簿→设置密码→确认密码。再次点击“保护工作簿”并输入密码可以取消保护。

5.3 给工作簿加密

有时希望给整个Excel文件加密，持有密码才能打开，可以使用此功能。再次打开时就会提示输入密码。

6. Excel What If 工具

6.1 单变量模拟分析

给定Excel一个含有多变量的表达式、目标值和单个可变变量，Excel会自动计算表达式的输出达到目标值时，可变变量的取值。

例如：想买房，利用PMT()计算等额本息每月还款金额，发现贷款22万买房，8%年利息，预计还300个月的情况下，每个月得还1698元，太多了，负担不起。希望Excel计算出，如果每月只需要还1100元，年利息和还款时间不变的情况下，最多能贷多少钱。

6.2 规划求解

跟Power Pivot类似，要先载入：选项→加载项→Excel加载项→转到→规划求解加载项。

下面举一个简单的例子（小学三年级难度）。有3个工厂都在生产产品，生产完了要运输到仓库中，现有如下要求：

（1）核心目标：总运输费要最低。

（2）每个工厂每季度生产产品数不低于20；

（3）1号工厂每季度生产产品数不高于92，2号不高于45，3号不高于55；

（4）每个季度，3个工厂的产量之和必须等于仓库需求量。

操作步骤是：数据→规划求解→设置目标（这个例子就是总费用的单元格）→目标要最小/最大/达到的值→变量所在区域→约束条件→没有其他要求就可以点确定了。

6.3 模拟运算表

有时同一个公式需要变化参数，计算出对应的多个结果来。（感觉和自动填充效果差不多。。。）

希望改变的变量的各种取值单独放一列→运算好的公式放在一旁（似乎只要被框进去就行）→框选好范围→数据→模拟分析→模拟运算表→输入引用列的单元格，选择原始变量所在单元格。

6.4 创建方案

有时在预测未来的数据时，可能会来回改变参数，对比运算出来的结果。

例如：根据预测增长率，计算四个季度和一年的销售额。增长率并不是固定的，而是多种情况，可能达到一般的预期，可能达到最好预期，也可能是最坏预期，如果希望这三种情况能方便地来回切换，而不用手动更改增长率，就可以创建成方案。

数据→模拟分析→方案管理器→添加→输入方案名、选择可变参数区域→输入这些参数的值，保存完成后，点到哪个方案，再点“显示”，Excel就会自动切换可变参数的值。

7. 使用宏自动完成重复任务

7.1 什么是宏

有时需要做一些重复的数据处理工作，例如对每周、每月拿到的数据进行相同的数据类型转换、格式调整等。而一个宏可以自动完成这些工作。

7.2 激活开发者工具

7.3 录制宏

Excel会记录录制期间的一切操作，下次按快捷键（比如下图设置的ctrl+j）时就会重复这些操作。

录完了记得关，关了才会保存下来。

7.4 使用VBA编辑宏

有时录完了宏，发现中间有个小错误，或者想加入点新功能，又不想重录，就可以使用VBA编辑宏。

开发工具→Visual Basic→模块文件夹→双击模块→改代码。

7.5 创建运行宏的按钮

直接按之前设定的快捷键、使用按钮，都可以调用宏。

开发工具→插入→按钮→在表中框一个矩形→指定宏，选择需要应用的宏→按钮生成后要改名→单击使用。