AI之所以会发展如此之迅猛，就是人们可以通过简单指令，从而得到相对满意的结果。

excel的各种函数之所以会被开发出来、新函数不断地出现，其目的也是要让用户“少做多得”。

今天我们就来探索一下excel中的一对多的查询方案。

什么是一对多？

所谓一对多就是输入一个查询条件，经过函数或者公式处理后，从目标数据表中，将所有符合条件的结果都罗列出来。

以上就是我所认为的一对多查询。也是我们的宗旨：do less,get more.

常用函数公式的短板。

常用到的查询：index (x)match、vlookup、hlookup、lookup、xlookup这些查询公式的短板：

一次查询返回的都是第一个查到的结果，即只能得到一个结果。

会有同学通过其他方法去嵌套到上面的公式中，从而做到了do less，get more的目标。

目前我们有什么方法可以做到一对多的查询呢？

1、filter函数：毋庸置疑，filter函数在这方面是最合适不过的了。但是这个函数也有一个短板，即是无法通过通配符或者关键字来实现一对多的查询。好在大家经过摸索，发现嵌入search或find函数就能解决这个短板了。

excel一对多查询并提取，excel中的一对多的查询方案探索-趣帮office教程网

=FILTER($B$2:$B$36,IFERROR(SEARCH(IF(H2<>"",H2,"未设置条件"),B2:B36),-1)>0,"未设置条件")
/*
IF(H2<>"",H2,"未设置条件"):这部分公式的意思是如果keyWord处没有输入内容，则显示"未设置条件"的提示
当有输入内容的时候，则显示输入的内容。
SEARCH(IF(H2<>"",H2,"未设置条件"),B2:B36)：这部分公式的意思是：将keyword与B2:B36列中的每个
单元格进行匹配，如果匹配上了，则会返回查找到的“位置”，而“位置”是个整数，会大于0，未找到则会返回
#VALUE!错误。
IFERROR(SEARCH(IF(H2<>"",H2,"未设置条件"),B2:B36),-1)>0:这部分公式即是filter公式的条件参数了。
用iferror函数将search函数的结果嵌套起来就是为了进行容错处理。iferror是将出现search函数返回的错误
容错处理成-1，当search函数查找到了目标时，会返回一个正整数，而未找到则是-1，因此将这部分与0进行比较
就能将查找到的结果展现出来
上方的示例中：输入的是“*浩然”,在下方则将姓名中所有带“浩然”的姓名全部罗列出来。从而实现了一对多的模
糊查找。
*/

2、TextSplit和TextJoin search if函数组合：关于这两个函数的具体介绍，可以查看《Excel这几个文本处理函数，效率翻倍！（二）》一文详细了解。这一组函数之所以可以用来进行查询，其主要作用不在这二者本身，而在于与之配合的search函数与if函数。之所以要将textsplit和textjoin放在一起，就是因为这对函数可以减小返回数组大小。

excel一对多查询并提取，excel中的一对多的查询方案探索-趣帮office教程网

=TEXTSPLIT(TEXTJOIN(",",TRUE,IF(N(IFERROR(SEARCH(H2,B2:B36),0))>0,B2:B36,"")),,",",TRUE,0)
/*
这一行公式，未像上面那样对初始输入进行判断，以下公式演示都将忽略这个步骤
SEARCH(H2,B2:B36)：这部分公式的意思是：将keyword与B2:B36列中的每个
单元格进行匹配，如果匹配上了，则会返回查找到的“位置”，而“位置”是个整数，会大于0，未找到则会返回
#VALUE!错误。
N(IFERROR(SEARCH(H2,B2:B36),0))：用iferror函数将search函数的结果嵌套起来就是为了进行容错处理。
iferror是将出现search函数返回的错误容错处理成0，当search函数查找到了目标时，会返回一个正整数，
而未找到则是0，因此将这部分与0进行比较就能将查找到的结果展现出来。
N函数的存在纯粹是为了避免有个别数学还处于文本格式状态，至于后面if比较出现错误。
IF(N(IFERROR(SEARCH(H2,B2:B36),0))>0,B2:B36,"")：这部分公式意思是将查找到的结果用与0进行比较
查找到的结果会大于0，如果大于0，则显示对应B列的单元格内容；否则显示成“”。
TEXTJOIN(",",TRUE,IF(N(IFERROR(SEARCH(H2,B2:B36),0))>0,B2:B36,""))这部分的作用即是将查找到的
符合条件的内容用","进行连接，忽略空白单元格。如此一来，就将查找到的姓名通过","连接起来了。
TEXTSPLIT(TEXTJOIN(",",TRUE,IF(N(IFERROR(SEARCH(H2,B2:B36),0))>0,B2:B36,"")),,",",TRUE,0)
的作用就是将上面查找到的用","连接起来的字符串，再进行切割成一个数组。
其实这组函数之所以也能查询，其核心内容是search函数和if函数。组合和切割函数仅仅是对结果进行分类，将
不符合要求的数据抛弃，留下符合要求的数据。
*/

3、SMALL SEQUENCE countif search if组合：为什么说这个函数组合也可以呢？其核心仍然是search if。而small函数则可以将查找到的值所在行号罗列出来。而行号罗列出来了，要找对应的值就很容易了。

excel一对多查询并提取，excel中的一对多的查询方案探索-趣帮office教程网

=SMALL(IF(IFERROR(SEARCH(H2,B2:B36),0)>0,ROW(B2:B36),""),SEQUENCE(COUNTIF(B2:B36,H2),1,1,1))

这个公式，小编在此就不讲解其各部分的含义了，仅解释一下small函数。此函数因过于复杂，在此仅仅是提供一个思路。

small函数，它的语法是small(数值,k)，数值容易理解，即是一个数值类型的数组，找出期中较小的k个值。如果k=1,则返回最小的，如果k=给定数组个数，则将返回全部数组。

为了能自动确定这个k的大小，就需要通过sequence函数来生成一个从符合条件的数的个数的序列，该序列从1开始，增值为1，最大值是符合查找条件的数据的个数。用公式进行表达即是

SEQUENCE(COUNTIF(B2:B36,H2),1,1,1)

4、tocol、torow search if。这一组函数的核心同样是search if,这是通配符查找的核心内容。而tocol、torow函数的亦是一个可以改变数组大小的函数。

=TOROW(array, [ignore], [scan_by_column])
=TOCOL(array, [ignore], [scan_by_column])
/*
array ：要作为行返回的数组或引用。
[ignore] ：是否忽略某些类型的值。 默认情况下，不会忽略任何值。 指定下列操作之一：
0    保留所有值（默认）
1    忽略空白
2    忽略错误
3    忽略空白和错误
[scan_by_column]：按列扫描数组。 默认情况下，按行扫描数组。 扫描确定值是按行排序还是按列排序。
如果省略 scan_by_column 或 FALSE，则按行扫描数组；如果为 TRUE，则按列扫描数组。
Excel 返回 #VALUE！ 当数组常量包含一个或多个不是整数的数字时。
当数组太大时，Excel 将返回 #NUM。
*/

excel一对多查询并提取，excel中的一对多的查询方案探索-趣帮office教程网

excel一对多查询并提取，excel中的一对多的查询方案探索-趣帮office教程网

TOROW(IF(SEARCH(H2,B2:B36)>0,B2:B36,""),3,TRUE)
TOCOL(IF(SEARCH(H2,B2:B36)>0,B2:B36,""),3,TRUE)
/*
上面这两个公式的意义完全一样，只不过是最后将数组溢出的方式不同。
参数
IF(SEARCH(H2,B2:B36)>0,B2:B36,"")：这层意思是通过对输入内容进行查找，如果查找到了
则显示对应内容所在行；若未查找到，则显示为“”。也有可能会被显示为错误，因为search函数返回的可能是错误
但是torow和tocol的第二个参数可以设为忽略空白和错误，这样就省去了我们自己对错误进行容错处理。
*/

总结：excel中想要实现一对多、通配符查找（模糊查找），大多数与search（find）是分不开的；而要把符合条件的数据列出来就需要公式中有改变输入数组大小的能力。这两者却一不可。

1、search（find）：查找的核心

2、改变数组大小的公式或者函数：筛选核心。