excel表格SEARCH和SUMPRODUCT函数的使用-Excel学习网

SUMPRODUCT是Excel最强大的工作表功能之一。例如，在这里，您可以在一个公式中使用它来在一个单元格中搜索许多项目的文本。

 

 

 

在 如何向Excel表中添加高级过滤器功能中，我解释了如何在表中使用长公式来简化复杂过滤。

 

该公式依赖于Excel的 SEARCH工作表功能，该功能使我们能够在另一个字符串中搜索一个字符串。搜索不区分大小写，可以使用 通配符。

 

但是，不幸的是，SEARCH旨在一次只搜索一个字符串。这个限制对我来说一直是个问题，因为当我在一列中过滤数据时，我经常需要包括两个以上的条件。

 

若要了解我的意思，请查看Excel表中“标签”列中四个单元格的内容：

 

|美国|国家统计局|每月| bls |失业率|美国MSA | mt |密苏拉州| 

|美国|国家统计局|每月|美国清算银行|失业率|失业率|县| mt |加勒廷县，mt | 

|美国|每月| sa | bls |利率|失业率|状态| mt | 

||美国|美国国家航空航天局|每周|就业|状态|西塔| mt |覆盖|

 

如果我想查看蒙大拿州的失业数据而忽略县，大都市统计区（“ MSA”）和经季节性调整（“ SA”）的数据怎么办？为此，我需要应用五个过滤器。

 

我以前的文章解释说，一种有效的方法是在表格中设置一个过滤器列，该列的公式在满足所有条件时将返回TRUE；否则，它们返回FALSE。但是，在该帖子中，该公式要求每个搜索到的单元格使用多个SEARCH函数。

 

但是现在，我将介绍一个公式，该公式只需要对每个搜索到的单元格使用一个SEARCH函数……无论您想对每个单元格应用多少个过滤器。

 

 

 

我上面列出的标签描述了可从圣路易斯联邦储备银行获得的经济数据。但是，即使您不在乎经济数据，我也强烈建议您使用“标签”列来处理Excel表中的数据。原因如下：

 

您的大多数数据可能是由IT部门或商业程序生成的。因此，您可能无法控制Excel表包含的代码和描述（元数据）。但是，如果您在表中添加“标签”列，您最终将能够获得对您有意义的信息。

 

我将在以后的文章中详细讨论这个想法，但是这里是开始的方法：

 

您的表可能包含一列，其中包含唯一标识每一行的代码，系列ID，总帐科目编号，SKU，产品编号等。因此，您可以使用该列代码和自己的“标记”列维护一个单独的表。然后，当您打开新版本的数据作为Excel表时，可以添加具有使用VLOOKUP 或INDEX - MATCH的公式的列， 以将自定义标签列添加到标准数据。

 

标记每行数据可能需要花费一些精力。但是，您只需要标记每行一次（除非您更改标记，您可以随意这样做）。从那时起，您将能够使用自定义标签从您的角度查看表数据。

 

 

此公式使用一个SEARCH函数在任何单元格中的文本中搜索列表中任意数量的项目。它以单个值的形式返回其发现的摘要。然后对该值的测试会使公式返回TRUE或FALSE，以指示该单元格是否符合所有条件。这是四行中的公式：

 

 

对于SEARCH函数执行并通过的每个测试， SUMPRODUCT函数都会将可比较的数字添加到其总数中。因此，如果搜索仅在文本中找到“ mt”和“ unemployment”，SUMPRODUCT将加1加8。如果这是您想要的条件，则当您测试值9时，该公式将返回TRUE，如下所示。

 

另一方面，如果您还需要“县”数据，则可以在总数中包括其值4。也就是说，您将测试13而不是9。

 

 

该公式的关键是SUMPRODUCT函数，该函数将其参数视为数组…即使该公式未输入数组也是如此。该函数在内存中设置一个临时列，该列对列表中的每个项目执行SEARCH测试。

 

我们不在乎列表中找到搜索文本的位置，我们只想知道搜索文本是否存在。因此，我们将SEARCH函数与NOT（ISERR（…））函数一起使用。如果找到该项目，则没有错误。因此 ISERR返回FALSE，而NOT函数将结果切换为TRUE。因此，TRUE表示已找到搜索文本。

 

另一方面，如果找不到搜索文本，则SEARCH返回错误值。因此ISERR返回TRUE，NOT函数将其切换为FALSE。因此FALSE表示未找到搜索文本。

 

最后，SUMPRODUCT函数将这些TRUE或FALSE结果乘以列表中的相应数字。由于TRUE等于1，FALSE等于零，因此SUMPRODUCT将找到的项目的编号相加。选择数字以使每个和代表值的唯一组合。因此，我们可以测试一个数字以指定所需的搜索成功和失败的任意组合。

 

 

再次是公式：

 

 

您可以通过多种方式修改和扩展它。例如…

 

…如果您对失业以外的蒙大纳州县信息感兴趣，则可以测试值5。（这意味着搜索“ mt”（1）和“ county”（4）必须成功，而其他所有搜索都将失败）

 

…如果您对蒙大拿州以外任何城市的失业信息感兴趣，则可以测试值10。（搜索“ msa”（2）和“失业”（8）成功，而所有其他搜索失败。）

 

…如果您决定暂时不关心“ county”标签是否存在，则可以将列表中的值4替换为零。或者，如果要临时选择任何状态，可以将列表中的值1替换为零。

 

…如果要使用F3：J3范围内的一行搜索文本项，而不是公式中的数组常量行，则可以将公式更改为：

 

 

…如果您要使用D3：D7范围内的一列搜索文本项，而不是一行项，则可以将公式更改为：

 

 

（请注意，在此公式末尾，数组常量中数字之间的分号。分号表示数据列而不是行。）

 

…如果要对表中没有的单元格使用此搜索技术，请用单元格引用替换“ [@Tags]”。

 

…如果要测试五个以上的项目，只需将它们添加到列表中，然后将连续的2的幂加到数字列表中即可。例如，如果要测试八个项目，则您的数字列表将为{1,2,4,8,16,32,64,128}。

 

最后，如果要在两个不同的单元格中搜索项目列表，则可以使用两个以上的SUMPRODUCT测试，它们都包含在一个 AND函数中，如下所示：

 

 

当然，如果要搜索四个单元格，则可以在AND函数中包含四个SUMPRODUCT测试。