excel - 计算一个范围内至少有一个数字的列

Question

我有一个大表按行和列分为类别。每个类别中的行/列数是不规则的。
在下面的示例中，行被划分为颜色，列被划分为组(a，b):



在由行\列类别组合创建的每个矩形中，我需要计算至少包含一个数字的列数。
答案应该是:



我当然缺少的是放在上面 M 列中的公式。
我从这个开始:

=SUMPRODUCT(--($B$2:$H$9<>0)*--($A$2:$A$9=$L2)*--($B$1:$H$1=$K2))  

这会计算每个矩形中包含数字的所有单元格，但我仍然需要将其分成列，然后不计算单元格，但检查是否 任意
列中的单元格包含一个数字，然后计算那些列。

如果我添加另一个表来聚合这一部分，然后从聚合表中计数，这当然会更容易。我的问题是这是否可以通过一种直接计算来完成？ (为什么？清洁、效率、优雅……)

感谢您帮助解决这个问题。

Answer 1

你可以用这样的数组公式来做到这一点。它使用 MMULT 查找满足各种条件的单元格的列总数，然后使用 SUM 查找这些总数中有多少大于零。

=SUM(--(MMULT(TRANSPOSE(ROW($B$2:$H$9))^0,N(($B$2:$H$9>0)*($B$1:$H$1=$A11)*($A$2:$A$9=$B11)))>0))

必须使用 CtrlShiftEnter 输入

经过反射(reflection)，您可以将其稍微简化为

=SUM(--(MMULT(TRANSPOSE(ROW($B$2:$H$9))^0,N($B$2:$H$9*($B$1:$H$1=$A11)*($A$2:$A$9=$B11)))>0))

而且您也不需要 N ，因为 MMult 第二部分的乘法结果已经是一组数字

=SUM(--(MMULT(TRANSPOSE(ROW($B$2:$H$9))^0,$B$2:$H$9*($B$1:$H$1=$A11)*($A$2:$A$9=$B11))>0))