excel公式技巧之连接数组运算

excel公式技巧之连接数组运算

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

如下图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+回车键。

使用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))

向下向右拖拉即可。

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。