每个月末，我必须计算上个月每天一组应用程序的并发使用情况。
利用其他帖子中分享的想法，我创建了一个公式，可以有效地计算每个应用程序的并发会话。
我遇到的问题是，每个月可能有 60,000+ 条会话线和 20+ 个不同的应用程序。要计算每个应用程序的并发使用量，我必须手动找到每个应用程序使用的单元格范围并调整公式，这非常繁琐！
我想知道是否可以创建一个宏，（一旦我将会话分类为应用程序和开始日期）是否可以针对应用程序名称的每次更改计算该应用程序的总体范围并输入公式？
SUMPRODUCT 公式如下：
=SUMPRODUCT(--($D$4:$D$7<=D6),--($E$4:$E$7>=D6))
答案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 计算。请做好准备，计算可能需要一些时间才能完成。