excel公式技能之连接数组运算-英雄云拓展知识分享

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

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

图1

可以看出，每一个查找的结果都是基于两个查找值。举例来看，单元格F4中得到的数量30是在源数据中查找同时满足单元格E4中的产品代码2A35⑵A36和单元格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（使用混合援用使得公式能够向下向右扩大），将两个查找值连接为单个值；第2个参数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+回车键。

使用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+回车键，以下图8所示。

图8

在单元格F4中的公式为：

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

向下向右拖拉便可。