一对多查询时的函数组合套路,你一定要牢记!

点击上方

蓝色

文字  关注我们吧!

送人玫瑰,手有余香,请将文章分享给更多朋友

动手操作是熟练掌握EXCEL的最快捷途径!

上周刚和大家讲了一对多查询的的方法。鉴于一对多查询在实际工作中经常出现,今天就再次强调一下一对多查询的函数组合拳!由于VLOOKUP函数那部分需要添加辅助列,相对比较简单,不再详细解释了。

01

INDEX函数+SMALL函数+IF函数法

完整的公式应该是“=IFERROR(INDEX($B$2:$B$17,SMALL(IF(A$2:A$17=$J$2,ROW($A$2:$A$17)-1,100),ROW(A1))),"")”,CTRL+SHIFT+ENTER回车。

思路:

  • 利用IF(A$2:A$17=$J$2,ROW($A$2:$A$17)构造出一个数组{2;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;13;FALSE;FALSE;FALSE;FALSE},再减去1,就得到“天龙八部”这部书所在的行号

  • IF公式中第三个参数“100”保证了当逻辑值是FALSE是,返回值是100,这个值足够大,至少要大过所有数据的行数,以保证FALSE对应的行不会被INDEX函数抓取到

  • 其余的INDEX函数和IFERROR函数就相对简单了,不再赘述

注意:在IF函数中第三个参数如果不书写,也是可以达到目的的。IF函数部分可以直接写成IF(A$2:A$17=$J$2,ROW($A$2:$A$17)-1)也是可以的。

02

OFFSET函数+SMALL函数+IF函数法

和上面的例子相同,完整的公式为“=IF(ROW()-1>COUNTIF($A$2:$A$17,$J$2),"",OFFSET($B$1,SMALL(IF(A$2:A$17=$J$2,ROW($A$2:$A$17)-1,100),ROW(A1)),0))”,CTRL+SHIFT+ENTER回车。

思路:

  • IF部分的公式和上例是相同的,请参看上例

  • OFFSET函数利用SMALL函数的返回值进行偏移

  • 着重讲一下ROW()-1>COUNTIF($A$2:$A$17,$J$2)这一部分。它是IF函数逻辑判断的第一个参数,含义是:当当前的行号-1(因为是从第二行开始的)大于某本著作在数据区域中出现的总次数时,意味着所有符合要求的数据都已经被抓取了,就返回空值

同样地,这里的IF函数第三个参数也可以省略。

文章推荐理由:

一对多查询时如此地重要,需要你牢记公式组合套路!

-END-

长按下方二维码关注EXCEL应用之家

面对EXCEL操作问题时不再迷茫无助

推荐阅读:

一对多查询?这里的方法是比较全的,值得你收藏!

涨知识了,筛选功能原来可以这样用!

你知道每个月你家交多少电费吗?

高级筛选?公式表示不服!

总结篇-IF函数实用终极帖

戳原文,更有料!免费模板文档!

(0)

相关推荐