Hi,
Vlookup的确可以算得上是EXCEL高频函数了。但是往往英雄孤掌难鸣,Vlookup也不例外。Vlookup配合match,column等函数,将大大提升Vlookup函数的使用效率,更适应更多的业务场景。
因此,今天我们来聊聊Vlookp+match+column会碰出怎样的火花?
一、初始Match
Match字面意思是匹配,配对的意思。
Match函数的语法为:
查找值和查找范围比较好理解,匹配模式需要理解一下:
匹配模式有三个可选参数:
举个例子就明白了。
数据如下:
当第三个参数为1或者-1时,计算机制要理解一下。参数为0是用得最多的,这个比较好理解。和vlookup联合用的时候,一般匹配也是精确匹配。
二、初识Column
Column是列的意思,在EXCEL中column函数非常简单,返回所在列的序号。它和row是一对函数。Row函数返回所在行的行号。
Column(),参数为空时,返回单元格所在的列号。
Coloumn(B:B),返回B列所在的列号。B列处于第二列,因此返回值为2。
三、Vlookup、Match、Column配合机制
Match和column函数可以配合vlookup实现动态区域引用,其作用机制在于可以动态返回vlookup查找区域的列的位置。
Match和column作用于Vlookup的第三个参数,成为vlookup的嵌套函数。
1.Vlookup+column实现大范围固定位置数据引用
先看需求,以下有两张表,表1是员工基础信息表,表格2需要从基础信息表中,将姓名到总工资所有的内容都复制过去。
当然,你可以一条一条找,然后再复制,但这样相当容易出错。
这个时候我们就可以在vlookup中嵌套column了。
单元格B26的公式为:
=VLOOKUP($A26,$A$2:$I$22,COLUMN(B:B),0)
因为要往后及往下复制公式,需要将工号单元格B26列相对引用,以及查找区域$A$2:$I$22绝对引用。查找区域需要选择一个最大的区域。
因为姓名是在查找区域的第2列,因此column参数是B:B列相对引用。在往后复制公式的时候,自动会变成C、D、E…,从而动态返回2,3,4等数,实现了动态引用返回的列。
2.Vlookup+match实现大范围不固定位置引用
先看需求,我们有一个各个部门的分月费用执行表。好心的同事,以及帮我们做好了。部门在行上,月份在列上。
现在给定部门和月份,动态查询所产生的成本。分析一下就会发现,这个时候,vlookup返回列的位置参数就是不确定了,需要根据指定月份来确定。
D11单元格公式:
=VLOOKUP(B11,$B$2:$N$6,MATCH(C11,$C$2:$N$2,0)+1,0)
Vlookup第一个、第二个参数都好理解。
这里match实现了拿月份在月份行中去匹配位置,这里的月份引用要绝对引用。+1是因为,vlookup返回列是从其引用范围的第2列开始的。这个参数需要结合实际情况予以调整。
四、总结
Vlookup还可以与其它函数结合起来实现更为复杂的功能。而match和coloumn是针对vlookup函数的第三个参数进行调整。
后续如果需要对vlookup函数的第二个参数进行动态调整的话,可能需要用到indirect或者offset等函数。后续有机会我们再专题讨论。
以上,是今日分享的内容。
虽然vlookup很简单,但是和其它函数结合在一起就可以实现我们的各种功能。
函数在Excel数据分析中非常重要,因为我们需要用它们来实现各种数据分析。
我是华哥。每日精进,不负光阴韶华。
赠人玫瑰,手留余香。欢迎评论、点赞、关注,转发。