☰

Excel：当计数单元格范围高于另一个单元格范围时，将 SUMPRODUCT 与 SUBTOTAL 相结合

tag-icon • tag-icon microsoft-excel
Excel：当计数单元格范围高于另一个单元格范围时，将 SUMPRODUCT 与 SUBTOTAL 相结合

嗨，超级用户社区，

我目前正在使用 SUMPRODUCT 公式SUMPRODUCT(--(H101:H106<L101:L106))

此公式返回一个数字，显示 H101:H106 范围内有多少个单元格高于 L101:L106 中的等效单元格。

我想做的是将其与 SUBTOTAL 结合起来，这样该公式就只与我过滤的行进行比较。我做了一些搜索，看到了一些将 SUBTOTAL 公式与 OFFSET 结合起来的复杂解决方案，但我不太清楚如何使它与我想要实现的目标一起工作。

希望这是有意义的，并提前感谢您的任何帮助！

答案1

基本方法是，您需要创建一个与其他布尔值行数相同的数组，然后将它们相乘。

要创建判断其是否可见的数组 True/False，请执行以下操作：

SUBTOTAL(3,OFFSET(H101,ROW(H101:H106)-MIN(ROW(H101:H106)),0))>0

ROW(H101:H106)-MIN(ROW(H101:H106))返回一个数组，在本例中为{0;1;2;3;4;5}

然后将该数组传递给 OFFSET，并将范围数组传递给 SUBTOTAL。

我们使用3或COUNTA来判断行是否可见。这将返回一个 TRUE/FALSE 的垂直数组，TRUE如果行可见则返回，FALSE否则返回。

因此，结合您的公式和 SUBTOTAL：

=SUMPRODUCT((SUBTOTAL(3,OFFSET(H101,ROW(H101:H106)-MIN(ROW(H101:H106)),0))>0)*(H101:H106<L101:L106))

将获取行可见且 H 中的值小于 L 中的值的计数。

注意事项：我忘记了在旧版本中这是否需要 Ctrl-Shift-Enter。

相关内容