☰

快速数组公式，用于对一列中连续相同值的另一列进行总计

tag-icon • tag-icon microsoft-excel worksheet-function
快速数组公式，用于对一列中连续相同值的另一列进行总计

这是Excel 数组公式用于计算连续相同的值。这里问题稍微复杂一些。

我有一列（列E）包含整数值，另一列（F）包含任意值。我想在列 中计算 的G累计总数F，但仅限于 的当前值为常数的连续行段F。使用公式可能更容易理解描述：

B2 =100                          # Length of sequence
B3 =3                            # Possible values in column E
D2 =SEQUENCE(B2)                 # Auxiliary variable
E2 =RANDARRAY(B2,,1,B3,TRUE)     # Sequence of integers between 1 and B3
F2 =RANDARRAY(B2)                # Sequence of values to be summed

G2 =IF(OR(D2=1,E2<>E1),0,G1)+F2  # Desired output, non-array formula version.
                                 # If the current value in E is the same 
                                 # as in the previous row, sum to the running total of F,
                                 # else start again with the current value of F.
                                 # Formula needs to be copied along the other columns.

正如链接的问题一样，我事先不知道序列有多长，我想使用数组公式来自动调整输出的长度，而不是在列G2中重复公式G。

以下公式有效：

H2 =LET(s,E2#,v,F2#,SCAN(0,D2#,LAMBDA(a,x,IF(OR(x=1,INDEX(s,x)<>INDEX(s,x-1)),0,a)+INDEX(v,x))))

但比上面的公式慢得多G2：当序列长度为数千时，它会变慢，而G2公式可以轻松处理数十万个值。

如何才能使数组公式与非数组公式一样快？

（顺便问一下，为什么数组公式这么慢？它里面的所有操作都应该非常快。）

PS 在链接的问题中，用户 Scott Craner 发现了一个巧妙的公式，使用BYROW而不是，但它依赖于列仅包含两个可能的不同值的SCAN事实。E

编辑

我终于找到了一个数组公式几乎与非数组版本一样快（即慢约 3 倍）：

H2 =LET(
        sect, E2#,
        values, F2#,
        n, ROWS(sect),
        s, SEQUENCE(n),
        sentinel_val, -1,
        same_as_next, FILTER(sect, s > 1) = FILTER(sect, s < n),
        adj_values, 
            CHOOSE(
                1 + (s > 1), 
                sentinel_val, 
                INDEX(
                    CHOOSE(same_as_next + 1, sentinel_val, values), 
                    s - 1)
                ),
        prev_sum, 
            SCAN(
                0, 
                adj_values, 
                LAMBDA(a,x, IF(x < 0, 0, a + x))
            ),
        prev_sum + values
   )

答案1

基于我的其他公式，我们查找不是该行的所有数字，然后从其起始位置到当前行对 F 求和。

=BYROW(E2#,LAMBDA(a,LET(b,ROW(a),s,E2#,f,F2#,m,MAX(IFERROR(XMATCH(FILTER(SEQUENCE(B3),SEQUENCE(B3)<>a),INDEX(s,1):INDEX(s,b-1),0,-1),0)+1),SUM(INDEX(f,m):INDEX(f,b-1)))))

再次强调，我不依赖 D，因此如果需要的话可以省略它。

随着 B3 的增加，所需时间也会增加。但当 B3 = 3 时，完成 100,000 并不长

在此处输入图片描述

数组公式很慢，我们可以使用 XMATCH 之类的东西来缓解其中的一些问题，并缩小我们实际执行函数的范围。但对于大型数据集，它们永远无法击败简单的非数组公式。

因此，另一个选择是仅使用包含另一个 IF 的简单公式：

=IF(F2<>"",IF(OR(D2=1,E2<>E1),0,G1)+F2,"")

并将其复制到整个列中。它看起来就像是动态的，没有计算成本。事实上，如果 F2 为空，它不会执行方程的其余部分，而只会返回""。没有什么比这更节省时间和更简单了。

在此处输入图片描述

答案2

我可能漏掉了一些东西，但不确定这里是否需要迭代LAMBDA方法SCAN。你能不能简单地使用：

=SUMIFS(F2#,D2#,"<="&D2#,D2#,">"&MAXIFS(D2#,D2#,"<"&D2#,E2#,"<>"&E2#),E2#,E2#)

例如？

相关内容