在数组论坛中使用CountIFs [英] Using CountIFs in an Array Forumla
问题描述
我试图计算给定类型的东西的次数,我需要这种行为来自动扩展到插入的行。例如:
= Arrayformula(COUNTIFS(I:I,I:I,H:H,H:H,G: G,G:G))
嵌套的countif公式在使用时会产生正确的值
我的数据类似于:
Column1 Column2 Column3结果
---------------------------------- ----------
苹果绿吃x
橙色橙色noteaten x
苹果红吃x
橙色橙色noteaten x
苹果绿吃x
...
x列是arrayformula输出的位置。
第1行的X应该查看所有数据并计算绿色苹果的数量 eaten
,下一个行会计数 noneaten
橙色橘子等等。我知道arrayformula没有使用集合函数,但是我没有发现任何关于 countif
的替代方法。
遗憾的是,在Google表格中,COUNTIFS无法在数组上迭代,例如COUNTIF可以(无论如何,在写这篇文章时)。
您需要使用MMULT,例如:
$ b = ArrayFormula(IF(ROW(G:G)= 1 , 结果,MMULT((G:G = TRANSPOSE(G:G))*(H:H = TRANSPOSE(H:H))*(I:I = TRANSPOSE(I:I)),SIGN(ROW( G:G)))))
但请注意,表格中似乎存在一个限制,其中G:G = TRANSPOSE(G:G)等不能超过1000万个元素。这对应于最多3162行。
另一种选择是使用字符串连接:
= 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)是一个制表符,但它可以是您确定不会出现在您的数据中的任何字符。
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))
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.
My data resembles:
Column1 Column2 Column3 Result
--------------------------------------------
apple green eaten x
orange orange noteaten x
apple red eaten x
orange orange noteaten x
apple green eaten x
...
The x column is where the arrayformula would output.
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
.
Unfortunately, in Google Sheets, COUNTIFS can not be iterated over an array, as eg COUNTIF can (at the time of writing this, anyway).
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)))))
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))
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屋!