☰

计算多个范围标准

tag-icon • tag-icon microsoft-excel worksheet-function microsoft-excel-2007 countif
计算多个范围标准

这是我在这个论坛上的第一篇帖子，所以可能表达得不太好。我正在使用 Excel 2007 为我的论文收集一些数据。我最近学会了如何将 COUNTIFS 嵌套在 SUMPRODUCT 中，以搜索多个条件，其中一个条件是范围而不是常数。但是，如果我需要搜索多个条件，其中两个（或更多）条件是范围而不是常数，则相同的公式不起作用。

假设我在 Excel 中有三个相邻的 AC 列，其中 A 称为“水果”，包含各种水果，B 称为“颜色”，C 称为“有毒吗？”，仅包含值“是”或“否”。如何将 A 列中所有为苹果或梨的项目与 B 列中所有为绿色或红色的项目相加，并将 C 列中所有不有毒的项目相加？

在这个简单的 Excel 表中，正确答案是 4，但以下公式给出的结果是 1：

=SUMPRODUCT(COUNTIFS(A:A,$E$2:$E$3,B:B,$F$2:$F$3,C:C,$G$3))

在此处输入图片描述

其中的标准是指我在外面手动设置的数组。

我一直在网络和论坛上寻找答案，找到了几十个相关帖子，有些甚至可能是相同的（具有多个 OR 条件范围的 Countif)，但我还是没能找到答案...我的论文已经快完成了，所以如果能提供任何帮助我将非常感激。

答案1

您无需嵌套任何内容。该函数SUMPRODUCT非常强大，允许您根据非常复杂的条件进行计数或求和。它支持逻辑 AND 和 OR 运算符。

TL;DR 版本

=SUMPRODUCT((($A$2:$A$10="Apple")+($A$2:$A$10="Pear"))*(($B$2:$B$10="Red")+($B$2:$B$10="Green"))*($C$2:$C$10<>"Yes"))

长版本（抱歉……）

您确实应该学习如何使用此功能，因为它非常有用并且可以为您节省大量时间。这里有一个关于如何使用它的快速指南，但我建议您保留一些带有简单示例的测试文件，例如您提供的那个，以便您可以尝试使用它。不要犹豫，将您要解决的不同问题分开。

基本上，SUMPRODUCT包含几个成员，每个条件都是一个条件。如果将这些条件相乘，则得到一个逻辑“与”。如果将这些条件相加，则得到一个逻辑“或”。

举个例子就容易多了！下面是使用水果示例对最终公式的细分。

笔记：为了让事情变得简单，我使用了表名Fruits，它允许使用命名范围并提高可读性。

在此处输入图片描述

它的工作原理如下。您的第一个条件实际上是一个逻辑或（您想要苹果或梨，因为水果不能同时是两者）。因此公式 (1) 有 2 个成员，或 2 个条件：水果 = 苹果，水果 = 梨。

(Fruits[Fruit]="Apple")TRUE如果是苹果则返回，否则返回FALSE。通过将其与第二个成员（(Fruits[Fruit]="Pear")）相加，您将获得一个由 0 和 1 组成的数组。逻辑如下：

  • 假 + 假 = 0
  • 真 + 假 = 1
  • 真 + 真 = 1

然后，SUMPRODUCT将计算该数组的所有元素（0 和 1）的和。这与计数元素基本相同。结果如预期一样为 8（单元格 D14）。

第二个条件（颜色）的工作原理完全相同。如果您想添加第三种颜色（例如“橙色”），只需在方程中添加一个新成员即可+。

第三个条件更简单，你只有 1 个标准。我<>故意使用了符号，以表明你也可以排除元素。在公式 (1) 中，你可以使用例如(Fruits[Fruit]<>"Banana")。

重要的：请注意，在 中SUMPRODUCT，当只有一个成员时（如第 3 个等式），您需要将结果转换为数字。请记住： 创建一个和SUMPRODUCT的数组。您可以使用函数轻松完成此操作，或者编写，它将转换为 1，和转换为 0。TRUEFALSEN(...)--(...)TRUEFALSE

现在，这 3 个条件可以单独起作用，但我们希望将它们组合成 1 个公式。由于我们希望这 3 个条件中的每一个都为真（水果、颜色和有毒），因此我们需要创建一个逻辑 AND。这可以像我们做 OR 一样完成，但这次我们将使用乘法 ( *)：

  • 假 * 假 = 0
  • 真 * 假 = 0
  • 真 * 真 = 1

对于我们的 3 个条件，我们只需使用我们之前编写的 3 个单独公式，并将它们放在 中SUMPRODUCT。这 3 个成员必须用括号括起来，并用 分隔*（我们基本上将它们相乘）。

以下是最终的公式，供您尝试：

=SUMPRODUCT((Fruits[Fruit]="Apple")+(Fruits[Fruit]="Pear"))
=SUMPRODUCT((Fruits[Color]="Red")+(Fruits[Color]="Green"))
=SUMPRODUCT(N(Fruits[Poisonous]<>"Yes"))

最后一句：

=SUMPRODUCT(((Fruits[Fruit]="Apple")+(Fruits[Fruit]="Pear"))*((Fruits[Color]="Red")+(Fruits[Color]="Green"))*(Fruits[Poisonous]<>"Yes"))

附注：

  • 您测试的每个条件（例如颜色 = 红色）都必须放在括号内：(Fruits[Color]="Red")
  • 如果有或条件，括号的顺序非常重要。例如，等式(X and (Y or Z))与 不同((X and Y) or Z)。
  • 您可以使用经典运算符来测试条件：= 表示相等，<> 表示不同，> 和 < 表示大于/小于，>= 和 <= 表示大于/小于或等于。
  • 我们用 来SUMPRODUCT计数，但我们也可以用它来求和。如果等式中的一个元素没有符号=，则将计算其值（见下面的示例，其中 G 列被求和）。
  • 可以将条件"Apple"替换为对单元格的引用，单元格本身可以是下拉菜单。最好使用变量，而不是直接在公式中写入文本。
  • SUMPRODUCT可能是一个资源密集型公式，因为它要进行乘法和求和... 取决于您要测试的条件数、数据集的大小以及您使用的次数SUMPRODUCT。对于更简单的条件，SUM.IFS可能更快。
  • SUMPRODUCT还支持部分文本搜索，如下所示：

在此处输入图片描述

答案2

这里有一个替代解决方案，效果很好，更适合原始作者需要列出可能的标准。我改编了在这个答案。

基本上有两种计算值的方法：

  • 要么使用“白名单”，列出可接受的值，要么使用ISNUMBER
  • 要么使用“黑名单”，排除不可接受的值，要么使用ISNA

白名单：

=SUMPRODUCT(
    (ISNUMBER(MATCH(Fruits[Fruit],Whitelist[Fruit];0)))
    *
    (ISNUMBER(MATCH(Fruits[Color],Whitelist[Color];0)))
    *
    (ISNUMBER(MATCH(Fruits[Poisonous],Whitelist[Poisonous];0)))
)

黑名单：

=SUMPRODUCT(
    (ISNA(MATCH(Fruits[Fruit],Blacklist[Fruit],0)))
    *
    (ISNA(MATCH(Fruits[Color],Blacklist[Color],0)))
    *
    (ISNA(MATCH(Fruits[Poisonous],Blacklist[Poisonous],0)))
)

我强烈建议您不要在列上使用它，因为它可能会占用大量资源。我还没有在非常大的值列表上尝试过它，但请随意分享您的结果！

在此处输入图片描述

答案3

我不确定我是否完全理解了这一点，但似乎可以通过以下方式得到答案

=SUMPRODUCT(COUNTIF($E$2:$E$3,$A$2:$A$10), COUNTIF($F$2:$F$4,$B$2:$B$10), COUNTIF($G$3,$C$2:$C$10))

尝试解释：我们知道COUNTIF($B$2:$B$10, "Red") （或者，等价地，COUNTIF($B$2:$B$10, $F$2)计算红色水果，得出 3 个计数。同样，COUNTIF($B$2:$B$10, "Green") （或者，等价地，COUNTIF($B$2:$B$10, $F$3)计算绿色水果，得出 5 个计数。因此，直观地尝试使用COUNTIF($B$2:$B$10, $F$2:$F$3) 来计算红色或绿色的水果是诱人的。这本身不起作用，但我们可以通过执行以下操作使其起作用

=SUM(COUNTIF($B$2:$B$10, $F$2:$F$3))

作为数组公式，或

=SUMPRODUCT(COUNTIF($B$2:$B$10, $F$2:$F$3))

就像普通公式一样。它的一大优点SUMPRODUCT是它被设计为自动处理数组，因此您可以使用它来处理数组，而无需数组公式。

但上述公式的作用正如前面的讨论所期望的那样：它们将 3+5 相加，得到 8。这一点很重要：它们将两个数字的数组相加，因为我们要查看两种颜色。当我们在不同的列中查找不同数量的值时，这会给我们带来麻烦。

COUNTIF($E$2:$E$3, "Yellow")，COUNTIF($E$2:$E$3, "Red")和 COUNTIF($E$2:$E$3, "Green")乍一看似乎没什么意义；它们的值分别为 0、1 和 1。但这意味着会COUNTIF($E$2:$E$3,$A$2:$A$10)产生一个包含 9 个数字的数组，即：0、1、1、1、1、1、1、1、1、1 和 1。猜猜怎么着？总和仍然是 8。

记住如何SUMPRODUCT工作。  SUMPRODUCT(X11:X14, Y11:Y14, Z11:Z14)例如，等于

                （X11×× ）+                 （×× ）+                 （×× ）+                 （×× ）Y11​Z11
X12Y12Z12
X13Y13Z13
X14Y14Z14

因此它需要具有相同长度的数组参数。二、二和一是一个问题；15、26 和其他数字也是一个问题。九、九和九是好的。

现在查看、 、X示例，但进行以下替换：YZ

  • X11→A2是我们寻找的果实之一，
  • Y11→B2是我们正在寻找的颜色之一，
  • Z11→C2是“否”，

依此类推。由于 TRUE 为 1，FALSE 为 0，SUMPRODUCT如果每列包含我们要查找的值（因为 1×1×1=1），则矩阵的每一行都是 1，如果每列中的行都不可接受，则矩阵的每一行都是 0。因此，我们有一个包含九个 0 和 1 的列表，我们将其相加。

答案4

因为这是关键问题，”我想计算一下绿色或红色但无毒的苹果和梨（不是香蕉）的数量“”。

因此，我建议以表格格式提供的解决方案以满足您的要求。

检查屏幕截图：

在此处输入图片描述

在 E2 中写入此公式：=COUNTIFS($A$2:$A$10, "="&$D2,$B$2:$B$10,"="&E$1)

笔记，向右填充公式一单元格，然后向下填充一行，您将获得苹果和梨的数量。

在 G2 中写入此公式：=COUNTIFS($A$2:$A$10, "="&$D2,$C$2:$C$10,"=No")

笔记，填满第一行，你会得到一定数量的无毒苹果和梨。

最后，对无毒列求和。

相关内容