Excel学习笔记：P16-VLOOKUP函数与绝对参照设定

---

一、VLOOKUP函数

现在我有如下一个记录学生成绩的表格，我想输入学生的学号，直接输出学生的姓名、成绩等信息。



分析：假如现在我想查询学号为103的学生的平均成绩，一般我们都会表格的最左列查找他的学号，找到之后再从这一行找到他的平均成绩。这个 “L” 型的搜寻思路就是VLOOKUP函数的运作原理。



现在我们来实际应用VLOOKUP函数，如图所示，我们输入学生的学号，要直接输出学生的姓名、平均成绩和成绩等级。具体操作步骤如下图所示：

二、相对参照与绝对参照

2.1 相对参照与绝对参照

什么时候使用模糊查询呢？比如说要查询学生的成绩等级，而在等级表中没有75这个数值，我们就可以使用模糊比对这个功能，将75归类在70分对应的等级。(使用模糊比对时，使用的表格必须采用递增的顺序进行排列)



具体操作流程为：
①查询的关键字为总平均成绩所在单元格，因此这里应为J3。
②查询范围为M和N列的分数区间表，因此这里为M3:N7。
③查询结果为成绩的等级，在第二列，因此这里为2。
④因为要开启模糊查询，因此第四个参数为1(TRUE)。


具体效果如下图所示：



可以发现，成绩这一栏输入公式并下拉后，出错，具体原因如下：
在求平均成绩时，我们把函数往下复制，函数的位置参数随着单元格位置的更改而自动更新。这个特性在Excel中称为相对参照，我们以AVERAGE函数为例，如下图所示：




在使用VLOOKUP时，相对参照这一特性也是存在的。由于这一特性的存在，导致等级表也会跟着下移。





为解决此问题，我们需要使用绝对参照。直观地说，就是将函数涉及的表格范围锁定在原地。锁定的方法很简单，只需将函数中涉及的表格范围参数选中，按下F4就行。此时涉及的表格范围参数周围会出现 “$”，代表此时涉及的表格范围已经转换为绝对参照了。

2.2 VLOOKUP函数与IF函数对比

①VLOOKUP函数更加简洁



②使用VLOOKUP函数时，如果要修改等级表的分数区间，我们不用去修改同学的成绩公式，只需在等级表中修改就行。

2.3 绝对参照的应用

在将姓名的VLOOKUP函数往下拖移时，也得考虑表格的绝对参照。即涉及的表格应该锁定在原来的位置。因为不管VLOOKUP函数复制到底下的哪一个储存格，我们依然得使用同一个学号栏作为关键字，并且在同一个资料范围进行查询。



如果想界面简洁，可以选中右边不想显示的表格，右键，点击隐藏。



若要取消隐藏，选中隐藏的那一列，右键，取消隐藏。



注：目前这个表格有一些问题，比如我输入一个不存在的学号，或者学号栏清空，地下仍然会显示错误代码。这个问题就留到下一篇博客记录解决办法。