By 时代数字趋势2021-12-13
你是否常常在工作上需要在众多的数据中找出某项产品的特定数值,或者是你需要查询班上每人的成绩平均数,但是却又不知道要使用甚么样的函数,才能帮助你迅速地解决这些工作上的要求?
EXCEL 的 VLOOKUP 函数能够帮助你解决这些问题。利用 VLOOKUP 函数,你可以快速地在各个数据表格中找到相对应的项目,不用担心因人为疏失而选取到错误的内容。
那么 VLOOKUP 是什么样的函数,又如何运作呢?
VLOOKUP 的函数运作原理,是从最左侧栏数开始往下往右寻找到数据表内对应的项目。
从示意图表就能看出它的搜寻原理就如同一个 L 字型的逻辑,一开始从最左侧的资讯依序往下寻找,再往右开始找到你所要的资讯并回传为最终结果。
这里要特别注意的是 VLOOKUP 函数是从最左栏开始查询,所以如果你要查询的数值不在栏位的最左侧,而是从中间的栏位开始查询的话,你就需要透过其他方式来处理,像是使用 INDEX、MATCH 函数才能正确显示你所需要的项目。
本篇文章将为你详细介绍 VLOOKUP 函数的使用说明与实际用法,让你透过它更快的从数据中筛选自己需要的结果。
- VLOOKUP 函数使用说明
- 1. VLOOKUP 函数写法
- 2. 设定查询数值
- 3. 设定 VLOOKUP 函数
- 4. 绝对参照
- 结论
VLOOKUP 函数使用说明
1. VLOOKUP 函数写法
=VLOOKUP{lookup_value,table_array,col_index_num,[range_lookup]}
也就相当于
=VLOOKUP{查询项目,数据查询範围,数据内栏位号码,是否精準比对}
从函数内可以看见 VLOOKUP 函数内拥有4种参数,以下则是相关函数的说明。
例如使用分数区间表来表示分数的话,设定为大致符合,那么查询时的分数即使没有完全与区间表格相同,仍将会依照递增排序回传数值,函数内预设 “Ture” 是大致符合,”False” 则是完全相同。
接下来我们将会以微软网页版(Microsoft 365)的免费线上 EXCEL 表格做为示範操作。
并以图中的考试成绩分数表为範例,实际说明函数的各步骤将会如何达成我们所需要的结果。
2. 设定查询数值
首先你需要将你所要查询的数值输入表格内,这样函数才能依据你所要查询的数值,对应到表格範围内进行搜寻,像是下图内,我们想要依照座号来做各种成绩资讯的查询,那么就得先输入想查询的座号来做为根据。
3. 设定 VLOOKUP 函数
接下来假设你想要找到第二项数据的讯息,也就是各个座号对应的姓名的话,你需要将滑鼠点在 C4 储存格,也就是姓名右侧的空格。
再来当你想要插入 VLOOKUP 函数时,你可以从 EXCEL 的左上方功能列找到「插入」,点击插入后你可以在左上角看见「函数」并点击,这时就会看到 EXCEL 让你选择你想使用甚么样的函数,同样地你也可以直接按下函数的符号就可以马上插入预设的函数。
接下来选择 VLOOKUP 函数,而函数需要输入之前介绍的四项参数,而四项参数的设定当然可以用手动输入特定数值,但是这样做的话查询其他项目时就无法一一对应更动后的数值,因此在输入公式的时候,请点击你要依据的储存格,这样就可以直接依照特定的储存格来做为查询依据。
像是第一项参数首先以点击储存格 C3 做为查询项目,点选完后请使用逗点隔开参数。
接下来第二项参数则是範围的选取,使用滑鼠左键将想查询的数据範围圈选出来。
如图内 E3-J13 的格子,那么函数就会依据这个範围来查找相对应的数据,接下来同样使用逗点隔开参数后。
第三项参数我们需要设定数据範围内,函数搜寻时的对应栏位,像「姓名」在数据範围内就是由最左边算起第2栏位,因此我们这里输入数字2即可。
最后第四项参数则是设定是否使用精準比对,而在先前设定的数据範围内,因为我们使用的查询项目能够精準的对应到搜寻範围内的数据,而 EXCEL 内预设 FALSE 为完全相同,TRUE 为大致符合,所以这里的函数写入设定为 FALSE 即可。
那么我们接下来透过重複以上的步骤,并且修改栏位编号,我们就能得到如同下表的最终结果。
可以看到当你自由切换座号时,就会跟着随之变换数字,而成绩因为还没设定好所以目前预设是为数字0。
那么我们假如要利用右边的分数区间表,以总平均分数产生出对应到的「成绩」,同样地可以选取 K3 储存格后插入 VLOOKUP 函数。
参数方面因为要抓取总平均分数为查询项目,因此我们将设定 J3 储存格为第一项参数,数据查询範围我们则以 M3 拉至 N8 的表格为第二项参数的範围设定。
接下来成绩在数据範围内为第2栏,所以第三项参数设定为2,第四项参数因为总平均分数来说,无法精準的对应到成绩的分数区间,所以输入 TRUE 让 EXCEL 自动大致符合即可。
而这边有个特别要注意的地方就是,EXCEL 是採用向下递增的顺序做比对,所以如果原本的参照区间没有设定好的话,就会显示出不正确的对应值,所以使用大致符合的功能时,要记得调整区间的对应表为递增。
以上设定好的话就会变成如图的配置。
4. 绝对参照
那么像是 EXCEL 还有一个向下複製的功能,因此当你上述的步骤设定好函数之后,你可能想透过下拉右下角来「相对参照」複製公式,但是当你下拉之后可能就会发现发生这样的情况。
你会发现到往下到第4格的成绩变成无法判断的号码,这其实是因为当时设定参数的时候,没有用绝对参照的位置去设定,这样的话当你往下拉来複製公式时,你原先所选取的数据範围也会跟着数据往下移动,导致函数在搜寻设定的範围时出现抓取错误的问题。
就会变成如图中红色框内,抓取到下方的白色储存格,而上方的区间有误,是错误的数据搜寻範围。
这时候我们就可以使用「绝对参照」来使得函数内的参数选取範围是固定的,你可以透过对参数选取範围,以图中的 M3:N8 为例,选取后按下 F4 即可使所选範围出现$符号,就能使选取範围固定不会跟着移动。
但因为只有固定数据查询範围,所以其他各项参数的数值依然可以透过 EXCEL「相对参照」的功能来往下複製公式,最终就可以如图获得想要的成绩结果了。
结论
熟练使用EXCEL的各种技巧,能够对平时文书工作上节省了许多的时间,并且能够帮助你消除掉许多人为失误的因素,让你的错误率显着降低。
善用这个VLOOKUP函数的实用技巧,就能使你在搜寻特定的相关数据时,再也不用烦恼无法处理庞大表格资讯的问题了。
不过即使看完了上述的说明,也要多实际尝试几次不同的操作,对各种函数的牢牢掌握,才能达到不论是工作或是其他业务上都能得心应手。