excel公式技巧的连排运算

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

如下图1所示，在单元格区域A2:C16是源数据，在单元格区域E2:G10是所需的交叉表报表，它显示了每个产品的l和r的数量。

图1

可以看出，每次搜索的结果基于两个搜索值。例如，在单元F4中获得的数量30是搜索源数据以满足单元E4中的产品代码2A35-2A36和单元F3中的L的结果。实现这种二分搜索法的一种方法是将两个搜索值与公式中源数据表中的两个搜索列连接起来。单元F4中的数组公式为：

=INDEX(3:加元16加元，MATCH(E4F 3美元，3:澳元16澳元：澳元16澳元16澳元：澳元16.0英镑))

其中，MATCH函数用于获取待搜索值在源数据中的相对位置，其第一个参数lookup_value的值为$E4F$3(公式可以使用混合引用向下向右扩展)，两个搜索值连接成单个值；第二个参数lookup_array的值是$ a $3: $ a $16 $ b $: $ b $16，它连接源数据中搜索值的列。

图2显示了一个改进的方法，即在连接时，在要连接的项目之间添加一个分隔符，这使得公式更加健壮。因为如果要搜索的值都是数字，连接后可能会出现意外的结果。

图2。

使用DGET函数寻找多个条件。

如果数据集有字段名(即每列顶部的名称)，DGET函数可以执行基于多条件的搜索，如下图3所示。请注意，条件单元格表示同一行中的“与”条件和不同行中的“或”条件。

图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(3:加元16加元，MATCH(E4F 3美元，3:澳元16澳元：澳元16澳元16澳元：澳元16澳元))

向下拖动到所有数据单元格。

图6

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

如果可以对查找列进行排序，则可以使用查找函数来处理数组操作，而无需按Ctrl+Shift+Enter。

使用LOOKUP函数。

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

=LOOKUP(E4F $ 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(E4F $ 3，A $ 3: $ A $ 16 $ B $ 3: $ B $ 16，)，0))

向右下拖。