哈啰，大家好
今天要跟大家介绍的函数
叫做 VLOOKUP
我们可以利用这个函数
制作一个简单的成绩查询表
只要输入同学的学号
就能够立即查出这位同学的所有成绩喔
我们首先来想像一个情境
假设今天你手上有一份成绩单
然后有一位学号为 103 的同学
想要麻烦你
查询他这次考试的「总平均」分数
那你会如何查询呢？
一般我们都会从表格的最左栏
开始寻找他的学号
找到之后
再从这一列找出他的「总平均」
而这个 L 形的搜寻思路
其实就是 VLOOKUP 的运作原理
好，我们来实际看看这个函数的操作方法
在画面的左侧
我设计了一个简单的查询表
为了方便测试
我先在表格内输入一个学号 103
再按下 Enter 键移到「姓名」栏
接着，我按下「插入函数」按钮
来寻找 VLOOKUP 函数
将它点选之后再按下「确定」
OK，在这个对话框中
VLOOKUP 函数需要我们填上四个资讯
它才能为我们查询同学的成绩
第一个是 Lookup Value
简单说就是你要使用的「关键字」
以目前来说
我们想要找的是学号 103 的同学
虽然我们也可以直接在栏位中输入 103
但是更好的方法
是直接将这个栏位指定至 C3 储存格
之后如果我们在 C3 输入其它的学号
像是 105
107 等等
VLOOKUP 就能直接将它们视为新的关键字了
VLOOKUP 所需要的第二项资料
是 Table Array
也就是你的「资料范围」
而目前所有可供查询的资料
都在这张成绩单里面
所以我们可以直接使用滑鼠
将它框选起来就 OK 啰
而这边有一个务必要记住的重点
就是 VLOOKUP 函数在进行查询时
它走的是我们刚才提到的 L形动线
也就是说，VLOOKUP 只会使用
框选范围中的「最左栏」来寻找关键字
因此，如果我们的关键字是「学号」的话
我们就得确认这份表格的最左栏
也同样是「学号」
如此才能产生正确的搜寻结果
OK，那下一个阶段
就是如果我们成功地找到要查询的学号
那我们又要如何将学生的成绩
传回到查询表内呢？
VLOOKUP 采用了一个非常简单的方式
也就是从最左栏开始依序数数
举个例子
如果我想要得知同学的国文成绩
我就请 VLOOKUP 传回第 3 栏的资料
而如果我需要「总平均」的分数
我就指定第 6 栏
依此类推
至于目前我们所在的栏位
是查询同学的「姓名」
而姓名在表格中是左起第二栏
因此我直接在对话框内输入 2 就可以啰
VLOOKUP 的最后一个栏位功能较为特殊
就是你可以指定在查询时
是要进行「精准比对」
或是「模糊比对」
以目前这个查询表来说
「精准比对」是必要的
因为当我们在查询一个学号时
必须在资料表内找到完全相同的学号
这样传回来的成绩才是正确的
因此，我们可以依照对话框中的说明
在第四栏中输入 FALSE
代表这次的查询
我们选择采用「精准比对」的模式
再按下「确定」
就可以查出同学的姓名啰
我们也可以测试看看其它的学号
VLOOKUP 也都能够查到正确的结果
好，我们很快地再将重点复习一次
顺便认识一下 VLOOKUP 函数写在公式列时
它所呈现的结构
VLOOKUP 一共有四个引数
第一个是用来查询的「关键字」
第二个是资料所在的范围
第三个是找到符合关键字的记录之后
它要传回左起第几栏的资料？
以及最后，我们是要采用「精准」
或是「模糊」的查询模式？
说到这里，也许你会好奇
那在什么情况之下
我们会使用到 VLOOKUP的「模糊比对」模式呢？
其实「等第表」就是一个很好的例子
譬如有位同学的成绩是 75 分
但是在等第表内是没有 75 这个数字的
因此我们可以透过「模糊比对」的方式
将成绩 75 分的这位同学
归类在 70 分的这个等第
只是在进行模糊比对时
官方说明有提到一个重点
就是用来参照的表格
必须采用「递增」方式排列
如果采用「递减」方式排列的话
VLOOKUP 函数就无法产生正确的查询结果喔
OK，那我们就一起来找出所有同学的成绩吧
这一次我们不妨来体验一下
使用公式列来建立 VLOOKUP 函数
我将输入游标
放置在第一位同学的成绩栏
接着按下键盘的等号
输入函数名称 VLOOKUP
然后一个左括号
首先我们来指定关键字所在的储存格
也就是第一位同学的「总平均」分数
我们利用这个分数来寻找他的成绩
接着我输入一个逗号
进入到函数的第二个引数「资料范围」
在此我们可以直接将旁边的等第表
框选起来就 OK 了
找到第一位同学的分数区间之后
我们必须请 VLOOKUP 回传他的成绩
而成绩在这份表格之中是「左起第二栏」
因此我输入一个逗号之后
再按下数字键的 2
最后，目前的查询类型
是归类在「模糊比对」的模式
因此我再输入一个逗号之后
依照底下的提示讯息输入 TRUE
再补上一个右括号就完成了函数的设定啰
这时，你可能迫不及待想把这个公式向下复制
来计算出其它同学的成绩
然而，我们却发现底下的栏位
出现了错误讯息
而这是怎么一回事呢？
这个原因追究起来
其实是 Excel 公式的一个特性
如果我们回头复习一下
当初「总平均」公式是如何完成的
就会知道问题是出在那里了
我们知道「总平均」的范围
就是考试的三科成绩
而当我们计算出第一位同学的总平均之后
我们将公式向下复制
如此每一列的函数范围
也会随着位置变化而一同更新
这个特性，在 Excel 中称之为
「相对参照」
而在使用 VLOOKUP 函数时
「相对参照」的特性也是存在的
因此，当我们查询第一位同学的成绩时
虽然用来查询的「关键字」
和「资料范围」的位置都没有任何的问题
但是当公式复制到第二位同学的时候
我们就会发现除了关键字的栏位
也跟着公式下移之外
连同等第表的范围也跟着向下掉了一排
于是到了愈后面的同学
资料范围就愈来愈偏离等第表的原始位置
那理所当然 VLOOKUP
也就无法传回正确的成绩了
因此，我们这里要认识一个非常重要的设定
称之为「绝对参照」
比较直白的说法
就是将函数中所设定的范围
「锁」在原地不动
设定的方式非常简单
我们只要将要锁定的范围反白起来
再按一下键盘的 F4 按钮
只要看到储存格位置的旁边
显示了「货币符号」
就代表这些范围已经被转换为「绝对参照」了
最后，我再将公式复制到底下的所有同学
这时，由于总平均的栏位
仍属于传统的「相对参照」
因此它依然会随着公式位置变化
而一同移动
相对地
已套用「绝对参照」的等第表
不管函数被复制那一个储存格
它的范围都会被锁定在原地
如此 VLOOKUP 就能查出所有同学的正确成绩啰
而同样是成绩判断
你会发现相较于上一集所介绍的 IF 函数
使用 VLOOKUP 不但公式较为简洁
日后若需要更新等第表的分数区间
我们也不用另外费力去修改同学的成绩公式喔
最后，我们可以将「绝对参照」的概念
应用在先前的查询表
在将「姓名」的 VLOOKUP 公式向下复制之前
我们同样得考虑这个函数的「关键字」
和「资料范围」是否应该锁定在原来的位置？
这个答案是肯定的
因为 VLOOKUP 不管复制到底下的那一个储存格
我们依然得使用同一个学号栏做为关键字
并且在同一个资料范围进行查询
因此这两个位置是不能移动的
所以我在公式列内
将 VLOOKUP 的前两个引数
也就是「关键字」和「资料范围」反白起来
再按一下键盘的 F4 来套用绝对参照
接着，我再将公式复制到底下的储存格
由于「总平均」是在表格中左起第 6 个栏位
因此我将函数中的第三个引数修改为 6
至于最后的「成绩」栏
我同样依照它在表格中的顺位
将数值修改为 7
如此，我们就能透过这个表格
来查询所有同学的成绩了
如果你希望提供使用者
一个更简洁的搜寻介面
你甚至也可以将查询表以外的栏位框选起来
并且按下滑鼠右键
点选「隐藏」
之后如果要还原的话
我们可以将隐藏的栏位选取之后
再到右键执行「取消隐藏」就 OK 啰
影片的最后要提醒大家
目前这个查询表仍有一些尚未解决的问题
譬如我如果输入一个不存在的学号
或是我清空学号栏的内容时
底下的栏位都会显示错误的讯息
而这个部份我们就留到下次的影片
再和大家介绍啰
OK，那今天我们就先聊到这里
我们同样下回再见，拜拜