本篇詳細(xì)介紹Excel中LAMBDA的遞歸運(yùn)算，包括遞歸的含義和理解、遞歸公式組成、遞歸要點(diǎn)、遞歸條件、遞歸的經(jīng)典應(yīng)用。本篇中遞歸運(yùn)算，沒有借助LAMBDA的伴侶函數(shù)。是一篇小白都能看懂的LAMBDA遞歸教程，非常適合沒有編程基礎(chǔ)的小白掌握遞歸運(yùn)算。

在前面的文章《新函數(shù)LAMBDA的用法（上）——不用VBA做自定義函數(shù)》中，小窩為大家介紹了LAMBDA函數(shù)的基本用法以及第一個優(yōu)點(diǎn)——不用VBA實(shí)現(xiàn)可重復(fù)使用的自定義函數(shù)。今天則向大家介紹LAMBDA的第二個優(yōu)點(diǎn)，遞歸運(yùn)算。

1.何為遞歸？

遞歸來自數(shù)學(xué)與計(jì)算機(jī)編程，指的是函數(shù)在運(yùn)行中重復(fù)調(diào)用自身。

很抽象！下面用一個簡單的數(shù)字累加來說明。

例：求任意兩個給定的整數(shù)之間的遞增數(shù)列和，如2與6之間的數(shù)列2、3、4、5、6的和。

如果已經(jīng)有2、3、4、5、6數(shù)列，用SUM函數(shù)可以直接求和。

但現(xiàn)在只有起始數(shù)2和截止數(shù)6，如何求它們之間的數(shù)列和呢？

忘掉序列函數(shù)SEQUENCE。我們用數(shù)學(xué)的思路來看，以2開始，數(shù)列中下一個數(shù)總等于上一個數(shù)加1,直到等于截止數(shù)6為止：

假如用F(2,6)表示數(shù)列2到6的和，則運(yùn)算過程可以模擬如下：

圖中黃色的部分，屬于循環(huán)部分。初始值2，調(diào)用函數(shù)后生成新值3；新值3作為新的初始值，調(diào)用函數(shù)后生成新值4……直到生成值等于截止數(shù)。當(dāng)“遞數(shù)”過程——可以是遞增也可以是遞減，此處是遞增——結(jié)束后，就把所有數(shù)“歸攏”——代入算式——得出結(jié)果。

這就是遞歸：先循環(huán)生成要進(jìn)行計(jì)算的各個變量值，然后再來計(jì)算。

遞有兩層意思：

第一，通過遞增或者遞減的方式生成新的數(shù)。

可以是值的遞增或者遞減，如X+1、X+2、X-1、MOD(X,10)、X/10等；

也可以是行數(shù)的遞增或者遞減得到新的引用，如OFFSET(X,1,)、OFFSET(X,-1,)等；

也可以是字符串的位數(shù)的遞增遞減，如len(x)-1，len(x)+1)等。

第二，傳遞，把生成的數(shù)暫存并傳遞到下一次循環(huán)中。

歸，把所有循環(huán)生成的數(shù)從暫存的棧中取出代入算式進(jìn)行運(yùn)算得到結(jié)果。

設(shè)置起始數(shù)為變量X，截止數(shù)為Y，下一個數(shù)為X+1,直到X=Y為止，我們自定義函數(shù)TWONUMB完成遞歸運(yùn)算：

=LAMBDA(X,Y,IF(X=Y,X,X+TWONUMB(X+1,Y)))

公式中的X+TWONUMB(X+1,Y)部分對應(yīng)前方圖中黃色部分。函數(shù)運(yùn)行時，總是判斷X是否符合終止條件，不符合，就重復(fù)調(diào)用TWONUMB函數(shù)，由TWONUMB(X+1,Y)生成新的X值。

在B5中使用剛定義的TWONUM函數(shù)，即可求任意兩個整數(shù)之間的序列和。

2.Exel中遞歸函數(shù)組成和必備條件

1）遞歸函數(shù)組成

初步了解什么是遞歸后，再來了解在Excel中遞歸函數(shù)的組成部分。典型的遞歸函數(shù)包括5部分：

變量：就是函數(shù)參數(shù)，數(shù)量1~253之間。

終止條件：停止循環(huán)的條件。

終止值：當(dāng)循環(huán)停止時，變量等于多少。

運(yùn)算式：包含所有變量在內(nèi)的運(yùn)算式。通常有兩個部分，變量循環(huán)和變量應(yīng)用。其中變量循環(huán)式必須有。

變量循環(huán)：包含函數(shù)名稱、變量表達(dá)，按公式前方的變量順序交代所有變量的生成方式。

變量循環(huán)要點(diǎn)：

（1）函數(shù)名稱必須與定義框中名稱一致；

（2）有多少個變量就必須有多少個變量表達(dá)，其順序與變量一致。

變量表達(dá)解讀（以XY兩個變量為例）：

可以是計(jì)算式，如TWONUMB(X+1,Y)中的X+1，表示每次循環(huán)X變量都會加1；

可以是變量自身，如TWONUMB(X+1,Y)中的Y，表示每次循環(huán)Y變量保持初始值不變；

可以是一個常數(shù)，如TWONUMB(5,Y-1)中的5，表示除開初始值外，每次循環(huán)X變量都等于5；

可以引用另一個變量，如TWONUMB(Y,Y-1)中的Y，表示除初始值外，每次循環(huán)X都引用新的Y變量；

可以是多個變量的計(jì)算式，如TWONUMB(X+Y-1,Y)中的X+Y-1,表示每次循環(huán)X都等于X+Y-1。

變量應(yīng)用：交代循環(huán)生成的各個變量怎么計(jì)算——不是每個遞歸函數(shù)都必須有。

譬如前方TWONUMB函數(shù)，如果刪除變量應(yīng)用“X+”，則返回的就是循環(huán)終止時的X值而不是和。

=LAMBDA(X,Y,IF(X=Y,X,TWONUMB(X+1,Y)))

變量應(yīng)用包括變量引用和結(jié)果符號兩部分。

變量引用是一個表達(dá)式，確定循環(huán)生成的變量是否使用、怎么使用。譬如當(dāng)前引用就一個“X”，意思是不計(jì)算Y變量，只原樣引用每個X值。小白要注意：不是所有變量都會參與結(jié)果值的運(yùn)算！有些變量只是用來作為終止條件的，并不參加值的運(yùn)算。這種不參與值運(yùn)算的變量，就相當(dāng)于你跳繩的時候站在傍邊幫你計(jì)數(shù)喊停的人！

結(jié)果符號明確每個循環(huán)變量代入引用后的結(jié)果怎么歸攏。譬如當(dāng)前符號是“+”，就表示各變量代入引用后的結(jié)果需要累加。

最初接觸遞歸的小白可能與小窩一樣會在此處犯錯：以為加，就表示直接把得到的循環(huán)變量相加。

譬如，求下方單元格中各數(shù)據(jù)包含的數(shù)字和，如數(shù)據(jù)“78”，其數(shù)字和就是15。

此處至少有兩種遞歸求解方法。其中一種是通過余數(shù)來累加，自定義函數(shù)caishuhe:

=LAMBDA(X,IF(X=0,0,MOD(X,10)+CAISHUHE(TRUNC(X/10))))

小窩剛學(xué)LAMBDA遞歸時，認(rèn)為這里的遞歸循環(huán)是這樣的：

第1次循環(huán)：CAISHUHE(TRUNC(45618/10))= 4561

第2次循環(huán)：CAISHUHE(TRUNC(4561/10)=456

第3次循環(huán)：CAISHUHE(TRUNC(456/10)=45

第4次循環(huán)：CAISHUHE(TRUNC(45/10)=4

第5次循環(huán)：CAISHUHE(TRUNC(4/10)=0

結(jié)果= MOD(45618,10)+4561+456+45+4+0=5074

這可是大大的錯了！

并非直接加上各循環(huán)變量，而要把各循環(huán)變量代入變量引用中運(yùn)算后再相加。

結(jié)果=MOD(45618,10)+ MOD(4561,10) + MOD(456,10) + MOD(45,10) + MOD(4,10) + 0=24

2)遞歸運(yùn)算必備條件

遞歸運(yùn)算必須具備三個條件。

第一：存在終止條件。

循環(huán)必須有限，要設(shè)置終止條件讓其能停止。LAMBDA自定義遞歸函數(shù)時，使用IF函數(shù)表達(dá)終止條件。

實(shí)際上在Excel中，不但要有終止條件，還得滿足循環(huán)次數(shù)不大于5459。（下方會帶大家來測試這點(diǎn)）

第二：具體的變量循環(huán)方式。

在“遞”的過程中需要循環(huán)生成變量值。怎么生成？就必須在變量循環(huán)部分交代。

前方求兩個數(shù)之間的序列和的TWONUMB函數(shù)，是基于初始值2，采用X+1的方式不斷生成新的初始值，而截止值Y保持不變。

=LAMBDA(X,Y,IF(X=Y,X,X+TWONUMB(X+1,Y)))

如果倒過來基于截止數(shù)6，則需要按Y-1的方式生成。定義TWONUMB2函數(shù)：

=LAMBDA(X,Y,IF(Y=X,Y,Y+TWONUMB2(X,Y-1)))

第三：至少有一個變量是遞增或者遞減并能指向終止條件。

小窩在測試中發(fā)現(xiàn)，不管有多少個變量，至少得有一個變量是遞增或遞減的，并能指向終止條件，否則出錯。

譬如TWONUMB2函數(shù)，包含兩個變量X和Y，起始數(shù)是2，截止數(shù)是6。變量循環(huán)部分不能寫成TWONUMB2(X,Y)、TWONUMB2(2,Y)、TWONUMB(X,Y-3)等。

TWONUMB2(X,Y)表示重復(fù)循環(huán)XY初始值，沒有遞增或遞減，無法指向終止條件；

TWONUMB2(2,Y)表示X變量循環(huán)值都等于2，Y變量等于初始值，也不可能達(dá)到Y=2的終止條件；

TWONUMB(X,Y-3)表示Y變量每次循環(huán)都減去3，其不可能達(dá)到Y=2的終止條件。

各位伙伴可以試試是否如此。

3.Excel當(dāng)前最大遞歸循環(huán)次數(shù)

遞歸運(yùn)算必須有終止條件，讓循環(huán)有限。不僅如此，Excel自身也限制了遞歸循環(huán)的次數(shù)。

沒有設(shè)置終止條件或者循環(huán)變量無法指向終止條件，遞歸函數(shù)會出現(xiàn)#NUM！錯誤！

除此外，即便正確設(shè)置了終止條件，如果循環(huán)次數(shù)超過了Excel遞歸循環(huán)最大次數(shù)的限制，也會提示#NUM！錯誤！

測試：

小窩準(zhǔn)備了兩個變量X和Y。如果Y每減少1，X就增加1，終止條件是Y等于0。如此，Y是多少，就表示遞歸循環(huán)了多少次。設(shè)置X的初始值是0，大家一起來看看Y最大等于多少。

自定義最大循環(huán)數(shù)函數(shù)MAXXH:

=LAMBDA(X,Y,IF(Y=0,X,MAXXH(X+1,Y-1)

從下方動圖可以看出，當(dāng)Y值大于5459，就出現(xiàn)了#NUM！錯誤。這說明Excel當(dāng)前支持的最大遞歸循環(huán)次數(shù)是5459。

4.LAMBDA的經(jīng)典遞歸運(yùn)用

小窩例舉部分Excel中LAMBDA遞歸運(yùn)算典型案例，方便大家更好地理解遞歸運(yùn)算。

1）根據(jù)替換表替換字符

按替換表中的規(guī)則替換字符。

如果采用“查找和替換”則需要進(jìn)行5次操作；如果直接用SUBSTITUTE函數(shù)，公式會隨著替換字符數(shù)的增多嵌套多層SUBSTITUTE。因此，多字符替換更適合用自定義遞歸函數(shù)TIHUAN進(jìn)行。

=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)并向下填充即可。

說明：

公式含三個變量（參數(shù)），text代表原數(shù)據(jù)，old代表首個原字符，new代表首個新字符。

該公式?jīng)]有變量應(yīng)用，只有變量循環(huán)：

TIHUAN(SUBSTITUTE(text,OFFSET(old,0,),OFFSET(new,0,)),OFFSET(old,1,),OFFSET(new,1,))

循環(huán)中的第一部分SUBSTITUTE(text,OFFSET(old,0,),OFFSET(new,0,))，交代text的生成方式——引用每個new變量替換每個old變量。

循環(huán)式中的第二部分OFFSET(old,1,)交代了old的生成方式——引用old的下一行字符；

循環(huán)式中的第三部分OFFSET(new,1,)交代了new的生方式——引用new的下一行字符。

以A26單元格為例：

當(dāng)前的遞歸公式有一個小缺點(diǎn)：替換表最后一個原字符下必須是空單元格。

小窩再分享一個不需要空單元格的遞歸函數(shù)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）反轉(zhuǎn)字符串的排列

譬如將“我愛她”反轉(zhuǎn)后就變成“她愛我”。

小窩帶大家看看怎么用遞歸完成字符串的反轉(zhuǎn)排列。

自定義fanzhuan函數(shù)：

=LAMBDA(text,IF(text="","",RIGHT(text,1)&fanzhuan(LEFT(text,LEN(text)-1))))

然后在B34中輸入公式=fanzhuan(A34)并下拉填充即可。

說明：

變量應(yīng)用中的引用表達(dá)式RIGHT(text,1)，表示循環(huán)生成的每個text都需要代入該式中運(yùn)算；

結(jié)果符號“&”，表示將經(jīng)過RIGHT提取后的結(jié)果鏈接成字符串。

變量循環(huán)fanzhuan(LEFT(text,LEN(text)-1))，表示每次用LEFT減少一位提取生成新的text。

3.查找累加銷售金額首次大于某值的月份

下面是2023年各月的銷售數(shù)量?，F(xiàn)在需要查累計(jì)銷售第一次突破3500的月份。

如果采用傳統(tǒng)函數(shù)，可能要建立輔助列才能找到月份。用自定義遞歸函數(shù)，可以一步到位。

自定義leijia函數(shù)：

=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代表首個月份。

公式?jīng)]有變量應(yīng)用部分。

在變量循環(huán)中，OFFSET(x,1,)表示引用下一行銷量；y+OFFSET(x,1,)表示累加銷量；OFFSET(n,1,)表示引用下一行月份。

最后，當(dāng)累計(jì)銷量Y>3500的時候，返回相應(yīng)的N——月份。

這里最難理解的部分是為何要用兩個變量X和Y分別來表達(dá)首月銷量。

X代表具體單元格值，而OFFSET(x,1,)中的X代表的是單元格地址，這是兩個不同的屬性，不能在變量循環(huán)中寫成X+OFFSET(x,1,)，否則就出現(xiàn)#VALUE！錯誤。各位小白一定要注意這點(diǎn)?。∵@就是為何用兩個變量的原因。

4）提取所有字母

在《新函數(shù)LAMBDA的用法（上）——不用VBA做自定義函數(shù)》中小窩分享了用遞歸方法提取所有數(shù)字，這里用遞歸法提取所有字母。

使用自定義tizimu函數(shù)：

=LAMBDA(x,IF(x="","",IF((LEFT(x,1)>="a")*(LEFT(x,1)<="z"),LEFT(x,1)," ")&tizimu(RIGHT(x,LEN(x)-1))))

說明：

公式只有一個變量X，代表要提取的原始數(shù)據(jù)。

變量循環(huán)tizimu(RIGHT(x,LEN(x)-1))，用RIGHT逐次遞減一位提取。

變量應(yīng)用IF((LEFT(x,1)>="a")*(LEFT(x,1)<="z"),LEFT(x,1)," ")&，將每個變量值用LEFT提取一位并判斷是否屬于字母，是保留，不是則變成空格；最后將各結(jié)果用&鏈接成字符串。

5）自定義一對多dvlookup查找函數(shù)

很多人都像小窩一樣，喜歡Vlookup函數(shù)，遺憾的是它只返回第一個符合要求的值。小窩試著自定義dvlookup函數(shù)，實(shí)現(xiàn)一對多查找。

自定義dvlookup函數(shù)：

=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函數(shù)用法與vlookup非常接近，有3個變量，X是查找值，y是查找區(qū)域，z是返回列。

6）元素組合

要求將等級、年級、班級組合成具體的班級名稱。

自定義zuhe函數(shù)：

=LAMBDA(arr,y,TOCOL(IF(y=1,"",zuhe(arr,y-1))&TOROW(INDEX(arr,,y),1)))

說明：

含arr 和y兩個變量。Arr變量表示要組合的區(qū)域，Y變量表示列數(shù)。

變量循環(huán)部分zuhe(arr,y-1)，arr不變，列數(shù)每次循環(huán)減去1。

變量應(yīng)用部分TOCOL(    &TOROW(INDEX(arr,,y),1))，將Arr中的最后一列先變成橫排再變成豎排，再與倒數(shù)第2列的橫排值用&相連，相連后豎排……

5.總結(jié)

LAMBDA遞歸在Excel表格實(shí)際處理中可能用得不多。只有涉及到循環(huán)處理的地方才考慮用它。

LAMBDA遞歸運(yùn)算在剛開始接觸的時候，不易理解。小窩也是摸索很久才有所得，如果文中有錯誤，歡迎指出。

LAMBDA還有6個伴侶函數(shù)，MAP、REDUCE、SCAN、MAKEARRAY、BYCOL、BYROW。結(jié)合這6大函數(shù)，LAMBDA在實(shí)際工作中運(yùn)用更大。后續(xù)小窩將為大家一一介紹。

感謝您的點(diǎn)贊，分享！

本文配套的練習(xí)課件請?zhí)砑涌头⑿?span>buluowojiaoyu索取。

做Excel高手，快速提升工作效率，部落窩教育Excel精品好課任你選擇！

掃下方二維碼關(guān)注公眾號，可隨時隨地學(xué)習(xí)Excel：

相關(guān)推薦：

新函數(shù)LAMBDA的用法（上）——不用VBA做自定義函數(shù)

單串?dāng)?shù)字和字母的萬能提取公式

VLOOKUP經(jīng)典用法12例

動態(tài)數(shù)組函數(shù)TOCOL和TOROW

版權(quán)申明：

本文作者小窩；部落窩教育享有稿件專有使用權(quán)。若需轉(zhuǎn)載請聯(lián)系部落窩教育。