ExcelHome技术论坛

 找回密码
 免费注册

QQ登录

只需一步,快速开始

快捷登录

搜索
EH技术汇-专业的职场技能充电站 妙哉!函数段子手趣味讲函数 Excel服务器-会Excel,做管理系统 Excel Home精品图文教程库
HR薪酬管理数字化实战 Excel 2021函数公式学习大典 Excel数据透视表实战秘技 打造核心竞争力的职场宝典
300集Office 2010微视频教程 数据工作者的案头书 免费直播课集锦 ExcelHome出品 - VBA代码宝免费下载
用ChatGPT与VBA一键搞定Excel WPS表格从入门到精通 Excel VBA经典代码实践指南
查看: 408201|回复: 326

[分享] VLOOKUP进阶——你可能所不知道的VLOOKUP用法

    [复制链接]

TA的精华主题

TA的得分主题

发表于 2014-8-19 11:02 | 显示全部楼层 |阅读模式
本帖已被收录到知识树中,索引项:VLOOKUP
本帖最后由 看见星光 于 2014-9-11 13:07 编辑

=====================写在前面====================


VLOOKUP函数,号称函数家族中的大众情人,是最简单最实用的函数之一,也是我们最常用的三个函数之一(其余两个是霸主级的SUMIF),关于它的每一寸肌肤(男生)每一块肌肉(女生),想必均都了如指掌,熟捻于心了。但是,您是否真正了解他的心呢?

因为这句反问,勾起伤心往事的,男生请走开,女生拥抱安慰,哈哈。

接下来我们有三个问题。
第一个,VLOOKUP能否查询符合条件的多个数值?就像经典数组套路INDEX+SMALL+IF那样?
第二个,VLOOKUP能否进行条件求和?就像SUMIF那样?
第三个,VLOOKUP第一参数,能否支持数组?

这三个问题,想必很多新人都会这样回答——不行……吧……?
我以前也这么认为。
但,这是真的吗?
——这当然不是真的。

这个帖子,咱们希望通过四个例子,扩展大家对于VLOOKUP函数的理解,更新大家对于VLOOKUP固定而死板的印象。





帖子进了知识树,所以看帖子的人总还是会有吧,所以我还是更新下例题附件吧:
您可能所不知道的VLOOKUP.zip (24.47 KB, 下载次数: 13157)
再增加一个附件:入门VLOOKUP
(年前给同事培训做的一个小东西,仅供练习消遣,真正的入门贴,还是看知识树哈)
入门VLOOKUP.rar (124.65 KB, 下载次数: 13250)

补注:

有些朋友看不明白多维数组部分,可以抹黑公式,按F9查看计算过程,如果实在茫无头绪,不妨置之不理,只要能看懂辅助列部分,相信便是有所收获,呵呵,对吧?(我每次发呵呵,都忍不住想起胡剑么么哒,晕)。

点评

第一个参数搞成数组,似乎第三个数组就无效了  发表于 2015-4-16 15:39

评分

36

查看全部评分

TA的精华主题

TA的得分主题

 楼主| 发表于 2014-8-19 11:14 | 显示全部楼层
本帖最后由 看见星光 于 2014-8-22 12:43 编辑

第一节:VLOOKUP按指定次数重复数据

如下图,有这样一道题,要求按照B列的指定次数,重复C列的班级名称,结果放入E列。
1.jpg

看到这道题,好学的表亲们,大概会立刻想起祝老师的某个基础操作教程动画,函数了得的亲们,会立刻想起某个多维数组套路。
但咱们这里只想VLOOKUP。

如果用VLOOKUP,这题怎么做?
很简单。
只需要两步。

第一步,A2输入公式:=A1+B2,向下填充
第二步,E2输入公式:=IFERROR(VLOOKUP(ROW(A1),A:C,3,0),E3)&""向下拖动。
然后……结果……

如下图:
2.jpg

我们结合两个公式,解释下其中过程。

第一个公式:A1+B2,是计算相关次数的累计值,比较好理解。
第二个公式:=IFERROR(VLOOKUP(ROW(A1),A:C,3,0),E3)&"",看起来是常用的VLOOKUP套路,但其实有两个很有意思的地方。

其一,VLOOKUP的查找值——ROW(A1)在公式的下拉过程中,通过查找1,2,3,4,5,(电脑配音,12345,上~山~打~老~虎)……来返回结果。

其二,屏蔽VLOOKUP错误值的方式。如果VLOOKUP查找不到相关数值,比如此例中的1和2,通常会返回错误值#N/A,而我们通过IFERROR,使它返回公式所在单元格的下一个单元格的值。
比如,我们在E2输入公式,VLOOKUP函数的错误值则返回E3,公式向下拖动,E3的错误值返回E4……如此类推,直至VLOOKUP函数返回正确值——则之前通过IFERROR函数判断为错误值的单元格,自然统一更新为相应的正确值(……脑海里播放多米诺骨牌从依次跌倒到依次站起的画面)。
然后再进行新一轮循环判断、数据更正。

最后的&””,是函数里常用的屏蔽零值的技法,以便在VLOOKUP公式下拉过界时,返回的零值显示为空白。

以上两个公式,除了VLOOKUP(ROW(A1)……)的技巧外,还利用了函数的另外一个技巧,我们姑且称之为上下其手。
何谓上下其手?简而言之,便是拿公式所在单元格的上下单元格结果为己用。
第一个公式,=A1+B2,是上手,拿公式所在单元格的上一个单元格的值为己用。很多人比较熟悉常用,已经很了解了。
第二个公式,=IFERROR(VLOOKUP(ROW(A1),A:C,3,0),E3)&""是下手,拿公式所在单元格的下一个单元格的值为己用。大家用的可能就比较少了。因为少用,所以才显得比较有意思。大家有闲时,不妨多想下,兴许别有收获哈。

==========我是似水温柔的分割线==========

通过辅助列的方式,我们实现了按指定次数重复数据。
下面咱们要做的,便是丢掉辅助列,直接用一个公式得出结果。
即,我们需要把A列累计次数求和的数据,放入VLOOKUP公式的查找范围中,以便直接得出所需要的结果。

我们可以使用这样的公式:
SUMIF(OFFSET($B$2,,,ROW($1:$4),),"<>")
这是一个累计求和的多维数组套路,类似的套路还有MMULT、INDIRECT、SUBTOTAL等。
这个公式,是通过OFFSET函数,制作多维求和统计范围,比如B2:B2,B2:B3,B2:B4……
最后使用SUMIF进行求和。

我们把这一段放入VLOOKUP函数中:
E2=IFERROR(VLOOKUP(ROW(A1),IF({1,0},SUMIF(OFFSET($B$2,,,ROW($1:$4),),"<>"),$C$2:$C$5),2,0),E3)&""
如此,这个公式便正式写完了。

当然,如果用LOOKUP,公式可以简洁:
=LOOKUP(ROW(A1),SUMIF(OFFSET($B$1,,,ROW($1:$5),),"<>")+1,$C$2:$C$6)&""

真是晕菜了,这一节,我发了N次,一直提示我有不良信息,我开始以为是上下其手,但最后发现……
是上山打~~虎。。
好吧,爱~~虎油,不让说。
~虎也不行。

唉,老~虎,老~虎,你炽热地发光, 照得夜晚的森林灿烂辉煌(可怜的布莱克)……

评分

13

查看全部评分

TA的精华主题

TA的得分主题

 楼主| 发表于 2014-8-19 11:28 | 显示全部楼层
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件       ★免费下载 ★       ★ 使用帮助
本帖最后由 看见星光 于 2014-8-20 12:41 编辑

第二节:VLOOKUP查询符合条件的多个结果。

通过第一节的内容,我们初步认识了VLOOKUP(ROW(A1),……)的技巧。
这一节,我们需要利用这个技巧,回答开篇所提到的第一个问题。
VLOOKUP能否查询符合条件的多个数值?就像经典数组套路INDEX+SMALL+IF那样?

3.jpg

如上图,我们需要提取C列符合F1班级的姓名,放入E4:E15

通常我们会使用INDEX+SMALL+IF的数组套路:
E4=INDEX(C:C,SMALL(IF($B$1:$B$15=F$1,ROW($1:$15),4^8),ROW(A1)))&""

如果使用VLOOKUP,我们应该怎么做?
其实也简单。
我们还是如第一节那般,先采用辅助列的方式。
A2=COUNTIF(B$2:B2,F$1)
向下填充。
E4=IFERROR(VLOOKUP(ROW(A1),A:C,3,0),"")
向下填充。

结果……如下:
4.jpg


这里,咱们依然利用了VLOOKUP(ROW(A1)……)的技巧。
第一个公式:=COUNTIF(B$2:B2,F$1)
我们使用COUNTIF函数,配合相对引用的原理,统计班级的累计重复次数。
第二个公式:=IFERROR(VLOOKUP(ROW(A1),A:C,3,0),"")
我们通过VLOOKUP查询ROW(a1)12345,上山打老……),来返回与之相对应的C列姓名结果,最后外套IFERROR函数,屏蔽VLOOKUP查询不到结果而返回的错误值,使之返回空白。

在数据量大时,我们使用INDEX+SMALL数组查询数据,难免卡机,此时不妨使用VLOOKUP+辅助列的方式,当然,辅助列我们不能再使用低效函数COUNTIF了,我们可以使用这样的公式:
=(B2=$F$1)+A1
(感谢Bodhidharma老师指正错误之处哈)

==========我是往事如烟的分割线==========

理解了辅助列的意义,加深了VLOOKUP(ROW(A1),……)技巧的理解,我们下面要做的,依然是丢掉辅助列,把辅助列的内容,放到公式中,直接使用一个公式得出结果。

我们依然可以使用OFFSETCOUNTIF的统计范围进行多维引用,比如:
=COUNTIF(OFFSET(B$2,,,ROW($1:$14)),F$1)
这个公式的意思,是使用COUNTIFB2:B2,B2:B3,B2:B4……直至B2:B15的范围内,分别统计F1数值的重复次数,得出来的结果,自然是和辅助列是一致的。

我们将这一段公式,放入VLOOKUP函数公式中:
=IFERROR(VLOOKUP(ROW(A1),IF({1,0},COUNTIF(OFFSET($B$2,,,ROW($1:$14)),$F$1),$C$2:$C$15),2,0),"")
如此,这个公式也便正式写完了。

==========我是如烟往事的分割线==========

当然,如果您确实了解透彻了VLOOKUP的心,关于VLOOKUP查询符合条件的多个数值,我们其实也可以写成这样:

=IFERROR(VLOOKUP($F$1,OFFSET($B$1:$C$1,SMALL(IF($B$1:$B$15=$F$1,ROW($1:$15)-1),ROW(1:1)),,15),2,0),"")
或者这样:
=IFERROR(VLOOKUP($F$1,INDIRECT("b"&SMALL(IF($B$1:$B$15=$F$1,ROW($1:$15)),ROW(1:1))&":c15"),2,0),"")

我们结合第二个函数套路来稍微解释下此中过程。

SMALL(IF($B$1:$B$15=$F$1,ROW($1:$15)),ROW(1:1))
IF函数判断B1:B15的值,是否等于F1,并返回相对应的行数序号ROW(1:15),或者FALSE。(为什么将IF的假值留白,而不是像许多学友那样习惯性的输入4^8之类?因为这里没有必要撒,逻辑值天生就比数值大不是……)
SMALL函数,按IF函数的结果,在公式下拉的过程中,依次从小到大取数,即ROW(1:1),ROW(2:2),取得最小值,第二小值……。

INDIRECT函数,搭配SMALL所取得的结果,完成对VLOOKUP查找范围从大到小的限定。
比如此例中的INDIRECT(“B”&13&”:C15”),INDIRECT(“B”&14&”:C15”)……。
由于VLOOKUP天生只取首个匹配结果,所以咱们通过查找范围的精确限定,便可以使它依次取得所有符合条件的结果……

最后外套IFERROR函数,屏蔽错误值,使之返回空白。
...
..
.

好啦,现在,咱们可以很清楚的知道,关于VLOOKUP无法提取符合条件多个数值的说法,是不正确的。呵呵。(我每次发呵呵,都会想起胡剑么么哒,唉)


点评

耶…COUNTIF不是易失性函數,然後MAX還是遍歷,對於運算效率的幫助應該不會很大  发表于 2014-8-19 12:49

评分

3

查看全部评分

TA的精华主题

TA的得分主题

 楼主| 发表于 2014-8-19 11:34 | 显示全部楼层
本帖最后由 看见星光 于 2014-9-9 12:54 编辑

第三节:VLOOKUP条件求和以及T/N+IF{1}技巧建立内存数组的一个应用小例。

这一节,我们来回答开篇所提到的第二个以及第三个问题:
VLOOKUP能否进行条件求和?就像SUMIF那样?
VLOOKUP第一个参数能否支持数组引用?

如下图,有这样一道题,需要在E1,求出A列存在的D3:D6班级的成绩之和。
5.jpg


解这道题的方法有很多种,我们通常使用SUMIF
数组:=SUM(SUMIF(A1:B5,D4:D6,B1))
或者:
数组:=SUM((A2:A5=TRANSPOSE(D4:D6))*B2:B5)

如果用VLOOKUP,又怎么做呢?
我们可以写成这样:
E1=SUM(VLOOKUP(T(IF({1},D4:D6)),A:B,2,0))
这个公式不需要按数组三键。

我们来简单了解下这个公式。
重点是VLOOKUP的查找值,T(IF({1},D4:D6))
我们知道D4:D6,是需要进行查找统计的班级名称,那么为什么要在其外套TIF函数?或者,我们反过来想,为什么不套TIF函数,VLOOKUP就只对查询范围的第一个数值(金庸班)进行查询呢?
我们可以这么简单的理解。
T/N+IF组合,是让VLOOKUP函数的第一参数,接受数组形式,因此返回相应的内存数组。
如此,VLOOKUP方能对每一个查找值进行查询统计。
具体解释参见小翟斑竹的贴子http://club.excelhome.net/thread-1115878-1-1.html

如果为了避免错误值的问题,比如D4:D6出现了查询范围不曾出现的班级名称:天仙班,公式可以修改为:
数组:=SUM(IFERROR(VLOOKUP(T(IF({1},D4:D6)),A:B,2,0),0))

=========我是温暖恰春的分割线=========

6.jpg
再看一道题。
如上图,对A列存在的D4:D6的班级进行求和,班级重复的只计算一次,答案是305。

我们通常使用这样的数组公式:
=SUM(SUMIFS(B:B,A:A,D4:D6)/COUNTIF(A:A,D4:D6))
或者:
=SUM(N(INDIRECT("b"&MATCH(D4:D6,A1:A9,))))

其实我们也可以使用VLOOKUP:
=SUM(VLOOKUP(T(IF({1},D4:D6)),A:B,2,0))
依然由于VLOOKUP天生就只取首个匹配的结果的缘故,所以咱们也就不需要对重复数据进行二次处理。
如果要屏蔽错误值,依然要增加IFERROR:
数组=SUM(IFERROR(VLOOKUP(T(IF({1},D4:D6)),A:B,2,0),0))

=========我是恰春温暖的分割线=========

综合以上两个问题,咱们不难发现,在条件求和方面,VLOOKUP和SUMIF还是有所不同的。
如果未加以处理,VLOOKUP只对第一次出现的数据进行计算,这是它的短处,当然,未必不是它的长处。
如果未加以处理,SUMIF会对所有数据进行求和,不论重复与否,这是它的长处,当然,未必不是它的短处。

=========我只是分割线=========

T/N+IF{1}技巧建立内存数组的一个应用小例
QQ图片20140821223202.jpg

如图,判定D列姓名的相对累计重复次数(中文名和英文名如果是同一个人的名字,则同样视为重复

这道题如果用辅助列,会很简单。
直接把名字统一转换为中文或者英文,再使用COUNTIF进行重复次数计算。
比如E2公式:=IFERROR(VLOOKUP(D2,A:B,2,0),D2),下拉后将名字统一更换为中文;再使用公式=COUNTIF($E$2:E2,E2),下拉后便可得出正确结果。

但如果不用辅助列呢?

如果我们继续之前的解题思路,将查询的名字,统一更换为中文或者英文,再进行重复次数的计算,我们依然可以使用VLOOKUP函数。

比如公式:F2=SUM(N(IFERROR(VLOOKUP(T(IF({1},$D$2:D2)),A:B,2,0),$D$2:D2)=IF(CODE(D2)<91,VLOOKUP(D2,A:B,2,),D2)))

IFERROR(VLOOKUP(T(IF({1},$D$2:D2)),A:B,2,0),$D$2:D2),是在公式下拉的过程中,将第二行到公式所在行的D列姓名,统一转换为中文,并以可以计算的内存数组的形式保存相关值。
IF(CODE(D2)<91,VLOOKUP(D2,A:B,2,),D2),是将D列需要判定重复次数的值,统一转化为中文。
SUM(N……)是统计第一个公式的内存数组值等于第二个公式返回值的次数,即相关名字的重复次数。
...
..

.

评分

3

查看全部评分

TA的精华主题

TA的得分主题

 楼主| 发表于 2014-8-19 11:40 | 显示全部楼层
本帖最后由 看见星光 于 2014-11-17 15:20 编辑

第四节:VLOOKUP在字符串提取中的使用小例。

我们依然用题来说事哈。

下面这道题,我们需要提取A列单元格内第一个数值,结果如B列。
7.jpg

我们通常使用数组公式:
B2=MID(A2,MATCH(1=1,ISNUMBER(-MID(A2,ROW($1:$99),1)),),1)
上面这个公式,通过ISNUMBERMID组合,来判断单元格内每一个字符是否是数值,再通过MATCH函数,对首个数值的位置进行定位,最后通过MID函数来取值。

如果我们用VLOOKUP来处理呢?
我们可以写成这样:
数组:=VLOOKUP(,MID(A2,ROW($1:$99),1)*{0,1},2,)
这个公式,依然利用MID函数,把单元格内的字符拆成个体,分别乘以01,如此则产生两列数据,一列由MID(A2,ROW($1:$99),1)*0得来,另外一列由MID(A2,ROW($1:$99),1)*1得来。
我们知道文本*0,是错误值,数值*0,结果为0
于是当我们利用VLOOKUP,查找第一列的0值,得出来的结果,便是首个0值所对应的数值——即我们所需要的结果。

这个技巧,并不仅仅局限于提取首个数字的使用,比如一个稍微复杂的示例:
1.jpg
如上图。数据区域是一些数据,有的人名后有电话号码,有的人名后没有电话号码,现在要求把没有电话号码的人名增补电话号码,增补的电话号码从哪来呢?向下数,从距离最近的拥有电话号码的人名那儿来,(关系再远,比如表大爷,毕竟也是一家人不是?)
结果如C列。

我们可以使用这样的公式:

=B3&IF(COUNT(--MID(B3,ROW($1:$52),11)),,VLOOKUP(,MID(PHONETIC(B3:$B$36),ROW($1:$201),11)*{0,1},2,0))

IF(COUNT(--MID(B3,ROW($1:$52),11)),是判断单元格内是否有电话号码。
VLOOKUP(,MID(PHONETIC(B3:$B$36),ROW($1:$201),11)*{0,1},2,0))
PHONETIC函数,将数据区域捏合为一个数据,MID函数,从中提取手机号码,最后通过VLOOKUP(,数据*{0,1},2,)的技巧,将MID的提取结果,分别乘以0和1,如此前所言,文本乘0,为错误值,数值乘0,结果为0,最后通过VLOOKUP来取得首个匹配结果,便是距离最近的手机号码。
最后有B3黏合提取的电话号码。

..
.

后记:
这篇帖子,只是分享思路和技巧,并不是建议每类问题用vlookup去解决。术业有专攻,每个函数,均有长处和短处,而且,数据应该适应函数,而不是函数来适应数据,不管什么时候,数据录入的规范性,都是最重要的哈。


再后记:
第一次写这类分享文,从早上9点钟动笔时的信心满满,到中午11.30草草结束时的垂头丧气,这中间的过程,真他妈的苦。如果不是忌惮旁边MM的心理承受能力,俺真想砸桌子骂脏话。妹的,俺果然还是适合讲故事,不适合玩技术分析……嗯,只希望这篇破烂东西,可以开拓大家对于VLOOKUP函数的视野,拓展下思维方式,嗯,祝安。。。





评分

7

查看全部评分

TA的精华主题

TA的得分主题

发表于 2014-8-19 12:24 | 显示全部楼层
前排旺位招租!!!
多谢楼主,先顶再看!!

TA的精华主题

TA的得分主题

发表于 2014-8-19 13:08 | 显示全部楼层
看见星光 发表于 2014-8-19 13:02
LOOKUP应该是最强大的函数之一,不管是数组运转还是技巧方面,VLOOKUP能做的,它通常也能做到,而且很多时 ...

星光大侠,Vlookup是我最喜欢的函数,没有之一...除了sum以外这是我接触的第二个函数,感触深刻.

点评

函数没有喜不喜欢,只有合不合用  发表于 2014-8-19 15:17

评分

1

查看全部评分

TA的精华主题

TA的得分主题

发表于 2014-8-19 14:48 | 显示全部楼层

TA的精华主题

TA的得分主题

 楼主| 发表于 2014-8-19 14:50 | 显示全部楼层
本帖最后由 看见星光 于 2014-8-19 14:51 编辑
满坛皆为吾师 发表于 2014-8-19 13:08
星光大侠,Vlookup是我最喜欢的函数,没有之一...除了sum以外这是我接触的第二个函数,感触深刻.

居然敢说你是在认识IF那妞之前认识的VLOOKUP!赤裸裸的喜新厌旧哇!!哈哈,有朵花了,先送了。

TA的精华主题

TA的得分主题

发表于 2014-8-19 15:17 | 显示全部楼层
您需要登录后才可以回帖 登录 | 免费注册

本版积分规则

手机版|关于我们|联系我们|ExcelHome

GMT+8, 2024-3-29 05:20 , Processed in 0.056883 second(s), 9 queries , Gzip On, Redis On.

Powered by Discuz! X3.4

© 1999-2023 Wooffice Inc.

沪公网安备 31011702000001号 沪ICP备11019229号-2

本论坛言论纯属发表者个人意见,任何违反国家相关法律的言论,本站将协助国家相关部门追究发言者责任!     本站特聘法律顾问:李志群律师

快速回复 返回顶部 返回列表