excel里如何使用SUMPRODUCT进行跨工作表统计数量-Excel学习网

在Excel表中，您可以在列中的任意两个条件上进行过滤。但是在单独的列表中过滤任意数量的项目不是很好吗？如果在计算列中使用Excel的SUMPRODUCT函数，则可以。

 

 

Excel 2007引入了强大的表格功能，如下图所示。表格使您可以轻松地对数据进行排序和过滤。

 

但是，过滤能力至少有两个问题。首先，您最多只能使用两个条件来过滤任何列。

 

其次，每次更换过滤器时，大约需要六步。因此，更换过滤器并不是一个快速的过程。如果您只需将一个或多个过滤器复制并粘贴到一系列单元格中，甚至使用公式来更改过滤器，就会容易得多。

 

该图说明了解决这些问题的方法。

 

在图中，“表的项目”列只是电子表格的A列中的文本项目的列表。D列（不属于表）显示了Items列的搜索条件列表。如果公式B在列A的相邻项目中的SearchFor列表中找到任何项目，则该公式将返回TRUE。

 

在更新列表SearchFor，您只需按下F9键（如果你手动计算模式设置），然后如果你，如果你想要的物品只希望找到的项目，或FALSE筛选TRUE找到列 未找到。

 

 

这是我在上面的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”，然后搜索三组破折号，但失败了。因此，它返回了显示的错误值数组，中间夹着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列表包含任何空单元格，则整个Found列将返回TRUE。因此，我们需要仔细定义此列表。

 

一种选择是使用动态范围名称仅定义我们正在使用的搜索词列表。但是我不会在这里讨论这种方法。

 

另一个选择是输入一些您知道列表不包含的搜索项，例如上面D列中显示的三组破折号。然后，当您要将其他搜索词添加到列表中时，只需用实际要使用的搜索文本替换一组破折号即可。

 

但是，不要包含太多破折号，因为列表中的每个其他项都需要花费额外的时间来计算。因此，如果A列中的列表很长，则可能会明显降低计算速度。