☰

Excel：如何计算包含“X”且上方单元格包含“Y”的单元格数量

tag-icon • tag-icon microsoft-excel worksheet-function
Excel：如何计算包含“X”且上方单元格包含“Y”的单元格数量

我是 Excel 的普通用户，这是我以前遇到过的问题，最终找到了不同的方法来解决，但在这种情况下，我认为这是我唯一的选择。

这是我的表单的示例图片：
工作表样本

我想计算范围内单元格包含“1a”且包含 1a 的单元格上方的单元格包含“Math”的次数。显然，这个例子很简单，但我认为解决这个问题的逻辑与更大的工作表相同，其中我的数据完全填充了它。

在此示例中，答案应该是两次。我该如何构建公式来使这种类型的条件计算在内？

答案1

您可以使用 COUNTIFS 公式。它允许您指定多个条件。使用 pat2015 的示例，如果数据在范围 A3:J8 中，则公式应为

=COUNTIFS(A3:J7;"Math";A4:J8;"1a")

（请注意标准之间的一行偏移）

截屏

答案2

我建议的解决方案需要一个辅助列。假设您的数据排列在单元格 A3:J8 中，使用 K 列作为辅助列，并在其中输入以下数组公式

{=SUM(IF($A4:$J4="1a",IF($A3:$J3="Math",1,0)))}

将公式放在不带括号的位置，然后在公式栏中按 CTRL+SHIFT+ENTER 使其成为数组公式。这将给出“1a”的计数，其上方有单词“Math”。从 K4 开始并将其向下拖动。对于交替行，它将返回 0，但这无关紧要。现在只需对辅助列求和。您可以使用简单的 SUM 函数来实现这一点。可能有更聪明的方法来实现此目的，而无需辅助列，甚至可以使用 VBA 宏。

在此处输入图片描述

答案3

如果重新格式化数据以使 Math 和 1a 位于同一个单元格中：

=G5&" "&G6

然后你可以使用 countif：

=COUNTIF($F$15:$K$18,N15)

在此处输入图片描述

答案4

=COUNTIFS(F3:K11, "Math", F4:K12, "1a")

已经由惠誉496，是一个好的开始。（注意：有些语言环境使用;来分隔函数参数；其他语言环境使用,。）它有一个可能的问题：它将计算“Math”在偶数行，而“1a”在其下方的奇数行的单元格对。（虽然问题没有提到这个问题，但插图表明不应该计算这样的对。）

为了解决这个问题，我首先将上述公式转换成产生相同结果的公式，但使用SUMPRODUCT：

=SUMPRODUCT(--(F3:K11="Math"), --(F4:K12="1a"))

这里我们使用了标准技巧，即在布尔表达式前面加上 ，-- 将 TRUE 变成 1，将 FALSE 变成 0，并使用 的SUMPRODUCT隐式乘法作为逻辑与。然后我将其扩展为

=SUMPRODUCT(--(F3:K11="Math"), --(F4:K12="1a"), --(MOD(ROW(F3:K11),2)=1))

测试“Math”是否出现在奇数行（MOD(ROW(…),2)=1）。这失败了，因为 Excel 似乎将其视为ROW(F3:K11)与相同ROW(3:11)，生成一个由 9 个值组成的线性数组，而不是由 54 个值组成的矩形数组。（恕我直言，这是 Excel 中的一个错误。）我发现我可以通过强制它考虑列，然后忽略它们来解决这个问题：

=SUMPRODUCT(--(F3:K11="Math"), --(F4:K12="1a"), --(MOD(ROW(F3:K11)+0*COLUMN(F3:K11),2)=1))

它计算每个单元格的列号（COLUMN(…)），然后将其乘以 0。这样就行了。下图与问题中 OP 的数据相同，但在单元格 J8和 中添加了“Math”和“1a” J9。  fitch496的 COUNTIFS公式（我将其放在我的答案的顶部）和我的简单SUMPRODUCT公式都计算了这个未对齐的“Math 1a”并得出总数 3；我的最终SUMPRODUCT公式忽略了未对齐的“Math 1a”并得出总数 2。

      电子表格
        （请参阅该文章的来源以获得上述内容的可复制粘贴的副本。）

我的公式可以稍微简化一下：

  • --(MOD(row number,2)=1)取行号模 2 并测试它是否等于 1。如果是，比较测试结果为 TRUE，然后通过 将其转换为 1。--如果不是 1，则一定是 0；与 1 的比较结果为 FALSE，然后通过 将其转换为 0。--我们可以省去逻辑运算（测试质量然后应用于--布尔结果），只需使用。MOD(row number,2)
  • --(Boolean)就是。最内层将 TRUE 转换为 1，将 FALSE 转换为 0，但随后它将 1 转换为 −1。然后外层将 −1 转换回 1。由于我们取两个因数的乘积，外层相互抵消，因此我们可以忽略它们。-(-(Boolean))-----

因此，最终的简化公式是

=SUMPRODUCT(-(F3:K11="Math"), -(F4:K12="1a"), MOD(ROW(F3:K11)+0*COLUMN(F3:K11),2))

相关内容