☰

COUNTIF 带公式表达式

tag-icon • tag-icon microsoft-excel worksheet-function countif
COUNTIF 带公式表达式

我有一个网格，其标题名称在第一行和第一列，例如：

A 乙 C 德
A
乙
C
德

行和列中具有相同标题名称的单元格代表给定列的“有效”值。

例如，假设我有这个：

A 乙 C 德
A 1
乙 3
C 1
德 4

在这种情况下，第二列的“有效”值为“1”，第三列的“有效”值为“3”，第四列的“有效”值为“1”，第五列的“有效”值为“4”。

现在对于每一行，我想计算它有多少个有效值。

我使用了两种条件格式来突出显示“有效”值以及等于该值的值：

  • 黄色单元格表示“=B$1=$A2”
  • 绿色单元格为“=AND(NOT(ISBLANK(B2)),INDEX(B$2:B$10,MATCH(B$1,$A$2:$A$10,0))=B2)”

它看起来是这样的：

截屏

我需要一个可以计算这些值的公式。基本上，就是最后那个“COUNT”列的公式。在屏幕截图上，您可以看到它应该根据当前网格值计算哪些值。

答案1

在此处输入图片描述

一步一步来看，我们可以使用以下命令检索每列的有效值：

=INDEX($B$2:$E$5,XMATCH($B$1:$E$1,$A$2:$A$5),XMATCH($B$1:$E$1,$B$1:$E$1))

上述公式位于图像中的单元格 H1 中。

我们可以检查单元格的值是否等于该列的有效值：

=$B$2:$E$5=H1#

该公式位于图像中的单元格 H2 中。

然后我们可以计算每行中 TRUE 值的数量：

=BYROW(H2#,LAMBDA(r,SUM(N(r*1))))

该公式位于图像中的单元格 N2 中。它将函数 SUM(N(row*1)) 应用于检查数组中的每一行。因此，只要它发现 TRUE，它就有 1，只要它发现 FALSE，它就有 0。将这些 1 和 0 相加即可得到正确的结果。

如果您想要一个单一公式，只需选择包含标题的整个表格，则可以使用它（如图中的单元格 Q2 所示）：

=LET(
    arr,$A$1:$E$5,
    colh,INDEX(arr,1,2):INDEX(arr,1,COLUMNS(arr)),
    rowh,INDEX(arr,2,1):INDEX(arr,ROWS(arr),1),
    dat,INDEX(arr,2,2):INDEX(arr,ROWS(arr),COLUMNS(arr)),
    valid,INDEX(dat,XMATCH(colh,rowh),XMATCH(colh,colh)),
    chk,dat=valid,
    BYROW(chk,LAMBDA(r,SUM(N(r*1))))
    )
  • arr：你的桌子的范围
  • colh：表格的列标题
  • rowh：表格的行标题
  • dat：表中不带标题的数据
  • 有效：主对角线的有效值（相当于上面的第一个公式）
  • chk：检查某一行中的值是否等于该列的有效值（相当于上面的第二个公式）
  • 那么最后一部分就是上面的第三个公式，给出输出

我想说的是，使用最近宣布的新功能 TAKE、DROP、CHOOSEROWS 和 CHOOSECOLS，colh、rowh 和 dat 中出现的 INDEX:INDEX 的许多无意义的事情将变得更加简单，如下所述这里。我目前无法使用这些功能，所以我使用了旧的方法。

答案2

一个简单的方法是计算单独一组列上的值。

然后，当没有匹配项时，将列值指定为零 (0)，当您想要使用公式对它们进行计数时，将列值指定为一 (1)。

之后，用另一列对它们进行总结。

为了使展示更加整洁，您可以隐藏这些列。

更复杂的方法是计算彩色单元格的数量。不幸的是，没有原生函数可以做到这一点，但有很多方法。然而，它们需要高级知识才能理解 VBA 函数等。

在这里，我找到了一篇文章，解释了一些方法在 Excel 中计算彩色单元格数量，或者如果那个不合适，你可以谷歌搜索其他选项。

相关内容