在数组公式中使用 COUNTIFS [英] Using COUNTIFS in an array formula

问题描述

我正在尝试计算给定类型发生的次数，我需要此行为自动扩展到插入的行.类似的东西:

I'm trying to count the number of times something of a given type occurs and I need this behaviour to automatically expand to inserted rows. Something like:

=Arrayformula(COUNTIFS(I:I,I:I,H:H,H:H,G:G,G:G))

在单行上使用时，嵌套的 countif 公式将产生正确的值，但当前数组公式一直向下输出 1.

The nested countif formula will result in a correct value when used on a single row but currently the array formula is outputting 1 all the way down.

我的数据类似于:

    Column1   Column2    Column3   Result
--------------------------------------------
   apple     green      eaten       x

   orange    orange     noteaten    x

   apple     red        eaten       x

   orange    orange     noteaten    x

   apple     green      eaten       x

...

x 列是 arrayformula 将输出的位置.

The x column is where the arrayformula would output.

第 1 行的 X 应该查看所有数据并计算吃掉的青苹果的数量，下一行计算未吃橙色的橙子，依此类推.我知道 arrayformula 不采用聚合函数，但我没有找到任何关于 countif 的替代方法.

X on Row 1 should look through all the data and count up the number of green apples eaten, the next row would count noneaten orange oranges, and so on. I know that arrayformula doesn't take aggregate functions but I didn't find anything on alternatives to countif.

推荐答案

不幸的是，在 Google Sheets 中，COUNTIFS 不能迭代数组，例如 COUNTIF 可以(无论如何).

Unfortunately, in Google Sheets, COUNTIFS can not be iterated over an array, as eg COUNTIF can (at the time of writing this, anyway).

您需要求助于 MMULT，例如:

You would need to resort to MMULT, something like:

=ArrayFormula(IF(ROW(G:G)=1,"Result",MMULT((G:G=TRANSPOSE(G:G))*(H:H=TRANSPOSE(H:H))*(I:I=TRANSPOSE(I:I)),SIGN(ROW(G:G)))))

但请注意，Sheets 中似乎存在限制，即由 G:G=TRANSPOSE(G:G) 等形成的二维数组不能超过 1000 万个元素.这对应于最多 3162 行.

but be aware there appears to be a limitation in Sheets whereby the 2D array formed by G:G=TRANSPOSE(G:G) etc cannot exceed 10 million elements. This corresponds to a maximum of 3162 rows.

另一种选择是使用字符串连接:

Another option is to use concatenation of strings:

=ArrayFormula(COUNTIF(G:G&CHAR(9)&H:H&CHAR(9)&I:I,G:G&CHAR(9)&H:H&CHAR)(9)&I:I))

绕过3162"限制.CHAR(9) 是制表符，但它可以是您确定不会出现在数据中的任何字符.

which gets around the "3162" limitation. CHAR(9) is a tab character, but it could be any character that you are certain will not appear in your data.

这篇关于在数组公式中使用 COUNTIFS的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！