excel表格SEARCH和SUMPRODUCT函数的使用--英雄云拓展知识分享

SUMPRODUCT是Excel最强盛的工作表功能之一。举例来看，在这里，您可以在一个公式中使用它来在一个单元格中搜索许多项目的文本。在 如何向Excel表中添加高级过滤器功能中，我解释了如何在表中使用长公式来简化复杂过滤。该公式依赖于Excel的 SEARCH工作表功能，该功能使我们能够在另外一个字符串中搜索一个字符串。搜索不辨别大小写，可使用 通配符。但是，令人遗憾的是，SEARCH旨在一次只搜索一个字符串。这个限制对我来讲一直是个问题，由于当我在一列中过滤数据时，我常常需要包括两个以上的条件。若要了解我的意思，请查看Excel表中“标签”列中4个单元格的内容：|美利坚合众国|国家统计局|每个月| bls |失业率|美利坚合众国MSA | mt |密苏拉州||美利坚合众国|国家统计局|每个月|美利坚合众国清算银行|失业率|失业率|县| mt |加勒廷县，mt ||美利坚合众国|每个月| sa | bls |利率|失业率|状态| mt |||美利坚合众国|美利坚合众国国家航空航天局|每周|就业|状态|西塔| mt |覆盖|如果我想查看蒙大拿州的失业数据而疏忽县，大都市统计区（“ MSA”）和经季节性调剂（“ SA”）的数据怎样办？为此，就某些情况而言，我需要利用5个过滤器。我之前的文章解释说，一种行之有效的的方法是在表格中设置一个过滤器列，该列的公式在满足所有条件时将返回TRUE；否则，它们返回FALSE。但是，在该帖子中，该公式要求每一个搜索到的单元格使用多个SEARCH函数。但是现在，我将介绍一个公式，该公式只需要对每一个搜索到的单元格使用一个SEARCH函数……不管您想对每一个单元格利用多少个过滤器。

中断：为何应将标签添加到Excel表我上面列出的标签描写了可从圣路易斯联邦储备银行取得的经济数据。但是，即便您不在意经济数据，我也强烈建议您使用“标签”列来处理Excel表中的数据。缘由以下：您的大多数数据多是由IT部门或商业程序生成的。因此，这种情况下，您可能没法控制Excel表包括的代码和描写（元数据）。但是，如果您在表中添加“标签”列，您终究将能够取得对您成心义的信息。我将在以后的文章中详细讨论这个想法，但是这里是开始的方法：您的表可能包括一列，其中包括唯一标识每行的代码，系列ID，总帐科目编号，SKU，产品编号等。因此，这种情况下，您可使用该列代码和自己的“标记”列保护一个单独的表。然后，当您打开新版本的数据作为Excel表时，可以添加具有使用VLOOKUP 或INDEX – MATCH的公式的列， 以将自定义内涵标签列添加到标准数据。标记每行数据可能需要花费一些精力。但是，您只需要标记每行一次（除非您更改标记，您可以随便这样做）。从那时起，您将能够使用自定义内涵标签从您的角度查看表数据。引入多标准搜索公式此公式使用一个SEARCH函数在任何单元格中的文本中搜索列表中任意数量的项目。它以单个值的情势返回其发现的摘要。然后对该值的测试会使公式返回TRUE或FALSE，以唆使该单元格是不是符合所有条件。这是4行中的公式：= SUMPRODUCT（NOT（ISERR（SEARCH（{“ mt”，“ msa”，“ county”，“ unemployment”，“ | nsa |”}，[@ Tags]））））* {1,2,4,8， 16}）= 9对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将找到的项目的编号相加。选择数字以使每一个和代表值的唯一组合。因此，这种情况下，我们可以测试一个数字以指定所需的搜索成功和失败的任意组合。扩大多标准搜索公式再次是公式：= SUMPRODUCT（NOT（ISERR（SEARCH（{“ mt”，“ msa”，“ county”，“ unemployment”，“ | nsa |”}，[@ Tags]））））* {1,2,4,8， 16}）= 9您可以经过多种方式修改和扩大它。例如……如果您对失业之外的蒙大纳州县信息感兴趣，则可以测试值5。（这意味着搜索“ mt”（1）和“ county”（4）必须成功，而其他所有搜索都将失败）…如果您对蒙大拿州之外任何城市的失业信息感兴趣，则可以测试值10。（搜索“ msa”（2）和“失业”（8）成功，而所有其他搜索失败。）…如果您决定暂时不关心“ county”标签是不是存在，则可以将列表中的值4替换为零。或，如果要临时选择任何状态，可以将列表中的值1替换为零。…如果要使用F3：J3范围内的一行搜索文本项，而不是公式中的数组常量行，则可以将公式更改成：= SUMPRODUCT（NOT（ISERR（SEARCH（$ F $ 3：$ J $ 3，[@ Tags]]））* {1,2,4,8,16}）…如果您要使用D3：D7范围内的一列搜索文本项，而不是一行项，则可以将公式更改成：= SUMPRODUCT（NOT（ISERR（SEARCH（$ D $ 3：$ D $ 7，[@ Tags]]））* {1; 2; 4; 8; 16}）（请注意，在此公式末尾，数组常量中数字之间的分号。分号表示数据列而不是行。）…如果要对表中没有的单元格使用此搜索技术，请用单元格援用替换“ [@Tags]”。…如果要测试5个以上的项目，只需将它们添加到列表中，然后将连续的2的幂加到数字列表中便可。举例来看，如果要测试8个项目，则您的数字列表将为{1,2,4,8,16,32,64,128}。最后，如果要在两个不同的单元格中搜索项目列表，则可使用两个以上的SUMPRODUCT测试，它们都包括在一个 AND函数中，以下所示：= AND固然，如果要搜索4个单元格，则可以在AND函数中包括4个SUMPRODUCT测试。