873三篇文章搞定VLOOKUP这个迷人小精灵-中级篇
873 |
三篇文章搞定VLOOKUP这个迷人小精灵-中级篇 |
||||
示范档 |
无 |
范例档 |
873.XLSX |
结果档 |
873F.XLSX |
在Excel函数界,有400多位成员,只有VLOOKUP是公认的大众情人。这么多年来,即使有更强大的函数组合能够实现它的功能,也从来没有谁能真正取代它的江湖地位。
在历年的文章
835 不会这5个Excel函数别说您熟练使用Excel VLOOKUP函数(字串匹配函数)
Q129 VLOOKUP与合并储存格(不同格数合并,5格内)
中提到这次锦子老师将为大家揭开VLOOKUP的神秘面纱,初级、中级、高级一应俱全,值得收藏。
2、VLOOKUP中级——成为大神的必经之路
VLOOKUP除了简单的搜寻外,还可以实现一些更复杂的搜寻匹配。
案例1:多列搜寻快速输入公式
多栏搜寻时,其他参数不变,公式「=VLOOKUP(L2,$A$2:$J$71,?,0)」,最重要的是要修改第三个参数的值,因为栏在变化,第3个参数也在发生变化。
职称在员工代号后的第二栏,那么第三个参数应该是3,公式:「=VLOOKUP(L2,$A$2:$J$71,3,0)」;
部门在员工代号后的第三栏,那么第三个参数应该是2,公式:「=VLOOKUP(L2,$A$2:$J$71,4,0)」。
我们可以将第几栏用其他函数的计算结果来实现,一般情况下最常用的有两种方法:
(1)1=COLUMN(A1);2=COLUMN(B1)……
(2)1=MATCH(A1,$A$1:$J$1,0);2=MATCH(B1,$A$1:$J$1,0)……
最终公式有两种:
(1)=VLOOKUP($L2,$A$2:$J$71,COLUMN(B2),0)
(2)=VLOOKUP($L2,$A$2:$J$71,MATCH(B$1,$A$1:$J$1,0),0)
案例2:数值区间模糊搜寻(计算每个月薪的所得税金额)
注意:搜寻的数值范围必须由小到大排列,输出结果是和搜寻值最接近但比它小的那个值。
K2储存格公式:「=F2*VLOOKUP(F2,$M$5:$N$8,2,1)」,再将公式到K3:K71储存格,结果如下图所示。
案例3:模糊条件模糊搜寻
VLOOKUP的第一参数可以自动进行模糊匹配,将以E3开头的储存格找到并匹配其对应的储存格内容。
N11储存格公式:「=VLOOKUP(M11,$B$2:$K$71,5)」,如下图所示
案例4、使用万用字元精确搜寻
VLOOKUP的第一个参数还支援万用字元“*”,使用万用字元后相当于确定了搜寻条件,可以实现精确搜寻,搜寻结果也是返回首次满足条件的记录的相应值。
N11储存格公式:「=VLOOKUP("*"&M11&"*",$B$2:$K$71,5,0)」