文 前 推 薦
編按：
本篇詳細介紹Excel中LAMBDA的遞迴運算，包括遞迴的含義和理解、遞迴公式組成、遞迴要點、遞迴條件、遞迴的經典套用。本篇中遞迴運算，沒有借助LAMBDA的伴侶函式。是一篇小白都能看懂的LAMBDA遞迴教程，非常適合沒有編程基礎的小白掌握遞迴運算。
在前面的文章【新函式LAMBDA的用法（上）——不用VBA做自訂函式】中，小窩為大家介紹了LAMBDA函式的基本用法以及第一個優點——不用VBA實作可重復使用的自訂函式。
今天則向大家介紹LAMBDA的第二個優點，遞迴運算。
1.何為遞迴？
遞迴來自數學與電腦編程，指的是函式在執行中重復呼叫自身。
很抽象！下面用一個簡單的數位累加來說明。
例：求任意兩個給定的整數之間的遞增數列和，如2與6之間的數列2、3、4、5、6的和。
如果已經有2、3、4、5、6數列，用SUM函式可以直接求和。
但現在只有起始數2和截止數6，如何求它們之間的數列和呢？
忘掉序列函式SEQUENCE。我們用數學的思路來看，以2開始，數列中下一個數總等於上一個數加1,直到等於截止數6為止：
假如用F(2,6)表示數列2到6的和，則運算過程可以模擬如下：
圖中黃色的部份，屬於迴圈部份。初始值2，呼叫函式後生成新值3；新值3作為新的初始值，呼叫函式後生成新值4……直到生成值等於截止數。當「遞數」過程——可以是遞增也可以是遞減，此處是遞增——結束後，就把所有數「歸攏」——代入算式——得出結果。
這就是遞迴：先迴圈生成要進行計算的各個變量值，然後再來計算。
遞有兩層意思：
第一，透過遞增或者遞減的方式生成新的數。
可以是值的遞增或者遞減，如X+1、X+2、X-1、MOD(X,10)、X/10等；
也可以是行數的遞增或者遞減得到新的參照，如OFFSET(X,1,)、OFFSET(X,-1,)等；
也可以是字串的位數的遞增遞減，如len(x)-1，len(x)+1)等。
第二，傳遞，把生成的數暫存並傳遞到下一次迴圈中。
歸，把所有迴圈生成的數從暫存的棧中取出代入算式進行運算得到結果。
設定起始數為變量X，截止數為Y，下一個數為X+1,直到X=Y為止，我們自訂函式TWONUMB完成遞迴運算：
=LAMBDA(X,Y,IF(X=Y,X,X+TWONUMB(X+1,Y)))
公式中的X+TWONUMB(X+1,Y)部份對應前方圖中黃色部份。函式執行時，總是判斷X是否符合終止條件，不符合，就重復呼叫TWONUMB函式，由TWONUMB(X+1,Y)生成新的X值。
在B5中使用剛定義的TWONUM函式，即可求任意兩個整數之間的序列和。
2. Exel中遞迴函陣列成和必備條件
1）遞迴函陣列成
初步了解什麽是遞迴後，再來了解在Excel中遞迴函式的組成部份。典型的遞迴函式包括5部份：
變量：就是函式參數，數量1~253之間。
終止條件：停止迴圈的條件。
終止值：當迴圈停止時，變量等於多少。
運算式：包含所有變量在內的運算式。通常有兩個部份，變量迴圈和變量套用。其中變量迴圈式必須有。
變量迴圈：包含函式名稱、變量表達，按公式前方的變量順序交代所有變量的生成方式。
變量迴圈要點：
（1）函式名稱必須與定義框中名稱一致；
（2）有多少個變量就必須有多少個變量表達，其順序與變量一致。
變量表達解讀（以XY兩個變量為例）：
可以是計算式，如TWONUMB(X+1,Y)中的X+1，表示每次迴圈X變量都會加1；
可以是變量自身，如TWONUMB(X+1,Y)中的Y，表示每次迴圈Y變量保持初始值不變；
可以是一個常數，如TWONUMB(5,Y-1)中的5，表示除開初始值外，每次迴圈X變量都等於5；
可以參照另一個變量，如TWONUMB(Y,Y-1)中的Y，表示除初始值外，每次迴圈X都參照新的Y變量；
可以是多個變量的計算式，如TWONUMB(X+Y-1,Y)中的X+Y-1,表示每次迴圈X都等於X+Y-1。
變量套用：交代迴圈生成的各個變量怎麽計算——不是每個遞迴函式都必須有。
譬如前方TWONUMB函式，如果刪除變量套用「X+」，則返回的就是迴圈終止時的X值而不是和。
=LAMBDA(X,Y,IF(X=Y,X,TWONUMB(X+1,Y)))
變量套用包括變量參照和結果符號兩部份。
變量參照是一個運算式，確定迴圈生成的變量是否使用、怎麽使用。譬如當前參照就一個「X」，意思是不計算Y變量，只原樣參照每個X值。小白要註意：不是所有變量都會參與結果值的運算！有些變量只是用來作為終止條件的，並不參加值的運算。這種不參與值運算的變量，就相當於你跳繩的時候站在傍邊幫你計數喊停的人！
結果符號明確每個迴圈變量代入參照後的結果怎麽歸攏。譬如當前符號是「+」，就表示各變量代入參照後的結果需要累加。
最初接觸遞迴的小白可能與小窩一樣會在此處犯錯：以為加，就表示直接把得到的迴圈變量相加。
譬如，求下方單元格中各封包含的數位和，如數據「78」，其數位和就是15。
此處至少有兩種遞迴求解方法。其中一種是透過余數來累加，自訂函式caishuhe:
=LAMBDA(X,IF(X=0,0,MOD(X,10)+CAISHUHE(TRUNC(X/10))))
小窩剛學LAMBDA遞迴時，認為這裏的遞迴迴圈是這樣的：
第1次迴圈：CAISHUHE(TRUNC(45618/10))= 4561
第2次迴圈：CAISHUHE(TRUNC(4561/10)=456
第3次迴圈：CAISHUHE(TRUNC(456/10)=45
第4次迴圈：CAISHUHE(TRUNC(45/10)=4
第5次迴圈：CAISHUHE(TRUNC(4/10)=0
結果= MOD(45618,10)+4561+456+45+4+0=5074
這可是大大的錯了！
並非直接加上各迴圈變量，而要把各迴圈變量代入變量參照中運算後再相加。
結果=MOD(45618,10)+ MOD(4561,10) + MOD(456,10) + MOD(45,10) + MOD(4,10) + 0=24
2) 遞迴運算必備條件
遞迴運算必須具備三個條件。
第一：存在終止條件。
迴圈必須有限，要設定終止條件讓其能停止。LAMBDA自訂遞迴函式時，使用IF函式表達終止條件。
實際上在Excel中，不但要有終止條件，還得滿足迴圈次數不大於5459。（下方會帶大家來測試這點）
第二：具體的變量迴圈方式。
在「遞」的過程中需要迴圈生成變量值。怎麽生成？就必須在變量迴圈部份交代。
前方求兩個數之間的序列和的TWONUMB函式，是基於初始值2，采用X+1的方式不斷生成新的初始值，而截止值Y保持不變。
=LAMBDA(X,Y,IF(X=Y,X,X+TWONUMB(X+1,Y)))
如果倒過來基於截止數6，則需要按Y-1的方式生成。定義TWONUMB2函式：
=LAMBDA(X,Y,IF(Y=X,Y,Y+TWONUMB2(X,Y-1)))
第三：至少有一個變量是遞增或者遞減並能指向終止條件。
小窩在測試中發現，不管有多少個變量，至少得有一個變量是遞增或遞減的，並能指向終止條件，否則出錯。
譬如TWONUMB2函式，包含兩個變量X和Y，起始數是2，截止數是6。變量迴圈部份不能寫成TWONUMB2(X,Y)、TWONUMB2(2,Y)、TWONUMB(X,Y-3)等。
TWONUMB2(X,Y)表示重復迴圈XY初始值，沒有遞增或遞減，無法指向終止條件；
TWONUMB2(2,Y)表示X變量迴圈值都等於2，Y變量等於初始值，也不可能達到Y=2的終止條件；
TWONUMB(X,Y-3)表示Y變量每次迴圈都減去3，其不可能達到Y=2的終止條件。
各位夥伴可以試試是否如此。
3. Excel當前最大遞迴迴圈次數
遞迴運算必須有終止條件，讓迴圈有限。不僅如此，Excel自身也限制了遞迴迴圈的次數。
沒有設定終止條件或者迴圈變量無法指向終止條件，遞迴函式會出現#NUM！錯誤！
除此外，即便正確設定了終止條件，如果迴圈次數超過了Excel遞迴迴圈最大次數的限制，也會提示#NUM！錯誤！
測試：
小窩準備了兩個變量X和Y。如果Y每減少1，X就增加1，終止條件是Y等於0。如此，Y是多少，就表示遞迴迴圈了多少次。設定X的初始值是0，大家一起來看看Y最大等於多少。
自訂最大迴圈數函式MAXXH:
=LAMBDA(X,Y,IF(Y=0,X,MAXXH(X+1,Y-1)
從下方動圖可以看出，當Y值大於5459，就出現了#NUM！錯誤。這說明Excel當前支持的最大遞迴迴圈次數是5459。
4. LAMBDA的經典遞迴運用
小窩例舉部份Excel中LAMBDA遞迴運算典型案例，方便大家更好地理解遞迴運算。
1）根據替換表替換字元
按替換表中的規則替換字元。
如果采用「尋找和替換」則需要進行5次操作；如果直接用SUBSTITUTE函式，公式會隨著替換字元數的增多巢狀多層SUBSTITUTE。因此，多字元替換更適合用自訂遞迴函式TIHUAN進行。
=LAMBDA(text,old,new,IF(old="",text,TIHUAN(SUBSTITUTE(text,OFFSET(old,0,),OFFSET(new,0,)),OFFSET(old,1,),OFFSET(new,1,))))
在B26中輸入公式=TIHUAN(A26,$D$26,$E$26)並向下填充即可。
說明：
公式含三個變量（參數），text代表原數據，old代表第一個原字元，new代表第一個新字元。
該公式沒有變量套用，只有變量迴圈：
TIHUAN(SUBSTITUTE(text,OFFSET(old,0,),OFFSET(new,0,)),OFFSET(old,1,),OFFSET(new,1,))
迴圈中的第一部份SUBSTITUTE(text,OFFSET(old,0,),OFFSET(new,0,))，交代text的生成方式——參照每個new變量替換每個old變量。
迴圈式中的第二部份OFFSET(old,1,)交代了old的生成方式——參照old的下一行字元；
迴圈式中的第三部份OFFSET(new,1,)交代了new的生方式——參照new的下一行字元。
以A26單元格為例：
當前的遞迴公式有一個小缺點： 替換表最後一個原字元下必須是空單元格。
小窩再分享一個不需要空單元格的遞迴函式TIHUAN2:
=LAMBDA(text,old,new,LET(X,CONCAT(old),Y,CONCAT(new),IF(LEFT(X,1)="",text,TIHUAN2(SUBSTITUTE(text,LEFT(X,1),LEFT(Y,1)),RIGHT(X,LEN(X)-1),RIGHT(Y,LEN(Y)-1)))))
2）反轉字串的排列
譬如將「我愛她」反轉後就變成「她愛我」。
小窩帶大家看看怎麽用遞迴完成字串的反轉排列。
自訂fanzhuan函式：
=LAMBDA(text,IF(text="","",RIGHT(text,1)&fanzhuan(LEFT(text,LEN(text)-1))))
然後在B34中輸入公式=fanzhuan(A34)並下拉填充即可。
說明：
變量套用中的參照運算式RIGHT(text,1)，表示迴圈生成的每個text都需要代入該式中運算；
結果符號「&」，表示將經過RIGHT提取後的結果連結成字串。
變量迴圈fanzhuan(LEFT(text,LEN(text)-1))，表示每次用LEFT減少一位提取生成新的text。
3.尋找累加銷售金額首次大於某值的月份
下面是2023年各月的銷售數量。現在需要查累計銷售第一次突破3500的月份。
如果采用傳統函式，可能要建立輔助列才能找到月份。用自訂遞迴函式，可以一步到位。
自訂leijia函式：
=LAMBDA(x,y,n,IF(y>3500,n,leijia(OFFSET(x,1,),y+OFFSET(x,1,),OFFSET(n,1,))))
在D41中輸入公式=leijia(B41,B41,A41)即可。
說明：
公式中用到3個變量。X變量代表首月銷量地址，Y變量代表首月銷量，N代表第一個月份。
公式沒有變量套用部份。
在變量迴圈中，OFFSET(x,1,)表示參照下一行銷量；y+OFFSET(x,1,)表示累加銷量；OFFSET(n,1,)表示參照下一行月份。
最後，當累計銷量Y>3500的時候，返回相應的N——月份。
這裏最難理解的部份是為何要用兩個變量X和Y分別來表達首月銷量。
X代表具體單元格值，而OFFSET(x,1,)中的X代表的是單元格地址，這是兩個不同的內容，不能在變量迴圈中寫成X+OFFSET(x,1,)，否則就出現#VALUE！錯誤。各位小白一定要註意這點！！這就是為何用兩個變量的原因。
4）提取所有字母
在【新函式LAMBDA的用法（上）——不用VBA做自訂函式】中小窩分享了用遞迴方法提取所有數位，這裏用遞迴法提取所有字母。
使用自訂tizimu函式：
=LAMBDA(x,IF(x="","",IF((LEFT(x,1)>="a")*(LEFT(x,1)<="z"),LEFT(x,1)," ")&tizimu(RIGHT(x,LEN(x)-1))))
說明：
公式只有一個變量X，代表要提取的原始數據。
變量迴圈tizimu(RIGHT(x,LEN(x)-1))，用RIGHT逐次遞減一位提取。
變量套用IF((LEFT(x,1)>="a")*(LEFT(x,1)<="z"),LEFT(x,1)," ")&，將每個變量值用LEFT提取一位並判斷是否屬於字母，是保留，不是則變成空格；最後將各結果用&連結成字串。
5）自訂一對多dvlookup尋找函式
很多人都像小窩一樣，喜歡Vlookup函式，遺憾的是它只返回第一個符合要求的值。小窩試著自訂dvlookup函式，實作一對多尋找。
自訂dvlookup函式：
=LAMBDA(x,y,z,IF(OR(x="",y=""),"",IF(OFFSET(y,0,0,1,1)=x,INDEX(y,1,z)," ")&dvlookup(x,OFFSET(y,1,0,1),z)))
說明：
自訂dvlookup函式用法與vlookup非常接近，有3個變量，X是尋找值，y是尋找區域，z是返回列。
6）元素組合
要求將等級、年級、班級組合成具體的班級名稱。
自訂zuhe函式：
=LAMBDA(arr,y,TOCOL(IF(y=1,"",zuhe(arr,y-1))&TOROW(INDEX(arr,,y),1)))
說明：
含arr 和y兩個變量。Arr變量表示要組合的區域，Y變量表示列數。
變量迴圈部份zuhe(arr,y-1)，arr不變，列數每次迴圈減去1。
變量套用部份TOCOL( &TOROW(INDEX(arr,,y),1))，將Arr中的最後一列先變成橫排再變成豎排，再與倒數第2列的橫排值用&相連，相連後豎排……
5. 總結
LAMBDA遞迴在Excel表格實際處理中可能用得不多。只有涉及到迴圈處理的地方才考慮用它。
LAMBDA遞迴運算在剛開始接觸的時候，不易理解。小窩也是摸索很久才有所得，如果文中有錯誤，歡迎指出。
LAMBDA還有6個伴侶函式，MAP、REDUCE、SCAN、MAKEARRAY、BYCOL、BYROW。結合這6大函式，LAMBDA在實際工作中運用更大。後續小窩將為大家一一介紹。
感謝您的點贊，分享！
Excel基礎技巧訓練營 ↓↓↓
點"閱讀原文"，學習更多的Excel視訊教程
