Excel函數(shù)式編程，離開這個(gè)技巧真不行：讓循環(huán)具有記憶功能

2022.11.07 北京

本篇是Excel函數(shù)式編程系列的一部分

之前我們詳細(xì)介紹過Excel中的函數(shù)循環(huán)（詳情參見這里）。在Excel中，我們通過函數(shù)：MAP/REDUCE/SCAN/BYROW/BYCOL/MAKEARRAY 實(shí)現(xiàn)循環(huán)。

我們可以將上述函數(shù)直接實(shí)現(xiàn)的循環(huán)成為“基本循環(huán)”。

“基本循環(huán)”有一個(gè)非常大的缺陷：缺少記憶功能 —— 即記不住之前的循環(huán)中計(jì)算的結(jié)果。

例如，我們通過MAP實(shí)現(xiàn)了一個(gè)循環(huán)，

=MAP(arr, LAMBDA(a, a + 1)

如下圖：

我們將左側(cè)的數(shù)組映射成右側(cè)的數(shù)組。當(dāng)前循環(huán)到了原數(shù)組元素a，通過MAP映射為結(jié)果數(shù)組中的元素a+1。但是，無論用什么方法，我們無從得知循環(huán)中的上一步生成的結(jié)果數(shù)組元素b+1。

而在SCAN循環(huán)中，

=SCAN(0, arr, LAMBDA(acc, a, acc + a)

我們還是只能知道當(dāng)前循環(huán)生成的結(jié)果數(shù)組元素acc+a，但是無從得知之前循環(huán)步驟中已經(jīng)生成的那些元素值。

同樣，在MAKEARRAY的循環(huán)中，

=MAKEARRAY(10, 10,    LAMBDA(r, c,         INDEX(arr, r, c)    ))

我們可以知道之前循環(huán)生成的所有元素的索引號(hào)，但是無法使用這個(gè)索引號(hào)獲得結(jié)果數(shù)組中已經(jīng)生成的元素值。

是否有必要使用帶“記憶”功能的循環(huán)？

有很多場景需要使用帶記憶功能的循環(huán)。這種循環(huán)并不依賴于一個(gè)實(shí)現(xiàn)給定的數(shù)組，而是在循環(huán)中每一次的結(jié)果都依賴于之前的循環(huán)結(jié)果。

假設(shè)循環(huán)處理邏輯用自定義函數(shù)f來表示，那么，

只要給定初值x0，

就可以通過f一直循環(huán)下去：

x1 = f(x0)

x2 = f(x1)

......

xn=f(xn-1)

這種循環(huán)就是典型的依賴于歷史數(shù)據(jù)的例子。

能否通過變量或中間表記住歷史結(jié)果？

能否在循環(huán)前設(shè)中間變量或中間表記住歷史結(jié)果？例如，

=LET(  matrix, a1:d20,  MAKEARRAY(20, 4,     LAMBDA(r, c,       根據(jù)r, c修改matrix中的值     )  )

在上面的“偽公式”中，我們先定義了一個(gè)變量，表示矩陣，并且將其對(duì)應(yīng)于單元格A1:D20區(qū)域，然后在MAKEARRAY循環(huán)中，計(jì)算出本次循環(huán)的值并記錄在MATRIX中。這樣我們需要用到歷史值的時(shí)候就可以直接訪問matrix中的對(duì)應(yīng)元素?。?！

這是行不通的！??！

在函數(shù)式編程中，一個(gè)重要的原則就是每個(gè)函數(shù)都只產(chǎn)生結(jié)果，而不會(huì)改變源數(shù)據(jù)！

所以，我們在Excel函數(shù)式編程中所定義的所有變量，都是“只讀”的。

解決方案

既然此路不通，我們還是將注意力回到Excel提供的這幾個(gè)“循環(huán)”函數(shù)。

在這些函數(shù)中，有兩個(gè)函數(shù)與眾不同：SCAN/REDUCE。

這兩個(gè)函數(shù)在循環(huán)給定數(shù)組之余，同時(shí)維護(hù)一個(gè)內(nèi)部的累加器。仔細(xì)考察，這個(gè)累加器其實(shí)是攜帶了歷史結(jié)果信息的。（具體參見Excel函數(shù)也可以循環(huán)之累加器函數(shù)：SCAN）

這里的累加器并不限于加法，實(shí)際上也可以通過Excel的函數(shù)進(jìn)行各種運(yùn)算，這些運(yùn)算可以根據(jù)數(shù)據(jù)類型分為數(shù)值運(yùn)算，邏輯值運(yùn)算和文本運(yùn)算。

假設(shè)我們要通過SCAN/REDUCE函數(shù)循環(huán)處理以下數(shù)組或區(qū)域：

如果累加器是數(shù)值，那么每一步的累加器結(jié)果應(yīng)該是：

顯然，我們無法從最后的合計(jì)結(jié)果中精確還原之前每一步累加器的結(jié)果。所以，這個(gè)方向?qū)ξ覀儧]有幫助。

如果累加器是邏輯值，那么每一步的累加器結(jié)果應(yīng)該是：

最后的結(jié)果是TRUE/FALSE，我們更加不能從這樣的值中分解出過去每一步的歷史結(jié)果。所以，這個(gè)方向也不成立。

最后我們看累加器類型是文本的情況：

對(duì)于文本來說，我們的常規(guī)運(yùn)算是字符串連接，可以通過&，或者TEXTJOIN函數(shù)完成。很明顯，如果采用合適的分隔符，我們是可以從最后的結(jié)果中，分解出每一步的歷史結(jié)果。

所以，采用SCAN/REDUCE函數(shù)，并且借助文本類型的累加器，就可以實(shí)現(xiàn)帶有”記憶“功能的循環(huán)。

之前提到了的依賴歷史數(shù)據(jù)的循環(huán)，其實(shí)現(xiàn)方式大約就是類似這樣的情形：

累加器變量acc，初值為""，數(shù)組元素x0，x1，x2，......，xn。循環(huán)中的處理函數(shù)是f

第一步：源數(shù)據(jù)為x0，這是初值, 將處理結(jié)果x0與acc連接成一個(gè)字符串（"x0")

第二步：此時(shí)，需要處理得到x1，邏輯是x1=f(x0)，此時(shí)，acc的值為"x0"，所以直接取出參與計(jì)算，可得到x1，然后將x1添加到acc的后面，得到新的acc，其值為"x0,x1"

第三步：此時(shí)，計(jì)算x2需要用到x1，從acc中取出最后一個(gè)逗號(hào)后面的元素，即上一步的計(jì)算結(jié)果x1，然后計(jì)算x2=f(x1)，并將計(jì)算結(jié)果x2添加到acc的后面，得到新的acc，其值為"x0,x1,x2"。

這樣一直到最后得到：

"x0,x1,x2,......,xn"

最后拆分acc，即可得到中間所有的計(jì)算結(jié)果。

也不一定每次都需要記錄所有的歷史結(jié)果。在下面的例子中，每一步計(jì)算只跟前一步相關(guān)，因此只要記錄前一步的結(jié)果即可。

實(shí)例：混沌圖像

這種每一步都依賴于之前計(jì)算結(jié)果的循環(huán)，最典型的就是迭代。

在很多年前，流行用這種迭代函數(shù)做出各種奇特的圖像，叫做混沌。比如：

上面這幅圖就是通過這兩個(gè)公式迭代得到的圖像。其中a, b, c, d 都是常數(shù)。

為了簡化后面的操作，我們將這個(gè)計(jì)算公式定義為自定義函數(shù)：

后面每次循環(huán)中的處理都調(diào)用函數(shù)FUNC。

然后通過下面的自定義函數(shù)實(shí)現(xiàn)這個(gè)迭代過程：

這個(gè)函數(shù)第一個(gè)參數(shù)n表示循環(huán)的步驟數(shù)，即最終生成的點(diǎn)數(shù)。x0，y0代表初值。

黃色加亮的部分代表循環(huán)，其中，

每一次循環(huán)都將本次結(jié)果記錄在累加器acc中（TEXTJOIN(",",1,p)。而本次結(jié)果p的計(jì)算是通過FUNC函數(shù)對(duì)x_prev和y_prev進(jìn)行計(jì)算。而x_prev和y_prev是從上一次計(jì)算的結(jié)果中的得來，這個(gè)結(jié)果記錄在上一步的acc中。

藍(lán)色加亮部分處理SCAN函數(shù)得到的結(jié)果，即每一步的acc，最后得到我們想要的結(jié)果：

這個(gè)結(jié)果是通過下面公式得到的：