Excel的循環(huán)函數(shù) - 累加器的秘密：用數(shù)組作累加器

2022.12.02 四川

Excel中的MAP，REDUCE，SCAN，BYROW，BYCOL，MAKEARRAY等函數(shù)可以實現(xiàn)循環(huán)控制。其中SCAN/REDUCE函數(shù)比較獨(dú)特，在循環(huán)時攜帶了一個累加器。

這些函數(shù)實現(xiàn)的循環(huán)有一個缺陷，那就是循環(huán)不具有“記憶”功能，即在循環(huán)中不能訪問之前循環(huán)計算的結(jié)果，所以我們設(shè)計了一個技巧，利用SCAN/REDUCE函數(shù)的累加器實現(xiàn)記憶功能（請參見Excel函數(shù)式編程，離開這個技巧真不行：讓循環(huán)具有記憶功能）。

這個技巧中將累加器設(shè)計為文本類型，但是有些朋友對此有點(diǎn)不滿意，因為處理的數(shù)據(jù)有各種類型，用文本類型中間過渡，感覺有些名不正言不順。

本文介紹一個新的技巧，用數(shù)組作為累加器。這樣就可以支持各種數(shù)據(jù)類型了。

原理

這個技巧的原理如下圖：

左邊是要處理的數(shù)據(jù)，數(shù)據(jù)的處理用函數(shù)F來表示。這個過程可以寫成下面的偽公式：

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

這里累加器acc初值為空，

第一次循環(huán)，acc是只有一個元素F(x0)的數(shù)組，

第二次循環(huán)，acc是包含兩個元素F(x0)，F(xiàn)(x1)的數(shù)組，

......

這樣在每次循環(huán)中，都可以隨時訪問acc中的記錄，從而得到以前循環(huán)的結(jié)果。

注：上述過程只能用REDUCE函數(shù)，如果使用SCAN函數(shù)將會產(chǎn)生“數(shù)組的數(shù)組”問題，目前Excel還不支持。

例1 YTD銷量

假設(shè)銷量記錄如下圖：

要計算各月YTD銷量，我們就可以使用下面的公式（當(dāng)然這個例子完全不需要使用這個技巧，我們只是借此說明這個技巧的用法）：

// YTD銷量YTDSALES = LAMBDA(    values, //數(shù)量，列數(shù)組    LET(        arr, REDUCE(            ,   //累加器初值為空數(shù)組            values, //數(shù)量            LAMBDA(                acc,    //累加器變量                a,  //數(shù)組元素                VSTACK(                    acc,                     a + CHOOSEROWS(acc, -1) //累加上一條                )            )        ),        arr    ));

主體就是REDUCE函數(shù)，累加器初值為空數(shù)組，在每一次計算中，都將前一步的計算結(jié)果取出，將其與本次循環(huán)的數(shù)組元素相加，結(jié)果通過VSTACK函數(shù)添加到acc數(shù)組的后面，構(gòu)成新的acc數(shù)組。

例2 通用累計計算公式

上面的技巧在遇到多行多列數(shù)組時，就不適用了。如下圖：

對多行多列累加時，一般是先行后列的順序，如果累加時還需要同時“記憶”歷史結(jié)果數(shù)據(jù)，就不能直接使用上面的技巧了。

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

ACCRUAL = LAMBDA( values, //數(shù)量，多行列數(shù)組 LET( arr, REDUCE( , //初值為空數(shù)組 values, //掃描數(shù)量數(shù)組 LAMBDA( acc, //累加器變量 a, //數(shù)組元素 HSTACK( acc, a + CHOOSECOLS(acc, -1) //累加上一條 ) ) ), WRAPROWS(arr, COLUMNS(values)) ))

這里，我們將整個多行多列數(shù)組作為一個單行的數(shù)組對待，使用REDUCE函數(shù)完成累計。直到這一部分，還是使用例1中的技巧。只不過最后需要將結(jié)果累加器數(shù)組（此時是一單行數(shù)組）使用WRAPROWS函數(shù)轉(zhuǎn)換為多行多列的結(jié)果數(shù)組。

注：這個函數(shù)中我們沒有使用VSTACK函數(shù)，而是使用了HSTACK函數(shù)。因為VSTACK函數(shù)在轉(zhuǎn)換為多行多列數(shù)組時得不到我們想要的結(jié)果。

這個自定義函數(shù)是累計功能的通用版本，可以代替例1中的自定義函數(shù)。

詳細(xì)解釋請看視頻