5个Excel函数组合使用,90%的一对多查询快速解决,教程免费送

(在文章最后领取教程)
excel全套教程 + excel各行各业模板, 免费送excel中的一对多查询,很多人都是不知道公式要怎么写的。就拿我们最常用的vlookup函数、lookup函数来说,仅仅依靠一个函数是无法做到的,有些人也会加上一些辅助列来达到一对多查询的目的,但都是比较麻烦的。
下面教你一组万能公式“index+small+if+row+iferror”,学会这个函数组合,可以解决excel中一对多查询90%以上的问题。
下面有一张图,想要在a1:f9单元格区域中,找出符合b11单元格“销售一部”的所有内容,并显示到以a13开始的单元格区域中,图片中符合条件的只有第2、第5和第6行,已经用其他颜色标出来。如果用这组函数来实现,你有什么思路吗?
具体操作步骤如下:1、选中a13单元格 -- 在编辑栏中输入公式“=iferror(index(a$1:a$9,small(if($a$2:$a$9=$b$11,row($a$2:$a$9)),row(a1))),)”-- 按组合键“ctrl+shift+enter”结束该公式。
2、可以看到a13单元格公式返回的结果是“销售1部”-- 将a13单元格往右拖拉至f13单元格 -- 往下拖拉至f15单元格 -- 即可得到部门为“销售一部”的所有数据。
3、动图演示如下。
4、公式解析。
(1)row($a$2:$a$9):
生成一个行号的数组{2;3;4;5;6;7;8;9},即a1:f9单元格区域中的第2行至第9行的行号。
(2)if($a$2:$a$9=$b$11,row($a$2:$a$9)):
判断$a$2:$a$9单元格区域中的内容是否跟$b$11单元格的内容相等,如果相等,返回
$a$2:$a$9对应的行号,否则,返回false。此时该公式得到的结果是一组数组{2;false;false;5;6;false;false;false}。数组中的2、5和6代表“销售1部”所在的行号。
(3)small(if($a$2:$a$9=$b$11,row($a$2:$a$9)),row(a1)):
small函数的作用是:在一列数值中,按从小到大的顺序取第n个值。第1个参数if($a$2:$a$9=$b$11,row($a$2:$a$9))为数据区域。第2个参数row(a1)为返回的数据在数组或数组区域里的位置(从小到大)。if($a$2:$a$9=$b$11,row($a$2:$a$9))返回的结果为{2;false;false;5;6;false;false;false}。由于small函数在计算最小值时忽略逻辑值true和false以及文本型数字。所以该公式最后得到的结果为{2;5;6;#num!;#num!;#num!;#num!;#num!}。
(4)index(a$1:a$9,small(if($a$2:$a$9=$b$11,row($a$2:$a$9)),row(a1))):
index函数的作用是:用来引用我们所需的信息。其有3个参数。第1个参数表示我们要引用的区域,第2个参数是表示要引用的行数,第3个参数表示要引用的列数。最终的结果就是引用区域内行和列的交叉内容。这里返回的结果是第2行、第5行和第6行单元格的内容,即“销售1部”。
(5)ifferor函数。
我们上两步返回的值都有错误值,这样看起来感觉不是很好,为了将错误值变成空,可以使用ifferor函数,ifferor函数有2个参数,如果我们将第2个参数设置为空,公式执行时会判断是否有错误值输出,如果有,直接让错误值不显示。下图是没有使用ifferor函数得到的结果。
以上就是小编今天要跟大家将的一对多查询的通用组合公式。相信大家看了这篇教程之后还是一脸懵逼的,看一遍没看懂就多看几遍,再跟着练习几次就可以学会了。
excel全套教程 + excel各行各业模板免费获取方式:1: 转发本篇文章 ( 让更多有需要的朋友看到)
2: 关注我的头条号( 后面还会分享更多精彩干货哟)
3: 私信我 “ 表格 ”