![添加 SUMPRODUCT 公式的宏](https://linux22.com/image/1590585/%E6%B7%BB%E5%8A%A0%20SUMPRODUCT%20%E5%85%AC%E5%BC%8F%E7%9A%84%E5%AE%8F.png)
答案1
对您来说可能有用的是使用命名范围。该名称范围可以设置为覆盖数据的所有行,也可以是动态的。如果 A:A 列中没有空白单元格,则类似 =COUNTA(A:A) 的公式会告诉您使用了多少行(包括标题)。如果标题行之前有空白,则可以调整公式以在空白行(假设为 6)中进行硬编码,方法是将公式调整为:
=COUNTA(A:A)+6
or
=COUNTA(A:A)+ROW(A7)
A7 将是标题行的第一行,并将说明其上方的所有行都是空白。因此,您的命名范围公式可能是:
=$I$1:INDEX(I:I,COUNTA(A:A))
因此,为了定义要查找 SUMPRODUCT 公式的范围,您可以使用另一个数组公式。现在这会在一定程度上拖慢您的系统,因为它将是一个重复多次的数组操作。完成后,我会得到大致的计算次数。假设您为第 I 列中的应用名称执行此操作,名称范围为 APP_LIST。
首先,您要找到数据带的起始行。这可以通过聚合函数来完成。聚合函数由许多不同的函数组成。您将对 14 和 15 感兴趣。它们将根据您使用的顺序对数组计算的结果进行升序或降序排序。然后,您还可以告诉聚合忽略错误,为其提供通过数学运算考虑的标准,然后确定您想要在排序结果中获得的哪个位置。AGGREGATE 函数可能如下所示:
AGGREGATE(15,6,ROW(APP_LIST)/(APP_LIST=I2),1)
上面的代码会告诉你你感兴趣的行,但你需要一个地址。为了获取地址,你需要将 AGGREGATE 函数嵌入到 INDEX 函数中。你可以使用 INDIRECT,但它是一个易失性函数,在使用工作表时会导致不必要的计算。因此,你范围内第一个单元格的地址将是:
=INDEX(D:D,AGGREGATE(15,6,ROW(APP_LIST)/(APP_LIST=I2),1))
要获取范围内的最后一行,其基本上是相同的公式,但您希望 AGGREGATE 以相反的方向排序。
=INDEX(D:D,AGGREGATE(14,6,ROW(APP_LIST)/(APP_LIST=I2),1))
现在,由于您需要对 D 列和 E 列进行此操作,因此您需要重复此过程
=INDEX(E:E,AGGREGATE(15,6,ROW(APP_LIST)/(APP_LIST=I2),1))
=INDEX(E:E,AGGREGATE(14,6,ROW(APP_LIST)/(APP_LIST=I2),1))
因此 D 列的范围变为:
INDEX(D:D,AGGREGATE(15,6,ROW(APP_LIST)/(APP_LIST=I2),1)):INDEX(D:D,AGGREGATE(14,6,ROW(APP_LIST)/(APP_LIST=I2),1))
E 列的范围变为:
INDEX(E:E,AGGREGATE(14,6,ROW(APP_LIST)/(APP_LIST=I2),1)):INDEX(E:E,AGGREGATE(14,6,ROW(APP_LIST)/(APP_LIST=I2),1))
现在你只需要把它转储到你的 SUMPRODUCT 中
=SUMPRODUCT(--(INDEX(D:D,AGGREGATE(15,6,ROW(APP_LIST)/(APP_LIST=I2),1)):INDEX(D:D,AGGREGATE(14,6,ROW(APP_LIST)/(APP_LIST=I2),1))<=D2),--(INDEX(E:E,AGGREGATE(15,6,ROW(APP_LIST)/(APP_LIST=I2),1)):INDEX(E:E,AGGREGATE(14,6,ROW(APP_LIST)/(APP_LIST=I2),1))>=D2))
将以上内容放在 J2 中并复制下来
现在我并不建议采用这种方式,因为计算量太大。使用 AGGREGATE,每次聚合调用都会产生 60K 行数据。在 sumproduct 中,AGGREGATE 被调用了 4 次。也就是说,仅在一个单元格中就有 240K 次计算。现在您还要将其复制下来 60K 行,这意味着仅从 AGGREGATE 中就有 60K * 240K = 1440 万次计算。您还需要处理 SUMPRODUCT 计算。请做好准备,计算可能需要一些时间才能完成。