Excel的循环函数 - 累加器的秘密：用数组作累加器

hercules028 2022-12-02 发布于四川

展开全文

Excel中的MAP，REDUCE，SCAN，BYROW，BYCOL，MAKEARRAY等函数可以实现循环控制。其中SCAN/REDUCE函数比较独特，在循环时携带了一个累加器。

这些函数实现的循环有一个缺陷，那就是循环不具有“记忆”功能，即在循环中不能访问之前循环计算的结果，所以我们设计了一个技巧，利用SCAN/REDUCE函数的累加器实现记忆功能（请参见Excel函数式编程，离开这个技巧真不行：让循环具有记忆功能）。

这个技巧中将累加器设计为文本类型，但是有些朋友对此有点不满意，因为处理的数据有各种类型，用文本类型中间过渡，感觉有些名不正言不顺。

本文介绍一个新的技巧，用数组作为累加器。这样就可以支持各种数据类型了。

原理

这个技巧的原理如下图：

左边是要处理的数据，数据的处理用函数F来表示。这个过程可以写成下面的伪公式：

=REDUCE(, arr, LAMBDA(acc, a, F(acc,a))

这里累加器acc初值为空，

第一次循环，acc是只有一个元素F(x0)的数组，

第二次循环，acc是包含两个元素F(x0)，F(x1)的数组，

......

这样在每次循环中，都可以随时访问acc中的记录，从而得到以前循环的结果。

注：上述过程只能用REDUCE函数，如果使用SCAN函数将会产生“数组的数组”问题，目前Excel还不支持。

例1 YTD销量

假设销量记录如下图：

要计算各月YTD销量，我们就可以使用下面的公式（当然这个例子完全不需要使用这个技巧，我们只是借此说明这个技巧的用法）：

// YTD销量YTDSALES = LAMBDA(    values, //数量，列数组    LET(        arr, REDUCE(            ,   //累加器初值为空数组            values, //数量            LAMBDA(                acc,    //累加器变量                a,  //数组元素                VSTACK(                    acc,                     a + CHOOSEROWS(acc, -1) //累加上一条                )            )        ),        arr    ));

主体就是REDUCE函数，累加器初值为空数组，在每一次计算中，都将前一步的计算结果取出，将其与本次循环的数组元素相加，结果通过VSTACK函数添加到acc数组的后面，构成新的acc数组。

例2 通用累计计算公式

上面的技巧在遇到多行多列数组时，就不适用了。如下图：

对多行多列累加时，一般是先行后列的顺序，如果累加时还需要同时“记忆”历史结果数据，就不能直接使用上面的技巧了。

我们可以使用下面的公式：

ACCRUAL = LAMBDA( values, //数量，多行列数组 LET( arr, REDUCE( , //初值为空数组 values, //扫描数量数组 LAMBDA( acc, //累加器变量 a, //数组元素 HSTACK( acc, a + CHOOSECOLS(acc, -1) //累加上一条 ) ) ), WRAPROWS(arr, COLUMNS(values)) ))

这里，我们将整个多行多列数组作为一个单行的数组对待，使用REDUCE函数完成累计。直到这一部分，还是使用例1中的技巧。只不过最后需要将结果累加器数组（此时是一单行数组）使用WRAPROWS函数转换为多行多列的结果数组。

注：这个函数中我们没有使用VSTACK函数，而是使用了HSTACK函数。因为VSTACK函数在转换为多行多列数组时得不到我们想要的结果。

这个自定义函数是累计功能的通用版本，可以代替例1中的自定义函数。

详细解释请看视频