掌握 Excel 数组公式 006：连接数组运算数组公式怎么用

卓越完美

连接运算符为：&，可以将两个或多个项连接成一个项。这些项可以是数字、文本（括在引号中）、公式结果等。

如下图 1 所示，单元格区域 A2:C16 是源数据，单元格区域 E2:G10 是所需的交叉报表，显示每种产品的 L 和 R 数量。

图1

可以看到，每个搜索结果都基于两个查找值。例如，单元格 F4 中的数量 30 是针对单元格 E4 中的产品代码 2A35-2A36 和单元格 F3 中的 L 搜索源数据的结果。实现此双值搜索的一种方法是将公式中的两个查找值和源数据表中要搜索的两列连接起来。单元格 F4 中的数组公式为：

=INDEX($C$3:$C$16,MATCH($E4&F$3,$A$3:$A$16&$B$3:$B$16,0))

其中，MATCH函数用于获取待查找值在源数据中的相对位置。其第一个参数lookup_value的值为$E4&F$3（使用混合引用是为了让公式可以向下和向右延伸），将两个查找值连接成一个值；第二个参数lookup_array的值为$A$3:$A$16&$B$3:$B$16，连接待查找值在源数据中所在的列。

下图2展示了一种改进的方法，就是在连接时，在需要连接的项之间添加分隔符，这样可以让公式更加健壮。因为如果要查找的值都是数字，连接之后可能会出现意想不到的结果。

图 2

使用DGET函数进行多条件搜索

如果数据集有字段名（位于每列顶部的名称），则DGET函数可以根据多个条件执行搜索，如图3所示。请注意，对于AND条件，条件单元格位于同一行，对于OR条件，条件单元格位于不同的行。

图 3

使用DGET函数的缺点是公式不能向下复制。

使用辅助列进行多条件搜索

如下图4所示，增加了辅助列，将包含要搜索的值的列合并为一列，这样就可以使用VLOOKUP函数进行搜索了。A3单元格中的公式为：=B3&"|"&C3，辅助列通过下拉到数据末尾的方式构造。G4单元格中的公式为：

=VLOOKUP($F4&"|"&G$3,$A$3:$D$16,4,0)

只需向下并向右拖动即可。

图 4

使用数据透视表查找

对于上面的例子，您还可以使用数据透视表来实现所需的报表，如下图5所示。

图 5

对搜索列进行排序并使用近似匹配进行搜索

执行双值搜索时，如果源数据中的列可以排序，则使用近似匹配进行搜索比使用精确匹配更快。（因为精确匹配从头到尾遍历列，而近似匹配执行二分查找）如下图 6 所示，先将“L/R？”列按升序排序，再将“产品代码”列按升序排序，在单元格 F4 中输入数组公式：

=INDEX($C$3:$C$16,MATCH($E4&F$3,$A$3:$A$16&$B$3:$B$16))

向下和向右拖动至所有数据单元。

图 6

可以看出，公式中的MATCH函数省略了参数match_type，默认进行近似匹配。

如果查找列可以排序，那么您可以使用 LOOKUP 函数来处理数组操作，而无需按 Ctrl+Shift+Enter。

使用 LOOKUP 函数

如果搜索列已排序，则可以使用 LOOKUP 函数。LOOKUP 函数执行近似匹配搜索，并且可以处理数组操作。对于上面的示例，在单元格 F4 中使用 LOOKUP 函数的公式为：

=LOOKUP($E4&F$3,$A$3:$A$16&$B$3:$B$16,$C$3:$C$16)

结果如下图7所示。

图 7

配方改进

INDEX 函数可以检索整行或整列。诀窍是将其 row_num 参数指定为 0 或省略它，这将检索整列。这样，无需按 Ctrl+Shift+Enter 即可改进上例中的公式，如下图 8 所示。

图 8

单元格 F4 中的公式为：

=INDEX($C$3:$C$16,MATCH($E4&F$3,INDEX($A$3:$A$16&$B$3:$B$16,),0))

只需向下和向右拖动即可。

《Ctrl+Shift+Enter：掌握Excel数组公式》学习笔记

完美 Excel

欢迎大家在下方留言，完善本文内容，让更多人学到更加完善的知识。

欢迎来到知识星球：完美Excel社区，进行技术交流与解答疑问，获取更多电子资料。