excel里如何使用SUMPRODUCT进行跨工作表统计数量--英雄云拓展知识分享

在Excel表中，您可以在列中的任意两个条件上进行过滤。但是在单独的列表中过滤任意数量的项目不是很好吗，我们将继续深入。如果在计算列中使用Excel的SUMPRODUCT函数，则可以。Excel 2007引入了强盛的表格功能，以下图所示。表格使您可以轻松地对数据进行排序和过滤。

但是，过滤能力最少有两个问题。首先，您最多只能使用两个条件来过滤任何列。其次，每次更换过滤器时，大概需要6步。因此，这种情况下，更换过滤器其实不是一个快捷的进程。如果您只需将一个或多个过滤器复制并粘贴到一系列单元格中，乃至使用公式来更改过滤器，就会容易很多。该图说明了解决这些问题的方法。在图中，“表的项目”列只是电子表格的A列中的文本项目的列表。D列（不属于表）显示了Items列的搜索条件列表。如果公式B在列A的相邻项目中的SearchFor列表中找到任何项目，则该公式将返回TRUE。在更新列表SearchFor，您只需按下F9键（如果你手动计算模式设置），然后如果你，如果你想要的物品只希望找到的项目，或FALSE挑选TRUE找到列 未找到。SUMPRODUCT公式这是我在上面的B列中使用的 SUMPRODUCT公式：B3：= SUMPRODUCT（NOT（ISERROR（SEARCH（SearchFor，[@ Items]）））+ 0）> 0如果您之前没有使用过Excel Tables，则“ [@Items]”参数对您来讲会很奇怪。该参数说：“从Items列确当前行返回值。” 也就是说，它在单元格B3中的工作方式与在此处使用单元格援用A3相同：B3：= SUMPRODUCT（NOT（ISERROR（SEARCH（SearchFor，A3）））+ 0）> 0事实上，当您在单元格B3中键入此公式，但单击而不是在单元格A3中键入时，Excel会返回“ [@Items]”。因此，这种情况下，在输入公式时，您实际上不需要了解Table语法。但是，还有一个问题需要更长的解释：这个SUMPRODUCT公式如何起作用？回答此问题的最好方法是遵守我最初用于创建公式的路径。为此，就某些情况而言，让我们使用单元格B4，当该单元格中的公式正常工作时，该单元格应返回TRUE …

步骤1：设置SEARCH功能。单元格B4中的SEARCH公式返回#VALUE！毛病，由于它试图将多个搜索结果返回到一个单元格中。（换句话说，它正在尝试为SearchFor列表中的每一个项目返回一个结果。）要查看这些结果是甚么，请单击公式栏中的任意位置，然后按F9键以计算公式。当您按F9键时，您会得到以下结果：

SEARCH函数返回#VALUE！ 当找不到商品时。在这里，Excel在“运动外套”中搜索“鞋子”，但失败了。它搜索“ b？w”并失败；它搜索“ c * s”并成功，返回“ 8”，然后搜索3组破折号，但失败了。因此，这种情况下，它返回了显示的毛病值数组，中间夹着8个。在公式栏中计算结果后，就像我在这里所做的那样，按Esc键返回原始公式。我们的下一步是将这些毛病值转换为FALSE值，并将“ 8”转换为TRUE值。为此，就某些情况而言，我们用NOT（ISERROR（…））包围SEARCH函数，从而为我们提供…

步骤2：将毛病设置为FALSE，将成功设置为TRUE。由于我们要对结果求和，所以需要将那些TRUE和FALSE转换为1和0（一和零）。最简单的方法是在公式中添加零。（这是行之有效的的，由于TRUE + 0 = 1且FALSE + 0 =0。）这给我们：B4：= NOT（ISERROR（SEARCH（SearchFor，[@ Items]）））+ 0测试编辑栏中的更改，我们现在看到：

步骤3：将TRUE设为1，将FALSE设为0。现在，我们需要将这些结果加起来。如果总数大于零，我们知道最少有一个过滤器起作用。我们经过再次扩大公式来添加结果…B4：= SUMPRODUCT（NOT（ISERROR（SEARCH（SearchFor，[@ Items]）））+ 0）> 0这是公式的终究版本。现在，我们可以将其复制到表的其余部份，从而得到以下结果：

步骤4：如果所有1的总和大于零，则返回TRUE。我们在这里使用SUMPRODUCT的缘由有两个。首先，即便公式中没有乘法（也没有“乘积”），此函数也会对数组中的值求和。因此，这种情况下，它可以满足我们的需求。其次，它省去了我们需要数组输入公式的其他版本的麻烦。如果您不介意在表中使用数组，则可以对这个较短的公式进行数组输入以得到相同的结果：B4：= OR（NOT（ISERROR（SEARCH（SearchFor，[@ Items]））））使用OR函数之所以行之有效的，是由于如果OR的任何参数为TRUE，则OR返回TRUE，这就是我们想要的。但是令人遗憾的是，我们必须告知OR将其参数视为数组，这就是为何我们必须对表达式进行数组输入的缘由。关于SearchFor列表的扼要说明如果SearchFor列表包括任何空单元格，则全部Found列将返回TRUE。因此，这种情况下，我们需要仔细定义内涵此列表。一种选择是使用动态范围名称仅定义内涵我们正在使用的搜索词列表。但是我不会在这里讨论这类方法。另外一个选择是输入一些您知道列表不包括的搜索项，例如上面D列中显示的3组破折号。然后，当您要将其他搜索词添加到列表中时，只需用实际要使用的搜索文本替换一组破折号便可。但是，不要包括太多破折号，由于列表中的每一个其他项都需要花费额外的时间来计算。因此，这种情况下，如果A列中的列表很长，则可能会明显下降计算速度。