问题描述
我有一个固定数量的列和动态行的Google表格.
I have a Google sheet with fixed number of columns and dynamic rows.
我喜欢使用countA对当前行中具有非空白值的字段进行计数.
I like to use countA to count fields with a value (non-blank) in the current row.
我在这里找到了公式,但不理解,两者都无法正常工作.
I found a formula here but don't understand it, neither can get it to work.
ArrayFormula(MMULT( LEN(A1:E)>0 ; TRANSPOSE(SIGN(COLUMN(A1:E1)))))
Sheet给我一个错误:函数MMULT参数1需要数字值.但是'TRUE'是布尔值,不能强制为数字."
Sheet gives me error: "Function MMULT parameter 1 expects number values. But 'TRUE' is a boolean and cannot be coerced to a number."
推荐答案
如果将LEN(A1:E)> 0返回的布尔值(真或假)转换为数字(1或0),则该公式应该起作用巴里已经提到过.通过将LEN()函数的输出包装在N函数或在其前面加上-".因此,假设您的数据从第2行开始,请查看是否可行:
The formula should work if you convert the booleans (true or false) returned by LEN(A1:E)>0 to numbers (1 or 0), as Barry already mentioned. This can be done quite easily by wrapping the output of the LEN()-function in an N-function or by preceding it with '--'. So, assuming your data starts in row 2, see if this works:
=ArrayFormula(MMULT( --(LEN(A2:E)>0) , TRANSPOSE(COLUMN(A2:E2)^0)))
另一种方法是使用COUNTIF()
An alternative way would be to use COUNTIF()
=ArrayFormula(COUNTIF(IF(A2:E<>"", row(A2:A),),row(A2:A)))
甚至可能也可以使用:
=ArrayFormula(MMULT( --(A2:E<>"") , TRANSPOSE(COLUMN(A2:E1)^0)))
如果您还想包含标题行,请尝试:
If you also want to include a header row, try:
=ArrayFormula(if(row(A:A)=1, "Header", MMULT( --(LEN(A:E)>0) , TRANSPOSE(COLUMN(A1:E1)^0))))
或
=ArrayFormula(if(row(A:A)=1, "Header", MMULT( --(A:E<>"") , TRANSPOSE(COLUMN(A1:E1)^0))))
或
=ArrayFormula(if(row(A:A)=1, "Header", COUNTIF(IF(not(isblank(A:E)), row(A:A),),row(A:A))))
(在评论中的新问题之后)
(after new question in comments)
如果您想对值求和,也可以使用MMULT()来完成:
If you want to sum the values, you can do that with MMULT() too:
=ArrayFormula(if(row(A:A)=1, "Header", MMULT(if(A1:E<>"", A1:E,0), transpose(column(A1:E1)^0))))
或使用sumif:
=ArrayFormula(if(row(A:A)=1, "Header", sumif(IF(COLUMN(A1:E1),ROW(A1:A)),ROW(A1:A),A1:E)))
注意:如果您想将输出限制为例如在col A中具有值的最后一行,请尝试:
NOTE: if you want to limit the output to let's say the last row that has values in col A, try:
=ArrayFormula(if(row(A:A)=1, "Header", IF(LEN(A1:A), MMULT(if(A1:E<>"", A1:E,0), transpose(column(A1:E1)^0)),)))
或再次使用sumif()
or, again with sumif()
=ArrayFormula(if(row(A:A)=1, "Header", if(len(A1:A), sumif(IF(COLUMN(A1:E1),ROW(A1:A)),ROW(A1:A),A1:E),)))
这篇关于数组公式当前行中的COUNTA(Google表格)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!