一对多查询时的函数组合套路,你一定要牢记!
点击上方
蓝色
文字 关注我们吧!
送人玫瑰,手有余香,请将文章分享给更多朋友
动手操作是熟练掌握EXCEL的最快捷途径!
上周刚和大家讲了一对多查询的的方法。鉴于一对多查询在实际工作中经常出现,今天就再次强调一下一对多查询的函数组合拳!由于VLOOKUP函数那部分需要添加辅助列,相对比较简单,不再详细解释了。
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)也是可以的。
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操作问题时不再迷茫无助
推荐阅读:
戳原文,更有料!免费模板文档!