之前我们说过vlookup的一些基本用法,和模糊查找的用法,还有向左查询的用法。今天说说通配符的用法。先上2个题目。 -01- 根据序号查询出货号。你可能会说,这不就是vlookup向左查询吗,用上次说的公式=VLOOKUP(D2,IF({1,0},B2:B9,A2:A9),2,)就可以查询出。没错,这样的方法可以实现,看来上次说的你已经学会了。 但是,今天我们用一种其他的方法——vlookup通配符的用法。先上公式=VLOOKUP("*",IF(D2=B2:B9,A2:A9,),1,)。 解释一下公式的意思,先看IF(D2=B2:B9,A2:A9,)函数,它的意思是在序号列中每一个是不是等于D3,如果是,就返回对应的货号;如果不是,就返回0,这样就构建了一个一维数组{0;0;"S9022";0;0;0;0;0},把我们要查找的货号找到了。 然后,怎么样把它提取出来,就用到vlookup通配符的用法了。我们看到这个数组里面只有数字和文本,而公式中的通配符查找的就是文本。这样就查找到文本,并返回它的值。 这里还有其他的方法可以查出,如=MID(VLOOKUP("1*",--(D2=B2:B9)&A2:A9,1,),2,99)。 解释一下这个函数的意思,还是先看vlookup第二参数,--(D2=B2:B9)&A2:A9,它的意思是在序号列中每一个是不是等于D3,如果是,就在其对应的货号前连接1,否则连接0。数组为{"0S9020";"0S9021";"1S9022";"0S9023";"0S9024";"0S9025";"0S9026";"0S9027"} vlookup第一参数为“1*”,意思就是查找以1开头的文本,再用mid函数从第2个字符开始提取,提取99个字符。 -02- 从信息列表中提取出手机号,你会怎么做呢?如果要求你只用vlookup函数,你又会怎么做呢? 先上公式=VLOOKUP("*",--MID(F2,ROW($1:$99),11)&"",1,)
--MID(F2,ROW($1:$99),11)将数组进行运算,将文字运算成错误值,文本型数字转化成数字。但是这样我们还是不能用vlookup通配符查找出来,因为通配符查找的是文本,所以这样--MID(F2,ROW($1:$99),11)&""再转化成文本型的数字,就能用通配符查找了。 延伸方法: 这里除了用通配符的方法,还有其他的方法。 (1). =VLOOKUP(,MID(F2,ROW($1:$99),11)*{0,1},2,),这种方法我觉得很精妙,是我在网上看到的。其中MID(F2,ROW($1:$99),11)*{0,1},构成了一个2列99行的二维数组,文字的通过相乘都变成错误值,文本型数字通过相乘得到{0,它本身},这样通过vlookup查找0,返回第二列,就查找到了。 (2). =MAX(IFERROR(--MID(F2,ROW($1:$99),11),)),这种方法是将错误值转化成0,然后提取最大值。 vlookup通配符的用法,你学会了吗?上面这2个题你还有其他的解法吗? |
|