进阶|熟练使用VLOOKUP函数之精解精析【深度长文】

图/文:安伟星

通过文本你可以学到:

Excel相对引用与绝对引用

Vlookup函数如何同时返回多列

Vlookup函数模糊匹配

在入门篇,我向大家讲解了Vlooup函数的基础知识和示例,可以看这里进行回顾:

入门|快速掌握VLOOKUP函数之精解精析

Vlookup函数是比较典型的“会者不难”这一类型的,他有很多的灵活变换。

掌握这些技巧,你会发现看似很难解决的问题,现在有了思路。


01、相对引用与绝对引用

为什么要讲这个知识点

因为Vlookup函数的四个参数中,有两个涉及到了引用范围的概念。

VLOOKUP(查找目标,查找范围,返回值的列数,精确OR模糊匹配)

①查找目标

②查找范围

什么是相对引用和绝对引用?

相对引用:引用的是单元格的相对位置。如果函数所在 单元格的位置改变,引用也随之改变。默认情况下,Excel中的函数使用相对引用。

相对引用

绝对引用:如果不希望引用的单元格随着函数的位置变化而变化,则为绝对引用。行号"和"列号"前加上美元符号($),这样就是单元格的绝对引用。

绝对引用

结论:如果使用相对引用,函数向下复制时,引用的单元格的“行数”会递增;函数向右复制时,引用的单元格的“列数”会递增。


如何切换?

将光标定位于函数中引用单元格,按F4,进行四个引用状态的切换。

①默认完全相对引用

②按一次F4:行和列绝对引用

③按二次F4:行绝对引用,列相对引用

④按三次F4:行相对引用,列绝对引用

相对引用和绝对引用切换

02、VLOOKUP同时返回多列值

VLOOKUP函数的语法为:

VLOOKUP(查找目标,查找范围,返回值的列数,精确OR模糊匹配)

VLOOKUP函数的第三个参数是查找返回值所在的列数,如果我们需要查找返回多列时,这个列数值需要一个个的更改,比如返回第2列的,参数设置为2,如果需要返回第3列的,就需要把值改为3。。。

列数不多的情况,当然可以手动修改,那如果是几十列呢?

能不能让第3个参数随着函数的位置不同,自动变更?即向后复制时自动变为2,3,4,5。。。

引入新的函数:Column

COLUMN函数可以返回指定单元格的列数,比如

=COLUMNS(A1)返回值1(A1所在的列为第一列)

=COLUMNS(B3) 返回值2   (B3所在的列为第二列)

如何应用

使用COLUMN函数的相对引用,=COLUMN(A1)向右复制时,A1会变成B1,C1,D1。。这样我们用COLUMN函数就可以转换成数字1,2,3,4。。。

注:这里的关键是将VLOOKUP函数的第三个参数设置为动态变化的。

举例说明

需要同时查找性别,年龄,成绩,爱好。

①在B16单元格中输入公式:=VLOOKUP($A16,$B$2:$F$11,COLUMN(B1),0)

②拖住B16单元格右下角的黑框,向右拖动进行复制,然后向下进行复制


公式说明

①$A16:这里只有列前边有$符号,意味着列是绝对引用,行是相对引用。这样就能实现在向右复制时,列数保持不变(一直是A列),行递增变化($A16→$A17→$A18)

②$B$2:$F$11:查找范围的引用区域,行和列均为绝对引用。确保函数在复制过程中,查找的范围不会变更。多数情况下,查找范围都是需要固定的。

③COLUMN(B1):在性别这一列的函数中,第三个参数值需要设定为2(因为性别在查找区域中处于第二列),向右复制是需要递增。

所以关键是COLUMN()的第一个返回值是2即可,这里的参数可以是B列的任一单元格。


03、模糊匹配

Vlookup函数的最后一个参数,如果是0(False)的话,代表精确匹配,在初级已经讲过了;如果是1(True)的话,是模糊模糊匹配。

模糊匹配如何应用呢?

首先我们需要了解一下VLOOKUP函数模糊查找的两个重要规则:

规则一:引用的数区域一定要从小到大排序(数字是从小到大排序,字符按照首字母排序)。杂乱的数据会返回意想不到的数据。

规则二:模糊查找,给定一个无法精确匹配的数值,它会找到和它最接近,但比它小的那个数。

举例:下图中从左侧工资表中,查找给出的工资的税率。可以看出,我们要查找的工资5800不在左侧的表格中,所以需要使用模糊查找。

模糊查找(数据纯属虚构)

在E3单元格中输入公式=VLOOKUP(D3,A3:B9,2,1)

结果返回了0.04,对应的是工资5500的税率。

为什么会这样,跟着默念:模糊查找会返回和它最接近,但比他小的数值。

在左侧数据表格中,与5800最解决且比5800小的数就是5500,所以会返回5500定义的税率。


但是,模糊查找有什么卵用呢?

当然有卵用!!!

最后一个实例

【例】:根据成绩等级根则,算出各位学生的得分等级。

解答:使用Vlookup函数的模糊匹配,结果秒出有木有!!

比用什么IF函数简单多了。

在C10单元格中写入公式=VLOOKUP(B10,$A$1:$C$6,3,1)

▼结论

根据模糊查找的规则,VLOOKUP科进行数字的区间查找(即查找给定的数字属于哪个区间)。

学完Vlookup入门教程,再结合上面这三个知识点,已经能够解决80%的查找问题。但是你还是会碰到Vlookup无法解决的查找问题,比如:如何从右向左查找、如何多条件查找……

这些知识就属于更高阶的内容了,请期待下一篇教程。

04、总结

①当需要引用的单元格随函数位置变化而变化时,使用相对引用,反之使用绝对引用;F4键可以快速进行相对引用和绝对引用的切换。

②COLUMN函数可以创造返回列数的动态变化

③模糊查找可以找到数值的区间

End.

Copyright © 2016 安伟星. All Rights Reserved.

我是安伟星(星爷)

Excel发烧友

微软Office认证大师

领英专栏作者

关注我,也许不能带来额外财富

但是一定会让你看起来很酷

文章均为原创,如需转载,请私信获取授权。

最后编辑于
©著作权归作者所有,转载或内容合作请联系作者
  • 序言:七十年代末,一起剥皮案震惊了整个滨河市,随后出现的几起案子,更是在滨河造成了极大的恐慌,老刑警刘岩,带你破解...
    沈念sama阅读 158,425评论 4 361
  • 序言:滨河连续发生了三起死亡事件,死亡现场离奇诡异,居然都是意外死亡,警方通过查阅死者的电脑和手机,发现死者居然都...
    沈念sama阅读 67,058评论 1 291
  • 文/潘晓璐 我一进店门,熙熙楼的掌柜王于贵愁眉苦脸地迎上来,“玉大人,你说我怎么就摊上这事。” “怎么了?”我有些...
    开封第一讲书人阅读 108,186评论 0 243
  • 文/不坏的土叔 我叫张陵,是天一观的道长。 经常有香客问我,道长,这世上最难降的妖魔是什么? 我笑而不...
    开封第一讲书人阅读 43,848评论 0 204
  • 正文 为了忘掉前任,我火速办了婚礼,结果婚礼上,老公的妹妹穿的比我还像新娘。我一直安慰自己,他们只是感情好,可当我...
    茶点故事阅读 52,249评论 3 286
  • 文/花漫 我一把揭开白布。 她就那样静静地躺着,像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上,一...
    开封第一讲书人阅读 40,554评论 1 216
  • 那天,我揣着相机与录音,去河边找鬼。 笑死,一个胖子当着我的面吹牛,可吹牛的内容都是我干的。 我是一名探鬼主播,决...
    沈念sama阅读 31,830评论 2 312
  • 文/苍兰香墨 我猛地睁开眼,长吁一口气:“原来是场噩梦啊……” “哼!你这毒妇竟也来了?” 一声冷哼从身侧响起,我...
    开封第一讲书人阅读 30,536评论 0 197
  • 序言:老挝万荣一对情侣失踪,失踪者是张志新(化名)和其女友刘颖,没想到半个月后,有当地人在树林里发现了一具尸体,经...
    沈念sama阅读 34,239评论 1 241
  • 正文 独居荒郊野岭守林人离奇死亡,尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 30,505评论 2 244
  • 正文 我和宋清朗相恋三年,在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 32,004评论 1 258
  • 序言:一个原本活蹦乱跳的男人离奇死亡,死状恐怖,灵堂内的尸体忽然破棺而出,到底是诈尸还是另有隐情,我是刑警宁泽,带...
    沈念sama阅读 28,346评论 2 253
  • 正文 年R本政府宣布,位于F岛的核电站,受9级特大地震影响,放射性物质发生泄漏。R本人自食恶果不足惜,却给世界环境...
    茶点故事阅读 32,999评论 3 235
  • 文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹,春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 26,060评论 0 8
  • 文/苍兰香墨 我抬头看了看天上的太阳。三九已至,却和暖如春,着一层夹袄步出监牢的瞬间,已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 26,821评论 0 194
  • 我被黑心中介骗来泰国打工, 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留,地道东北人。 一个月前我还...
    沈念sama阅读 35,574评论 2 271
  • 正文 我出身青楼,却偏偏与公主长得像,于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子,可洞房花烛夜当晚...
    茶点故事阅读 35,480评论 2 267

推荐阅读更多精彩内容